数据库使用QueryRunner模拟封装

模拟使用QueryRunner查询数据库：

首先创建一张表：

create table `user` (
	`id` int (11),
	`name` varchar (60),
	`birthday` date ,
	`sex` varchar (30),
	`home_address` varchar (150)
); 
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('1','张三','1990-02-21','男','北京');
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('2','李四','1995-07-14','女','天津');
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('3','王五','1996-03-08','男','河南');

创建一个封装对象User：

package rk.news.entity;

import java.util.Date;

/*
create table `user` (
	`id` int (11),
	`name` varchar (60),
	`birthday` date ,
	`sex` varchar (30),
	`home_address` varchar (150)
);
 */
public class User {
    private int id;
    private String name;
    private Date birthday;
    private String sex;
    private String home_address; //homeAddress

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getHome_address() {
        return home_address;
    }

    public void setHome_address(String home_address) {
        this.home_address = home_address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", home_address='" + home_address + '\'' +
                '}';
    }
}

连接数据库的配置dbcp-config.properties:

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/news_db
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

使用DBCPUtils去连接：

package rk.news.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @Author rk
 * @Date 2018/12/1 14:59
 * @Description:
 *      dbcp和p0都是常用的数据库的连接池
 **/
public class DBCPUtils {

    private static DataSource ds;
    private DBCPUtils(){}
    public static String url;
    public static String username;
    public static String password;
    static {
        try {
            Properties properties = new Properties();
            String path = "dbcp-config.properties";
            InputStream in = DBCPUtils.class.getClassLoader().getResourceAsStream(path);
            properties.load(in);
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            ds = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource(){
        return ds;
    }

    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void release(Connection con, Statement st, ResultSet rs){
        try {
                if(rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (con != null) {
                        con.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

使用QueryRunner去测试查询：

package rk.utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import rk.news.utils.DBCPUtils;
import java.sql.SQLException;

/**
 * @Author rk
 * @Date 2018/12/1 14:28
 * @Description:
 **/
public class TestDB {
    public static void main(String[] args) throws SQLException {
        QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
        String sql = "select count(1) from book";
        Long count = qr.query(sql, new ScalarHandler<Long>());
        System.out.println(count);
    }
}

下面模拟QueryRunner去实现：

首先使用传统的jdbc去查询：

package rk.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author rk
 * @Date 2018/12/4 16:56
 * @Description:
 *      模拟dbutil框架
 *      使用传统的jdbc的方式，将数据库表user中的数据查询出来，还可以按照条件查询
 **/
public class TestDBUtil1 {

    private Connection connection;
    private PreparedStatement ps;
    private ResultSet rs;

    @Before
    public void setUp() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
    }

    //条件查询
    @Test
    public void testQuery1() throws SQLException {
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        ps = connection.prepareStatement(sql);
        ps.setObject(1,1);
        rs = ps.executeQuery();
        User user = new User();
        while(rs.next()){
            int id = rs.getInt(1);
            String name = rs.getString("name");
            Date birthday = rs.getDate("birthday");
            String sex = rs.getString("sex");
            String home_address = rs.getString("home_address");
            user.setId(id);
            user.setHome_address(home_address);
            user.setName(name);
            user.setSex(sex);
            user.setBirthday(birthday);
        }
        System.out.println(user);
    }

    //查询全部
    @Test
    public void testQuery2() throws SQLException {
        String sql = "select id, name, birthday, sex, home_address from user ";
        ps = connection.prepareStatement(sql);
        rs = ps.executeQuery();
        List<User> list = new ArrayList<>();
        while(rs.next()){
            User user = new User();
            int id = rs.getInt(1);
            String name = rs.getString("name");
            Date birthday = rs.getDate("birthday");
            String sex = rs.getString("sex");
            String home_address = rs.getString("home_address");
            user.setId(id);
            user.setHome_address(home_address);
            user.setName(name);
            user.setSex(sex);
            user.setBirthday(birthday);
            list.add(user);
        }
        for(User user : list){
            System.out.println(user);
        }
    }

    //使用QueryRunner查询
    @Test
    public void testQuery3() throws SQLException {
        QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
        List<User> list = qr.query("select id, name, birthday,  sex,  home_address from user", new BeanListHandler<User>(User.class));
        for(User user : list){
            System.out.println(user);
        }
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
        System.out.println(user);

    }


    @After
    public void cleanUp() throws SQLException {
        if (rs != null){
            rs.close();
        }
        if (ps != null){
            ps.close();
        }
        if(connection != null){
            connection.close();
        }
    }
    
}

中级封装（通过反射）：

package rk.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author rk
 * @Date 2018/12/4 16:53
 * @Description:
 **/
public class TestDBUtil2 {
    private Connection connection;
    private PreparedStatement ps;
    private ResultSet rs;

    @Before
    public void setUp() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
    }

    //条件查询
    @Test
    public void testQuery1() throws SQLException, IllegalAccessException, InstantiationException, IntrospectionException, InvocationTargetException {
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        ps = connection.prepareStatement(sql);
        ps.setObject(1,1);
        rs = ps.executeQuery();

        Class clazz = User.class;
        Object obj = null;
        while(rs.next()){//要想自动封装结果集，只能用反射
            obj = clazz.newInstance();
            //通过反射的方式拿到obj中的字段或者setter
//            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++){
                String colName = rsmd.getColumnName(i);
                Object colValue = rs.getObject(colName);
                PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
//                Method rM = pd.getReadMethod();
                Method wM = pd.getWriteMethod();
//                System.out.println(rM);
//                System.out.println(wM);
                wM.invoke(obj,colValue);
            }
        }
        System.out.println(obj);
    }
    
    //查询全部
    @Test
    public void testQuery2() throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, IntrospectionException {
        String sql = "select id, name, birthday, sex, home_address from user";
        ps = connection.prepareStatement(sql);
        rs = ps.executeQuery();

        Class clazz = User.class;
        List list = new ArrayList();
        while(rs.next()){//要想自动封装结果集，只能用反射
            Object obj = clazz.newInstance();
            //通过反射的方式拿到obj中的字段或者setter
//            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++){
                String colName = rsmd.getColumnName(i);
                Object colValue = rs.getObject(colName);
                PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
//                Method rM = pd.getReadMethod();
                Method wM = pd.getWriteMethod();
//                System.out.println(rM);
//                System.out.println(wM);
                wM.invoke(obj,colValue);
            }
            list.add(obj);
        }
        for (Object obj : list){
            System.out.println(obj);
        }
    }
    
    //使用QueryRunner查询
    @Test
    public void testQuery3() throws SQLException {
        QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
        List<User> list = qr.query("select id, name, birthday,  sex,  home_address from user", new BeanListHandler<User>(User.class));
        for(User user : list){
            System.out.println(user);
        }
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
        System.out.println(user);

    }


    @After
    public void cleanUp() throws SQLException {
        if (rs != null){
            rs.close();
        }
        if (ps != null){
            ps.close();
        }
        if(connection != null){
            connection.close();
        }
    }

}

终极封装(通过自定义的MyQueryRunner)：

package rk.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.db.MyQueryRunner;
import rk.news.db.handler.impl.MyBeanHandler;
import rk.news.db.handler.impl.MyBeanListHandler;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;

import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.List;

/**
 * @Author rk
 * @Date 2018/12/4 16:53
 * @Description:
 **/
public class TestDBUtil3 {
    private Connection connection;
    private PreparedStatement ps;
    private ResultSet rs;

    @Before
    public void setUp() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
    }

    //初级版：要封装的对象，和结果集
    @Test
    public void testQuery1() throws SQLException, IllegalAccessException, InstantiationException, IntrospectionException, InvocationTargetException {
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        ps = connection.prepareStatement(sql);
        ps.setObject(1,1);
        rs = ps.executeQuery();

        MyQueryRunner mqr = new MyQueryRunner();
        User user = mqr.query( User.class, rs);
        System.out.println(user);

    }

    //使用QueryRunner查询
    @Test
    public void testQuery2() throws SQLException {
        QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
        List<User> list = qr.query("select id, name, birthday,  sex,  home_address from user", new BeanListHandler<User>(User.class));
        for(User user : list){
            System.out.println(user);
        }
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
        System.out.println(user);
    }

    //中级版：查询语句和封装对象和参数
    @Test
    public void testQuery3() {

        MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        User user = mqr.query( sql, User.class,1);
        System.out.println(user);

        User user1 = mqr.query( sql, new MyBeanHandler<User> (User.class) ,1);
        System.out.println(user1);

    }

    //条件查询最终版：查询语句和自定义类的泛型(封装对象)参数，返回一个对象
    @Test
    public void testQuery4() {

        MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
        String sql = "select id, name, birthday, sex, home_address from user where id = ?";
        User user = mqr.query( sql, User.class,1);
        System.out.println(user);

        User user1 = mqr.query( sql, new MyBeanHandler<User> (User.class) ,1);
        System.out.println(user1);

    }
    
    //全部查询最终版：查询语句和自定义类的泛型(封装对象)参数，返回是一个集合
    @Test
    public void testQuery5() {

        MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
        String sql = "select id, name, birthday, sex, home_address from user ";
        //不能指定接收的类型，最终封装
        List<User>  list = mqr.query(sql, new MyBeanListHandler<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }

    }
    
    @After
    public void cleanUp() throws SQLException {
        if (rs != null){
            rs.close();
        }
        if (ps != null){
            ps.close();
        }
        if(connection != null){
            connection.close();
        }
    }
    
}

MyQueryRunner类：

package rk.news.db;

import rk.news.db.handler.MyResultHandler;

import javax.sql.DataSource;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.*;

/**
 * @Author rk
 * @Date 2018/12/4 21:10
 * @Description:
 **/
public class MyQueryRunner {

    private Connection connection;

    public MyQueryRunner(){

    }
    public MyQueryRunner(DataSource dataSource) {
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //最终版：查询语句和自定义类的泛型(封装对象)参数
    public  <T> T query(String sql, MyResultHandler<T> mrh, Object ... params){
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++){
                ps.setObject(i+1,params[i]);
            }
            ResultSet rs = ps.executeQuery();
            return mrh.handle(rs);
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;


    }

    //中级版：查询语句和封装对象和参数
    public  <T> T query(String sql, Class<T> t, Object ... params){
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++){
                ps.setObject(i+1,params[i]);
            }
            ResultSet rs = ps.executeQuery();
            Class clazz = t;
            Object obj = null;
            while(rs.next()){//要想自动封装结果集，只能用反射
                obj = clazz.newInstance();
                //通过反射的方式拿到obj中的字段或者setter
                //            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++){
                    String colName = rsmd.getColumnName(i);
                    Object colValue = rs.getObject(colName);
                    PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
                    //                Method rM = pd.getReadMethod();
                    Method wM = pd.getWriteMethod();
                    //                System.out.println(rM);
                    //                System.out.println(wM);
                    wM.invoke(obj,colValue);
                }
            }
            return (T) obj;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }


