JavaWeb学习笔记（十七）之ApacheDbutils

ApacheDbutils重点类

DbUtils：连接数据库对象——jdbc辅助方法的集合类，线程安全

构造方法：DbUtils()
作用：控制连接，控制书屋，控制驱动加载额一个类。

QueryRunner：SQL语句的操作对象，可以设置查询结果集的封装策略，线程安全。

构造方法：

• QueryRunner()：创建一个与数据库无关的QueryRunner对象，后期再操作数据库的会后，需要手动给一个Connection对象，它可以手动控制事务。
  Connection.setAutoCommit(false); 设置手动管理事务
  Connection.commit(); 提交事务

• QueryRunner(DataSource ds)：创建一个与数据库关联的queryRunner对象，后期再操作数据库的时候，不需要Connection对象，自动管理事务。
  DataSource：数据库连接池对象。

构造函数与增删改查方法的组合：

• QueryRunner()
  update(Connection conn, String sql, Object… params)
  query(Connection conn, String sql, ResultSetHandler rsh, Object… params)

• QueryRunner(DataSource ds):已经连接了就不需要再连接了。
  update(String sql, Object… params)
  query(String sql, ResultSetHandler rsh, Object… params)

ResultSetHandler：封装数据的策略对象——将封装结果集中的数据，转换到另一个对象

注意！
DbUtils给我们提供了10个ResultSetHandler实现类，分别是：

• ArrayHandler： 将查询结果的第一行数据，保存到Object数组中
• ArrayListHandler 将查询的结果，每一行先封装到Object数组中，然后将数据存入List集合
• BeanListHandler 将查询结果的每一行封装到user对象，然后再存入List集合
• ColumnListHandler 将查询结果的指定列的数据封装到List集合中
• MapHandler 将查询结果的第一行数据封装到map结合（key等于列名，value等于列值）
• MapListHandler 将查询结果的每一行封装到map集合（key等于列名，value等于列值），再将map集合存入List集合
• BeanMapHandler 将查询结果的每一行数据，封装到User对象，再存入mao集合中（key等于列名，value==列值）
• KeyedHandler 将查询的结果的每一行数据，封装到map1（key等于列名，value等于列值 ），然后将map1集合（有多个）存入map2集合（只有一个）
• ScalarHandler 封装类似count、avg、max、min、sum…函数的执行结果

代码示例：
//DataSorceUtil.java

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.util.Properties;

public class DataSourceUtil {
    public static DataSource getDataSourceWithC3P0() throws PropertyVetoException {
        ComboPooledDataSource c3p0 = new ComboPooledDataSource();
        c3p0.setDriverClass("com.mysql.cj.jdbc.Driver");
        c3p0.setJdbcUrl("jdbc:mysql://localhost:3306/sqltest?serverTimezone=GMT%2B8");
        c3p0.setUser("root");
        c3p0.setPassword("root");

        return c3p0;
    }


    public static DataSource getDateSourceWithC3P0ByConfig() {
        ComboPooledDataSource c3p0 = new ComboPooledDataSource("rj");
        return c3p0;
    }

    public static DataSource getDataSourceWithDBCP() {
        BasicDataSource dbcp = new BasicDataSource();

        dbcp.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dbcp.setUrl("jdbc:mysql://localhost:3306/sqltest?serverTimezone=GMT%2B8");
        dbcp.setUsername("root");
        dbcp.setPassword("root");
        dbcp.setInitialSize(20);
        dbcp.setMaxActive(10);

        return dbcp;
    }

    public static DataSource getDataSourceWithDBCPByProperties() throws Exception {
        DataSource dbcp = null;
        Properties properties = new Properties();

        InputStream input = new DBCPDemo().getClass().getClassLoader().getResourceAsStream("dbcpconfig.properties");

        properties.load(input);

        dbcp = BasicDataSourceFactory.createDataSource(properties);
        return dbcp;
    }
}

第一步：创建queryRunner对象，用来操作sql语句
QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

第二步：创建sql语句
String sql = “insert into user values(null,?,?)”;

第三步：执行sql语句,params:是sql语句的参数
注意，给sql语句设置参数的时候，按照user表中字段的顺序

代码示例：
//查询操作: QueryRunner()

public class Test {

    // 只在结果中取一行
    public static void testArrayHandler() throws SQLException {
        // 和连接池建立联系
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());
        // 执行 SQL 语句,后面的参数是可变的,参数较多时可以用 Object 数组进行传递。
        // new ArrayHandler() 是 ResultSetHandle 的一个实现类,交代我们的返回值类型。
        Object[] student = runner.query("select * from student where sno > ?", new ArrayHandler(), 1);

        System.out.println(student[0] + "," + student[1]);

    }


    // 查询多行信息
    public static void testArrayListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        List<Object[]> student =  runner.query("select * from student where sno > ?", new ArrayListHandler(), 1001);

        for(Object[] s : student) {
            System.out.println(s[0] + "," + s[1]);
        }

    }

    // 拿到一个对象
    public static void testBeanHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        Student student = runner.query("select * from student where sno > ?", new BeanHandler<>(Student.class), 1);

        System.out.println(student.getSno()+","+ student.getSname());

    }

    // 放到多个对象中
    public static void testBeanListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        List<Student> students = runner.query("select * from student where sno > ?", new BeanListHandler<>(Student.class), 1);

        for(Student student : students) {
            System.out.println(student.getSno()+","+ student.getSname());

        }

    }


    public static void testBeanMapHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        Map<Integer,Student> students =runner.query("select * from student where sno > ?", new BeanMapHandler<Integer,Student>(Student.class,"sno"), 1);

        Student student = students.get(1002);
        System.out.println(student.getSno() + "," + student.getSname());
    }


    // 查询结果集中某一列 ColumnListHandler:  把结果集中某一列,保存到 List 中

    public static void testColumnListHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        List<String> names =runner.query("select * from student where sno > ?", new ColumnListHandler<String>("sname"),1);

        for(String name : names) {
            System.out.println(name);

        }

    }

    // long 可以, Integer 不 OK
    public static void testScalarHandler() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        Long result = runner.query("select count(*) from student where sno > ?", new ScalarHandler<Long>(),1);

        System.out.println(result);
    }


    public static void main(String[] args) throws SQLException {
//        testArrayHandler();
//        testArrayListHandler();
//        testBeanHandler();
//        testBeanListHandler();
//        testBeanMapHandler();
//        testColumnListHandler();
        testScalarHandler();
    }
}

代码示例”
//update操作(增删改):

public class UpdateDemo {

    public static void add() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        int result = runner.update("insert into student(sno,sname) value(?,?)", new Object[]{102, "李四"});

        System.out.println(result);
    }

    public static void delete() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        int result = runner.update("delete from student where sno = ?",1002);

        System.out.println(result);
    }

    public static void update() throws SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDateSourceWithC3P0ByConfig());

        int result = runner.update("update student set sname =? where sno =?",new Object[]{"笨笨",102});

        System.out.println(result);
    }

    public static void main(String[] args) throws SQLException {
//        add();
//        delete();
        update();
    }


}