---
title: "使用JDBC操作TiDB"
version: "v2"
status: "WIP"
description: "在本实验中，您将学习如何：使用Java连接、访问和修改TiDB Cloud Serverless集群中的数据；使用预处理语句和批量插入来提高应用程序性能；使用AUTO_RANDOM而不是AUTO_INCREMENT来避免热点；使用在线DDL执行在线模式更改；使用和比较悲观模式和乐观模式进行事务并发控制。"
---

# 使用JDBC操作TiDB

## 概述

TiDB是一个分布式、云原生、水平可扩展的数据库，支持混合事务和分析处理（HTAP）。对于应用程序开发人员来说，TiDB是一个始终可用、可靠且能够无限扩展的数据库。

Connector/J是MySQL兼容数据库的官方JDBC驱动程序实现。在本实验中，您将体验使用Connector/J操作TiDB的便利性。

+ 具体来说，在本实验中，您将学习如何：
  + 使用Java代码连接、访问和修改TiDB Cloud Serverless集群中的数据。
  + 使用预处理语句和批量插入来提高应用程序性能。
  + 使用`AUTO_RANDOM`而不是`AUTO_INCREMENT`来避免热点。
  + 使用在线DDL在不阻塞或不中断正在进行的交易的情况下向表添加列。
  + 使用和比较悲观模式和乐观模式进行并发控制。

### 所需时间

+ 本实验大约需要90分钟完成。

> [!NOTE]
> 在本实验中，您将使用基于Web的VS Code。在TiDB中使用基于Web的IDE不是强制性的。但是，这是云数据库协作中的常见方法。通过在云数据库附近部署开发客户端，我们可以更好地模拟应用程序服务器的真实行为和网络条件。

> [!TIP]
> 实验环境部署完成后，点击底部栏的**Next**开始**模块1**。

## 模块1：准备环境

### 步骤1. 在IDE中打开终端

+ 点击[这里](${LAB:SECURITY_ACCESS_PROXY_URL}?next=${LAB:"/vscode/?folder=/root/tidb-lab-java"|urlencode})在新浏览器窗口中打开本实验的VS Code环境。

+ 此操作可能会在您的浏览器上显示提示，说`xx-xxx-xxx-xx.sslip.io wants to see text and images copied to the clipboard`。请点击`Allow`。

+ 在弹出窗口中点击`Yes, I trust the authors`。

  ![90%](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/vscode-trust.jpg)

+ 点击`≡`、`Terminal`，然后点击`New Terminal`在VS Code中打开新终端。

  ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-open-terminal-0307.jpg)

### 步骤2. 更新env.sh文件

+ 在`tidb-lab-java/`文件夹下找到`env.sh`文件，更新TiDB Serverless凭据并保存。

  ```properties
  #!/bin/sh

  ## You only need to change this part
  export TIDB_HOST=${LAB:SERVERLESS_CLUSTER_HOST}
  export TIDB_PORT=${LAB:SERVERLESS_CLUSTER_PORT}
  export TIDB_USER='${LAB:SERVERLESS_CLUSTER_USERNAME}'
  export TIDB_PASSWORD='${LAB:SERVERLESS_CLUSTER_PASSWORD}'
  export TIDB_DB_NAME=${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  export USE_SSL='true'

  ## Do not change the following part
  jdbc_url="jdbc:mysql://${TIDB_HOST}:${TIDB_PORT}/${TIDB_DB_NAME}"
  if [ 'true' = "${USE_SSL}" ]; then
    jdbc_url="${jdbc_url}?sslMode=VERIFY_IDENTITY&enabledTLSProtocols=TLSv1.2,TLSv1.3"
  fi

  export TIDB_JDBC_URL=${jdbc_url}
  ```

+ 在终端中 `source env.sh`。
  ```bash
  source env.sh
  ```

+ 验证环境设置
  
  ```bash
  echo $TIDB_USER
  ```

  ```output
  '${LAB:SERVERLESS_CLUSTER_USERNAME}'
  ```

  ```bash
  echo $TIDB_PASSWORD
  ```

  ```output
  '${LAB:SERVERLESS_CLUSTER_PASSWORD}'
  ```

## 模块2：使用Java进行TiDB基本操作

运行以下Java代码连接到TiDB Cloud Serverless集群并执行一些基本操作。