    //初级版：要封装的对象，和结果集
    public  <T> T query(Class<T> t, ResultSet rs){
        try {
            Class clazz = t;
            Object obj = null;
            while(rs.next()){//要想自动封装结果集，只能用反射
                obj = clazz.newInstance();
                //通过反射的方式拿到obj中的字段或者setter
    //            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++){
                    String colName = rsmd.getColumnName(i);
                    Object colValue = rs.getObject(colName);
                    PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
    //                Method rM = pd.getReadMethod();
                    Method wM = pd.getWriteMethod();
    //                System.out.println(rM);
    //                System.out.println(wM);
                    wM.invoke(obj,colValue);
                }
            }
            return (T) obj;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;


    }
    
}

用来保存查询出来的结果：ResultHandler接口

package rk.news.db.handler;

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

/**
 * @Author rk
 * @Date 2018/12/5 11:39
 * @Description:
 **/
public interface MyResultHandler<T> {

    T handle(ResultSet rs) throws SQLException;
}

用来保存查询出来一个对象的结果：MyBeanHandler

package rk.news.db.handler.impl;

import rk.news.db.handler.MyResultHandler;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * @Author rk
 * @Date 2018/12/5 11:41
 * @Description:
 **/
public class MyBeanHandler<T> implements MyResultHandler<T> {

