JDBC核心技术（下）

一. JDBC的特殊使用情况

1.1 获取数据库自增长的主键

java.sql.prepareStatement提供的一个包含预编译 SQL 语句的新 PreparedStatement 对象，该对象能够返回自动生成的键

PreparedStatement prepareStatement(String sql,int autoGeneratedKeys)throws SQLException

创建一个默认 PreparedStatement 对象，该对象能获取自动生成的键。给定常量告知驱动程序是否可以获取自动生成的键。如果 SQL 语句不是一条 INSERT 语句，或者 SQL 语句能够返回自动生成的键（这类语句的列表是特定于供应商的），则忽略此参数。 

autoGeneratedKeys - 指示是否应该返回自动生成的键的标志，它是 Statement.RETURN_GENERATED_KEYS 或 Statement.NO_GENERATED_KEYS 之一 

@Test
public void returnPrimaryKey() throws Exception {
    /**
     * t_user插入一条数据! 并且获取数据库自增长的主键!
     * TODO: 使用总结
     *     1.创建prepareStatement的时候,告知,携带回数据库自增长的主键 (sql,Statement,RETURNGENERATED_KEYS)
     *     2.获取携带主键值的结果集对象,一行一列,获取对应的数据即可 ResultSet resultSet = statement,getGeneratedKeys();
     */
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbcdemo", "root", "root");

    String sql = "insert into t_user (account, password, nickname) values (?, ?, ?)";

    PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, "zhangsan");
    statement.setString(2, "zhangsantest");
    statement.setString(3, "admin123");

    int i = statement.executeUpdate();

    if (i > 0) {
        System.out.println("插入数据成功...");
        // 开始会先获取主键数据
        ResultSet resultSet = statement.getGeneratedKeys();
        // 此处移动一下光标
        resultSet.next();
        // 打印输出获取到的主键
        System.out.println(resultSet.getInt(1));
    } else {
        System.out.println("插入数据失败...");
    }

    // 释放资源
    statement.close();
    connection.close();
}

1.2 批量执行SQL语句

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。

JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch():清空缓存的数据。

@Test
public void testBatchInsert() throws Exception {

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbcdemo?rewriteBatchedStatements=true", "root", "root");
    String sql = "insert into t_user (account, password, nickname) values (?, ?, ?)";
    PreparedStatement statement = connection.prepareStatement(sql);

    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        statement.setObject(1, "ff" + i);
        statement.setObject(2, "ff" + i);
        statement.setObject(3, "ff" + i);
        statement.addBatch();
    }

    statement.executeBatch();
    long end = System.currentTimeMillis();
    System.out.println("批量插入数据完成,总共耗时: " + (end - start));
    statement.close();
    connection.close();
}

"jdbc:mysql:///jdbcdemo?rewriteBatchedStatements=true", "root", "root"

路径后面添加?rewriteBatchedStatements=true 允许批量插入

 

 二. 数据库连接池介绍

2.1 数据库连接池简介

JDBC中使用连接时都要创建一个Connection对象，使用完毕后将其销毁。这种重复创建、销毁的过程是特别耗费计算机性能以及计算时间，而数据库如果使用了数据库连接池，就能达到Connection对象的复用效果。

• 数据库连接池是一个容器。
• 功能：负责分配、管理数据库的连接(Connection)
• 它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。
• 释放空闲时间超过最大空闲时间的数据库连接，避免因为没有释放数据库连接而引起的数据库连接遗漏。
• 好处：
  • 资源复用
  • 提升系统响应速度
  • 避免数据库连接遗漏
• 数据库连接池在一开始就创建好了一些连接对象存储起来。用户需要连接数据库时，不需要自己创建连接，只需要从连接池中获取一个进行使用就行了，使用完毕后将连接对象归还给连接池 ，即为资源重用，也节省了频繁创建连接销毁连接所花费的时间，从而提高系统响应的速度。

常见的数据库连接池：

• DBCP
• C3P0
• Druid
• ...

2.2 Druid的使用

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

如何使用：

1. 将jar包放到当前工程下的lib目录，并在驱动jar上右键-->Build Path-->Add to Build Path。

 

2. 此处通过外部配置文件创建durid数据池连接

配置文件：

druidClassName=com.mysql.cj.jdbc.Driver
username=root
password=root
url=jdbc:mysql:///jdbcdemo

有一些可配置参数，如：

• initialSize：初始化时建立物理连接的个数
• maxActive：最大连接池数量
• ...

此处略，可以根据实际需求自行配置

public class DruidUseDemo {

