TiDB 和 Java 的简单 CRUD 应用程序
作者：TiDB 社区干货传送门
- 2022 年 8 月 19 日 北京
本文字数：10763 字
阅读完需：约 35 分钟
本文作者：王琦智
本文档将展示如何使用 TiDB 和 Java 来构造一个简单的 CRUD 应用程序。
注意：
推荐使用 Java 8 及以上版本进行 TiDB 的应用程序的编写。
建议：
如果你希望使用 Spring Boot 进行 TiDB 应用程序的编写，可以查看 Build the TiDB Application using Spring Boot。
第 1 步：启动你的 TiDB 集群
本节将介绍 TiDB 集群的启动方法。
使用 TiDB Cloud 免费集群
使用本地集群
此处将简要叙述启动一个测试集群的过程，若需查看正式环境集群部署，或查看更详细的部署内容，请查阅本地启动 TiDB。
部署本地测试集群
适用场景：利用本地 macOS 或者单机 Linux 环境快速部署 TiDB 测试集群，体验 TiDB 集群的基本架构，以及 TiDB、TiKV、PD、监控等基础组件的运行
下载并安装 TiUP。
{{< copyable “shell-regular” >}}
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
复制代码
声明全局环境变量。
注意：
TiUP 安装完成后会提示对应 profile 文件的绝对路径。在执行以下 source 命令前，需要根据 profile 文件的实际位置修改命令。
{{< copyable “shell-regular” >}}
source .bash_profile
复制代码
在当前 session 执行以下命令启动集群。
直接执行
tiup playground命令会运行最新版本的 TiDB 集群，其中 TiDB、TiKV、PD 和 TiFlash 实例各 1 个：{{< copyable “shell-regular” >}}
也可以指定 TiDB 版本以及各组件实例个数，命令类似于：
{{< copyable “shell-regular” >}}
上述命令会在本地下载并启动某个版本的集群（例如 v5.4.0）。最新版本可以通过执行
tiup list tidb来查看。运行结果将显示集群的访问方式：
注意：
支持 v5.2.0 及以上版本的 TiDB 在 Apple M1 芯片的机器上运行
tiup playground。以这种方式执行的 playground，在结束部署测试后 TiUP 会清理掉原集群数据，重新执行该命令后会得到一个全新的集群。
若希望持久化数据，可以执行 TiUP 的
--tag参数：tiup --tag <your-tag> playground ...，详情参考 TiUP 参考手册。
使用云原生开发环境
基于 Git 的预配置的开发环境: 现在就试试
该环境会自动克隆代码，并通过 TiUP 部署测试集群。
第 2 步：获取代码
git clone https://github.com/pingcap-inc/tidb-example-java.git
复制代码
使用 JDBC
使用 Mybatis（推荐）
使用 Hibernate（推荐）
进入目录 plain-java-jdbc：
cd plain-java-jdbc
复制代码
目录结构如下所示：
.├── Makefile├── plain-java-jdbc.iml├── pom.xml└── src └── main ├── java │ └── com │ └── pingcap │ └── JDBCExample.java └── resources └── dbinit.sql
复制代码
其中，dbinit.sql 为数据表初始化语句：
USE test;DROP TABLE IF EXISTS player;
CREATE TABLE player ( `id` VARCHAR(36), `coins` INTEGER, `goods` INTEGER, PRIMARY KEY (`id`));
复制代码
JDBCExample.java 是 plain-java-jdbc 这个示例程序的主体。因为 TiDB 与 MySQL 协议兼容，因此，需要初始化一个 MySQL 协议的数据源 MysqlDataSource，以此连接到 TiDB。并在其后，初始化 PlayerDAO，用来管理数据对象，进行增删改查等操作。
PlayerDAO 是程序用来管理数据对象的类。其中 DAO 是 Data Access Object 的缩写。在其中定义了一系列数据的操作方法，用来对提供数据的写入能力。
PlayerBean 是数据实体类，为数据库表在程序内的映射。PlayerBean 的每个属性都对应着 player 表的一个字段。
package com.pingcap;
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.*;
/** * Main class for the basic JDBC example. **/public class JDBCExample{ public static class PlayerBean { private String id; private Integer coins; private Integer goods;
public PlayerBean() { }
public PlayerBean(String id, Integer coins, Integer goods) { this.id = id; this.coins = coins; this.goods = goods; }
public String getId() { return id; }
public void setId(String id) { this.id = id; }
public Integer getCoins() { return coins; }
public void setCoins(Integer coins) { this.coins = coins; }
public Integer getGoods() { return goods; }
public void setGoods(Integer goods) { this.goods = goods; }
@Override public String toString() { return String.format(" %-8s => %10s\n %-8s => %10s\n %-8s => %10s\n", "id", this.id, "coins", this.coins, "goods", this.goods); } }
/** * Data access object used by 'ExampleDataSource'. * Example for CURD and bulk insert. */ public static class PlayerDAO { private final MysqlDataSource ds; private final Random rand = new Random();
PlayerDAO(MysqlDataSource ds) { this.ds = ds; }
/** * Create players by passing in a List of PlayerBean. * * @param players Will create players list * @return The number of create accounts */ public int createPlayers(List<PlayerBean> players){ int rows = 0;
Connection connection = null; PreparedStatement preparedStatement = null; try { connection = ds.getConnection(); preparedStatement = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)"); } catch (SQLException e) { System.out.printf("[createPlayers] ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); e.printStackTrace();
return -1; }
try { for (PlayerBean player : players) { preparedStatement.setString(1, player.getId()); preparedStatement.setInt(2, player.getCoins()); preparedStatement.setInt(3, player.getGoods());
preparedStatement.execute(); rows += preparedStatement.getUpdateCount(); } } catch (SQLException e) { System.out.printf("[createPlayers] ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); e.printStackTrace(); } finally { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
System.out.printf("\n[createPlayers]:\n '%s'\n", preparedStatement); return rows; }
/** * Buy goods and transfer funds between one player and another in one transaction. * @param sellId Sell player id. * @param buyId Buy player id. * @param amount Goods amount, if sell player has not enough goods, the trade will break. * @param price Price should pay, if buy player has not enough coins, the trade will break. * * @return The number of effected players. */ public int buyGoods(String sellId, String buyId, Integer amount, Integer price) { int effectPlayers = 0;
Connection connection = null; try { connection = ds.getConnection(); } catch (SQLException e) { System.out.printf("[buyGoods] ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); e.printStackTrace(); return effectPlayers; }
try { connection.setAutoCommit(false);
PreparedStatement playerQuery = connection.prepareStatement("SELECT * FROM player WHERE id=? OR id=? FOR UPDATE"); playerQuery.setString(1, sellId); playerQuery.setString(2, buyId); playerQuery.execute();
PlayerBean sellPlayer = null; PlayerBean buyPlayer = null;
ResultSet playerQueryResultSet = playerQuery.getResultSet(); while (playerQueryResultSet.next()) { PlayerBean player = new PlayerBean( playerQueryResultSet.getString("id"), playerQueryResultSet.getInt("coins"), playerQueryResultSet.getInt("goods") );
System.out.println("\n[buyGoods]:\n 'check goods and coins enough'"); System.out.println(player);
if (sellId.equals(player.getId())) { sellPlayer = player; } else { buyPlayer = player; } }
if (sellPlayer == null || buyPlayer == null) { throw new SQLException("player not exist."); }
if (sellPlayer.getGoods().compareTo(amount) < 0) { throw new SQLException(String.format("sell player %s goods not enough.", sellId)); }
if (buyPlayer.getCoins().compareTo(price) < 0) { throw new SQLException(String.format("buy player %s coins not enough.", buyId)); }
PreparedStatement transfer = connection.prepareStatement("UPDATE player set goods = goods + ?, coins = coins + ? WHERE id=?"); transfer.setInt(1, -amount); transfer.setInt(2, price); transfer.setString(3, sellId); transfer.execute(); effectPlayers += transfer.getUpdateCount();
transfer.setInt(1, amount); transfer.setInt(2, -price); transfer.setString(3, buyId); transfer.execute(); effectPlayers += transfer.getUpdateCount();
connection.commit();
System.out.println("\n[buyGoods]:\n 'trade success'"); } catch (SQLException e) { System.out.printf("[buyGoods] ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage());
try { System.out.println("[buyGoods] Rollback");
connection.rollback(); } catch (SQLException ex) { // do nothing } } finally { try { connection.close(); } catch (SQLException e) { // do nothing } }
return effectPlayers; }
/** * Get the player info by id. * * @param id Player id. * @return The player of this id. */ public PlayerBean getPlayer(String id) { PlayerBean player = null;
try (Connection connection = ds.getConnection()) { PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM player WHERE id = ?"); preparedStatement.setString(1, id); preparedStatement.execute();
ResultSet res = preparedStatement.executeQuery(); if(!res.next()) { System.out.printf("No players in the table with id %s", id); } else { player = new PlayerBean(res.getString("id"), res.getInt("coins"), res.getInt("goods")); } } catch (SQLException e) { System.out.printf("PlayerDAO.getPlayer ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); }
return player; }
/** * Insert randomized account data (id, coins, goods) using the JDBC fast path for * bulk inserts. The fastest way to get data into TiDB is using the * TiDB Lightning(https://docs.pingcap.com/tidb/stable/tidb-lightning-overview). * However, if you must bulk insert from the application using INSERT SQL, the best * option is the method shown here. It will require the following: * * Add `rewriteBatchedStatements=true` to your JDBC connection settings. * Setting rewriteBatchedStatements to true now causes CallableStatements * with batched arguments to be re-written in the form "CALL (...); CALL (...); ..." * to send the batch in as few client/server round trips as possible. * https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-3.html * * You can see the `rewriteBatchedStatements` param effect logic at * implement function: `com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries` * * @param total Add players amount. * @param batchSize Bulk insert size for per batch. * * @return The number of new accounts inserted. */ public int bulkInsertRandomPlayers(Integer total, Integer batchSize) { int totalNewPlayers = 0;
try (Connection connection = ds.getConnection()) { // We're managing the commit lifecycle ourselves, so we can // control the size of our batch inserts. connection.setAutoCommit(false);
// In this example we are adding 500 rows to the database, // but it could be any number. What's important is that // the batch size is 128. try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)")) { for (int i=0; i<=(total/batchSize);i++) { for (int j=0; j<batchSize; j++) { String id = UUID.randomUUID().toString(); pstmt.setString(1, id); pstmt.setInt(2, rand.nextInt(10000)); pstmt.setInt(3, rand.nextInt(10000)); pstmt.addBatch(); }
int[] count = pstmt.executeBatch(); totalNewPlayers += count.length; System.out.printf("\nPlayerDAO.bulkInsertRandomPlayers:\n '%s'\n", pstmt); System.out.printf(" => %s row(s) updated in this batch\n", count.length); } connection.commit(); } catch (SQLException e) { System.out.printf("PlayerDAO.bulkInsertRandomPlayers ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); } } catch (SQLException e) { System.out.printf("PlayerDAO.bulkInsertRandomPlayers ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); } return totalNewPlayers; }
/** * Print a subset of players from the data store by limit. * * @param limit Print max size. */ public void printPlayers(Integer limit) { try (Connection connection = ds.getConnection()) { PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM player LIMIT ?"); preparedStatement.setInt(1, limit); preparedStatement.execute();
ResultSet res = preparedStatement.executeQuery(); while (!res.next()) { PlayerBean player = new PlayerBean(res.getString("id"), res.getInt("coins"), res.getInt("goods")); System.out.println("\n[printPlayers]:\n" + player); } } catch (SQLException e) { System.out.printf("PlayerDAO.printPlayers ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); } }
/** * Count players from the data store. * * @return All players count */ public int countPlayers() { int count = 0;
try (Connection connection = ds.getConnection()) { PreparedStatement preparedStatement = connection.prepareStatement("SELECT count(*) FROM player"); preparedStatement.execute();
ResultSet res = preparedStatement.executeQuery(); if(res.next()) { count = res.getInt(1); } } catch (SQLException e) { System.out.printf("PlayerDAO.countPlayers ERROR: { state => %s, cause => %s, message => %s }\n", e.getSQLState(), e.getCause(), e.getMessage()); }
return count; } }
public static void main(String[] args) { // 1. Configure the example database connection.
// 1.1 Create a mysql data source instance. MysqlDataSource mysqlDataSource = new MysqlDataSource();
// 1.2 Set server name, port, database name, username and password. mysqlDataSource.setServerName("localhost"); mysqlDataSource.setPortNumber(4000); mysqlDataSource.setDatabaseName("test"); mysqlDataSource.setUser("root"); mysqlDataSource.setPassword("");
// Or you can use jdbc string instead. // mysqlDataSource.setURL("jdbc:mysql://{host}:{port}/test?user={user}&password={password}");
// 2. And then, create DAO to manager your data. PlayerDAO dao = new PlayerDAO(mysqlDataSource);
// 3. Run some simple examples.
// Create a player, who has a coin and a goods.. dao.createPlayers(Collections.singletonList(new PlayerBean("test", 1, 1)));
// Get a player. PlayerBean testPlayer = dao.getPlayer("test"); System.out.printf("PlayerDAO.getPlayer:\n => id: %s\n => coins: %s\n => goods: %s\n", testPlayer.getId(), testPlayer.getCoins(), testPlayer.getGoods());
// Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch. int addedCount = dao.bulkInsertRandomPlayers(1919, 114); System.out.printf("PlayerDAO.bulkInsertRandomPlayers:\n => %d total inserted players\n", addedCount);
// Count players amount. int count = dao.countPlayers(); System.out.printf("PlayerDAO.countPlayers:\n => %d total players\n", count);
// Print 3 players. dao.printPlayers(3);
// 4. Explore more.
// Player 1: id is "1", has only 100 coins. // Player 2: id is "2", has 114514 coins, and 20 goods. PlayerBean player1 = new PlayerBean("1", 100, 0); PlayerBean player2 = new PlayerBean("2", 114514, 20);
// Create two players "by hand", using the INSERT statement on the backend. addedCount = dao.createPlayers(Arrays.asList(player1, player2)); System.out.printf("PlayerDAO.createPlayers:\n => %d total inserted players\n", addedCount);
// Player 1 wants to buy 10 goods from player 2. // It will cost 500 coins, but player 1 cannot afford it. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will fail"); int updatedCount = dao.buyGoods(player2.getId(), player1.getId(), 10, 500); System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
// So player 1 has to reduce the incoming quantity to two. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will success"); updatedCount = dao.buyGoods(player2.getId(), player1.getId(), 2, 100); System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount); }}
复制代码
第 3 步：运行代码
本节将逐步介绍代码的运行方法。
第 3 步第 1 部分：JDBC 表初始化
使用 JDBC
使用 Mybatis（推荐）
使用 Hibernate（推荐）
在 Gitpod Playground 中尝试 JDBC: 现在就试试
使用 JDBC 时，需手动初始化数据库表，若你本地已经安装了 mysql-client，且使用本地集群，可直接在 plain-java-jdbc 目录下运行：
make mysql
复制代码
或直接执行：
mysql --host 127.0.0.1 --port 4000 -u root<src/main/resources/dbinit.sql
复制代码
若你不使用本地集群，或未安装 mysql-client，请直接登录你的集群，并运行 src/main/resources/dbinit.sql 文件内的 SQL 语句。
第 3 步第 2 部分：TiDB Cloud 更改参数
使用 JDBC
使用 Mybatis（推荐）
使用 Hibernate（推荐）
若你使用非本地默认集群、TiDB Cloud 或其他远程集群，更改 JDBCExample.java 内关于 Host、Port、User、Password 的参数：
mysqlDataSource.setServerName("localhost");mysqlDataSource.setPortNumber(4000);mysqlDataSource.setDatabaseName("test");mysqlDataSource.setUser("root");mysqlDataSource.setPassword("");
复制代码
若你设定的密码为 123456，而且从 TiDB Cloud 得到的连接字符串为：
mysql --connect-timeout 15 -u root -h xxx.tidbcloud.com -P 4000 -p
复制代码
那么此处应将参数更改为：
mysqlDataSource.setServerName("xxx.tidbcloud.com");mysqlDataSource.setPortNumber(4000);mysqlDataSource.setDatabaseName("test");mysqlDataSource.setUser("root");mysqlDataSource.setPassword("123456");
复制代码
第 3 步第 3 部分：运行
使用 JDBC
使用 Mybatis（推荐）
使用 Hibernate（推荐）
运行 make，这是以下两个操作的组合：
清理并构建 (make build)：
mvn clean package运行 (make run)：
java -jar target/plain-java-jdbc-0.0.1-jar-with-dependencies.jar
你也可以单独运行这两个 make 命令或原生命令。
第 4 步：预期输出
使用 JDBC
使用 Hibernate（推荐）
划线
评论
复制
发布于: 刚刚阅读数: 2
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021.12.15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论