从01开始
2023-03-24
目录

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
1

假设数据库运行在本机localhost​，端口使用标准的3306​，数据库名称是learnjdbc​，那么URL如下：

jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8
1

后面的两个参数表示不使用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();
1
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)) {
    ...
}
1
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();
    }
}
1
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)
1
2
3
4
5
6
7
8
9
10

‍

‍

# JDBC查询

获取到JDBC连接后，下一步我们就可以查询数据库了。查询数据库分以下几步：

  1. 通过Connection​提供的createStatement()​方法创建一个Statement​对象，用于执行一个查询；
  2. 第二步，执行Statement​对象提供的executeQuery("SELECT * FROM students")​并传入SQL语句，执行查询并获得返回的结果集，使用ResultSet​来引用这个结果集；
  3. 第三步，反复调用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();
    }
}
1
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
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 + "'");
    ...
}
1
2
3
4
5

其中，参数name​和pass​通常都是Web页面输入后由程序接收到的。

‍

如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"​，pass = "1234"​：

SELECT * FROM user WHERE login='bob' AND pass='1234'
1

‍

‍

但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass="​, pass = " OR pass='"​：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''
1

这个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);
    ...
}
1
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();
    }
}
1
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
1
2

使用PreparedStatement​和Statement​稍有不同，必须首先调用setObject()​设置每个占位符?​的值，最后获取的仍然是ResultSet​对象。

‍

‍

# 模糊查询写法

有时候我们需要用到模糊查询，也就是like。我们第一反应可能是这样写：

String sql = "select * from students where name like %小%";
1

这是错误的写法，因为%是特殊字符，正确写法：

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 '"+ "小" + "%'"
1
2
3
4
5
6
7
8

‍

‍

如果使用PreparedStatement：

PreparedStatement statement2 = conn.prepareStatement("select * from students where name like ?");
statement2.setObject(1, "%小%");
1
2

‍

# 存储过程

有时候我们会用到存储过程，调用存储过程语法如下：

CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, "dylan thomas");
proc.setInt(2, 69);
cs.execute();
1
2
3
4

‍

# 总结

JDBC接口的Connection​代表一个JDBC连接；

使用JDBC查询的步骤如下：

  1. 确定JDBC连接字符串
  2. 使用DriverManager类创建Connection对象
  3. 创建Statement对象
  4. 配置要执行的SQL，并使用executeQuery​方法来执行SQL
  5. 反复调用ResultSet​的next()​方法并读取每一行结果
  6. 注意关闭资源

‍

每个对象的作用：

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

‍

在GitHub上编辑此页 (opens new window)
上次更新: 2023/4/12 09:09:14
什么是JDBC
JDBC连接字符串

← JDBC连接字符串→

Theme by Vdoing | Copyright © 2022-2023 粤ICP备2022067627号-1 粤公网安备 44011302003646号