MySQL 基础21 DBUtils的CRUD操作

1.1 DBUtils的添加操作

1.1.1 工具类的准备

public class JDBCUtils2 {
    // 创建一个连接池：但是这个连接池只需要创建一次即可。
    private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
    
    /**
     * 获得连接的方法
     * @throws SQLException 
     */
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    
    /**
     * 获得连接池:
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
    
    /**
     * 释放资源的方法
     */
    public static void release(Statement stmt,Connection conn){
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            stmt = null;
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
    
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        // 资源释放：
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            rs = null;
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            stmt = null;
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

1.1.2 DBUtils的添加操作

@Test
    /**
     * 添加操作
     */
    public void demo1() throws SQLException{
        // 创建核心类：QueryRunner:
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        queryRunner.update("insert into account values (null,?,?)", "ddd",10000);
    }

1.2 DBUtils的修改操作

@Test
    /**
     * 修改操作
     */
    public void demo2() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        queryRunner.update("update account set name=?,money=? where id =?", "eee",20000,4);
    }

1.3 DBUtils的删除操作

@Test
    /**
     * 删除操作
     */
    public void demo3() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        queryRunner.update("delete from account where id = ?", 3);
    }

1. 4DBUtils的查询操作

数据库和表自己工具JavaBean来创建

创建JavaBean

public class Account implements Serializable{
    
    private static final long serialVersionUID = -1204360090882362330L;
    private int id;
    private String name;
    private double  money;
    public Account() {
        super();
        // TODO Auto-generated constructor stub
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getMoney() {
        return money;
    }
    public void setMoney(double money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", money=" + money
                + "]";
    }

    

}

ublic class DBUtilsDemo2 {

    @Test
    /**
     * 查询一条记录的操作
     */
    public void demo1() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        // 执行查询:
        Account account = queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){

            @Override
            public Account handle(ResultSet rs) throws SQLException {
                Account account = new Account();
                if(rs.next()){
                    account.setId(rs.getInt("id"));
                    account.setName(rs.getString("name"));
                    account.setMoney(rs.getDouble("money"));
                }
                return account;
            }
            
        }, 1);
        System.out.println(account);
    }
    
    @Test
    /**
     * 查询多条记录
     */
    public void demo2() throws SQLException{
        // 创建核心类:
        QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
        // 执行查询:
        List<Account> list = queryRunner.query("select * from account", new ResultSetHandler<List<Account>>(){
            @Override
            public List<Account> handle(ResultSet rs) throws SQLException {
                // 创建一个集合用于封装数据:
                List<Account> list = new ArrayList<Account>();
                while(rs.next()){
                    // 将结果集中的数据封装到对象中
                    Account account = new Account();
                    account.setId(rs.getInt("id"));
                    account.setName(rs.getString("name"));
                    account.setMoney(rs.getDouble("money"));
                    // 将这个对象存入到list集合：
                    list.add(account);
                }
                return list;
            }
        });
        // 使用集合:
        for (Account account : list) {
            System.out.println(account);
        }
    }
}