JDBC：driver（mysql） / Connection / Statement / 参数化 / ResultSet / HikariCP·源栈课堂·一起帮

JDBC：driver（mysql） / Connection / Statement / 参数化 / ResultSet / HikariCP

当前系列： JDBC&Hibernate

maven引入mysql-connector

JDBC接口是Java标准库自带的：

import java.sql.*;

演示：java.sql中定义的接口等

但要连接上数据库，还需要相应的驱动。我们使用（由Oracle提供的）MySQL JDBC驱动。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

数据库连接

直接使用DriverManager的静态方法getConnection()获取。

可以直接一个连接字符串搞定

String connStr = "jdbc:mysql://localhost:3306/17bang?user=root&useSSL=false&verifyServerCertificate=false"; 
Connection conn = DriverManager.getConnection(connStr);

注意从workbench复制之后，还要可能需要添加：

密码：如果设置了的话
数据库（schema）名称
不使用SSL的声明：useSSL=false&verifyServerCertificate=false
有时候还要指定字符编码：characterEncoding=utf8
……

还可以user,password分别传入：

不建议使用properties，太丑，^_^

无参SQL执行

由连接对象直接生成：

Statement insert = conn.createStatement();

然后调用execute ()方法，传入SQL语句：

insert.execute("INSERT Student VALUES(5, 'bo')");

如果要知道影响了多少行，可以使用executeUpdate()方法

int affectedRowsCount = insert.executeUpdate("UPDATE Student SET name= concat('yz-', name);");

注意executeUpdate()和execute()的区别仅限于返回值不同，他们理论上都可以执行任何SQL语句：

executeUpdate()：返回受影响的行数，int类型；
execute()：boolean类型：
- 如果不是SELECT语句，总是false；否则，
- 如果查询结果为空，false；不为空，true

传入参数

复习：SQL注入

所以我们应该使用PreparedStatement（Statement的子类），且其SQL语句中的参数用问号（?）表示

PreparedStatement pDel = conn.prepareStatement(
		"DELETE FROM Student WHERE id = ?;");

然后利用setXXX()方法设置参数值：

//第一个参数的值为整数2（int）
pDel.setInt(1, 2); // 注意下标从1开始

还可以有多个参数，多种类型：

PreparedStatement pUpdate = conn.prepareStatement(
		"UPDATE Student SET name= concat(?, name) WHERE id = ?;");
pUpdate.setString(1, "vip-");
pUpdate.setInt   (2, 5);

@试一试@：来点恶意代码？

准备工作：打开并能查看日志

首先打开mysql的general log，可以查看mysql的运行情况：
```
set global general_log=on;
```
通过查看mysql的system variable，得到general log的文件名：
能在C:\ProgramData\MySQL\MySQL Server 8.0\Data（根据你的操作系统安装设置等或有不同）下查看日志内容

String input = new Scanner(System.in).nextLine();

输入：fg' OR 1=1; #，对比：

拼字符串：

String sql = "SELECT COUNT(*) FROM Student WHERE name= '" + input + "'";
ResultSet rs = conn.createStatement().executeQuery(sql);

参数化：

String sql = "SELECT COUNT(*) FROM Student WHERE name= ?";
PreparedStatement statement = conn.prepareCall(sql);
statement.setString(1, input);

查看log，参数化之后：

SELECT COUNT(*) FROM Student WHERE name= 'fg\' OR 1=1;#'

特殊设置

比较特殊的有：

NULL：要传一个SQLType，可以直接从Types中取
```
pUpdate.setNull(3, Types.INTEGER);
```

时间：这里需要传入的是java.sql.Date/Time对象，有几种获取方式：

直接由字符串等生成

pUpdate.setDate(1, Date.valueOf("2021-09-25"));

基于Java8引入的LocalDate/Time生成

pUpdate.setTime(2, Time.valueOf(LocalTime.now()));