    private Class<T> clazz;
    public MyBeanHandler(Class<T> clazz){
        this.clazz = clazz;
    }

    @Override
    public T handle(ResultSet rs) {
        try {
            T obj = null;
            while (rs.next()) {//要想自动封装结果集，只能用反射
                obj = clazz.newInstance();
                //通过反射的方式拿到obj中的字段或者setter
                //            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String colName = rsmd.getColumnName(i);
                    Object colValue = rs.getObject(colName);
                    PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
                    //                Method rM = pd.getReadMethod();
                    Method wM = pd.getWriteMethod();
                    //                System.out.println(rM);
                    //                System.out.println(wM);
                    wM.invoke(obj, colValue);
                }
            }
            return obj;
        } catch (InstantiationException e ) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;

    }
}

用来保存查询出来一个对象集合的结果：MyBeanListHandler

package rk.news.db.handler.impl;

import rk.news.db.handler.MyResultHandler;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author rk
 * @Date 2018/12/5 11:54
 * @Description:
 **/
public class MyBeanListHandler<T>  implements MyResultHandler<List<T>> {
    private Class<T> clazz;

    public MyBeanListHandler(Class<T> clazz) {
        this.clazz = clazz;
    }

    @Override
    public List<T> handle(ResultSet rs) {
        try{
            List<T> list = new ArrayList<>();
            while(rs.next()){//要想自动封装结果集，只能用反射
                T obj = clazz.newInstance();
                //通过反射的方式拿到obj中的字段或者setter
//            clazz.getField() 没有办法直接获取对应的字段，因为不知道对象的具体内容，只有一个clazz
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++){
                    String colName = rsmd.getColumnName(i);
                    Object colValue = rs.getObject(colName);
                    PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
                    Method wM = pd.getWriteMethod();
                    wM.invoke(obj,colValue);
                }
                list.add(obj);
            }
            return list;
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;

    }
}

注意：在进行封装时，泛型的使用不是太熟悉的，还是要多了解了解！！！