### 步骤1. 连接到TiDB Cloud Serverless集群

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcConnectionServerless.java`文件。
  
+ `DemoJdbcConnectionServerless.java`中的以下Java代码片段将您连接到实验的TiDB Cloud Serverless集群。

  ```java
  ... 
  String tidbHost = System.getenv().getOrDefault("TIDB_HOST", "localhost");
  int tidbPort = Integer.parseInt(System.getenv().getOrDefault("TIDB_PORT", "4000"));
  String tidbUser = System.getenv().getOrDefault("TIDB_USER", "root");
  String tidbPassword = System.getenv().getOrDefault("TIDB_PASSWORD", "");
  String tidbDatabase = System.getenv().getOrDefault("TIDB_DATABASE", "test");
  
  String connectionUrl = "jdbc:mysql://" + tidbHost + ":" + tidbPort + "/" + tidbDatabase + 
          "?sslMode=VERIFY_IDENTITY&enabledTLSProtocols=TLSv1.2,TLSv1.3";

  connection = DriverManager.getConnection(connectionUrl, tidbUser, tidbPassword);
  ...
  ```

+ 您现在可以执行代码并查看结果。

  ```bash
  rm -f DemoJdbcConnectionServerless.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcConnectionServerless.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcConnectionServerless
  ```
  ```output
  Connection established.
  Connection closed.
  ```

### 步骤2. 基本CRUD操作

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcExecute.java`文件，您可以简要浏览源代码。
  
+ 在以下代码片段中，我们向您展示了一种创建表，然后插入和查询数据的方法。

  ```java
  ...
  // Create table
  connection.createStatement().executeUpdate("CREATE TABLE t1 (id int PRIMARY KEY, name char(4))");

  // Describe table
  printResultSetStringString("DESCRIBE test.t1", connection);

  // Explain SQL
  printResultSetStringString("EXPLAIN SELECT * FROM test.t1", connection);

  // Select
  printResultSetStringString("SELECT * FROM test.t1", connection);

  // Try DML
  printResultSetStringString("INSERT INTO test.t1 VALUES (100, 'WXYZ'); INSERT INTO test.t1 VALUES (200, 'ABCD')", connection);

  // Select again
  printResultSetStringString("SELECT * FROM test.t1", connection);
  ...
  ```
  + 请注意，为了简单起见，我们在代码中设置了`SET tidb_multi_statement_mode='ON'`，并在同一行执行两个SQL语句。请了解安全风险（如SQL注入）。

+ 您现在可以执行代码并查看结果。

  ```bash
  rm -f DemoJdbcExecute.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcExecute.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcExecute
  ```

  ```output
  Connection established.

  /* Executing: SET tidb_multi_statement_mode='ON'; */

  /* Executing: show variables like 'autocommit'; */
          Row#,  Variable_name, Value
          1) autocommit, ON

  /* Executing: describe test.t1; */
          Row#,  Field, Type
          1) id, int(11)
          2) name, char(4)

  /* Executing: explain select * from test.t1; */
          Row#,  id, estRows
          1) TableReader_5, 10000.00
          2) └─TableFullScan_4, 10000.00

  /* Executing: select * from test.t1; */
          Row#,  id, name

  /* Executing: insert into test.t1 values (100, 'WXYZ'); insert into test.t1 values (200, 'ABCD'); */

  /* Executing: select * from test.t1; */
          Row#,  id, name
          1) 100, WXYZ
          2) 200, ABCD
  Connection closed.
  ```

## 模块3：使用预处理语句和批量插入

使用预处理语句，您可以预编译语句以重复执行。预处理语句可以帮助我们防止SQL注入。此外，当查询很长且复杂，和/或网络是瓶颈时，预处理语句可能会提高查询执行性能。

批量插入使用户能够在单个SQL语句中插入多行数据。

在本模块中，我们查看预处理语句和批量插入的示例。

