Commons-dbutils介绍及使用

Commons-dbutils介绍及使用

Commons-dbutils使用说明

commons-dbutils的官网地址: Apache组织机构旗下的开源的工具类库
https://commons.apache.org/proper/commons-dbutils/
就是被用来完成jdbc操作,简化了jdbc操作的一种书写格式(查询多条记录,将这些记录封装List中)
针对原生Jdbc的建议封装

完成jdbc操作
使用步骤:
1)需要导入核心的jar包 commons-dbtils.jar
mysql驱动jar包
连接池–druid的jar包
junit单元测试:核心包junit.jar以及依赖包

2)有关commons-dbtils.jar 核心接口以及核心类有哪些
使用的执行对象----操作数据库：
org.apache.commons.dbutils.QueryRunner 里面封装就是PreparedStatement
两个通用的方法
query(xxx,ResultSetHandler rsh):针对dql语句来完成查询操作
update(xxx,xx):针对dml域操作:insert into,update,delete from …
核心接口:ResultSetHandler:针对结果集的处理

很多很多实现类
BeanListHandler:可以将查询的多条记录封装到List集合中

JDBC方式控制事务

package com.qf.jdbc_transaction_01;

import com.qf.utils.DruidJdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author Kuke
 * @date 2022/5/20 11:33
 * 现在需要使用JDBC方式控制事务:
 * 事务: 针对关系型数据库的一种机制
 *  	就是在执行业务操作过程中,同时执行多个sql或者多张表(添加/删除/修改),这些sql语句要么同时执行成功;
 *  	要么同时执行失败;
 *
 *  	张三给赵又廷转账---sql全部都是使用参数化的sql语句
 *
 *  	转账的方法(也可以自定义的方法)
 *  	public void transfer(String toName,String reveiveName,int money)
 */
public class JDBCTransactionDemo {
    public static void main(String[] args) {
        //声明Connection类型变量
        Connection connection = null ;
        PreparedStatement ps = null ;
        PreparedStatement ps2 = null ;
        try {
            //没有通过jdbc管理事务-----当同时执行多条sql,中间如果存在异常,第一条件语句成功了,第二条数据失败;
            // 转账业务失败----->应该在jdbc操作转账的业务中加入事务操作!

            //使用Jdbc控制事务--->通过获取连接对象之后,加入事务的方法
            //通过工具类获取连接对象
             connection = DruidJdbcUtils.getConnection();
            //开启事务---->利用Connection的功能void setAutoCommit(boolean autoCommit):默认自动提交
            //参数为false:禁用自动提交,需要手动提交事务
            connection.setAutoCommit(false) ;

            //准备sql---参数化sql
            String sql = "update account set balance = balance - ? where id = ?" ;
            //获取预编译对象
            ps = connection.prepareStatement(sql);
            //参数赋值
            ps.setInt(1,500) ;
            ps.setInt(2,1) ;

            String sql2 = "update account set balance = balance + ? where id = ?" ;
            //获取预编译对象
            ps2 = connection.prepareStatement(sql2);
            ps2.setInt(1,500) ;
            ps2.setInt(2,2);

            //分别执行更新操作
            int count = ps.executeUpdate();

            int i = 10/0 ;

            int count2 = ps2.executeUpdate();
            System.out.println(count+"---"+count2);


            //提交事务: 如果没有问题,提交事务---数据在能永久更新
            //Connection对象的方法:void commit()

        } catch (Exception e) {
            System.out.println("执行catch语句");
            //出现异常,程序执行catch语句
            //事务回滚
            //连接对象的方法void rollback():回滚到默认在更新之前的操作
            try {
                connection.rollback() ;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

            e.printStackTrace();
            /*System.out.println("异常抛出了");*/
        }finally {
            //任何情况下finally中的代码一定会执行的,除非 只执行这个语句之前,jvm退出了 System.exit(0) ;
            try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //释放资源
            DruidJdbcUtils.close(ps,connection);
            DruidJdbcUtils.close(ps2,connection);

        }
    }
}

练习：JDBC增删改查

WorkerDao：接口类

import com.qf.entity.Worker;

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

/**
 * @Author 豆三岁
 * @Date 2022-05-19 19:42
 **/
public interface WorkerDao {
    //根据学生id修改某个员工的信息方法 ：public void updateWorker(Worker worker) ;
    void updateWorker(Worker worker) throws SQLException;

    //根据id查询某个员工信息，public Worker findWorkerById(int id);
    Worker findWorkerById(int id) throws SQLException;

    //添加员工信息的功能：public void add(Worker worker) ;
    void add(Worker worker) throws SQLException;

    //查询所有员工信息的功能，List<Employee> findAll();
    List<Worker> findAll() throws SQLException;

    //根据id删除某个员工信息，public Worker deleteWorkerById(int id);
    int deleteWorkerById(int id) throws SQLException;

