---
title: "Working With TiDB Using JDBC"
version: "v2"
status: "WIP"
description: "In this lab, you will learn how to: Connect to, access and modify data in TiDB Cloud Serverless cluster by Java; Use prepared statements and batch inserts to improve the performance of your application; Use AUTO_RANDOM instead of AUTO_INCREMENT to avoid hotspots; Use online DDL to perform online schema changes; Use and compare pessimistic and optimistic modes for transaction concurrency control."
---

# Working With TiDB Using JDBC

## Overview

TiDB is a distributed, cloud-native, and horizontally scalable database that supports hybrid transactional and analytical processing (HTAP). For application developers, TiDB is a database that is always available, reliable, and capable of scaling without limits.

MySQL Connector/J is the official JDBC driver implementation for MySQL. TiDB is compatible with MySQL protocol. In this lab, you will enjoy the convenience of working with TiDB using MySQL Connector/J. 

+ Concretely, in this lab, you will learn how to:
  + Connect to, access and modify data in TiDB Cloud Serverless cluster by Java code.
  + Use prepared statements and batch inserts to improve the performance of your application.
  + Use `AUTO_RANDOM` instead of `AUTO_INCREMENT` to avoid hotspots.
  + Use online DDL to add columns to a table without blocking or interrupting ongoing transactions.
  + Use and compare pessimistic and optimistic modes for concurrency control.

### Duration

+ This lab should take you approximately 90 minutes to complete.

> [!NOTE]
> In this lab, you will work with a web-based VS Code. Using a web-based IDE is NOT compulsory in TiDB. However, it is a common approach in cloud database collaborations. By deploying a developer client close to the cloud database, we can better simulate the application server's real-world behavior and network conditions.

> [!TIP]
> Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**.

## Module 1: Prepare the Environment

### Step 1. Open a terminal in IDE

+ Click [here](${LAB:SECURITY_ACCESS_PROXY_URL}?next=${LAB:"/vscode/?folder=/root/tidb-lab-java"|urlencode}) to open the VS Code environment for this lab in a new browser window.

+ This operation may raises a prompt on your browser, saying `xx-xxx-xxx-xx.sslip.io wants to see text and images copied to the clipboard`. Please click `Allow`.

+ Click `Yes, I trust the authors` in the popup window.

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

+ Click on `≡`, `Terminal`, then `New Terminal` to open a new terminal in the VS Code.

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

### Step 2. Update the env.sh file

+ Find the `env.sh` file under folder `tidb-lab-java/`, update the TiDB Serverless credentials, and save it.

  ```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 the `env.sh` file in the terminal.
  ```bash
  source env.sh
  ```

+ Validate the environment settings
  
  ```bash
  echo $TIDB_USER
  ```

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

  ```bash
  echo $TIDB_PASSWORD
  ```

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

## Module 2: TiDB Basic Operations using Java

Run the following Java code to connect to TiDB Cloud Serverless cluster and do some basic operations.

### Step 1. Connect to TiDB Cloud Serverless cluster

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcConnectionServerless.java` file. 
  
+ The following Java code snippet in the `DemoJdbcConnectionServerless.java` connects you to the lab TiDB Cloud Serverless cluster.

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

+ You can now execute the code, and see the results.

  ```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.
  ```

### Step 2. Basic CRUD operations

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcExecute.java` file, you can briefly go through the source code. 
  
+ In the following code snippet, we show you one way of creating a table, and then inserting and querying the data.

  ```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);
  ...
  ```
  + Note that, for simplicity, we `SET tidb_multi_statement_mode='ON'` in the code, and executes two SQL statements in the one line. Please understand the security risks (such as SQL injections).

+ You can now execute the code, and see the results.

  ```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.
  ```

## Module 3: Using Prepared Statements and Batch Inserts

Using prepared statements, you can precompile the statements for repeated executions. Prepared statements can help us prevent SQL injections. Furthermore, prepared statements may improve the query execution performance when the query is long and complicated, and/or the network is the bottleneck.

Batch inserts enables the users to insert multiple rows of data in a single SQL statement. 

In this module, we look at examples for prepared statements and batch inserts.

### Step 1. Using prepared statements

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcPreparedStatement.java` file, and read the code file. 

+ There are two ways of using prepared statements. The first code snippet below shows the server side prepared statement. The second code block demonstrates client side prepared statements. In our example, you should see that the execution time is similar.

  ```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();
  }
  ``` 

+ You can now execute the code, and see the results.

  ```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.
  ```