### 步骤1. 使用预处理语句

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcPreparedStatement.java`文件并阅读代码文件。

+ 使用预处理语句有两种方法。下面的第一个代码片段显示服务器端预处理语句。第二个代码块演示客户端预处理语句。在我们的示例中，您应该看到执行时间相似。

  ```java
  // Reuse PS
  connection.setAutoCommit(true);
  PreparedStatement update1_ps = connection.prepareStatement("UPDATE test.t1 SET name = ? WHERE id = 1");
  System.out.println(">>> Reuse PS Begin repeating update.");
  long s1 = System.currentTimeMillis();
  for (int i = 0; i < 200; i++) {
      update1_ps.setString(1, Integer.toString(i));
      update1_ps.executeUpdate();
  }
  update1_ps.close();
  ```

  ```java
  /**
  * Client side caching prepared statement, cache hit for prepareStatement and close.
  * Try set cachePrepStmts=false|true to see the difference on elapsed time.
  */
  PreparedStatement update2_ps = null;
  System.out.println(">>> Non-Reuse PS Begin repeating update.");
  long s2 = System.currentTimeMillis();
  for (int i = 0; i < 200; i++) {
      update2_ps = connection.prepareStatement("UPDATE test.t1 SET name = ? WHERE id = 1");
      update2_ps.setString(1, Integer.toString(i));
      update2_ps.executeUpdate();
      update2_ps.close();
  }
  ``` 

+ 您现在可以执行代码并查看结果。

  ```bash
  rm -f DemoJdbcPreparedStatement.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcPreparedStatement.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcPreparedStatement
  ```
  ```output
  Connection established.
  >>> Reuse PS Begin repeating update.
  >>> End repeating update, elapsed: 4364(ms).
  >>> Non-Reuse PS Begin repeating update.
  >>> End repeating update, elapsed: 4225(ms).

  Executing query: select * from test.t1
          Row#, id, name
          1) 1, 199
  Turn on autocommit.
  Connection closed.
  ```

### 步骤2. 使用批量插入

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcBatchInsert.java`文件。

+ 在此步骤中，我们将向表中插入1000行。我们将比较`rewriteBatchedStatements=true`和`rewriteBatchedStatements=false`之间的性能。

  ```bash
  rm -f DemoJdbcBatchInsert.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcBatchInsert.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcBatchInsert
  ```
  ```output
  Connection established.
  >>> Begin insert 1000 rows.
  >>> End batch insert,rewriteBatchedStatements=true,elapsed: 172 (ms).
  Connection established.
  >>> Begin insert 1000 rows.
  >>> End batch insert,rewriteBatchedStatements=false,elapsed: 13252 (ms).

  /* Executing query: select count(*), max(name) from test.t1_batchtest; */
          Row#, count(*), max(name)
          1) 1000, 999
  Turn on autocommit.
  Connection closed.
  ```

## 模块4：使用AUTO_RANDOM而不是AUTO_INCREMENT来避免热点

+ 在像TiDB这样的分布式数据库中，数据被分成区域并分布在多个存储节点（TiKV节点）上。

+ 使用`AUTO_INCREMENT`作为主键可能会导致以下问题：
  + 使用`AUTO_INCREMENT`时，ID是连续的（1、2、3、4、...）
  + 新的插入都转到存储最新ID的同一区域
  + 这创建了一个"热点"，其中一个存储节点处理大部分写入流量
  + 其余存储节点未得到充分利用
  + 这个瓶颈可能会严重限制写入吞吐量

+ `AUTO_RANDOM`是解决方案：
  + `AUTO_RANDOM`生成非连续、分布式的ID
  + 分片位有助于在不同区域间分布写入
  + INSERT工作负载在所有存储节点间平衡
  + 这提高了高并发场景下的写入吞吐量和可扩展性

  ```bash
  rm -f DemoJdbcAutoRandom.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcAutoRandom.java       
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcAutoRandom
  ```
  ```output
  ...
  AUTO_RANDOM IDs (distributed to avoid hotspots):
  288230376151711745
  1441151880758558730
  3746994889972252677
  4323455642275676162
  4899916394579099654
  4899916394579099655
  6052837899185946628
  7493989779944505347
  7782220156096217097
  8070450532247928840

  AUTO_INCREMENT IDs (sequential, can cause hotspots):
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  ...
  ```

## 模块5：使用在线DDL执行在线模式更改

+ 本模块演示如何在**TiDB**中执行**在线模式更改（DDL操作）**，同时确保正在进行的**INSERT作业**继续运行而不中断。此过程中的一个关键挑战是处理**错误代码8028**，当模式变更暂时阻塞事务时会发生此错误。演示展示了一种管理此场景的健壮方法，允许**插入作业**在模式变更后恢复并继续。

+ 本模块由两个主要操作组成：

1. **插入作业（`--insert`）**
   + 持续向指定表插入新记录。
   + 处理由于并发DDL操作可能发生的潜在模式变更异常（错误代码**8028**）。
   + 在失败时重试插入操作以维持服务可用性。

2. **模式修改（`--alter`）**
   + 在插入进行时执行`ALTER TABLE`命令来修改表结构。
   + 演示TiDB允许**在线模式更改**，但事务可能由于模式变更而暂时失败。
   + 确保新模式成功应用，同时正在进行的交易相应适应。

