JDBC 的使用

使用 MariaDB，JDBC 所有操作全部使用预处理

SQL 的基本类型与 Java 类型的对应关系

CHAR(N) - String
VARCHAR(N) - String
BOOLEN - boolean
BIT - boolean
INT - int
BIGINT - long
FLOAT - float
DOUBLE - double
DECIMAL - BigDecimal
DATE - java.sql.Date
DATETIME - java.util.Date
TIMESTAMP - java.sql.Timestamp

其中 DATE 类型为日期（如 2018 年 12 月 11 日），DATETIME 类型为日期时间（如 2018 年 12 月 11 日 14:10:28.288），还有一点就是 java.sql 包中关于日期的需要 long 类型进行实例化的一般都是用毫秒做单位，如果不幸传入了秒，时间怕就定格在 1970 了

创建数据库与数据表

使用的建表 SQL 文件为 Demo.sql

MariaDB 10.x+ 才支持 DATETIME 设置 CURRENT_TIMESTAMP 或 NOW() 默认值，5.x 及以下需要把 DATETIME 改为 TIMESTAMP 才能使用 CURRENT_TIMESTAMP 或 NOW() 做默认值

CREATE DATABASE `demo`;

-------

USE `demo`;

-------

CREATE TABLE IF NOT EXISTS `userInfo` (
    `rowId` CHAR(36) NOT NULL COMMENT 'MySQL 不支持 DEFAULT UUID()',
    `telNum` CHAR(11) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT '0' COMMENT '0 为未知，1 为男性，2 为女性',
    `createDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleteMark` BOOLEAN NOT NULL DEFAULT '0' COMMENT '实际是个 TINYINT(1)',
    PRIMARY KEY (`rowId`),
    UNIQUE KEY `rowId` (`rowId`),
    UNIQUE KEY `telNum` (`telNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

------

CREATE TABLE `sendSmsLog` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `userId` CHAR(36) NOT NULL,
    `createDate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleteMark` BOOLEAN NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `fk_user_info_row_id` (`userId`),
    CONSTRAINT `fk_user_info_row_id` FOREIGN KEY (`userId`) REFERENCES `userInfo` (`rowId`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8mb4;

导入 SQL，mysql -u root -p < Demo.sql，数据库建立完成

JDBC 的连接前准备

添加 MariaDB 驱动的 Maven 依赖

<!-- MariaDB JDBC 驱动 -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.3.0</version>
    <scope>runtime</scope>
</dependency>

创建连接信息字符串

// JDBC 连接 URL
private static final String JDBC_URL = "jdbc:mariadb://localhost:3306/demo?useSSL=false&characterEncoding=utf8";
// 数据库用户名
private static final String JDBC_USER = "root";
// 数据库密码
private static final String JDBC_PASSWORD = "toor";

驱动的注册与销毁

如果是 JavaEE 程序，则需要先注册驱动器类

try {
    Class.forName("org.mariadb.jdbc.Driver");
} catch (ClassNotFoundException exp) {
    exp.printStackTrace();
}

当然，注册了就得在程序关闭时注销

// 获取加载器，下方需要判断驱动是否是应用自身加载的
ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
// 获取已加载的所有驱动
Enumeration<Driver> driverEnumeration = DriverManager.getDrivers();
while (driverEnumeration.hasMoreElements()) {
    Driver driver = driverEnumeration.nextElement();
    // 需要直接判断是否是同一个对象，所以使用了 == 而不是 equals(.)
    if (driver.getClass().getClassLoader() == classLoader) {
        try {
            // 注销驱动
            DriverManager.deregisterDriver(driver);
        } catch (SQLException exp) {
            LOGGER.warn("注销驱动异常：" + exp);
        }
    }
}

注册和销毁建议是创建一个 实现了 ServletContextListener 接口的监听器

创建 JDBC 连接

创建链接

try (Connection connection = DriverManager.getConnection(Demo.JDBC_URL, Demo.JDBC_USER, Demo.JDBC_PASSWORD)){
    // TODO SQL 操作
} catch (SQLException exp) {
    System.out.println(exp.getErrorCode() + " - " + exp.getSQLState() + " - " + exp.getMessage());
}

JDBC 插入数据

// 插入操作的 SQL 语句，注意 UUID() 函数的使用，直接作为 SQL 语句传入
final String insertSQL = "INSERT INTO `userInfo`(`rowId`, `telNum`) VALUES (UUID(), ?)";
// 预处理
try (PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
    // 占位符序号从 1 开始
    preparedStatement.setString(1, "13213213213");
    // 返回操作影响的行数
    int effectLine = preparedStatement.executeUpdate();
}

如果该表有自增的主键，那么 prepareStatement() 获取 PreparedStatement 时传入第二个参数 PreparedStatement.RETURN_GENERATED_KEYS，在 executeUpdate() 后通过 ResultSet resultSet = preparedStatement.getGenerateKeys(); long key = resultSet.getLong(1); 即可获取该自增列的值

JDBC 查询数据

// 查询操作的 SQL 语句
final String selectSQL = "SELECT `telNum`, `createDate` FROM `userInfo` WHERE `sex` = ?";
// 预处理
try (PreparedStatement preparedStatement = connection.prepareStatement(selectSQL)) {
    // 传参
    preparedStatement.setInt(1, 0);
    // 只有 SELECT 操作使用 executeQuery() 方法，返回结果集
    try (ResultSet resultSet = preparedStatement.executeQuery()) {
        // 循环获取结果
        while (resultSet.next()) {
            // 通过数据库列名查询
            String telNum = resultSet.getString("telNum");
            // 通过返回数据的列序号查询，从 1 开始
            // DATETIME 类型如果使用 getDate() 方法获取则会失去时间部分
            Timestamp timestamp = resultSet.getTimestamp(2);
            System.out.println(telNum + " - " + timestamp.toInstant());
        }
    }
}

JDBC 更改数据

final String updateSQL = "UPDATE `userInfo` SET `sex` = ? WHERE telNum = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
    preparedStatement.setInt(1, 1);
    preparedStatement.setString(2, "13213213213");
    int effectLine = preparedStatement.executeUpdate();
}

JDBC 删除数据

一般来说不会真的去删除数据，将 deleteMark 置为 true 即可

final String deleteSQL = "DELETE FROM `userInfo` WHERE `telNum` = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
    preparedStatement.setString(1, "13213213213");
    int effectLine = preparedStatement.executeUpdate();
}

JDBC 的事务

Connection connection = null;
// 获取连接
try {
    connection = DriverManager.getConnection(Demo.JDBC_URL, Demo.JDBC_USER, Demo.JDBC_PASSWORD);
    // 设置事务隔离级别
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    // 开启事务
    connection.setAutoCommit(false);
    ///////////////////
    // TODO 执行 SQL
    ///////////////////
    // 执行完成，提交
    connection.commit();
} catch (SQLException exp) {
    // 发生异常，进行回滚
    if (connection != null) {
        connection.rollback();
    }
} finally {
    if (connection != null) {
        // 恢复之前的模式
        connection.setAutoCommit(true);
        connection.close();
    }
}

JDBC 的连接池

这里使用的实现是 HikariCP，Maven 依赖为

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.2.0</version>
    <scope>compile</scope>
</dependency>

HikariCP 的使用

// 初始化 HikariConfig
HikariConfig hikariConfig = new HikariConfig();
// 配置数据库连接信息
hikariConfig.setJdbcUrl(Demo.JDBC_URL);
hikariConfig.setUsername(Demo.JDBC_USER);
hikariConfig.setPassword(Demo.JDBC_PASSWORD);
// 添加一些连接属性
hikariConfig.addDataSourceProperty("connectionTimeout", "1000");
hikariConfig.addDataSourceProperty("idleTimeout", "60000");
hikariConfig.addDataSourceProperty("maximumPoolSize", "10");
// 获取一个 DataSource
DataSource dataSource = new HikariDataSource(hikariConfig);
// 获取 Connection 对象
// close() 方法需要手动调用，即时这不是我们手动初始化的
// 实际上 close() 方法并不是关闭了连接，而是重新释放回连接池
try (Connection connection = dataSource.getConnection()) {
    // TODO SQL 操作
}
// 关闭 Hikari，对于 Web 程序尤其需要注意
((HikariDataSource) dataSource).close();

还有一点，如果使用的是 HikariCP，可以免去手动 Class.forName(String) 注册驱动，调用 HikariConfig 对象的 hikariConfig.setDriverClassName("org.mariadb.jdbc.Driver"); 即可