DBUtils.md

title	date	tags
DBUtils	2019-05-30 10:28:41 -0700	Java JDBC

学习笔记 : DBUtils工具

简介 : 为了更加简单地使用JDBC,Apache组织提供了一个工具类库commons-dbutils,它是操作数据库的一个组件,实现了对JDBC的简单封装,可以在不影响性能的情况下极大地简化JDBC的编码工作量.

API 介绍

简介 : commons-dbutils的核心是两个类为DbUtils,QueryRunner,和一个接口ResultSetHandler.

1. org.apache.commons.dbutils.DbUtils : 该类主要为如何关闭数据库连接,装载JDBC驱动程序之类的常规工作提供静态方法.
2. org.apache.commons.dbutils.QueryRunner : 该类简化了执行SQL语句的代码,它与ResultSetHandler组合在一起就能完成大部分的数据库操作,大大减少编码量.
3. org.apache.commons.dbutils.ResultSetHandler : 该接口用于处理ResultSet结果集,它可以将结果集中的数据转换为不同的形式.

CURE 案例

1. 数据表信息

-- auto-generated definition by Intellij IDEA
create table user
(
    id       int auto_increment primary key,
    name     varchar(15) null,
    password varchar(20) null
)

2. C3P0配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置: 在没有指定配置时默认使用该配置创建c3p0数据源对象 -->
    <default-config>
        <property name="user">xxxxxx</property>
        <property name="password">xxxxxx</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">
            jdbc:mysql://LOCALHOST/test?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true
        </property>
        <property name="checkoutTimeout">30000</property>
        <property name="initialPoolSize">5</property>
        <property name="maxIdleTime">600</property>
        <property name="maxPoolSize">10</property>
        <property name="minPoolSize">1</property>
        <property name="maxStatements">200</property>
    </default-config>

    <!-- 自定义配置 -->
    <named-config name="yu">
        <property name="user">xxxxxx</property>
        <property name="password">xxxxxx</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">
            jdbc:mysql://LOCALHOST/test?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true
        </property>
        <property name="initiaPoolSize">5</property>
        <property name="maxPoolSize">15</property>
    </named-config>
</c3p0-config>

3. 数据源工具类

package pers.huangyuhui.dbutils.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;

/**
 * @project: dbutils
 * @package: pers.huangyuhui.dbutils.utils
 * @description: 创建数据源
 * @author: HuangYuhui
 * @date: 5/31/2019-7:00 PM
 * @version: 1.0
 */
public class C3P0Utils {

    private static DataSource dataSource;

    //创建数据源
    static {
        dataSource = new ComboPooledDataSource("yu");
    }

    //获取数据源
    public static DataSource getDataSource() {
        return dataSource;
    }

}

4. Java Bean : 存储用户信息

package pers.huangyuhui.dbutils.bean;

/**
 * @project: dbutils
 * @package: pers.huangyuhui.dbutils.bean
 * @description: 用户信息
 * @author: HuangYuhui
 * @date: 5/31/2019-7:13 PM
 * @version: 1.0
 */
public class User {

    private Integer id;
    private String name;
    private String password;

    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

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

5. DAO层 : 封装数据库的基本操作

package pers.huangyuhui.dbutils.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import pers.huangyuhui.dbutils.bean.User;
import pers.huangyuhui.dbutils.utils.C3P0Utils;

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

/**
 * @project: dbutils
 * @package: pers.huangyuhui.dbutils
 * @description: 数据库的基本操作
 * @author: HuangYuhui
 * @date: 5/31/2019-7:07 PM
 * @version: 1.0
 */
public class DBUtilsDao {

    //获取数据源
    private static DataSource dataSource = C3P0Utils.getDataSource();
    //创建queryRunner对象
    private static QueryRunner queryRunner = new QueryRunner(dataSource);
    //sql
    private String SQL_SELECT = "select id,name,password from user where id = ?";
    private String SQL_UPDATE = "update user set name=?,password=? where id=?";
    private String SQL_INSERT = "insert into user(name,password) values(?,?)";
    private String SQL_SELECTALL = "select id,name,password from user";
    private String SQL_DELETE = "delete from user where id=?";

    // TODO: 6/8/2019 查询所有用户信息
    public List selectAll() throws SQLException {
        //获取用户列表
        List<User> list = queryRunner.query(SQL_SELECTALL, new BeanListHandler<User>(User.class));
        return list;
    }

    // TODO: 6/8/2019 查询单个用户信息
    public User select(int id) throws SQLException {
        //获取用户信息
        User user = queryRunner.query(SQL_SELECT, new BeanHandler<>(User.class), new Object[]{id});
        return user;
    }

    // TODO: 6/8/2019 添加用户信息
    public boolean insert(User user) throws SQLException {
        //判断是否添加成功
        int num = queryRunner.update(SQL_INSERT, new Object[]{user.getName(), user.getPassword()});
        if (num > 0) {
            return true;
        }
        return false;
    }

    // TODO: 6/8/2019 修改用户信息
    public boolean update(User user) throws SQLException {
        int num = queryRunner.update(SQL_UPDATE, new Object[]{user.getName(), user.getPassword(), user.getId()});
        if (num > 0) {
            return true;
        }
        return false;
    }

    // TODO: 6/8/2019 删除用户信息
    public boolean delete(int id) throws SQLException {
        int num = queryRunner.update(SQL_DELETE, id);
        if (num > 0) {
            return true;
        } else {
            return false;
        }
    }
}

6. 测试类

package pers.huangyuhui.dbutils.test;

import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;
import pers.huangyuhui.dbutils.bean.User;
import pers.huangyuhui.dbutils.dao.DBUtilsDao;

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

/**
 * @project: dbutils
 * @description: 测试数据库的基本操作
 * @author: HuangYuhui
 * @date: 5/31/2019-7:45 PM
 * @version: 1.0
 */
public class DBUtilsDaoTest {

    private static User user;
    private static DBUtilsDao dbUtilsDao;

    @BeforeClass
    public static void init() {
        user = new User();
        dbUtilsDao = new DBUtilsDao();
    }

    @Ignore
    @Test
    // TODO: 6/8/2019 测试添加用户操作
    public void insertTest() throws SQLException {
        user.setName("YUbuntu0109");
        user.setPassword("MyPassword");
        if (dbUtilsDao.insert(user)) {
            System.out.println("success to insert a user information !");
        } else {
            System.out.println("fail to insert a new user information !");
        }
    }


    @Ignore
    @Test
    // TODO: 6/8/2019 测试修改用户信息操作 
    public void updateTest() throws SQLException {
        user.setId(1);
        user.setName("YUbuntu0109-1");
        user.setPassword("MyPassword-1");
        if (dbUtilsDao.update(user)) {
            System.out.println("success to update the user information !");
        } else {
            System.out.println("fail to update the user information !");
        }
    }

    @Ignore
    @Test
    // TODO: 6/8/2019 测试删除用户操作
    public void deleteTest() throws SQLException {
        if (dbUtilsDao.delete(1)) {
            System.out.println("success to delete the user information !");
        } else {
            System.out.println("fail to delete the user information !");
        }
    }

    @Ignore
    @Test
    // TODO: 6/8/2019 测试查询单个用户信息操作
    public void selectTest() throws SQLException {
        System.out.println(dbUtilsDao.select(1));
    }

    @Ignore
    @Test
    // TODO: 6/8/2019 测试查询所有用户信息操作
    public void selectAllTest() throws SQLException {
        List users = dbUtilsDao.selectAll();
        System.out.println(users);
    }
}