关于DBUtils

DBUtils

1. DbUtils类

2. ☆QueryRunner类：

   该类封装了SQL的执行，是线程安全的。

   1. 可以实现增、删、改、查、批处理。

   2. 考虑了事务处理需要共用Connection。

   3. 最主要就是简化了SQL查询，它和ResultSetHandler组合在一起使用可以完成大部分的数据库操作，极大地减少了编码量。

      该类提供了两个构造方法：

      • QueryRunner(); 默认的构造方法；
      • QueryRunner(DataSource ds);需要一个javax.sql.DataSource 来做参数的构造方法。

3. ☆ResultSetHandler接口：查询的一个返回结果集。

4. ScalarHandler：将单个值封装、例如select count（*），求内容的条数。

创建一个和表中字段一一对应的类, get/set方法调用其属性。

此类中包含：

• 和字段对应的私有属性

• 各属性的get/set方法

• 有参构造，无参构造

• toString方法

public class Admin {
    private int id;
    private String username;
    private String password;

    public Admin(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

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

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

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

1.增删改

@Test
    public void testUpdate() throws Exception {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();

        //2.执行增删改
        QueryRunner qr = new QueryRunner();
        int update = qr.update(connection, "UPDATE beauty SET sex=? WHERE name='柳岩'", "男");

        System.out.println(update>0?"success":"fail");

        //3.关闭连接
        JDBCUtilsByDruid.close(null,null,connection);

    }

2.BeanHandler 单个查询

@Test
    public void testQuerySingle() throws Exception {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();

        //2.执行查询
        QueryRunner qr = new QueryRunner();
        Admin admin = qr.query(connection, "SELECT * FROM admin WHERE id=?", new BeanHandler<>(Admin.class), 1);
        System.out.println(admin);

        Boys boys = qr.query(connection, "select * from boys where usercp=?", new BeanHandler<>(Boys.class), 300);
        System.out.println(boys);

        //3.关闭连接
        JDBCUtilsByDruid.close(null,null,connection);

    }

3.BeanListHandler

foreach 遍历集合

@Test
    public void testQueryMultiple() throws Exception {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();

        //2.执行查询
        QueryRunner qr = new QueryRunner();
//        List<Boys> query = qr.query(connection, "select * from boys where usercp>?", new BeanListHandler<>(Boys.class), 10);
//        for (Boys boys:query
//             ) {
//            System.out.println(boys);
//        }

        List<Admin> list = qr.query(connection, "select * from admin", new BeanListHandler<>(Admin.class));
        for (Admin admin:list
             ) {
            System.out.println(list);
        }

        //3.关闭连接
        JDBCUtilsByDruid.close(null,null,connection);

    }

4.ScalarHandler 查询单个

返回值为查询个数。。

@Test //Scalar  将单个值封装
    public void testScalar() throws Exception {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();

        //2.执行查询 单个值如"select count(*) from admin"，返回所查内容的条数。
        QueryRunner qr = new QueryRunner();
//        Object o = qr.query(connection, "select count(*) from admin", new ScalarHandler());
//        System.out.println(o);


        //scanner只查询单个值，如果查询多个，只有第一个
        Object o1 = qr.query(connection, "select * from admin", new ScalarHandler());
        System.out.println(o1);

        //3.关闭连接
        JDBCUtilsByDruid.close(null,null,connection);
    }

练习

import com.miyon.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;

import java.sql.Connection;
import java.util.Scanner;

/**
 * 请输入编号：   1
 * 编号  姓名     邮箱        生日
 * 1    张三  123@q.com   1999-02-02
 */
public class TestPracticeWangFeng {
    @Test
    public void test() throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入编号：");
        int i = scanner.nextInt();
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner qs = new QueryRunner();
        Men query = qs.query(connection, "select * from men where id=?", new BeanHandler<>(Men.class),i);
        System.out.println(query);

        JDBCUtilsByDruid.close(null,null,connection);
    }
}