JDBC学习笔记

IDEA导入JDBC驱动的jar包

获取数据库连接

方式一

public void testConnection1() throws SQLException {
    // 获取 Driver 实现类对象
    Driver driver = new com.mysql.jdbc.Driver();
    // jdbc:mysql: -> 协议
    // localhost:  -> ip 地址
    // 3306        -> 默认 mysql 的端口号
    // test        -> test 数据库
    String url = "jdbc:mysql://localhost:3306/test";
    // 将用户名和密码封装在 Properties 中
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "root");
    // 获取连接
    Connection connection = driver.connect(url, info);

    System.out.println(connection);
}

方式二

通过反射创建 Driver 实现类的对象，提高方法通用性

public void testConnection2() throws Exception {
    // 获取 Driver 实现类对象：通过反射
    Class clazz = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();
    // 提供要连接的数据库
    String url = "jdbc:mysql://localhost:3306/test";
    // 将用户名和密码封装在 Properties 中
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "root");
    // 获取连接
    Connection connection = driver.connect(url, info);

    System.out.println(connection);
}

方式三

使用 DriverManager 替代 Driver

public void testConnection3() throws Exception {
    // 获取 Driver 实现类对象：通过反射
    Class clazz = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();
    // 提供要连接的数据库
    String url = "jdbc:mysql://localhost:3306/test";
    // 提供账号
    String user = "root";
    // 提供密码
    String password = "root";
    // 注册驱动
    DriverManager.registerDriver(driver);
    // 获取连接
    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

方式四

static {
    try {
        DriverManager.registerDriver(new Driver());
    } catch (SQLException var1) {
        throw new RuntimeException("Can't register driver!");
    }
}

由于 Driver 类的静态代码块帮我们注册了驱动，因此可以在方式三的基础上进一步简化

public void testConnection4() throws Exception {
    // 提供要连接的数据库
    String url = "jdbc:mysql://localhost:3306/test";
    // 提供账号
    String user = "root";
    // 提供密码
    String password = "root";
    // 加载 Driver
    Class.forName("com.mysql.jdbc.Driver");
    // 获取连接
    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

方式五

在方式四的基础上，将具体的连接信息写入到配置文件中

user=root
password=root
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

public void testConnection5() throws Exception {
    // 读取配置文件中的四个基本信息
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(is);
    // 提供要连接的数据库
    String url = properties.getProperty("url");
    // 提供账号
    String user = properties.getProperty("user");
    // 提供密码
    String password = properties.getProperty("password");
    // 加载 Driver
    String driverClass = properties.getProperty("driverClass");
    Class.forName(driverClass);
    // 获取连接
    Connection connection = DriverManager.getConnection(url, user, password);

    System.out.println(connection);
}

Statement 操作数据库

弊端：存在 SQL 注入的风险
例如：
原来的查询语句

SELECT USER,PASSWORD
FROM user_table
WHERE USER='' AND PASSWORD='';

SQL 注入后的查询语句

SELECT USER,PASSWORD
FROM user_table
WHERE USER='1' OR ' and password='=1 OR '1'='1';

PreparedStatement 操作数据库

添加数据

Connection connection = null;
PreparedStatement preparedStatement = null;
try {
    // 读取配置文件中的四个基本信息
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(is);
    // 提供要连接的数据库
    String url = properties.getProperty("url");
    // 提供账号
    String user = properties.getProperty("user");
    // 提供密码
    String password = properties.getProperty("password");
    // 加载 Driver
    String driverClass = properties.getProperty("driverClass");
    Class.forName(driverClass);
    // 获取连接
    connection = DriverManager.getConnection(url, user, password);
    // 预编译 SQL 语句，返回 PreparedStatement 实例
    String sql = "INSERT INTO customers(name,email,birth) VALUES(?,?,?)";// ? 占位符
    preparedStatement = connection.prepareStatement(sql);
    // 填充占位符，注意下标是从1开始
    preparedStatement.setString(1, "张三");
    preparedStatement.setString(2, "ZhangSan@gmail.com");
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    Date date = simpleDateFormat.parse("2000-01-01");
    preparedStatement.setDate(3, new java.sql.Date(date.getTime()));
    // 执行 SQL
    preparedStatement.execute();
} catch (IOException e) {
    e.printStackTrace();
} catch (ClassNotFoundException e) {
    e.printStackTrace();
} catch (SQLException throwables) {
    throwables.printStackTrace();
} catch (ParseException e) {
    e.printStackTrace();
} finally {
    // 资源关闭
    try {
        if (preparedStatement != null)
            preparedStatement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    try {
        if (connection != null)
            connection.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

创建 JDBCUtils 工具类

public class JDBCUtils {
    public static Connection getConnection() throws Exception {
        // 读取配置文件中的四个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        // 提供要连接的数据库
        String url = properties.getProperty("url");
        // 提供账号
        String user = properties.getProperty("user");
        // 提供密码
        String password = properties.getProperty("password");
        // 加载 Driver
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        // 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    public static void closeResource(Connection connection, Statement statement) {
        // 资源关闭
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void closeResource(Connection connection, Statement statement, ResultSet rs) {
        // 资源关闭
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs != null)
                rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

修改数据

Connection connection = null;
PreparedStatement ps = null;
try {
    // 1、获取数据库连接
    connection = JDBCUtils.getConnection();
    // 2、预编译 SQL 语句，返回 PreparedStatement 实例
    String sql = "UPDATE customers SET name=? WHERE id=?";
    ps = connection.prepareStatement(sql);
    // 3、填充占位符
    ps.setString(1, "贝多芬");
    ps.setInt(2, 18);
    // 4、执行
    ps.execute();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    // 5、关闭资源
    JDBCUtils.closeResource(connection, ps);
}

通用的增删改操作

public void update(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        // 1、获取数据库连接
        connection = JDBCUtils.getConnection();
        // 2、预编译 SQL 语句，返回 PreparedStatement 实例
        ps = connection.prepareStatement(sql);
        // 3、填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 4、执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 5、关闭资源
        JDBCUtils.closeResource(connection, ps);
    }
}

查询单行数据

Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
    connection = JDBCUtils.getConnection();
    String sql = "SELECT id,name,email,birth FROM customers WHERE id = ?";
    ps = connection.prepareStatement(sql);
    ps.setInt(1, 1);
    // 返回结果集
    resultSet = ps.executeQuery();
    if (resultSet.next()) {
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);
        String email = resultSet.getString(3);
        Date birth = resultSet.getDate(4);
		// 用 Customer 对象存储一行 Customers 表数据
        Customer customer = new Customer(id, name, email, birth);
        System.out.println(customer);
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResource(connection, ps, resultSet);
}

Customers 表通用的查询单行数据

public Customer queryForCustomers(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        // 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 获取列数
        int columnCount = rsmd.getColumnCount();
        if (rs.next()) {
            Customer customer = new Customer();
            for (int i = 1; i <= columnCount; i++) {
                // 获取每一列的数据
                Object columnValue = rs.getObject(i);
                // 获取列名
                String columnName = rsmd.getColumnName(i);
                // 获取属性
                Field field = Customer.class.getDeclaredField(columnName);
                // 设置权限
                field.setAccessible(true);
                // 设置值
                field.set(customer, columnValue);
            }
            return customer;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

Order 表通用的查询单行数据

通过 SQL 语句给列（字段）取别名的方式来解决属性名与表中列（字段）名不一致的情况

public Order orderForQuery(String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        if (rs.next()) {
            Order order = new Order();
            for (int i = 1; i <= columnCount; i++) {
                Object object = rs.getObject(i);
                // 通过 getColumnName 方法获取的列名是原来的列名而非别名
                // String columnName = rsmd.getColumnName(i);
                // getColumnLabel 获取到的是列的别名
                String columnLabel = rsmd.getColumnLabel(i);
                Field field = Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order, object);
            }
            return order;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

通用的查询单行数据

public <T> T getInstance(Class<T> clazz, String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        // 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 获取列数
        int columnCount = rsmd.getColumnCount();
        if (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                // 获取每一列的数据
                Object columnValue = rs.getObject(i);
                // 获取列的别名
                String columnLabel = rsmd.getColumnLabel(i);
                // 获取属性
                Field field = clazz.getDeclaredField(columnLabel);
                // 设置权限
                field.setAccessible(true);
                // 设置值
                field.set(t, columnValue);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

通用的查询多行数据

public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        connection = JDBCUtils.getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        rs = ps.executeQuery();
        // 获取结果集的元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        // 获取列数
        int columnCount = rsmd.getColumnCount();
        // 创建集合对象
        ArrayList<T> list = new ArrayList<>();
        while (rs.next()) {
            T t = clazz.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                // 获取每一列的数据
                Object columnValue = rs.getObject(i);
                // 获取列的别名
                String columnLabel = rsmd.getColumnLabel(i);
                // 获取属性
                Field field = clazz.getDeclaredField(columnLabel);
                // 设置权限
                field.setAccessible(true);
                // 设置值
                field.set(t, columnValue);
            }
            list.add(t);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection, ps, rs);
    }
    return null;
}

添加 Blob 类型的数据

Connection connection = null;
PreparedStatement ps = null;
try {
    connection = JDBCUtils.getConnection();
    String sql = "insert customers(name, email, birth, photo) values(?, ?, ?, ?)";
    ps = connection.prepareStatement(sql);
    ps.setObject(1, "大乔");
    ps.setObject(2, "123@126.com");
    ps.setObject(3, "345-08-07");
    FileInputStream is = new FileInputStream(new File("D:" +
            "\\浏览器下载\\A_temp\\白鹤梁神女.jpg"));
    ps.setBlob(4, is);
    ps.execute();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResource(connection, ps);
}

注意，默认情况下 MySQL？16M，但由于？，只能传输1M以下的文件，所以需要到 MySQL 配置文件 my.ini 末尾添加以下信息

max_allowed_packet=16M

查询 Blob 类型的数据

Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
InputStream is = null;
FileOutputStream fos = null;
try {
    connection = JDBCUtils.getConnection();
    String sql = "select id,name,email,birth,photo from customers where id=?";
    ps = connection.prepareStatement(sql);
    ps.setInt(1, 20);
    rs = ps.executeQuery();
    if (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        Date birth = rs.getDate("birth");
        Customer customer = new Customer(id, name, email, birth);
        System.out.println(customer);
        Blob photo = rs.getBlob("photo");
        is = photo.getBinaryStream();
        fos = new FileOutputStream("D:" +
                "\\浏览器下载\\A_temp\\大乔.jpg");
        byte[] buffer = new byte[1024];
        int len;
        while ((len = is.read(buffer)) != -1) {
            fos.write(buffer, 0, len);
        }
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
        if (fos != null)
            fos.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    try {
        if (is != null)
            is.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    JDBCUtils.closeResource(connection, ps, rs);
}

批量插入

方式一

Connection connection = null;
PreparedStatement ps = null;
try {
    connection = JDBCUtils.getConnection();
    String sql = "INSERT biao(`name`) VALUES(?)";
    ps = connection.prepareStatement(sql);
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 20000; i++) {
        ps.setString(1, "name_" + i);
        // 攒 SQL
        ps.addBatch();
        if (i % 500 == 0) {
            // 执行 SQL
            ps.executeBatch();
            // 清空 SQL
            ps.clearBatch();
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为" + (end - start) + "ms");
} catch (Exception e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResource(connection, ps);
}

注意，MySQL 默认不支持批处理，
所以需要在配置文件中 url 后添加?rewriteBatchedStatements=true

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

方式二

Connection connection = null;
PreparedStatement ps = null;
try {
    connection = JDBCUtils.getConnection();
    // 设置不允许自动提交数据
    connection.setAutoCommit(false);
    String sql = "INSERT biao(`name`) VALUES(?)";
    ps = connection.prepareStatement(sql);
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 1000000; i++) {
        ps.setString(1, "name_" + i);
        // 攒 SQL
        ps.addBatch();
        if (i % 500 == 0) {
            // 执行 SQL
            ps.executeBatch();
            // 清空 SQL
            ps.clearBatch();
        }
    }
    // 提交数据
    connection.commit();
    long end = System.currentTimeMillis();
    System.out.println("花费的时间为" + (end - start) + "ms");
} catch (Exception e) {
    e.printStackTrace();
} finally {
    JDBCUtils.closeResource(connection, ps);
}

事务控制

以下操作将会导致数据的自动提交

|-- DDL 操作一旦执行，都会自动提交
|-- DML 操作默认情况下一旦执行，就会自动提交
	|-- 通过 set autocommit=false 的方式取消 DML 的自动提交
|-- 默认在关闭连接时，会自动提交数据

考虑事务后的通用的增删改操作

// 考虑事务后的通用的增删改操作
public void update(Connection connection, String sql, Object... args) {
    PreparedStatement ps = null;
    try {
        // 1、获取数据库连接
//            connection = JDBCUtils.getConnection();
        // 2、预编译 SQL 语句，返回 PreparedStatement 实例
        ps = connection.prepareStatement(sql);
        // 3、填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 4、执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 5、关闭资源
        JDBCUtils.closeResource(null, ps);
    }
}

考虑事务以后的代码实现

Connection connection = null;
try {
    connection = JDBCUtils.getConnection();
    // 取消数据的自动提交
    connection.setAutoCommit(false);
    String sql1 = "update user_table set balance=balance+100 where user=?";
    update(connection, sql1, "AA");
    // 模拟网络异常
    System.out.println(10 / 0);
    String sql2 = "update user_table set balance=balance-100 where user=?";
    update(connection, sql2, "BB");
    System.out.println("转账成功");
    // 提交数据
    connection.commit();
} catch (Exception e) {
    e.printStackTrace();
    // 回滚数据
    try {
        connection.rollback();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
} finally {
	// 考虑连接可能后续仍然会使用，所以恢复自动提交数据
	// 主要是在数据库连接池使用
    try {
        connection.setAutoCommit(true);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    JDBCUtils.closeResource(connection, null);
}

事务的 ACID 属性及4种隔离级别

// 获取当前连接的隔离级别
connection.getTransactionIsolation();
// 设置当前连接的隔离级别
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

DAO 及相关实现类

BaseDAO

public abstract class BaseDAO {
    // 考虑事务后的通用的增删改操作
    public void update(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            // 1、获取数据库连接
//            connection = JDBCUtils.getConnection();
            // 2、预编译 SQL 语句，返回 PreparedStatement 实例
            ps = connection.prepareStatement(sql);
            // 3、填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 4、执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5、关闭资源
            JDBCUtils.closeResource(null, ps);
        }
    }

    // 考虑事务后的通用的查询操作，返回一条记录
    public <T> T getInstance(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    // 获取每一列的数据
                    Object columnValue = rs.getObject(i);
                    // 获取列的别名
                    String columnLabel = rsmd.getColumnLabel(i);
                    // 获取属性
                    Field field = clazz.getDeclaredField(columnLabel);
                    // 设置权限
                    field.setAccessible(true);
                    // 设置值
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    // 考虑事务后的通用的查询操作，返回多条记录
    public <T> List<T> getForList(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取列数
            int columnCount = rsmd.getColumnCount();
            // 创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 1; i <= columnCount; i++) {
                    // 获取每一列的数据
                    Object columnValue = rs.getObject(i);
                    // 获取列的别名
                    String columnLabel = rsmd.getColumnLabel(i);
                    // 获取属性
                    Field field = clazz.getDeclaredField(columnLabel);
                    // 设置权限
                    field.setAccessible(true);
                    // 设置值
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    // 用于查询特殊值
    public <T> T getValue(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (T) rs.getObject(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
}

CustomerDAO

public interface CustomerDAO {
    // 将 customer 对象添加到数据库中
    void insert(Connection connection, Customer customer);

    // 根据 id 删除表中一条记录
    void deleteById(Connection connection, int id);

    // 根据 customer 对象的 id，将数据表中该 id 对应的记录修改成 customer 对象
    void update(Connection connection, Customer customer);

    // 针对指定的 id 查询得到对应的 Customer 对象
    Customer getCustomerById(Connection connection, int id);

    // 查询表中的所有记录
    List<Customer> getAll(Connection connection);

    // 返回数据表中数据的条目数
    Long getCount(Connection connection);

    // 返回数据表中最大的生日
    Date getMaxBirth(Connection connection);
}

CustomerDAOImpl

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
    @Override
    public void insert(Connection connection, Customer customer) {
        String sql = "insert customers(name,email,birth) values(?,?,?)";
        update(connection, customer.getName(), customer.getEmail(), customer.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from customers where id=?";
        update(connection, sql, id);
    }

    @Override
    public void update(Connection connection, Customer customer) {
        String sql = "update customers set name=?,email=?,birth=? where id=?";
        update(connection, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id,name,email,birth from customers where id=?";
        Customer customer = getInstance(connection, Customer.class, sql, id);
        return customer;
    }

    @Override
    public List<Customer> getAll(Connection connection) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> list = getForList(connection, Customer.class, sql);
        return list;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        return getValue(connection, sql);
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from customers";
        return getValue(connection, sql);
    }
}

BaseDAO 的优化

由于造具体的 DAO 时以及明确了查询的对象，因此可以将 BaseDAO 中的 getInstance 和 getForList 其中的 Class clazz 参数去除，具体实现：使 BaseDAO 成为泛型类，而其子类继承时使用具体的泛型

public abstract class BaseDAO<T> {
    private Class<T> clazz = null;

    {
        // 获取父类的泛型
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
        clazz = (Class<T>) actualTypeArguments[0];
    }
    
    ...
}

public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
	...
}

数据库连接池

C3P0 数据库连接池的两种实现方式

@Test
public void testGetConnection() throws PropertyVetoException, SQLException {
    // 获取 C3P0 数据库连接池
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass("com.mysql.jdbc.Driver");
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
    cpds.setUser("root");
    cpds.setPassword("root");

    Connection connection = cpds.getConnection();
    System.out.println(connection);
}

@Test
public void testGetConnection1() throws PropertyVetoException, SQLException {
    // 获取 C3P0 数据库连接池
    ComboPooledDataSource cpds = new ComboPooledDataSource("helloC3P0");
    Connection connection = cpds.getConnection();
    System.out.println(connection);
}

c3p0-config.xml

<?xml version="1.0" encoding="ISO-8859-1"?>

<c3p0-config>
    <named-config name="helloC3P0">
        <!-- 提供获取连接的四个基本信息 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!-- 进行数据库连接池管理的基本信息 -->
        <!-- 当数据库中连接池的个数不够时， C3P0 一次性向数据库服务器申请的连接数 -->
        <property name="acquireIncrement">5</property>
        <!--  C3P0 数据库连接池中初始化时的连接数 -->
        <property name="initialPoolSize">10</property>
        <!--  C3P0 数据库连接池维护的最少连接数 -->
        <property name="minPoolSize">10</property>
        <!--  C3P0 数据库连接池维护的最多连接数 -->
        <property name="maxPoolSize">100</property>

        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <!-- C3P0 数据库连接池最多维护的 Statement 的个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接中可以最多使用的 Statement 的个数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

DBCP 数据库连接池的两种实现方式

@Test
public void testGetConnection() throws SQLException {
    // 创建了 DBCP 的数据库连接池
    BasicDataSource source = new BasicDataSource();
    // 设置基本信息
    source.setDriverClassName("com.mysql.jdbc.Driver");
    source.setUrl("jdbc:mysql:///test");
    source.setUsername("root");
    source.setPassword("root");
    // 获取连接
    Connection connection = source.getConnection();
    System.out.println(connection);
}

@Test
public void testGetConnection1() throws Exception {
    // 创建了 DBCP 的数据库连接池
    Properties pros = new Properties();
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//        FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
    pros.load(is);
    DataSource source = BasicDataSourceFactory.createDataSource(pros);
    Connection connection = source.getConnection();
    System.out.println(connection);
}

dbcp.properties

username=root
password=root
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driverClassName=com.mysql.jdbc.Driver

Druid 数据库连接池

@Test
public void getConnection() throws Exception {
    Properties pros = new Properties();
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
    pros.load(is);
    DataSource source = DruidDataSourceFactory.createDataSource(pros);
    Connection connection = source.getConnection();
    System.out.println(connection);
}

druid.properties

username=root
password=root
url=jdbc:mysql:///test
driverClassName=com.mysql.jdbc.Driver

JDBCUtils 通过三个数据库连接池分别获取连接

// 创建一个 C3P0 数据库连接池
private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloC3P0");

// 使用 C3P0 数据库连接池获取一个连接
public static Connection getConnection1() throws Exception {
    return cpds.getConnection();
}

// 创建一个 DBCP 数据库连接池
private static DataSource source1;

static {
    try {
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
//          FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
        pros.load(is);
        source1 = BasicDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

// 使用 DBCP 数据库连接池获取一个连接
public static Connection getConnection2() throws Exception {
    return source1.getConnection();
}

// 创建一个 Druid 数据库连接池
private static DataSource source2;

static {
    try {
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        source2 = DruidDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static Connection getConnection3() throws Exception {
    return source2.getConnection();
}

Apache-DBUtils实现CRUD操作

public class QueryRunnerTest {
    // 测试插入
    @Test
    public void testInsert() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "insert into customers(name,email,birth)values(?,?,?)";
            int insertCount = runner.update(connection, sql, "Jerry", "jerry@126.com", "1998-01-23");
            System.out.println("插入了" + insertCount + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询单条记录
    // BeanHandler 是 ResultSetHandler 接口的实现类，用于封装表中的一条记录
    @Test
    public void testQuery1() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(connection, sql, handler, 19);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询多条记录
    // BeanListHandler 是 ResultSetHandler 接口的实现类，用于封装表中的多条记录构成的集合
    @Test
    public void testQuery2() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(connection, sql, handler, 19);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询单条记录
    // MapHandler 是 ResultSetHandler 接口的实现类，对应表中的一条记录
    // 将字段及字段对应的值作为 Map 的 key 和 value
    @Test
    public void testQuery3() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(connection, sql, handler, 19);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询多条记录
    // MapListHandler 是 ResultSetHandler 接口的实现类，对应表中的多条记录构成的集合
    @Test
    public void testQuery4() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = runner.query(connection, sql, handler, 19);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询单条记录
    // ArrayHandler 是 ResultSetHandler 接口的实现类，用于封装表中的一条记录的各个属性
    @Test
    public void testQuery5() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id=?";
            ArrayHandler handler = new ArrayHandler();
            Object[] query = runner.query(connection, sql, handler, 19);
            for (Object o : query) {
                System.out.println(o);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询单条记录
    // ArrayListHandler 是 ResultSetHandler 接口的实现类，用于封装表中的多条记录的各个属性
    @Test
    public void testQuery6() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id<?";
            ArrayListHandler handler = new ArrayListHandler();
            List<Object[]> list = runner.query(connection, sql, handler, 5);
            for (Object[] o : list) {
                for (Object obj : o) {
                    System.out.println(obj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 测试查询特殊值，ScalarHandler 用于查询特殊值
    @Test
    public void testQuery7() {
        Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection3();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Object o = runner.query(connection, sql, handler);
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    // 资源的关闭
    public void testClose(Connection connection, Statement statement, ResultSet rs) {
        try {
            DbUtils.close(connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(rs);
    }
}