JDBC连接、查询和更新
# 01.JDBC连接、查询和更新
本文我们演示下如何用JDBC连接、查询和更新数据库
# JDBC连接
使用JDBC时,我们先了解什么是Connection。Connection代表一个JDBC连接,它相当于Java程序到数据库的连接(通常是TCP连接)。打开一个Connection时,需要准备URL、用户名和口令,才能成功连接到数据库。
URL是由数据库厂商指定的格式,例如,MySQL的URL是:
jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2
假设数据库运行在本机localhost
,端口使用标准的3306
,数据库名称是learnjdbc
,那么URL如下:
jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8
后面的两个参数表示不使用SSL加密,使用UTF-8作为字符编码(注意MySQL的UTF-8是utf8
)。
更多URL可以参考下一篇文章,这里先用着。
在Java中,可以使用字符串来存储连接信息:
// 1. JDBC连接的URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
String JDBC_USER = "learn";
String JDBC_PASSWORD = "learnpassword2";
// 2. 注册驱动,告诉Java程序我们要使用MySQL的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
// 3.获取连接:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接:
conn.close();
2
3
4
5
6
7
8
9
10
11
12
13
核心代码是DriverManager
提供的静态方法getConnection()
。
因为JDBC连接是一种昂贵的资源,所以使用后要及时释放。使用try (resource)
来自动释放JDBC连接是一个好方法:
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
...
}
2
3
在进行查询之前,我们先确保数据库连接正常,完整代码如下:
package chapter2JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCDemo1 {
public static void main(String[] args) throws SQLException {
String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
String JDBC_USER = "learn";
String JDBC_PASSWORD = "learnpassword2";
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
System.out.println(conn);
conn.close();
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
如果能正常连接,则可以正常打印Connection对象。如果连接失败,会抛出异常,例如我上面的代码中,密码字段写错了:
Exception in thread "main" java.sql.SQLException: Access denied for user 'learn'@'localhost' (using password: YES)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at chapter2JDBC.JDBCDemo1.main(JDBCDemo1.java:13)
2
3
4
5
6
7
8
9
10
# JDBC查询
获取到JDBC连接后,下一步我们就可以查询数据库了。查询数据库分以下几步:
- 通过
Connection
提供的createStatement()
方法创建一个Statement
对象,用于执行一个查询; - 第二步,执行
Statement
对象提供的executeQuery("SELECT * FROM students")
并传入SQL语句,执行查询并获得返回的结果集,使用ResultSet
来引用这个结果集; - 第三步,反复调用
ResultSet
的next()
方法并读取每一行结果。
完整查询代码如下:
package chapter2JDBC;
import java.sql.*;
public class JDBCDemo2Select {
public static void main(String[] args) throws SQLException {
String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
String JDBC_USER = "learn";
String JDBC_PASSWORD = "learnpassword";
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select id, grade, name, gender from students where gender = 1");
while (rs.next()){
long id = rs.getLong(1); //注意:索引从1开始
long grade = rs.getLong(2);
String name = rs.getString(3);
int gender = rs.getInt(4);
System.out.printf("id: %s, grade: %s, name: %s, gender:%s, \n", id, grade,name, gender);
}
rs.close();
statement.close();
conn.close();
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
执行结果:
id: 1, grade: 1, name: 小明, gender:1
id: 2, grade: 1, name: 小红, gender:1
id: 5, grade: 2, name: 小牛, gender:1
id: 6, grade: 2, name: 小兵, gender:1
id: 9, grade: 3, name: 小青, gender:1
id: 10, grade: 3, name: 小王, gender:1
2
3
4
5
6
注意要点:
Statment
和ResultSet
都是需要关闭的资源,可以嵌套使用try (resource)
确保及时关闭。关闭和打开的顺序:
打开时:Connection -> Statement -> ResultSet
关闭时:ResultSet-> Statement -> Connection.建议使用try-catch块执行关闭,以防前面关闭意外出错了,导致后面的代码就不执行了,进而导致后面的关闭失败。
rs.next()
用于判断是否有下一行记录,如果有,将自动把当前行移动到下一行(一开始获得ResultSet
时当前行不是第一行);ResultSet
获取列时,索引从1
开始而不是0
;必须根据
SELECT
的列的对应位置来调用getLong(1)
,getString(2)
这些方法,否则对应位置的数据类型不对,将报错。也可以使用
getString(列名);
的方式来获取数据,这样不易出错。注意到JDBC查询的返回值总是
ResultSet
,即使我们写这样的聚合查询SELECT SUM(score) FROM ...
,也需要按结果集读取。
# 数据类型
有的童鞋可能注意到了,使用JDBC的时候,我们需要在Java数据类型和SQL数据类型之间进行转换。JDBC在java.sql.Types
定义了一组常量来表示如何映射SQL数据类型,但是平时我们使用的类型通常也就以下几种:
SQL数据类型 | Java数据类型 |
---|---|
BIT, BOOL | boolean |
INTEGER | int |
BIGINT | long |
REAL | float |
FLOAT, DOUBLE | double |
CHAR, VARCHAR | String |
DECIMAL | BigDecimal |
DATE | java.sql.Date, LocalDate |
TIME | java.sql.Time, LocalTime |
注意:只有最新的JDBC驱动才支持LocalDate
和LocalTime
。
# SQL注入攻击
使用Statement
拼字符串非常容易引发SQL注入的问题,这是因为SQL参数往往是从方法参数传入的。
那么什么是SQL注入呢?我们来看一个例子:假设用户登录的验证方法如下:
User login(String name, String pass) {
...
stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
...
}
2
3
4
5
其中,参数name
和pass
通常都是Web页面输入后由程序接收到的。
如果用户的输入是程序期待的值,就可以拼出正确的SQL。例如:name = "bob"
,pass = "1234"
:
SELECT * FROM user WHERE login='bob' AND pass='1234'
但是,如果用户的输入是一个精心构造的字符串,就可以拼出意想不到的SQL,这个SQL也是正确的,但它查询的条件不是程序设计的意图。例如:name = "bob' OR pass="
, pass = " OR pass='"
:
SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''
这个SQL语句执行的时候,根本不用判断口令是否正确,这样一来,登录就形同虚设。
# PreparedStatement
要避免SQL注入攻击,一个办法是针对所有字符串参数进行转义,但是转义很麻烦,而且需要在任何使用SQL的地方增加转义代码。
还有一个办法就是使用PreparedStatement
。使用PreparedStatement
可以完全避免SQL注入的问题,因为PreparedStatement
始终使用?
作为占位符,并且把数据连同SQL本身传给数据库,这样可以保证每次传给数据库的SQL语句是相同的,只是占位符的数据不同,还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement
可以改写如下:
User login(String name, String pass) {
...
String sql = "SELECT * FROM user WHERE login=? AND pass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, name);
ps.setObject(2, pass);
...
}
2
3
4
5
6
7
8
所以,PreparedStatement
比Statement
更安全,而且更快。
使用Java对数据库进行操作时,必须使用PreparedStatement,严禁任何通过参数拼字符串的代码!
我们把上面使用Statement
的代码改为使用PreparedStatement
:
package chapter2JDBC;
import java.sql.*;
public class JDBCDemo3PreparedStatement {
public static void main(String[] args) throws SQLException {
String JDBC_URL = "jdbc:mysql://localhost:3306/learnjdbc";
String JDBC_USER = "learn";
String JDBC_PASSWORD = "learnpassword";
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
PreparedStatement statement = conn.prepareStatement("select id, grade, name, gender from students where gender = ? and grade = ?");
statement.setObject(1, "M"); //注意:索引从1开始
statement.setObject(2, 3); //注意:索引从1开始
ResultSet rs = statement.executeQuery();
while (rs.next()){
long id = rs.getLong("id");
long grade = rs.getLong("grade");
String name = rs.getString("name");
int gender = rs.getInt("gender");
System.out.printf("id: %s, grade: %s, name: %s, gender:%s \n", id, grade,name, gender);
}
rs.close();
statement.close();
conn.close();
}
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
运行结果:
id: 11, grade: 3, name: 小林, gender:0
id: 12, grade: 3, name: 小贝, gender:0
2
使用PreparedStatement
和Statement
稍有不同,必须首先调用setObject()
设置每个占位符?
的值,最后获取的仍然是ResultSet
对象。
# 模糊查询写法
有时候我们需要用到模糊查询,也就是like。我们第一反应可能是这样写:
String sql = "select * from students where name like %小%";
这是错误的写法,因为%是特殊字符,正确写法:
String sql = "select * from students where name like '%小%'";
String sql = "select * from students where name like '小%'";
String sql = "select * from students where name like '%小'";
// 或者用拼接
String sql = "select * from [dbo].[sal] where empno like '%"+ "小" + "'";
String sql = "select * from students where name like '%"+ "小" + "'";
String sql = "select * from students where name like '"+ "小" + "%'"
2
3
4
5
6
7
8
如果使用PreparedStatement:
PreparedStatement statement2 = conn.prepareStatement("select * from students where name like ?");
statement2.setObject(1, "%小%");
2
# 存储过程
有时候我们会用到存储过程,调用存储过程语法如下:
CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, "dylan thomas");
proc.setInt(2, 69);
cs.execute();
2
3
4
# 总结
JDBC接口的Connection
代表一个JDBC连接;
使用JDBC查询的步骤如下:
- 确定JDBC连接字符串
- 使用DriverManager类创建Connection对象
- 创建Statement对象
- 配置要执行的SQL,并使用
executeQuery
方法来执行SQL - 反复调用
ResultSet
的next()
方法并读取每一行结果 - 注意关闭资源
每个对象的作用:
- DriverManager:驱动管理对象
- Connection:数据库连接对象
- Statement、PreparedStatement:执行sql的对象
- ResultSet:结果集对象,封装查询结果
注意:
- 使用JDBC查询时,总是使用
PreparedStatement
进行查询而不是Statement
; - 查询结果总是
ResultSet
,即使使用聚合查询也不例外。
完整代码已上传至Gitee和GitHub:
Gitee:src/chapter2JDBC · 小林/LearnJavaEE - 码云 - 开源中国 (opens new window)
GitHub:LearnJavaEE/src/chapter2JDBC at master · Peter-JXL/LearnJavaEE (opens new window)