knowledge

JDBC 操作数据库

  • JDBC 使用
  • SQL 注入攻击的应对
  • 数据库查询方法
  • 数据库写入方法
  • 连接池的使用
  • DBUtils
  • Apache Commons DBUtils

JDBC 使用

全称：Java DataBase Connectivity，Java 数据库连接，是 Java 语言操作数据库的一种规范，是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。

1. 加载并注册数据库 JDBC 驱动

下载数据库 jar 包，添加到项目文件夹lib中，在开发工具中选择File -> Project Structure -> Modules -> Dependencies添加 jar 包。 代码中使用 Class.forName() 方法加载并注册数据库驱动，如：

Class.forName("com.mysql.cj.jdbc.Driver");
1

2. 创建数据库连接

String dbURL = "jdbc:mysql://localhost:3306/imooc_java?useSSL=false&useUnicode=true&characterEncoding=UTF-8&severTimezone=Aisa/Shanghai&allowPublicKeyRetrieval=true";
String dbUsername = "root";
String dbPassword = "123456";
Connection conn = DriverManager.getConnection(dbURL,dbUsername, dbPassword);
1
2
3
4

3. 创建 Statement 对象

用来创建 SQL 语句

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from employee where dname='研发部'");
1
2

4. 遍历查询结果

while (rs.next()) {
  Integer eno = rs.getInt(1);
  String ename = rs.getString("ename");
  Float salary = rs.getFloat("salary");
  String dname = rs.getString("dname");
  System.out.println(eno + ", " + ename + ", " + salary + ", " + dname);
}
1
2
3
4
5
6
7

5. 关闭连接，释放资源

conn.close();
1

JDBC 驱动程序

需要从各大数据库厂商的官网下载对应数据库的驱动程序。

SQL 注入攻击的应对

解决方法：使用 PreparedStatement 对象代替 Statement

String sql = "select * from employee where ename=? and salary=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, ename);
pstmt.setFloat(2, salary);
ResultSet rs = pstmt.executeQuery();
1
2
3
4
5

数据库写入方法

INSERT

所有写操作都是使用executeUpdate方法，返回值是int类型，表示影响的行数。

Connection conn = DBUtils.getConnection();
String sql = "insert into employee(eno, ename, salary, dname) values(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, eno);
pstmt.setString(2, ename);
pstmt.setFloat(3, salary);
pstmt.setString(4, dname);
int cnt = pstmt.executeUpdate();
System.out.println("cnt: " + cnt);
1
2
3
4
5
6
7
8
9

UPDATE

Connection conn = DBUtils.getConnection();
String sql = "update employee set salary=? where eno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1, salary);
pstmt.setInt(2, eno);
int cnt = pstmt.executeUpdate();
System.out.println("cnt: " + cnt);
1
2
3
4
5
6
7

DBUtils

封装 DBUtils 工具方法。

创建新的数据库连接，和关闭数据库连接。

Apache Commons DBUtils

Apache Commons DBUtils 是 Apache 组织提供的一个开源 JDBC 工具类库，它是对 JDBC 的简单封装，学习成本极低，并且使用 DBUtils 可以极大简化 JDBC 编码的工作量，同时也不会影响程序的性能。

事务机制

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。不能只执行其中的一部分操作。

使用场景，比如银行转账，需要保证转账的两个操作要么都执行，要么都不执行。 批量操作，比如批量插入，需要保证所有数据都插入成功，要么都不插入。如果有一条失败，那就全部回滚。

JDBC 有两种事物模式：

  • 自动提交模式
  • 手动提交模式

自动提交模式

默认模式，每执行一次写操作，就会自动提交一次事务。

此模式无法保证多数据一致性。

手动提交模式

动手提交模式下，需要手动调用commit方法提交事务。或者调用rollback方法回滚事务。

手动提交模式下，可以保证多数据一致性。