> [!TIP]
> **关键实现细节：**
>
> + **处理TiDB元数据锁**：脚本**禁用元数据锁定**（`SET GLOBAL tidb_enable_metadata_lock = OFF`）以允许DDL更改而不阻塞查询。
> + **自动错误处理**：插入作业**检测错误代码8028**并**重试失败的事务**以防止崩溃。
> + **并发执行**：脚本允许在单独的终端中运行**插入作业**和**模式修改**，模拟数据库更新与正在进行的交易同时发生的真实场景。

+ 首先运行插入作业。

  ```bash
  rm -f DemoJdbcOnlineDDL.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL.java  
  sleep 1   
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL --insert
  ```
  ```output
  ...
  Inserting record 0
  Inserting record 1
  Inserting record 2
  Inserting record 3
  Inserting record 4
  Inserting record 5
  ...
  ```

+ 通过点击VS Code中的`+`按钮创建新终端。

    [comment]: <![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-add-terminal-0319.jpg)>

+ 在新终端中运行模式修改。

  ```bash
  source env.sh
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL --alter
  ```
  ```output
  Executing ALTER TABLE on online_ddl_test
  Connected to TiDB: xxxxxx.xxxxxx@gateway01.us-west-2.prod.aws.tidbcloud.com:4000 using database test
  Table online_ddl_test exists, proceeding with ALTER TABLE
  Executing: ALTER TABLE online_ddl_test ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A')
  ALTER TABLE completed successfully in 0.39 seconds

  Updated table structure:
    id: int(11)
    k: int(11)
    c: char(120)
    pad: char(60)
    ed: varchar(10)
  ```

+ 在VS Code中，切换回第一个终端，我们可以看到插入作业继续运行并显示重试日志。

  ```output
  ...
  Inserting record 105
  Inserting record 106
  Schema mutation encountered, retrying...
  Inserting record 107
  Inserting record 108
  ...
  ```

+ 插入DML和DDL操作都没有被阻塞。

+ 您现在可以使用终端中的`CTRL + c`终止正在运行的插入程序。

## 模块6：并发控制 - 悲观模式 vs 乐观模式

在所有支持并发访问的数据库中，都存在冲突的风险，多个事务可能同时尝试更新相同的数据。虽然设计良好的数据模型可以最小化此类冲突的可能性，但它们仍然存在可能性并且必须得到有效管理。数据库提供处理并发数据访问的机制，两种主要方法是悲观和乐观并发控制。TiDB支持这两种方法。

本模块展示TiDB中悲观锁和乐观锁的行为。