    /**
     * 通过软链接实例化数据库连接池对象
     */
    public void testUseDruidBySoft() throws Exception {
        // 读取外部配置文件properties
        Properties properties = new Properties();

        // src下的文件,可以通过类加载器的方式获取
        InputStream ips = DruidUseDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        // 使用连接池的工具类创建连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();

        // TODO:数据库CRUD

        // 回收链接
        connection.close();
    }

三. JDBC事务

3.1 数据库事务介绍

• 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
• 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
• 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。 
   

3.2JDBC事务处理

• 数据一旦提交，就不可回滚。
• 数据什么时候意味着提交？
  • 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
  • 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。
• JDBC程序中为了让多个 SQL 语句作为一个事务执行：
  • 调用 Connection 对象的 setAutoCommit(false):以取消自动提交事务
  • 在所有的 SQL 语句都成功执行后，调用 commit():方法提交事务
  • 在出现异常时，调用 rollback():方法回滚事务

若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

通过一个模拟转账案例实现JDBC的事务操作,直接上代码:

3.2.1 JDBCUtils工具类封装

public class JDBCUtilsV2 {

    private static DataSource dataSource = null;

    // 利用线程本地变量,存储连接信息! 确保一个线程的多个方法可以获取同一个connection!
    private static ThreadLocal<Connection> tl = new ThreadLocal<>();

    //驱动加载在整个程序中只加载一次，所以放到静态语句块中
    static {
        //初始化连接对象
        Properties properties = new Properties();
        InputStream ips = JDBCUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
        try {
            properties.load(ips);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 对外提供的连接的方法
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        // 先判断线程本地变量是否存在
        Connection connection = tl.get();
        // 第一次没有，就从线程池获取
        if (connection == null) {
            connection = dataSource.getConnection();
            tl.set(connection);
        }
        return connection;
    }

    /**
     *  回收线程的方法
     * @throws SQLException
     */
    public static void freeConnection() throws SQLException {
        Connection connection = tl.get();
        if (connection != null) {
            tl.remove();// 清空线程变量本地数据
            connection.setAutoCommit(true);//事务状态回归
            connection.close();// 回收到连接池
        }
    }

}

3.2.2 转账模拟操作 

数据库操作方法相关的BankDao 

public class BankDao {

    /**
     * 加钱的数据库操作方法
     *
     * @param account 加钱的账号
     * @param money   加钱的金额
     */
    public void add(String account, int money) throws Exception {

        Connection connection = JDBCUtilsV2.getConnection();
        // 创建sql语句
        String sql = "update t_bank set money = money + ? where account = ? ;";
        // 创建statement
        PreparedStatement ps = connection.prepareStatement(sql);
        // 占位符赋值
        ps.setObject(1, money);
        ps.setObject(2, account);
        // 执行sql语句
        ps.executeUpdate();
        // 关闭链接
        ps.close();

        System.out.println("加钱成功！");

    }

    /**
     * 减钱的数据库操作方法
     *
     * @param account 减钱的账号
     * @param money   减钱的金额
     */
    public void sub(String account, int money) throws Exception {
        Connection connection = JDBCUtilsV2.getConnection();
        // 创建sql语句
        String sql = "update t_bank set money = money - ? where account = ? ;";
        // 创建statement
        PreparedStatement ps = connection.prepareStatement(sql);
        // 占位符赋值
        ps.setObject(1, money);
        ps.setObject(2, account);
        // 执行sql语句
        ps.executeUpdate();
        // 关闭链接
        ps.close();

        System.out.println("减钱成功！");
    }
}

银行卡业务方法,调用dao方法以及测试

public class BankService {

    @Test
    public void test() throws Exception {
        transfer("zhangsan", "lisi", 500);
    }

    /**
     * TODO:
     *  事务添加是在业务方法中
     *  利用try catch 代码块开启和提交事务、事务回滚
     *  将connection传入到dao，dao只负责使用，对象在finally中进行资源释放
     *
     * @param addAccount
     * @param subAccount
     * @param money
     * @throws Exception
     */
    public void transfer(String addAccount, String subAccount, int money) throws Exception {

        BankDao bankDao = new BankDao();
        // 一个事务的最基本要求是同一个链接对象 connection
        // 注册驱动
        Connection connection = JDBCUtilsV2.getConnection();

        // 开启事务
        try {
            // 关闭事务自动提交
            connection.setAutoCommit(false);

            // 执行数据库操作
            bankDao.add(addAccount, money);
            System.out.println("==========");
            bankDao.sub(subAccount, money);

            // 事务提交
            connection.commit();
        } catch (Exception e) {
            // 事务回滚
            connection.rollback();
            // 抛出异常
            throw e;
        } finally {
            JDBCUtilsV2.freeConnection();
        }

    }
}

四. BaseDao封装

JDBC连接和操作数据库，按照连接几个基本步骤操作就可以了，但是做数据CRUD的时候除了sql语句不同之外，其他代码都是一样的。可以封装一个BaseDao方法，把数据库操作的几个基本过程封装为几个独立的方法，把所有数据库操作类的共有属性抽象出来，然后数据库操作类都继承这个BaseDao类即可。

话不多说，直接上代码：

/**
 * @Description 抽取公共代码, 封装一个BaseDao工具类
 *
 * TODO:封装两个方法,一个简化非DQL,一个简化DQL
 */
public abstract class BaseDao {