### Step 2. Using batch inserts

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcBatchInsert.java` file.

+ In this step, we will insert 1000 rows into the table. We will compare the performance between `rewriteBatchedStatements=true` and `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.
  ```

## Module 4: Use AUTO_RANDOM Instead of AUTO_INCREMENT To Avoid Hotspots

+ In a distributed database like TiDB, data is divided into regions and distributed across multiple storage nodes (TiKV nodes).

+ Using `AUTO_INCREMENT` as the primary key may leads to the following issue:
  + When using `AUTO_INCREMENT`, IDs are sequential (1, 2, 3, 4, ...)
  + New inserts all go to the same region where the latest IDs are stored
  + This creates a "hotspot" where one storage node handles most write traffic
  + The rest of the storage nodes are underutilized
  + This bottleneck can severely limit write throughput

+ `AUTO_RANDOM` is the rescue:
  + `AUTO_RANDOM` generates non-sequential, distributed IDs
  + The shard bits help distribute writes across different regions
  + The INSERT workload is balanced across all storage nodes
  + This improves write throughput and scalability for high-concurrency scenarios

  ```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
  ...
  ```

## Module 5: Use Online DDL To Perform Online Schema Changes

+ This module illustrates how to perform **online schema changes (DDL operations)** in **TiDB** while ensuring that an ongoing **INSERT job** continues running without interruption. A key challenge in this process is handling **error code 8028**, which occurs when schema mutations temporarily block transactions. The demo shows a robust approach to managing this scenario, allowing the **insert job** to recover and continue despite the schema change.

+ This module consists of two main operations:

1. **Insert Job (`--insert`)**
   + Continuously inserts new records into a specified table.
   + Handles potential schema mutation exception (error code **8028**) that may occur due to concurrent DDL operations.
   + Retries the insert operation in case of failure to maintain service availability.

2. **Schema Modification (`--alter`)**
   + Executes an `ALTER TABLE` command to modify the table structure while inserts are in progress.
   + Demonstrates that TiDB allows **online schema changes**, but transactions may momentarily fail due to schema mutations.
   + Ensures that the new schema is successfully applied while ongoing transactions adapt accordingly.

> [!TIP]
> **Key Implementation Details:**
>
> + **Handling TiDB Metadata Lock**: The script **disables metadata locking** (`SET GLOBAL tidb_enable_metadata_lock = OFF`) to allow DDL changes without blocking queries.
> + **Automatic Error Handling**: The insert job **detects error code 8028** and **retries failed transactions** to prevent crashes.
> + **Concurrent Execution**: The script enables running the **insert job** and **schema modification** in separate terminals, simulating a real-world scenario where database updates occur alongside ongoing transactions.

+ Run the insert job first.

  ```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
  ...
  ```

+ Create a new terminal by clicking the `+` button in the VS Code.

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

+ Run the schema modification in the new terminal.

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

+ On VS Code, switch back to the first terminal, we can see that the insert job continues with a retry log.

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

+ Neither the insert DML nor the DDL operation are blocked.

+ You can now kill the running insertion program using `CTRL + c` in the terminal.

## Module 6: Concurrency Control - Pessimistic Mode VS Optimistic Mode

In all database that supports concurrent access, there's a risk of conflicts, where multiple transactions may attempt to update the same data simultaneously. While a well-designed data model can minimize the chances of such conflicts, they remain a possibility and must be effectively managed. Databases offer mechanisms to handle concurrent data access, with the two primary approaches being pessimistic and optimistic concurrency control. TiDB supports both approaches.

This module shows both pessimistic lock and optimistic lock behaviors in TiDB.

### Step 1. Read the pessimistic mode demo code

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcPessimisticLock.java` file.

+ In the following code snippet, two threads are started. Each thread will create a connection to TiDB.

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

+ In the main method, two threads will be initiated and try to update the same record. Both thread 1 and 2 have auto commit disabled.

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

### Step 2. Run the demo code in pessimistic mode

+ Run the demo code in pessimistic mode and observe the output. Note that thread two is not able to commit until thread 1 commits. One possible output is shown.

  ```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
  ```

### Step 3. Read the optimistic mode demo code

+ On the left, in the **EXPLORER** section of the VS Code, click `DemoJdbcOptimisticLock.java` file.

+ Similar to the code in pessimistic mode, two threads are started and try to update the same row simultaneously.

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

+ In the case of conflicts, a simple retry is implemented.
  
  ```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();
    }
  }
  ```

### Step 4. Run the demo in optimistic mode

+ You can run the demo code for optimistic lock using the following command. We first run the code with `no-retry`, and then with `retry`. Possible outputs are shown.
  
  ```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
  ```

### Step 5. Go to the TiDB Cloud Console to start your own journey

+ The hands-on lab ends here. You can now start your TiDB Cloud journey [here](https://tidbcloud.com/clusters). If you had been using your own free-tier cluster for this lab, you'll find it listed on the same page, the cluster name is ${LAB:SERVERLESS_CLUSTER_NAME}. 

## Conclusion

In this lab, you have learned how to connect to TiDB Cluster by Java. You have also learned how to use JDBC library to interact with TiDB. Include connect, insert, query one and query many.

Then, we went through the prepared statements and batch inserts to improve the performance of your application.

After that, we learned how to use `AUTO_RANDOM` to avoid hotspots and how to perform an online schema change without interrupting business.

Finally, we showed you the pessimistic lock and optimistic lock for concurrency control in TiDB.

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