### 步骤1. 阅读悲观模式演示代码

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcPessimisticLock.java`文件。

+ 在以下代码片段中，启动了两个线程。每个线程将创建到TiDB的连接。

  ```output
  ...
  for (int i = 0; i < 2; i++) {
      connections.add(DriverManager.getConnection(connection_string));
  }
  ...
  ```

+ 在main方法中，将启动两个线程并尝试更新同一条记录。线程1和线程2都禁用了自动提交。

  ```java
  new Thread(new DemoJdbcPessimisticLock.RowUpdater(0, id, 1, 6000)).start();
  new Thread(new DemoJdbcPessimisticLock.RowUpdater(1, id, 1000, 2000)).start();
  ```

### 步骤2. 在悲观模式下运行演示代码

+ 在悲观模式下运行演示代码并观察输出。注意线程2在线程1提交之前无法提交。显示了一个可能的输出。

  ```bash
  rm -f DemoJdbcTxPessimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcPessimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcPessimisticLock
  ```
  ```output
  Connection established.
  Connection B session started
  Connection A session started
  Connection A session: BEGIN PESSIMISTIC
  Connection A session: UPDATE test_tx_pessimistic SET name = 'Connection A' WHERE id = 5188146770730811393
  Connection B session: BEGIN PESSIMISTIC
  Connection B session: UPDATE test_tx_pessimistic SET name = 'Connection B' WHERE id = 5188146770730811393
  Connection A session: Commit
  Connection A session: Checking result

  /* Executing query: select id, name from test_tx_pessimistic; */
          Row#, id, name
          1) 5188146770730811393, Connection A

  Connection B session: Commit
  Connection B session: Checking result

  /* Executing query: select id, name from test_tx_pessimistic; */
          Row#, id, name
          1) 5188146770730811393, Connection B
  ```

### 步骤3. 阅读乐观模式演示代码

+ 在左侧VS Code的**EXPLORER**部分，点击`DemoJdbcOptimisticLock.java`文件。

+ 与悲观模式中的代码类似，启动两个线程并尝试同时更新同一行。

  ```java
  new Thread(new DemoJdbcOptimisticLock.RowUpdater(0, id, retryCommit, 1, 6000, 1000)).start();
  new Thread(new DemoJdbcOptimisticLock.RowUpdater(1, id, retryCommit, 1000, 2000, 9000)).start();
  ```

+ 在冲突的情况下，实现了简单的重试。
  
  ```java
  if (this.retryCommit) {
    try {
        Thread.sleep(this.waitBefore2ndCommit);
    } catch (InterruptedException ie) {
        ie.printStackTrace();
    }
    try {
        System.out.println(connectionTags[this.connectionNo] + " session: " + "Commit");
        Statement s = c.createStatement();
        s.executeUpdate("UPDATE test_tx_optimistic SET name = '" + connectionTags[this.connectionNo]
                + "' WHERE id = " + rowid);
        c.commit();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
  }
  ```

### 步骤4. 在乐观模式下运行演示

+ 您可以使用以下命令运行乐观锁的演示代码。我们首先使用`no-retry`运行代码，然后使用`retry`。显示了可能的输出。
  
  ```bash
  rm -f DemoJdbcOptimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock no-retry
  ``` 

  ```output
  Connection established.
  Connection A session started
  Connection B session started
  Connection A session: BEGIN OPTIMISTIC
  Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 4899916394579099649
  Connection B session: BEGIN OPTIMISTIC
  Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 4899916394579099649
  Connection B session: Commit
  Connection B session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 4899916394579099649, Connection B

  Connection A session: Commit
  Connection A ErrorCode: 9007
  Connection A SQLState: HY000
  Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=458625318024118280, conflictStartTS=458625318286262286, conflictCommitTS=458625318889193486, key=????, reason=Optimistic [try again later]
  < Session in Connection A RAISED THE EXCEPTION !!! >
  Connection A session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 4899916394579099649, Connection B
  ```

  ```bash
  rm -f DemoJdbcOptimisticLock.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock.java
  sleep 1
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOptimisticLock retry
  ```

  ```output
  Connection established.
  Connection A session started
  Connection B session started
  Connection A session: BEGIN OPTIMISTIC
  Connection A session: UPDATE test_tx_optimistic SET name = 'Connection A' WHERE id = 2017612633061982209
  Connection B session: BEGIN OPTIMISTIC
  Connection B session: UPDATE test_tx_optimistic SET name = 'Connection B' WHERE id = 2017612633061982209
  Connection B session: Commit
  Connection B session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 2017612633061982209, Connection B

  Connection A session: Commit
  Connection A ErrorCode: 9007
  Connection A SQLState: HY000
  Connection A Error: java.sql.SQLException: Write conflict, txnStartTS=458625338366492677, conflictStartTS=458625338628898820, conflictCommitTS=458625339231567877, key=????, reason=Optimistic [try again later]
  < Session in Connection A RAISED THE EXCEPTION !!! >
  Connection A session: Commit
  Connection A session: Checking result

  /* Executing query: select id, name from test_tx_optimistic; */
          Row#, id, name
          1) 2017612633061982209, Connection A
  ```

### 步骤5. 转到TiDB Cloud控制台开始您自己的旅程

+ 动手实验到此结束。您现在可以[在这里](https://tidbcloud.com/clusters)开始您的TiDB Cloud旅程。如果您在本实验中使用了自己的免费层集群，您会在同一页面上找到它，集群名称是${LAB:SERVERLESS_CLUSTER_NAME}。

## 结论

在本实验中，您学习了如何使用Java代码连接到 TiDB 集群。您还学习了如何使用 JDBC 库与TiDB交互。包括连接、插入、查询一个和查询多个。

然后，我们通过预处理语句和批量插入来提高应用程序的性能。

之后，我们学习了如何使用`AUTO_RANDOM`来避免热点，以及如何在不中断业务的情况下执行在线模式更改。

最后，我们向您展示了TiDB中用于并发控制的悲观锁和乐观锁。

```hbsptform
{
  "ui": {
    "titleLevel": 3
  },
  "portalId": "4466002",
  "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf",
  "region": "na1",
  "sfdcCampaignId": "701RC00000VqCODYA3"
}
```