apache-commons-dbutils + Druid + JDBC 简单实现 CRUD

- [一、类库介绍](https://cloud.tencent.com/developer/audit/support-plan/10680784#_1)
    - [JDBC](https://cloud.tencent.com/developer/audit/support-plan/10680784#JDBC_4)
    - [DBUtils](https://cloud.tencent.com/developer/audit/support-plan/10680784#DBUtils_7)
    - [Druid](https://cloud.tencent.com/developer/audit/support-plan/10680784#Druid_10)
- [二、功能分析](https://cloud.tencent.com/developer/audit/support-plan/10680784#_13)
- [三、代码实现](https://cloud.tencent.com/developer/audit/support-plan/10680784#_19)
    - [建表](https://cloud.tencent.com/developer/audit/support-plan/10680784#_22)
    - [配置类代码](https://cloud.tencent.com/developer/audit/support-plan/10680784#_39)
    - [封装 Druid](https://cloud.tencent.com/developer/audit/support-plan/10680784#_Druid_199)
    - [封装 DBUtils](https://cloud.tencent.com/developer/audit/support-plan/10680784#_DBUtils_302)
    - [封装 Dao](https://cloud.tencent.com/developer/audit/support-plan/10680784#_Dao_487)
    - [测试](https://cloud.tencent.com/developer/audit/support-plan/10680784#_741)

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) DEFAULT NULL,
  `password` varchar(25) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<dependencies>
        <!--Junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>

        <!--servlet api-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>

        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
            <scope>compile</scope>
        </dependency>

        <!--Druid 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

        <!--apache dbUtils : 简化 JDBC 操作-->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>

        <!--lombok : 自动生成 get set 方法-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.8</version>
        </dependency>
    </dependencies>

    <build>
        <finalName>jdbc-demo</finalName>
        <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
            <plugins>
                <plugin>
                    <artifactId>maven-clean-plugin</artifactId>
                    <version>3.1.0</version>
                </plugin>
                <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.22.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-war-plugin</artifactId>
                    <version>3.2.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-install-plugin</artifactId>
                    <version>2.5.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-deploy-plugin</artifactId>
                    <version>2.8.2</version>
                </plugin>
            </plugins>
        </pluginManagement>
        <!--maven插件-->
        <plugins>
            <!--jdk编译插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>utf-8</encoding>
                </configuration>
            </plugin>
            <!--tomcat插件-->
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <!-- tomcat7的插件， 不同tomcat版本这个也不一样 -->
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.1</version>
                <configuration>
                    <!-- 通过maven tomcat7:run运行项目时，访问项目的端口号 -->
                    <port>8080</port>
                    <!-- 项目访问路径  本例：localhost:9090,  如果配置的aa， 则访问路径为localhost:9090/aa-->
                    <path>/</path>
                </configuration>
            </plugin>

        </plugins>
    </build>

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///school?characterEncoding=utf-8
username = root
password = root
initialSize=5
maxActive=10
maxWait=3000

<web-app>
    <display-name>Archetype Created Web Application</display-name>
    <!-- 配置 Druid 监控信息显示页面 -->
    <servlet>
        <servlet-name>DruidStatView</servlet-name>
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
        <init-param>
            <!-- 允许清空统计数据 -->
            <param-name>resetEnable</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <!-- 用户名 -->
            <param-name>loginUsername</param-name>
            <param-value>root</param-value>
        </init-param>
        <init-param>
            <!-- 密码 -->
            <param-name>loginPassword</param-name>
            <param-value>root</param-value>
        </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>
</web-app>

package top.wsuo.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;
import java.sql.*;

/**
 * Druid数据库连接池工具类
 *
 * @Author shuo wang
 * @Date 2020/4/29 0029 14:46
 * @Version 1.0
 */
public class DruidUtils {

    // 数据源
    private static DataSource dataSource;

    // 注册驱动,使用静态代码块,类一旦加载就会执行
    static {
        try {
            // 获取类对象,读取配置文件
            InputStream resource = DruidUtils
                    .class
                    .getClassLoader()
                    .getResourceAsStream("druid.properties");
            Properties properties = new Properties();
            properties.load(resource);
            dataSource = DruidDataSourceFactory
                    .createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    /**
     * 获取连接池对象供commons使用
     *
     * @return 返回Druid连接池对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

    /**
     * 释放2个资源
     *
     * @param conn      连接对象
     * @param statement statement对象
     */
    public static void close(Connection conn,
                             PreparedStatement statement) {
        assert conn != null;
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        assert statement != null;
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 释放3个资源
     *
     * @param conn      连接对象
     * @param statement statement对象
     * @param resultSet 返回结果集
     */
    public static void close(Connection conn,
                             PreparedStatement statement,
                             ResultSet resultSet) {

        close(conn, statement);
        assert resultSet != null;
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package top.wsuo.dao;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import top.wsuo.util.DruidUtils;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 定义一个用来被继承的对数据库进行基本操作的 Dao
 * 这里为什么要定义成一个抽象类呢 ?
 * - 对于一个父类，如果它的某个方法在父类中实现出来没有任何意义，
 * - 必须根据子类的实际需求来进行不同的实现,就要定义为抽象类
 * - 像我们的BaseDao,我们需要它根据自己的业务需求灵活的变化
 * - 比如有时候需要查询User类,有时候需要Student类
 *
 * @param <T>
 */
public abstract class BaseDao<T> {

    /*
     * 注意这里可以使用 QueryRunner 的两个构造方法来获取该对象
     *  - 一个是无参构造: 默认自己管理事务，因为框架没有连接池无法获得数据库连接
     *  - 另外一个是有参构造: 传入一个连接池对象,
     *    数据库事务交给DBUtils框架进行管理 ---- 默认情况下每条SQL语句单独一个事务。
     *
     * */

    // 使用 Druid 的连接池
    private QueryRunner queryRunner =
            new QueryRunner(DruidUtils.getDataSource());

    // 定义一个变量来接收泛型的类型
    private Class<T> type;

    // 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
    public BaseDao() {

        // 获取子类的类型
        Class clazz = this.getClass();

        // 获取父类的类型，ParameterizedType表示的是带泛型的类型，
        // getGenericSuperclass()用来获取当前类的父类的类型
        ParameterizedType parameterizedType =
                (ParameterizedType) clazz.getGenericSuperclass();

        // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型，
        // 这个方法会返回一个Type的数组
        Type[] types =
                parameterizedType.getActualTypeArguments();

        // 获取具体的泛型的类型
        // noinspection unchecked
        this.type = (Class<T>) types[0];
    }

    /**
     * 手动提交事务
     * 通用的-增删改-操作, 但是一般只用于修改
     * 这里使用的是用户自定义的 Connection 对象,这样的话用户可以自己控制事务
     * 其他的查询方法不提供 Connection 对象,因为查询不涉及事务的操作.
     */
    public int updateCommit(Connection conn, String sql, Object... params) {
        int count = 0;
        try {
            count = queryRunner.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


    /**
     * 数据的增删改
     * 默认一个SQL语句为一个事务,数据库事务交给DBUtils框架进行管理
     *
     * @param sql    SQL语句
     * @param params 执行参数
     * @return 返回受影响的行数
     */
    public int update(String sql, Object... params) {
        int count = 0;
        try {
            count = queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 自动提交事务的查询方法
     *
     * @param sql    SQL 语句
     * @param params 查询参数
     * @return 返回泛型
     */
    public T queryBean(String sql, Object... params) {
        T t = null;
        try {
            t = queryRunner
                    .query(sql, new BeanHandler<>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t;
    }

    /**
     * 自动提交事务的查询所有方法
     *
     * @param sql    SQL 语句
     * @param params 查询参数
     * @return 返回泛型集合
     */
    public List<T> queryBeanList(String sql, Object... params) {
        List<T> list = null;
        try {
            list = queryRunner
                    .query(sql, new BeanListHandler<>(type), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 自动提交事务的值查询
     *
     * @param sql    SQL 语句
     * @param params 参数
     * @return 返回数值如 count(*) sum(total) ...
     */
    public Object queryValue(String sql, Object... params) {
        Object count = null;
        try {
            count = queryRunner
                    .query(sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 处理事务提交与回滚
     *
     * @param connection 连接的对象
     */
    public void commit(Connection connection) {
        try {
            DbUtils.commitAndClose(connection);
        } catch (SQLException e) {
            System.out.println("事务提交失败!");
            DbUtils.rollbackAndCloseQuietly(connection);
            e.printStackTrace();
        }
    }
}

package top.wsuo.pojo;

import java.util.List;

/**
 * 分页类
 *
 * @Author shuo wang
 * @Date 2020/4/30 0030 21:07
 * @Version 1.0
 */
public class Page<T> {
    private static final int PAGE_CURR = 1; // 当前页码
    private static final int PAGE_SIZE = 5; // 每页的数量

    private List<T> list;   // 实体类
    private Integer current;    // 当前页码
    private Integer size;   // 每页的条数
    private int totalRecord;    // 总记录数

    public Page(Integer current, Integer size) {
        this.current = current;
        this.size = size;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public Integer getCurrent() {
        return this.current;
    }

    public void setCurrent(Integer current) {
        this.current = current;
    }

    public Integer getSize() {
        return this.size;
    }

    public void setSize(Integer size) {
        this.size = size;
    }

    public int getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
    }

    @Override
    public String toString() {
        return "Page{\n" +
                "\tlist=" + list +
                ",\n \t当前页码=" + current +
                ",\n \t每页的数量=" + size +
                ",\n \t总记录数=" + totalRecord +
                "\n}";
    }
}

package top.wsuo.dao;

import top.wsuo.pojo.Page;
import top.wsuo.pojo.User;

import java.util.List;

/**
 * 用户接口类
 *
 * @author shuo wang
 * @version 1.0
 * @date 2020/4/30 0030 20:27
 */
public interface UserDao {
    /**
     * 根据User对象中的用户名和密码从数据库中获取一条记录
     */
    User queryUser(User user);

    /**
     * 根据User对象中的条件从数据库中获取多条记录
     */
    List<User> queryAll();

    /**
     * 分页从数据库中获取多条记录
     */
    Page<User> queryPageList(Page<User> page);

    /**
     * 根据User对象中的用户名从数据库中获取一条记录
     */
    boolean checkUsername(User user);

    /**
     * 向数据库中插入User对象
     */
    int saveUser(User user);

    /**
     * 向数据库中修改User对象
     */
    int updateUser(User user);

    /**
     * 向数据库中删除User对象
     */
    int deleteUser(int id);
}

package top.wsuo.dao.impl;

import top.wsuo.dao.BaseDao;
import top.wsuo.dao.UserDao;
import top.wsuo.pojo.Page;
import top.wsuo.pojo.User;
import top.wsuo.util.DruidUtils;

import java.sql.Connection;
import java.util.List;

/**
 * 用户操作的DAO实现类
 *
 * @Author shuo wang
 * @Date 2020/4/30 0030 21:02
 * @Version 1.0
 */
public class UserDaoImpl extends BaseDao<User> implements UserDao {

    /**
     * 根据姓名和密码查询用户,登陆时用
     *
     * @param user 用户
     * @return 返回用户
     */
    @Override
    public User queryUser(User user) {
        String sql = "select * from user where username = ? and password = ?";
        return queryBean(sql, user.getUsername(), user.getPassword());
    }

    /**
     * 查询所有
     *
     * @return 返回集合
     */
    @Override
    public List<User> queryAll() {
        String sql = "select * from user";
        return queryBeanList(sql);
    }

    /**
     * 分页查询
     *
     * @param page 分页
     * @return 返回分页对象
     */
    @Override
    public Page<User> queryPageList(Page<User> page) {
        //  where username like concat('%', ? '%')
        String sql2 = "select * from user limit ? offset ?";
        int size = page.getSize();
        int curr = page.getCurrent();
        List<User> userList = queryBeanList(sql2, size, (curr - 1) * size);
        page.setTotalRecord(queryAll().size());
        page.setList(userList);
        return page;
    }

    /**
     * 检查用户名是否存在
     *
     * @param user 用户对象
     * @return 返回布尔值
     */
    @Override
    public boolean checkUsername(User user) {
        return queryUser(user) != null;
    }

    /**
     * 保存用户到数据库
     *
     * @param user 用户
     * @return 返回受影响的行数
     */
    @Override
    public int saveUser(User user) {
        String sql = "insert into user(username, password, address, phone) " +
                "values(?,?,?,?);";
        return update(sql, user.getUsername(), user.getPassword(),
                user.getAddress(), user.getPhone());
    }


    /**
     * 处理事务的修改方法
     *
     * @param user 实体类
     * @return 受影响的行数
     */
    @Override
    public int updateUser(User user) {
        Connection connection = DruidUtils.getConnect();
        int i = updateCommit(connection, "update user set username = ?, password = ?, " +
                        "address = ?, phone = ?", user.getUsername(),
                user.getPassword(), user.getAddress(), user.getPhone());
//        updateCommit(connection, "语句二", user);
        commit(connection); // 提交事务
        return i;
    }

    /**
     * 根据id删除
     *
     * @return 返回受影响的行数
     */
    @Override
    public int deleteUser(int id) {
        String sql = "delete from user where id = ?";
        return update(sql, id);
    }
}

package top.wsuo.dao;

import org.junit.Test;
import top.wsuo.dao.impl.UserDaoImpl;
import top.wsuo.pojo.Page;
import top.wsuo.pojo.User;

/**
 * 测试类
 *
 * @Author shuo wang
 * @Date 2020/4/30 0030 21:53
 * @Version 1.0
 */
public class DaoTest {

    private UserDaoImpl dao = new UserDaoImpl();

    /**
     * 保存数据测试方法
     */
    @Test
    public void saveTest() {
        User user = new User("test1",
                "123",
                "东北",
                "123456789");
        int i = dao.saveUser(user);
        System.out.println("插入成功: " + i);
    }

    /**
     * 查询用户
     */
    @Test
    public void queryUser() {
        User user = new User("test2",
                "123",
                "东北",
                "123456789");
        User user1 = dao.queryUser(user);
        System.out.println(user1);
    }

    /**
     * 分页查询测试方法
     */
    @Test
    public void pageTest() {
        Page<User> page = new Page<>(2,3);
        Page<User> pageList = dao.queryPageList(page);
        System.out.println(pageList);
    }

    /**
     * 修改测试方法
     */
    @Test
    public void updateTest() {
        User user = new User(
                "test6",
                "456",
                "吉林",
                "34234234"
        );
    }

    /**
     * 删除测试方法
     */
    @Test
    public void deleteTest() {
        int i = dao.deleteUser(1);
        System.out.println("删除成功: " + i);
    }
}

Page{
	list=[User(id=5, username=test5, password=123, address=东北, phone=123456789), User(id=6, username=test6, password=123, address=东北, phone=123456789), User(id=7, username=test1, password=123, address=东北, phone=123456789)],
 	当前页码=2,
 	每页的数量=3,
 	总记录数=7
}