    //查询worker表中的总记录数
    int count() throws SQLException;

}

Worker：实体类

/**
 * @Author 豆三岁
 * @Date 2022-05-19 19:40
 **/
public class Worker {
    private int id;
    private String name;
    private int age;
    private String gender;
    private String address;
    private Double salary;

    public Worker() {
    }

    public Worker(int id, String name, int age, String gender, String address, Double salary) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.address = address;
        this.salary = salary;
    }

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    @Override
    public String toString() {
        return "WorkerDao{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                ", address='" + address + '\'' +
                ", salary=" + salary +
                '}';
    }
}

WorkerDaoImpl：接口实现类

import com.qf.Dao.WorkerDao;
import com.qf.Utils.DruidJdbcUtils;
import com.qf.entity.Worker;
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 java.sql.SQLException;
import java.util.List;

/**
 * @Author 豆三岁
 * @Date 2022-05-20 14:49
 **/
public class WorkerDaoImpl implements WorkerDao {

    /**
     * 根据学生id修改某个学生的信息方法
     * @param worker
     */
    @Override
    public void updateWorker(Worker worker) throws SQLException {
        //执行对象
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());
        //准备sql语句
        String sql= "update worker set name = ?,age = ?,gender = ?,address = ?,salary = ?  where id = ? ";
        //更新
        int i = queryRunner.update(sql,
                worker.getName(),
                worker.getAge(),
                worker.getGender(),
                worker.getAddress(),
                worker.getSalary(),
                worker.getId()
        );
        if (i==1){
            System.out.println("恭喜你，修改成功！");
        } else {
            System.out.println("修改失败！！！！");
        }
    }

    /**
     * 查询员工信息
     * @param id  根据id查询worker表中数据
     * @return  返回结果
     * @throws SQLException
     */
    @Override
    public Worker findWorkerById(int id) throws SQLException {
        //执行对象
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());
        //准备sql语句
        String sql = "select * from worker where id = ?";
        //执行方法
        Worker worker = queryRunner.query(sql, new BeanHandler<Worker>(Worker.class),id);
        //返回结果
        return worker;
    }

    /**
     * 添加员工信息
     * @param worker
     * @throws SQLException
     */
    @Override
    public void add(Worker worker) throws SQLException {
        //执行对象
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());
        //准备sql语句
        String sql = "insert into worker(id,name,age,gender,address,salary) values(?,?,?,?,?,?)";
        //执行方法
        int i = queryRunner.update(sql,
                worker.getId(),
                worker.getName(),
                worker.getAge(),
                worker.getGender(),
                worker.getAddress(),
                worker.getSalary()
        );
        if (i == 1){
            System.out.println("添加成功！！！");
        } else{
            System.out.println("添加失败！请重新添加！！！");
        }

    }

    /**
     * 查询表中所有数据
     * @return
     * @throws SQLException
     */
    @Override
    public List<Worker> findAll() throws SQLException {
        //commons-dbutils的使用步骤
        //1)导入dbutilsjar包
        //2)创建数据库的执行对象
        //QueryRunner
        //public QueryRunner(DataSource ds)
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());

        //3)准备sql语句---参数化的sql---查询全部员工数据
        String sql = "select * from worker";

        //4)执行它的通用方法
        //query(String sql,ResultSetHandler rsh)
        //参数1:sql语句
        //参数2:结果集的处理对象:接口
        //最终目的将所有的记录封装到List<Employee>中
        //public BeanListHandler<T>(Class<T> type) :参数是存储当前类型的字节码文件对象
        List<Worker> query = queryRunner.query(sql, new BeanListHandler<Worker>(Worker.class));

        return query;
    }

    /**
     * 删除员工数据
     * @param id  根据员工ID删除
     * @return  返回影响行数
     * @throws SQLException
     */
    @Override
    public int deleteWorkerById(int id) throws SQLException {
        //执行对象
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());
        //准备sql语句
        String sql = "delete from worker where id = ?";
        //执行方法
        int i = queryRunner.update(sql, id);
        return i;
    }

    /**
     * 统计表中数据个数
     * @return  返回数据个数
     * @throws SQLException
     */
    @Override
    public int count() throws SQLException {

        //执行对象
        QueryRunner queryRunner = new QueryRunner(DruidJdbcUtils.getDataSource());
        //准备sql语句
        String sql = "select count(id) from worker";
        //执行方法
        Object query = queryRunner.query(sql, new ScalarHandler<>());
        //String类有万能方法:valueOf(可以传递任意类型包括Object)---->String
        String str = String.valueOf(query);
        //在String--->Integer的parseInt(String s)---->int
        int i = Integer.parseInt(str);
        return i;

    }
}

Junit_Test：单元测试(增删改查)方法：

package com.qf.Junit;

import com.qf.Impl.WorkerDaoImpl;
import com.qf.entity.Worker;
import org.junit.Test;

import java.sql.SQLException;

/**
 * @Author 豆三岁
 * @Date 2022-05-20 16:17
 **/
public class Junit_Test {

