JDBC:driver(mysql) / Connection / Statement / 参数化 / ResultSet

更多
2021年08月14日 10点06分 作者:叶飞 修改

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); 

@试一试@:来点恶意代码?

准备工作:打开并能查看日志
  1. 首先打开mysql的general log,可以查看mysql的运行情况:
    set global general_log=on;
  2. 通过查看mysql的system variable,得到general log的文件名:

  3. 能在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对象,有几种获取方式:
    1. 直接由字符串等生成
      pUpdate.setDate(1, Date.valueOf("2021-09-25"));
    2. 基于Java8引入的LocalDate/Time生成
      pUpdate.setTime(2, Time.valueOf(LocalTime.now()));
    3. 基于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()执行,数据库没有被要求执行。


事务

单个数据库的事务是基于连接的:
  1. 首先要把默认自动事务关掉
  2. 在try中提交事务
  3. 在catch中回滚
  4. 在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()) { // 在此获取连接

@想一想@:是不是我们自己都可以做?

注意事项:

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


作业

见:持久化:ADO&JDBC:driver / 连接 / SQL注入 / 事务 / 批处理 / 结果集 / 连接池

  1. 用DriverManager完成第1题
  2. 用HikariCP连接池完成其他题目
JDBC
赞: 0 踩: 0

打赏
已收到打赏的 帮帮币

你的 打赏 非常重要!
为了保证文章的质量,每一篇文章的发布,都已经消耗了作者 1 枚 帮帮币
没有“帮帮币”,作者无法发布新的文章。

全系列阅读
评论 / 0

持久化


ADO&EF

如何通过C#进行数据库的读取,包含ADO.NET和Entity Framework相关知识……

JDBC&Hibernate

Java连接数据库操作,包括JDBC、Hibernate和mybatis等

全部
关键字



帮助

反馈