DBUtils使用

DBUtils使用

package jdbc;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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 java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 小邱
 * @version 0.0.1
 * @description DbUtils
 * @since 2021/9/29 14:32
 * commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，
 * 并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
 * API介绍：
 * org.apache.commons.dbutils.QueryRunner
 * org.apache.commons.dbutils.ResultSetHandler
 * 工具类：org.apache.commons.dbutils.DbUtils
 */
public class DbUtilsTest {
    //测试增删改:update
    @Test
    public void update() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = Druid.getConnection();
        String sql = "insert into t_user(username,password,email) values(?,?,?)";
        int update = queryRunner.update(connection,sql, "小花", 123456, "xiaohua@qq.com");
        System.out.println("影响了"+update+"条记录");

    }
    //测试查询一条数据:BeanHandler/MapHandler
    @Test
    public void queryOne() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = Druid.getConnection();
        String sql = "SELECT `id`,`username`,`password`,`email` FROM t_user WHERE id = ?";
        BeanHandler<User> handler = new BeanHandler<User>(User.class);
        User user = queryRunner.query(connection, sql, handler, 1);
        System.out.println(user);
        DbUtils.close(connection);
    }
    //测试查询多条数据:BeanListHandler/MapListHandler
    @Test
    public void queryMore() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = Druid.getConnection();
        String sql = "SELECT `id`,`username`,`password`,`email` FROM t_user WHERE id > ?";
        BeanListHandler<User> handler = new BeanListHandler<User>(User.class);
        List<User> userList = queryRunner.query(connection, sql, handler, 1);
        userList.forEach(System.out::println);
        DbUtils.close(connection);

    }
    //测试查询于最大的，最小的，平均的，总和，个数相关的数据：ScalarHandler
    @Test
    public void queryScalar() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = Druid.getConnection();
        String sql = "SELECT count(*) FROM t_user WHERE id > ?";
        ScalarHandler handler = new ScalarHandler();
        long count = (long) queryRunner.query(connection, sql, handler, 1);
        System.out.println(count);
        DbUtils.close(connection);
    }
    //自定义ResultSetHandler的实现类
    @Test
    public void result() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = Druid.getConnection();
        String sql = "SELECT `id`,`username`,`password`,`email` FROM t_user WHERE id = ?";
        ResultSetHandler<User> handler = new ResultSetHandler<User>(){
            @Override
            public User handle(ResultSet resultSet) throws SQLException {
                if (resultSet.next()){
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String password = resultSet.getString("password");
                    String email = resultSet.getString("email");
                    return new User(id, username, password, email);
                }
                return null;
            }
        };
        User user = queryRunner.query(connection, sql, handler, 1);
        System.out.println(user);
        DbUtils.close(connection);
    }
}