看懂commons-dbutils源码及dbutils使用

IoneFine

于 2020-02-25 16:43:23 发布

阅读量835

点赞数

分类专栏： # Java 文章标签： java jdbc dbutils

本文链接：https://blog.csdn.net/jiduochou963/article/details/104499783

版权

Java 专栏收录该内容

46 篇文章 4 订阅

订阅专栏

一、自己写dbutils

1.1 Maven配置文件：pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>dbutils</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
    </dependencies>
</project>

1.2 IAccountDao.java及AccountDaoImpl.java

路径：src/main/java/top/onefine/dao/IAccountDao.java

package top.onefine.dao;

import top.onefine.domain.Account;

import java.util.List;

/**
 * 账户的持久层接口
 */
public interface IAccountDao {

    void save(Account account);
    void update(Account account);
    void delete(Integer accountId);
    Account findById(Integer accountId);
    List<Account> findAll();
}

路径：src/main/java/top/onefine/dao/impl/AccountDaoImpl.java

package top.onefine.dao.impl;

import top.onefine.dao.IAccountDao;
import top.onefine.dbassit.DBAssit;
import top.onefine.domain.Account;
import top.onefine.handler.impl.BeanHandler;
import top.onefine.handler.impl.BeanListHandler;
import top.onefine.utils.C3P0Util;

import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao {

    private DBAssit dbAssit = new DBAssit(C3P0Util.getDataSource());

    public void save(Account account) {
        String sql = "insert into account (name, money) values (?, ?)";
        dbAssit.update(sql, account.getName(), account.getMoney());
    }

    public void update(Account account) {
        String sql = "update account set name = ?, money = ? where id = ?";
        dbAssit.update(sql, account.getName(), account.getMoney(), account.getId());
    }

    public void delete(Integer accountId) {
        String sql = "delete from account where id = ?";
        dbAssit.update(sql, accountId);
    }

    public Account findById(Integer accountId) {
        String sql = "select * from account where id = ?";
        return (Account)dbAssit.query(sql, new BeanHandler<Account>(Account.class), accountId);
    }

    public List<Account> findAll() {
        String sql = "select * from account";
        return (List<Account>) dbAssit.query(sql, new BeanListHandler(Account.class));
    }
}

1.3 Account.java

路径：src/main/java/top/onefine/domain/Account.java

package top.onefine.domain;

import java.io.Serializable;

/**
 * 账户的实体类
 */
public class Account implements Serializable {

    private Integer id;
    private String name;
    private Float money;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Float getMoney() {
        return money;
    }

