JDBC总结

1.QueryRunner类：该类封装了SQL的执行，是线程安全的。可以实现增、删、改、查、批处理。ResultSetHandler接口：该接口用于处理java.sql.ResultSet。ArrayHandler：把结果集合的第一行数据转成对象数组。ArrayListHandler：把结果集合中的每一行数据都转成一个数组，再存放到List中。BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。ColumnListHandler:将结果集中某一列的数据存放到List中。 KeyedHandler(name)：将结果集中的每行数据都封装到Map里，再把这些map再存放到一个map里，其key为指定的key。MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。MapLisrHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。

创建存放的Actor对象：

private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;

public Actor() {
}

public Actor(Integer id, String name, String sex, Date borndate, String phone) {
    this.id = id;
    this.name = name;
    this.sex = sex;
    this.borndate = borndate;
    this.phone = phone;
}

public Integer getId() {
    return id;
}

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

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getSex() {
    return sex;
}

public void setSex(String sex) {
    this.sex = sex;
}

public Date getBorndate() {
    return borndate;
}

public void setBorndate(Date borndate) {
    this.borndate = borndate;
}

public String getPhone() {
    return phone;
}

public void setPhone(String phone) {
    this.phone = phone;
}

@Override
public String toString() {
    return "\nActor{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", sex='" + sex + '\'' +
            ", borndate=" + borndate +
            ", phone='" + phone + '\'' +
            '}';
}

引入DBUtils相关的jar，加入到本Project中才能使用DBUtils类和接口。

返回查询结果是多行记录如下，没有查询到就置空。

public static void main(String[] args) throws SQLException {
        DBUtils_USE use = new DBUtils_USE();
        use.testQueryMany();
    }

    public void testQueryMany() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id >= ?";
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.print(actor);
        }
        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

返回的查询结果是单行记录：

public static void main(String[] args) throws SQLException {
        DBUtils_USE use = new DBUtils_USE();
        use.testQuerySingle();
    }
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?";
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 10);
        System.out.println(actor);
        //释放资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

测试DML(update,insert,delete)：

public static void main(String[] args) throws SQLException {
        DBUtils_USE use = new DBUtils_USE();
        use.testDML();
    }
    public void testDML() throws SQLException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
//修改   String sql = "update actor set name = ? where id = ?";
//      int affectedRow = queryRunner.update(connection, sql, "小鹏", 4);

//增加   String sql = "insert into actor values(null, ? , ? , ?, ?)";
//      int affectedRow = queryRunner.update(connection, sql, "小红", "女", "2008-8-8", "66666");

//删除   String sql = "delete from actor where id = ?";
//      int affectedRow = queryRunner.update(connection, sql, 6);
        System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响到表");
        JDBCUtilsByDruid.close(null, null, connection);
    }