    /**
     * 封装简化非DQL语句
     *
     * @param sql    带占位符的SQL语句
     * @param params 占位符的值,传入占位符的值必须等于sql参数中占位符的位置
     * @return 执行影响的行数
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {
        // 获取链接
        Connection connection = JDBCUtilsV2.getConnection();
		
        // 创建 preparedStatement 对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
		
        // 占位符赋值,此处的可变参数可以当作数组使用
        for (int i = 1; i <= params.length; i++) {
            preparedStatement.setObject(i, params[i - 1]);
        }

        // 发送sql语句
        int rows = preparedStatement.executeUpdate();
        preparedStatement.close();

        // 是否回收连接,需要考虑是不是事务
        if (connection.getAutoCommit()) {
            // 没有开启事务,正常回收连接
            JDBCUtilsV2.freeConnection();
        }
        // 如果开启了事务,不要管连接即可,提交给业务层处理
        //connection.setAutoCommit(false);
        return rows;
    }

    /**
     * 将查询的结果封装到一个实体类集合
     * 非DQL语句封装方法 -> 返回值 固定为int
     * DQL语句封装方法 -> 返回值 ?
     *
     * @param clazz  要接值的实体类集合的模板对象
     * @param sql    查询语句,要求列名或者别名等于实体类的属性名
     * @param params 占位符的值,和?位置对应传递
     * @param <T>    声明的结果的泛型
     * @return 查询的实体类集合
     */
    public <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) throws Exception {
        // 获取连接
        Connection connection = JDBCUtilsV2.getConnection();
		
        // 创建preparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
		
        // 占位符赋值
        if (params == null && params.length != 0) {
            for (int i = 1; i < params.length; i++) {
                preparedStatement.setObject(i, params[i - 1]);
            }
        }

        // 发送SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
		
        List<T> list = new ArrayList<>();

        // 获取列的信息对象
        // TODO: metData 装的当前结果集列的信息对象 (可以获取列的名称根据下角标,可以获取列的数量)
        ResultSetMetaData metaData = resultSet.getMetaData();

        // 遍历水平列
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            // 调用类的无参构造函数实例化对象
            T t = clazz.newInstance();
			
            // 自动遍历列
            for (int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);
                // 获取指定列下角标的列的名称! ResultSetMetaData
                // getColumnLabel: 会获取别名,如果没有写别名才是列的名称
                // getColumnName : 只会获取列的名称
                String properyName = metaData.getColumnLabel(i);

                // 反射给对象的属性值赋值
                Field field = clazz.getDeclaredField(properyName);
                // 属性可以设置,打破private的修饰限制
                field.setAccessible(true);
                // 参数1:要赋值的对象   参数2:具体的属性值
                field.set(t, value);
            }
			
            list.add(t);
        }

        // 关闭资源
        resultSet.close();
        preparedStatement.close();
        // 没有事务可以关闭
        if (connection.getAutoCommit()) {
            JDBCUtilsV2.freeConnection();
        }
		
        return list;
    }
}

测试代码：

public class PSCRUDDemo extends BaseDao {

    @Test
    public void testInsert() throws SQLException {
        String sql = "insert into t_user(account, password, nickname) values(?, ?, ?);";
        int rows = executeUpdate(sql, "test", "12345678", "test");
        System.out.println("插入" + rows + "行数据成功");
    }

    @Test
    public void testDelete() throws Exception {

        String sql = "delete from t_user where id = ?";
        int rows = executeUpdate(sql, 116644);
        System.out.println("删除" + rows + "行数据成功");
    }

    @Test
    public void testUpdate() throws Exception {
        String sql = "update t_user set nickname =? where id =?";
        int rows = executeUpdate(sql, "孙猴子", 1);
        System.out.println("修改" + rows + "行数据完成");
    }

    /**
     * 查询所有数据,结果封装到list<Map>集合中
     */
    @Test
    public void testSelect() throws Exception {
        String sql = "select * from t_user";
        List<User> users = executeQuery(User.class, sql);
        System.out.println(users);

    }

}

---

温故而知新。

JDBC相关的内容告一段落，欢迎大家指正，互相学习。

---