欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库开发四:JDBC数据库开发进阶三(commons-dbutils原理)

程序员文章站 2022-05-28 11:35:25
...

自定义封装泛型工具类

package dbutils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 封装泛型工具类
 * Created by kevin on 2020/4/2.
 */
public class QR<T> {
    private DataSource dataSource;

    public QR() {
        super();
    }

    public QR(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 增删改
     * @param sql
     * @param parms
     * @return
     */
    public int update(String sql,Object...parms){
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = dataSource.getConnection();
            pstmt = connection.prepareStatement(sql);
            initParams(pstmt,parms);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
           throw new RuntimeException(e);
        } finally {
            try {
                if(pstmt!=null){
                    pstmt.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void initParams(PreparedStatement pstmt,Object...parms){
        try {
            for(int i=0;i<parms.length;i++){
                pstmt.setObject(i+1,parms[i]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 查询单个对象
     * @param sql
     * @param rh
     * @param parms
     * @return
     */
    public T query(String sql,RsHandler<T> rh,Object...parms){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            connection = dataSource.getConnection();
            pstmt = connection.prepareStatement(sql);
            initParams(pstmt,parms);
            rs = pstmt.executeQuery();
            return rh.handle(rs);
        } catch (SQLException e) {
           throw new RuntimeException(e);
        } finally {
            try {
                if(pstmt!=null){
                    pstmt.close();
                }
                if(rs!=null){
                    rs.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

/**
 * 处理对象映射
 * @param <T>
 */
interface RsHandler<T>{
    public T handle(ResultSet rs) throws SQLException;
}

自定义调用

package dbutils;

import org.junit.Test;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 仿commons-dbutils
 * Created by kevin on 2020/4/2.
 */
public class Demo2 {
    @Test
    public void fun1(){
//        Stu stu = new Stu();
//        stu.setNumber("0002");
//        stu.setName("王五");
//        stu.setAge(30);
//        stu.setGender("男");
//        stu.setPassword("111111");
//        add(stu);

        Stu stu2 = load("0002");
        System.out.println(stu2.toString());
    }

    public int add(Stu stu){
        QR<Stu> stuQr = new QR<Stu>(JdbcUtils.getDataSource());
        String sql="insert into stu values(?,?,?,?,?)";
        Object[] params = {stu.getNumber(),stu.getName(),stu.getAge(),stu.getGender(),stu.getPassword()};
        return stuQr.update(sql,params);
    }

    public Stu load(String number){
        QR<Stu> stuQr = new QR<Stu>(JdbcUtils.getDataSource());
        String sql="select * from stu where number=?";
        Object[] params = {number};
        RsHandler<Stu> rh = new RsHandler<Stu>() {
            @Override
            public Stu handle(ResultSet rs) throws SQLException{
                if(!rs.next())  return null;
                Stu stu = new Stu();
                stu.setNumber(rs.getString("number"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setGender(rs.getString("gender"));
                stu.setPassword(rs.getString("password"));
                return stu;
            }
        };
        return stuQr.query(sql,rh,params);
    }
}

使用commons-dbutils

package dbutils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * commons dbutils
 * Created by kevin on 2020/4/2.
 */
public class Demo3 {
    @Test
    public void fun1(){
       QueryRunner qr= new QueryRunner(JdbcUtils.getDataSource());
        String sql="insert into stu values(?,?,?,?,?)";
        Object[] params = {"0003","李四",30,"男","111111"};
        try {
            qr.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void fun2(){
        QueryRunner qr= new QueryRunner(JdbcUtils.getDataSource());
        String sql="select * from stu where number=?";
        Object[] params = {"0003"};
        try {
//            ResultSetHandler<Stu> handler = new ResultSetHandler<Stu>() {
//                @Override
//                public Stu handle(ResultSet rs) throws SQLException {
//                    if(!rs.next())  return null;
//                    Stu stu = new Stu();
//                    stu.setNumber(rs.getString("number"));
//                    stu.setName(rs.getString("name"));
//                    stu.setAge(rs.getInt("age"));
//                    stu.setGender(rs.getString("gender"));
//                    stu.setPassword(rs.getString("password"));
//                    return stu;
//                }
//            };//该部分代码commons-dbutils已经有5个实现类了可以直接调用
            //BeanHandler类是ResultSetHandler实现类,BeanHandler类使用需知(数据库列名必须与实体类名称相同)
            BeanHandler<Stu> beanHandler = new BeanHandler<Stu>(Stu.class);
            Stu stu = qr.query(sql,beanHandler,params);
            System.out.println(stu.toString());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

相关标签: 数据库开发