JDBC的DBUtils工具类包的使用：增删查改，多表连接的自定义Handler处理器

1、增删查改

import entity.MultiTableQeury;
import entity.StudentJava;
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 org.junit.Test;
import utils.JDBCutils;

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

/**
 * @Author ZRX
 * @Date 2023/6/14 14:24
 * @Description commons.DBUtils---Apache组织的jdbc工具类包，可使jdbc代码更简洁
 * @Version
 */
public class DBUtilsMethod {
   
    //增加
    public void add() throws SQLException {
        //1、获取sql对象
        QueryRunner queryRunner=new QueryRunner();
        Connection conn= JDBCutils.getConnection();
        String sql="insert into student values(?,?,?,?,?,?)";

        //2、执行添加操作的方法
        int update=queryRunner.update(conn,sql,null,"张蓝",25,"昆明",21,1);

        System.out.println("当前添加的记录数位： " + update);
        //关闭资源
        DbUtils.closeQuietly(conn);
    }

    
    //删除
    public void delete() throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        Connection connection=JDBCutils.getConnection();
        String sql="delete from student where id=?";
        int del=queryRunner.update(connection,sql,6);
        System.out.println("del = " + del);
        DbUtils.closeQuietly(connection);
    }

    
    //更改数据
    public void update() throws SQLException {
        QueryRunner qr=new QueryRunner();
        Connection connection=JDBCutils.getConnection();
        String sql="update student set address=? where username=?";
        int up=qr.update(connection,sql,"曲靖","张蓝");
        System.out.println("up = " + up);
        DbUtils.closeQuietly(connection);
    }

    
    //查询全部数据
    public void selectAll() throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        Connection conn=JDBCutils.getConnection();
        String sql="select*from student";
        //采用BeanListHandler处理器
        BeanListHandler<StudentJava> blist=new BeanListHandler<>(StudentJava.class);
        List<StudentJava> list =queryRunner.query(conn,sql,blist);
        System.out.println("list = " + list);
        DbUtils.closeQuietly(conn);
    }

    
    //查询单条记录
    public void selectByName() throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        Connection connection=JDBCutils.getConnection();
        String sql="select *from student where username=?";

        BeanHandler<StudentJava> bh=new BeanHandler<>(StudentJava.class);
        StudentJava stu=queryRunner.query(connection,sql,bh,"李思");
        System.out.println("address= " + stu.getAddress());
        DbUtils.closeQuietly(connection);
    }

    
    //Dbutils下聚合函数（count，avg，sum，min，max）的使用，即查询数据库表中的单个值
    public void selectMax() throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        Connection connection=JDBCutils.getConnection();
        String sql="select max(age) from student";

        ScalarHandler scalarHandler=new ScalarHandler();

        Object o=queryRunner.query(connection,sql,scalarHandler);
        Number number=(Number) o;
        int i=number.intValue();
        System.out.println("i = " + i);
        DbUtils.closeQuietly(connection);
    }
}

2、自定义的Handler处理器（实现多表连接查询一条或多条记录）

2.1 自定义的MultiTableListHandler多表查询类

import entity.Body;
import entity.MultiTableQeury;
import entity.StudentJava;
import org.apache.commons.dbutils.ResultSetHandler;

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

/**
 * @Author ZRX
 * @Date 2023/6/14 20:42
 * @Description 自定义多表查询处理器
 * @Version
 */
public class MultiTableListHandler<T> implements ResultSetHandler <List<T>> {
    MultiTableQeury mtq=null;
    List<T> list=new ArrayList<>();
    @Override
    public List<T> handle(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            StudentJava studentJava=new StudentJava();
            studentJava.setId(resultSet.getInt(1));
            studentJava.setUsername(resultSet.getString(2));

            Body body=new Body();
            body.setHeight(resultSet.getDouble(3));
            body.setWeight(resultSet.getDouble(4));

            mtq=new MultiTableQeury(body,studentJava);
            list.add((T) mtq);
            resultSet.close();
        }
        return list;
    }
}

注意：这里需要创建一个以其他表的实体类对象为参数的MultiTableQeury类

2.2 自定义类的使用（和DBUtils的工具类一样）

//多表查询：采用自定义handler处理器,可查询多表连接的一条或多条记录
    public void  selectMultiTable() throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        Connection connection=JDBCutils.getConnection();
        String sql="select stu.id,stu.username,body.height,body.weight from student as stu 
        left join body on stu.id=body.stuid ";

        MultiTableListHandler<MultiTableQeury> mth=new MultiTableListHandler<>();
        List<MultiTableQeury> list=queryRunner.query(connection,sql,mth);
        System.out.println("学生身高体重信息：" +list.toString() );
        DbUtils.closeQuietly(connection);
    }