    public void setMoney(Float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

1.4 DBAssit.java

路径：src/main/java/top/onefine/dbassit/DBAssit.java

package top.onefine.dbassit;

import top.onefine.handler.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.*;

/**
 * 封装的工具类
 */
public class DBAssit {
    private DataSource dataSource;

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

    /**
     * 增删改的方法
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            // 1. 得到连接
            conn = dataSource.getConnection();  // 从数据源中获取连接
            // 2. 使用连接和参数的sql语句创建预处理对象
            pstm = conn.prepareStatement(sql);
            // 3. 得到sql语句参数的源信息（如有几个参数，都有什么类型等等）
            ParameterMetaData pmd = pstm.getParameterMetaData();
            // 4. 判断语句中参数的个数和方法参数params的个数是否一致，不一致肯定没法执行
            int parameterCount = pmd.getParameterCount();  // 参数的个数（即?的个数）
            if (parameterCount > 0)
                if (params == null)
                    throw new NullPointerException("缺失sql语句执行所需的必要参数");
                else if (params.length != parameterCount)
                    throw new RuntimeException("sql语句所需的参数个数与传入的参数个数不一致，sql语句无法执行");
            // 5. 给sql语句的参数赋值
            for (int i = 0; i <parameterCount; i++)
                pstm.setObject(i+1, params[i]);  // parameterCount取值从1开始，而params取值从0开始
//            // 6. 执行语句
//            int res = pstm.executeUpdate();
//            // 7. 返回执行结果
//            return res;
            return pstm.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(conn, pstm, null);
        }  // 快捷键 ctrl+alt+t
    }

    public Object query(String sql, ResultSetHandler rsh, Object... params) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            // 1. 得到连接
            conn = dataSource.getConnection();  // 从数据源中获取连接
            // 2. 使用连接和参数的sql语句创建预处理对象
            pstm = conn.prepareStatement(sql);
            // 3. 得到sql语句参数的源信息（如有几个参数，都有什么类型等等）
            ParameterMetaData pmd = pstm.getParameterMetaData();
            // 4. 判断语句中参数的个数和方法参数params的个数是否一致，不一致肯定没法执行
            int parameterCount = pmd.getParameterCount();  // 参数的个数（即问号的个数）
            if (parameterCount > 0)
                if (params == null)
                    throw new NullPointerException("缺失sql语句执行所需的必要参数");
                else if (params.length != parameterCount)
                    throw new RuntimeException("sql语句所需的参数个数与传入的参数个数不一致，sql语句无法执行");
            // 5. 给sql语句的参数赋值
            for (int i = 0; i <parameterCount; i++)
                pstm.setObject(i+1, params[i]);  // parameterCount取值从1开始，而params取值从0开始
            // 6. 执行语句
            rs = pstm.executeQuery();
            // 7. 返回执行结果
            return rsh.handle(rs);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(conn, pstm, rs);
        }  // 快捷键 ctrl+alt+t
    }

    private void release(Connection conn, PreparedStatement pstm, ResultSet rs) {
        try {
            // 注意释放的顺序
            if (rs != null)
                rs.close();

            if (pstm != null)
                pstm.close();

            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1.5 C3P0数据源配置（MySQL8）

路径：src/main/resources/c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/study_eesy?serverTimezone=UTC</property>
        <!--
                扩展：MySQL jdbc 6.0 版本以上必须配置“serverTimezone”参数
                UTC代表的是全球标准时间
                若我们使用的时间是北京时区也就是东八区，领先UTC八个小时。url的时区使用中国标准时间。也是就serverTimezone=Asia/Shanghai
        -->
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
</c3p0-config>

路径：src/main/java/top/onefine/utils/C3P0Util.java：

package top.onefine.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * C3P0
 */
public class C3P0Util {

    private static ComboPooledDataSource ds = new ComboPooledDataSource();

    public static DataSource getDataSource(){
        return ds;
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 测试
     * @param args 参数
     */
    public static void main(String[] args) {
        System.out.println(getDataSource());
    }
}

1.6 结果集封装接口及实现类demo

路径：src/main/java/top/onefine/handler/ResultSetHandler.java

package top.onefine.handler;

import java.sql.ResultSet;

/**
 * 结果集 封装的接口
 * 该怎么封装结果集是自己的事情
 */
public interface ResultSetHandler <T> {

    /**
     * 结果集封装的方法
     * @param rs
     */
    Object handle(ResultSet rs);
}

路径：src/main/java/top/onefine/handler/impl/BeanHandler.java

package top.onefine.handler.impl;

import top.onefine.handler.ResultSetHandler;

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

/**
 * 结果集封装的具体实现类demo
 * 此类实现的是把一个结果集rs的内容封装到一个指定的实体类对象中
 *
 * 使用要求：
 *          实体类中的属性必须和表中的列名一致（sql语句查询出来的列名 一致，查询的时候可以取别名）
 */
public class BeanHandler<T> implements ResultSetHandler <T> {

    private Class<T> domainClass;

    /**
     * 带参数的构造函数，防止domainClass对象缺失
     * @param domainClass 字节码
     */
    public BeanHandler(Class<T> domainClass) {
        this.domainClass = domainClass;
    }

    /**
     * 把rs的内容封装到domainClass所表示的类中
     * @param rs 结果集
     * @return Object对象
     */
    public T handle(ResultSet rs) {
        try {
            // 1. 创建一个实体类对象
            T bean = domainClass.newInstance();
            // 2. 判读是否有结果集
            if (rs.next()) {
                // 3. 得到结果集rs中所有的列名
                // 想要得到列名，得先得到结果集的源信息
                ResultSetMetaData rsmd = rs.getMetaData();
                // 得到源信息之后，还要得到有多少列
                int columnCount = rsmd.getColumnCount();
                // 遍历列数
                for (int i = 0; i < columnCount; i++) {
                    // 得到每列的名称
                    String columnName = rsmd.getColumnName(i+1);
                    // 列名其实就是实体类的属性名称，于是就可以使用列名得到实体类中属性的描述器
                    // 属性描述器即实体类中定义的私有类成员和它的get以及set方法
                    PropertyDescriptor pd = new PropertyDescriptor(columnName, domainClass);
                    // 获取属性的写入方法（即set方法）
                    Method method = pd.getWriteMethod();
                    // 获取当前列名所对应的值
                    Object columnValue = rs.getObject(columnName);
                    // 通过执行写方法把得到的值给属性赋上
                    method.invoke(bean, columnValue);
                }
            }
            // 4. 返回
            return bean;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

路径：src/main/java/top/onefine/handler/impl/BeanListHandler.java

package top.onefine.handler.impl;

import top.onefine.handler.ResultSetHandler;

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

public class BeanListHandler <T> implements ResultSetHandler <T> {

    private Class<T> domainClass;

    public BeanListHandler(Class<T> domainClass) {
        this.domainClass = domainClass;
    }

    /**
     * 把rs的内容封装到domainClass所表示的类中
     * @param rs 结果集
     * @return List
     */
    public List<T> handle(ResultSet rs) {
        try {
            List<T> list = new ArrayList<T>();
            T bean;  // 对象复用
            //2.判断是否有结果集
            while(rs.next()){
                //1.创建一个实体类对象
                bean = domainClass.newInstance();
                //3.得到结果集rs中所有的列名
                //要想得到列名，得先得到结果集的源信息
                ResultSetMetaData rsmd = rs.getMetaData();
                //得到源信息之后，还要得到有多少列
                int columnCount = rsmd.getColumnCount();
                //遍历列数
                for(int i=1; i<=columnCount; i++){
                    //得到每列的名称
                    String columnName = rsmd.getColumnName(i);
                    //列名其实就是实体类的属性名称，于是就可以使用列名得到实体类中属性的描述器
                    PropertyDescriptor pd = new PropertyDescriptor(columnName,domainClass);//实体类中定义的私有类成员和它的get以及set方法
                    //获取属性的写入方法(set方法)
                    Method method = pd.getWriteMethod();
                    //获取当前列名所对应的值
                    Object columnValue = rs.getObject(columnName);
                    //通过执行写方法把得到的值给属性赋上
                    method.invoke(bean,columnValue);
                }
                //把封装好的对象加入到集合中
                list.add(bean);
            }
            //4.返回
            return list;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}

1.7 测试

路径：src/test/java/top/onefine/test/Demo.java

package top.onefine.test;

import top.onefine.dao.impl.AccountDaoImpl;
import top.onefine.domain.Account;

import java.util.List;

/**
 * 测试
 */
public class Demo {
    public static void main(String[] args) {
//        Account account = new Account();
//        account.setName("One fine");
//        account.setMoney(1234f);
//        AccountDaoImpl accountDao = new AccountDaoImpl();
//        accountDao.save(account);

//        AccountDaoImpl accountDao = new AccountDaoImpl();
//        Account account = accountDao.findById(3);
//        System.out.println(account);

        AccountDaoImpl accountDao = new AccountDaoImpl();
        List<Account> all = accountDao.findAll();
        System.out.println(all);
    }
}

二、dbutils使用

2.1 不变的文件

路径：src/main/java/top/onefine/domain/Account.java 1.3
路径：src/main/resources/c3p0-config.xml 1.5
路径：src/main/java/top/onefine/utils/C3P0Util.java 1.5
路径：src/test/java/top/onefine/test/Demo.java 1.7

2.2 Maven配置文件：pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>top.onefine</groupId>
    <artifactId>dbutils2</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/c3p0/c3p0 -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>

    </dependencies>
</project>

2.3 IAccountDao.java及AccountDaoImpl.java

路径：src/main/java/top/onefine/dao/IAccountDao.java

package top.onefine.dao;

import top.onefine.domain.Account;

import java.util.List;

/**
 * 账户的持久层接口
 */
public interface IAccountDao {

    void save(Account account);
    void update(Account account);
    void delete(Integer accountId);
    Account findById(Integer accountId);
    List<Account> findAll();
}

路径：src/main/java/top/onefine/dao/impl/AccountDaoImpl.java

package top.onefine.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import top.onefine.dao.IAccountDao;
import top.onefine.domain.Account;
import top.onefine.utils.C3P0Util;

import java.sql.SQLException;
import java.util.List;

/**
 * 账户的持久层操作
 */
public class AccountDaoImpl implements IAccountDao {

    private QueryRunner runner = new QueryRunner(C3P0Util.getDataSource());

    public void save(Account account) {
        String sql = "insert into account (name, money) values (?, ?)";

        try {
            runner.update(sql, account.getName(), account.getMoney());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void update(Account account) {
        String sql = "update account set name = ?, money = ? where id = ?";
        try {
            runner.update(sql, account.getName(), account.getMoney(), account.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void delete(Integer accountId) {
        String sql = "delete from account where id = ?";
        try {
            runner.update(sql, accountId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Account findById(Integer accountId) {
        String sql = "select * from account where id = ?";
        try {
            return (Account)runner.query(sql, new BeanHandler<Account>(Account.class), accountId);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public List<Account> findAll() {
        String sql = "select * from account";
        try {
            return (List<Account>) runner.query(sql, new BeanListHandler<Account>(Account.class));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}