    /**
     * 修改数据：根据id修改worker表中的数据
     * @throws SQLException
     */
    @Test
    public void UpdateWorkerById() throws SQLException {
        WorkerDaoImpl workerDao = new WorkerDaoImpl();
        //创建学生
        Worker worker = new Worker();
        worker.setId(6);
        worker.setName("张三丰");
        worker.setAge(100);
        worker.setGender("男");
        worker.setAddress("武当山");
        worker.setSalary(9999.00);
        //调用功能
        workerDao.updateWorker(worker);

    }
    
    /**
     * 根据员工id获取员工信息
     * @throws SQLException
     */
    @Test
    public void FindById() throws SQLException {
        WorkerDaoImpl workerDao = new WorkerDaoImpl();
        Worker workerById = workerDao.findWorkerById(5);
        if (workerById != null ){
            System.out.println(workerById);
        } else{
            System.out.println("查无此人！");
        }

    }
    
    /**
     * 添加学生信息
     * @throws SQLException
     */
    @Test
    public void add() throws SQLException {
        WorkerDaoImpl workerDao = new WorkerDaoImpl();
        Worker worker = new Worker();
        worker.setId(7);
        worker.setName("杨过");
        worker.setAge(28);
        worker.setGender("男");
        worker.setAddress("绝情谷");
        worker.setSalary(8888.88);
        workerDao.add(worker);
    }
    
    
    /**
     * 单元测试：查询worker表中所有数据
     * @throws SQLException
     */
    @Test
    public void testFindAll() throws SQLException {
        //创建接口对象
        WorkerDao workerDao = new WorkerDaoImpl();
        List<Worker> list = workerDao.findAll();
        if (list!=null){
            //遍历list集合
            for (Worker worker : list) {
                //输出表数据
                System.out.println(worker);
            }
        }else {
            System.out.println("表中没有数据！！！");
        }
    }
    
    /**
     * 根据指定id删除员工信息
     * @throws SQLException
     */
    @Test
    public void deleteById() throws SQLException {
        WorkerDaoImpl workerDao = new WorkerDaoImpl();
        int i = workerDao.deleteWorkerById(3);
        if (i == 1){
            System.out.println("删除成功！！！");
        } else{
            System.out.println("删除失败！请重试！！！");
        }
    }
    
    /**
     * 查询worker表中的总记录数
     * @throws SQLException
     */
    @Test
    public void count() throws SQLException {
        WorkerDaoImpl workerDao = new WorkerDaoImpl();
        int i = workerDao.count();
        System.out.println("总记录数为："+i+"条!");
    }
}

DruidJdbcUtils：JDBC封装类

package com.qf.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author Kuke
 * @date 2022/5/19 17:40
 * 改进优化:
 * 1)从DataSource获取连接对象getConnection(),DataSource替代了DriverManager (连接池获取连接对象)
 * 2)读取的连接池的配置文件
 * 3)提供静态代码块----加载当前类的时候,直接读取连接池的配置文件,
 * 获取的连接池对象---DruidDataSourceFactroy工厂类获取数据源对象
 *
 */
public class DruidJdbcUtils {

    //成员变量位置:提供ThreadLocal<Connection>:模拟线程,每一个线程使用自己的Connection
    private static ThreadLocal<Connection> t1 = new ThreadLocal<>() ;
    //声明一个DataSource类型的变量
    private static DataSource ds ;

    //无参构造私有化:目的外界不能new对象了
    private DruidJdbcUtils(){}

    //静态代码块
    static{
        try {
            //创建属性集合列表
            Properties prop = new Properties() ;
            //直接读取连接池的配置文件
            InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            //将字节输入流的内容加载属性列表中
            prop.load(inputStream) ;
            //DruidDataSourceFactroy工厂类获取数据源对象
            ds = DruidDataSourceFactory.createDataSource(prop);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //封装一个功能:获取数据源
    public static DataSource getDataSource(){
        return  ds;
    }

    //封装一个功能:获取数据库的连接对象
    public static Connection getConnection(){
        //1)首先要从当前线程中获取连接对象
        try {
            Connection conn = t1.get();
            //2)判断conn是空的
            if(conn==null){
                //当前线程中没有开始绑定连接对象
                //3)从数据源连接池获取连接对象
                conn = ds.getConnection() ;
                //4)将当前连接对象绑定给自己的线程中
                t1.set(conn);
            }
            return conn ;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null ;
    }

    //封装释放资源
    public static void close(PreparedStatement ps,Connection conn){
        close(null,ps,conn);
    }
    public static void close(ResultSet rs, PreparedStatement ps,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

        if(conn!=null){
            try {
                conn.close();
                //需要将自己线程中的连接对象解绑
                t1.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //测试方法
/*    public static void main(String[] args) {
       // DataSource dataSource = DruidJdbcUtils.getDataSource();
      //  System.out.println(dataSource);
        //获取连接对象
        Connection connection = DruidJdbcUtils.getConnection();
        System.out.println(connection);
    }*/

}

druid.properties：Druid连接池文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/homework11
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000