基于Java的老版本日期时间戳（long）生成（复习，一地鸡毛啊！¯\_(ツ)_/¯）

//两个getTime()能看懂么？
new Date(Calendar.getInstance().getTime().getTime())  
//两个Date重名了
new Date(new java.util.Date().getTime())

PS：其实哪怕是没有参数，用prepareStatement也是OK的。

ResultSet

要获取表结构的结果集，就需要调用executeQuery()方法

ResultSet students =  conn.prepareStatement("SELECT * FROM Student").executeQuery();

然后，调用其next()方法，获取所有结果：

while (students.next()) {
	System.out.println(
		"id="   + students.getInt(1)         + "    "+
		"name=" + students.getString("name"));
}

演示：关闭连接后报异常

关闭连接

推荐优先使用try (resource)来自动释放

try(Connection conn = DriverManager.getConnection(connStr)){

@想一想@：为什么？因为总是关闭，类似于conn.close();写在finally里。

另外，Statement等也是实现了AutoCloseable的。但是，关闭连接会自动关闭由其创建的所有资源。

批处理

结合general log演示：

//也可以是Statement
PreparedStatement pUpdate = conn.prepareStatement(
		"UPDATE Student SET name = CONCAT('p-', name) WHERE id=?");
for (int i = 0; i < ids.length; i++) {
	pUpdate.setInt(1, i);
	pUpdate.addBatch();    //往里面加
}
//一次性的执行
pUpdate.executeBatch();

打开general_log演示：直到executeBatch()执行，数据库没有被要求执行。

事务

单个数据库的事务是基于连接的：

首先要把默认自动事务关掉
在try中提交事务
在catch中回滚
在finally中还原成默认事务（bug之源……）

conn.setAutoCommit(false);
try {
	//多条SQL语句
	conn.commit();				
} catch (Exception e) {
	conn.rollback();
} finally {
	//千万不要忘记这个！
	conn.setAutoCommit(true);
}

结合general log演示

存储过程

复习：mysql自带系统存储过程

-- 检查数据库17bang中有没有表student，用@result表示结果
call sys.table_exists('17bang', 'student', @result);

JDBC为存储过程调用准备了特别的API：

CallableStatement proc = conn.prepareCall("call sys.table_exists(?, ?, ?)");

存储过程的参数，如果是：

输入（input）参数，和上述参数化SQL一样使用setXXX()方法设置；
```
proc.setString(1, "17bang");
proc.setString(2, "student");
```

输出（output）参数，就需要使用registerOutParameter()方法注册，

//参数下标只管位置，不区分参数是输入还是输出
proc.registerOutParameter(3, Types.VARCHAR);

最后通过getXXX()方法获得

System.out.println(proc.getString(3));

不要忘了调用：

proc.execute();

连接池：HikariCP

据说是最好用的一个，^_^，

maven地址：https://mvnrepository.com/artifact/com.zaxxer/（因为众所周知的原因，很难打开）

首先要配置连接池：

HikariConfig config = new HikariConfig();
//以下和配置连接一样的
config.setJdbcUrl("jdbc:mysql://localhost:3306/17bang");
config.setUsername("root");
config.setPassword("");
//以下是池所独有的
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10

根据配置获取一个池实例（）

DataSource pool = new HikariDataSource(config);

然后从池中拿连接：

try (Connection conn = ds.getConnection()) { // 在此获取连接

@想一想@：是不是我们自己都可以做？

注意事项：

只需要创建一个/次连接池对象就够了，所有的连接从里面拿（静态/单例等实现），不要反复的创建连接池！
几乎所有的连接池实现，都仍然要求在连接使用完毕后关闭（调用close()方法）。在底层实现上，通过连接池拿到的连接和通过DriverManager拿到的连接是不一样的，所以它的close()方法也已经被override重写了，一般来说都会被放回连接池中。（断点演示HikariCP）
好的连接池都会提供监控和灵活的配置，以便根据项目的实际运行情况进行调优。