Connection conn = null;
PreparedStatement pstmt = null;
try {
  conn = DBUtils.getConnection();
  // 关闭自动提交，开启事务
  conn.setAutoCommit(false);
  String sql = "insert into employee(eno, ename, salary, dname) values(?,?,?,?)";
  for (int i = 100; i < 200; i++) {
    if (i == 105) {
      throw new RuntimeException("插入失败");
    }
    pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, i);
    pstmt.setString(2, "员工" + i);
    pstmt.setFloat(3, 4000);
    pstmt.setString(4, "市场部");
    pstmt.executeUpdate();
  }
  // 提交事务
  conn.commit();
} catch (Exception e) {
  e.printStackTrace();
  try {
    if (conn != null && !conn.isClosed()) {
      // 回滚事务
      conn.rollback();
    }
  } catch (Exception e1) {
    e1.printStackTrace();
  }
}
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
29
30
31

数据库查询方法

  • 分页：limit
  • 时间日期处理
  • 数据批处理

分页：limit

将数据转换成实体类对象保存。

时间日期处理

JDBC 获取日期使用可以使用 java.sql.Date，它继承自 java.util.Date，可以相互兼容。

写入的时候，需要将输入的字符串转换成 java.sql.Date 类型，分为两步：

  1. 将日期字符串转换成 java.util.Date 类型
String strHiredate = in.next();
java.util.Date udHiredata = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
  udHiredata = sdf.parse(strHiredate);
} catch (Exception e) {
  System.out.println("您输入的日期格式有误，格式为：yyyy-MM-dd");
}
1
2
3
4
5
6
7
8
  1. 将 java.util.Date 类型转换成 java.sql.Date 类型
long time = udHiredata.getTime();
java.sql.Date sdHiredate = new java.sql.Date(time);
1
2

数据批处理

  • addBatch：将参数添加到批处理任务
  • executeBatch：执行批处理任务
String sql = "insert into employee(eno, ename, salary, dname) values(?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
for (int i = 200; i < 300; i++) {
  pstmt.setInt(1, i);
  pstmt.setString(2, "员工" + i);
  pstmt.setFloat(3, 4000);
  pstmt.setString(4, "市场部");
  // 将参数添加到批处理任务
  pstmt.addBatch();
}
// 执行批处理任务
pstmt.executeBatch();
1
2
3
4
5
6
7
8
9
10
11
12

连接池的使用

在实际开发中，不会直接使用 JDBC 连接数据库，而是使用连接池。在项目启动时，会创建一定数量的数据库连接，放入连接池中。当需要连接数据库时，直接从连接池中获取连接，使用完毕后，再将连接放回连接池中。

  • 阿里巴巴的 Druid 连接池
  • C3P0 连接池

Druid 连接池

Druid 是阿里巴巴开发的一个开源的数据库连接池，它是目前最好的数据库连接池。

  1. 加载属性文件，下载 Druid 的 jar 包，导入项目中，创建druid-filter.properties配置文件。
Properties properties = new Properties();
String propertyFile = DruidSample.class.getResource("/druid-filter.properties").getPath();
try {
  // 空格会被转义成%20，需要URLDecoder解码，还原成空格
  propertyFile = URLDecoder.decode(propertyFile, "UTF-8");
  properties.load(new FileInputStream(propertyFile));
} catch (Exception e) {
  e.printStackTrace();
}
1
2
3
4
5
6
7
8
9
  1. 获取 DataSource 数据源对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
1
  1. 创建数据库连接对象
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
1
2

常见错误

连接池最大上线为 20，当创建到第 21 个时，程序会卡死。

小技巧，在线上环境可以将最小和最大都设置为同一个值，程序一运行，就分配好所有资源，创建好所有连接，避免后面再重新创建连接。这样对程序管理和性能都有帮助。

initialSize=20
maxActive=20
1
2
Last Updated: 2023/8/2 10:45:34
Contributors: licong96