我的书签
添加书签
移除书签TiDB 和 Java 的简单 CRUD 应用程序
本文档将展示如何使用 TiDB 和 Java 来构造一个简单的 CRUD 应用程序。
注意
推荐使用 Java 8 及以上版本进行 TiDB 的应用程序的编写。
拓展学习视频
小贴士
如果你希望使用 Spring Boot 进行 TiDB 应用程序的编写，可以查看 Build the TiDB Application using Spring Boot。
第 1 步：启动你的 TiDB 集群
本节将介绍 TiDB 集群的启动方法。
- TiDB Cloud
- 本地集群
- Gitpod
你可以部署一个本地测试的 TiDB 集群或正式的 TiDB 集群。详细步骤，请参考：
基于 Git 的预配置的开发环境：现在就试试
该环境会自动克隆代码，并通过 TiUP 部署测试集群。
第 2 步：获取代码
1. git clone https://github.com/pingcap-inc/tidb-example-java.git
- 使用 Mybatis（推荐）
- 使用 Hibernate（推荐）
- 使用 JDBC
Mybatis 是当前比较流行的开源 Java 应用持久层框架，本文将以 Maven 插件的方式使用 MyBatis Generator 生成部分持久层代码。
进入目录 plain-java-mybatis：
1. cd plain-java-mybatis
目录结构如下所示：
1. .
2. ├── Makefile
3. ├── pom.xml
4. └── src
5. └── main
6. ├── java
7. │ └── com
8. │ └── pingcap
9. │ ├── MybatisExample.java
10. │ ├── dao
11. │ │ └── PlayerDAO.java
12. │ └── model
13. │ ├── Player.java
14. │ ├── PlayerMapper.java
15. │ └── PlayerMapperEx.java
16. └── resources
17. ├── dbinit.sql
18. ├── log4j.properties
19. ├── mapper
20. │ ├── PlayerMapper.xml
21. │ └── PlayerMapperEx.xml
22. ├── mybatis-config.xml
23. └── mybatis-generator.xml
其中，自动生成的文件有：
src/main/java/com/pingcap/model/Player.java：Player 实体类文件src/main/java/com/pingcap/model/PlayerMapper.java：Player Mapper 的接口文件src/main/resources/mapper/PlayerMapper.xml：Player Mapper 的 XML 映射，它是 Mybatis 用于生成 Player Mapper 接口的实现类的配置
这些文件的生成策略被写在了 mybatis-generator.xml 配置文件内，它是 Mybatis Generator 的配置文件，下面配置文件中添加了使用方法的说明：
1. <!DOCTYPE generatorConfiguration PUBLIC
2. "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
3. "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
5. <generatorConfiguration>
6. <!--
7. <context/> entire document: https://mybatis.org/generator/configreference/context.html
9. context.id: A unique identifier you like
10. context.targetRuntime: Used to specify the runtime target for generated code.
11. It has MyBatis3DynamicSql / MyBatis3Kotlin / MyBatis3 / MyBatis3Simple 4 selection to choice.
12. -->
13. <context id="simple" targetRuntime="MyBatis3">
14. <!--
15. <commentGenerator/> entire document: https://mybatis.org/generator/configreference/commentGenerator.html
17. commentGenerator:
18. - property(suppressDate): remove timestamp in comments
19. - property(suppressAllComments): remove all comments
20. -->
21. <commentGenerator>
22. <property name="suppressDate" value="true"/>
23. <property name="suppressAllComments" value="true" />
24. </commentGenerator>
26. <!--
27. <jdbcConnection/> entire document: https://mybatis.org/generator/configreference/jdbcConnection.html
29. jdbcConnection.driverClass: The fully qualified class name for the JDBC driver used to access the database.
30. Used mysql-connector-java:5.1.49, should specify JDBC is com.mysql.jdbc.Driver
31. jdbcConnection.connectionURL: The JDBC connection URL used to access the database.
32. -->
33. <jdbcConnection driverClass="com.mysql.jdbc.Driver"
34. connectionURL="jdbc:mysql://localhost:4000/test?user=root" />
36. <!--
37. <javaModelGenerator/> entire document: https://mybatis.org/generator/configreference/javaModelGenerator.html
38. Model code file will be generated at ${targetProject}/${targetPackage}
40. javaModelGenerator:
41. - property(constructorBased): If it's true, generator will create constructor function in model
42. -->
43. <javaModelGenerator targetPackage="com.pingcap.model" targetProject="src/main/java">
44. <property name="constructorBased" value="true"/>
45. </javaModelGenerator>
47. <!--
48. <sqlMapGenerator/> entire document: https://mybatis.org/generator/configreference/sqlMapGenerator.html
49. XML SQL mapper file will be generated at ${targetProject}/${targetPackage}
50. -->
51. <sqlMapGenerator targetPackage="." targetProject="src/main/resources/mapper"/>
53. <!--
54. <javaClientGenerator/> entire document: https://mybatis.org/generator/configreference/javaClientGenerator.html
55. Java code mapper interface file will be generated at ${targetProject}/${targetPackage}
57. javaClientGenerator.type (context.targetRuntime is MyBatis3):
58. This attribute indicated Mybatis how to implement interface.
59. It has ANNOTATEDMAPPER / MIXEDMAPPER / XMLMAPPER 3 selection to choice.
60. -->
61. <javaClientGenerator type="XMLMAPPER" targetPackage="com.pingcap.model" targetProject="src/main/java"/>
63. <!--
64. <table/> entire document: https://mybatis.org/generator/configreference/table.html
66. table.tableName: The name of the database table.
67. table.domainObjectName: The base name from which generated object names will be generated. If not specified, MBG will generate a name automatically based on the tableName.
68. table.enableCountByExample: Signifies whether a count by example statement should be generated.
69. table.enableUpdateByExample: Signifies whether an update by example statement should be generated.
70. table.enableDeleteByExample: Signifies whether a delete by example statement should be generated.
71. table.enableSelectByExample: Signifies whether a select by example statement should be generated.
72. table.selectByExampleQueryId: This value will be added to the select list of the select by example statement in this form: "'<value>' as QUERYID".
73. -->
74. <table tableName="player" domainObjectName="Player"
75. enableCountByExample="false" enableUpdateByExample="false"
76. enableDeleteByExample="false" enableSelectByExample="false"
77. selectByExampleQueryId="false"/>
78. </context>
79. </generatorConfiguration>
mybatis-generator.xml 在 pom.xml 中，以 mybatis-generator-maven-plugin 插件配置的方式被引入：
1. <plugin>
2. <groupId>org.mybatis.generator</groupId>
3. <artifactId>mybatis-generator-maven-plugin</artifactId>
4. <version>1.4.1</version>
5. <configuration>
6. <configurationFile>src/main/resources/mybatis-generator.xml</configurationFile>
7. <verbose>true</verbose>
8. <overwrite>true</overwrite>
9. </configuration>
11. <dependencies>
12. <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
13. <dependency>
14. <groupId>mysql</groupId>
15. <artifactId>mysql-connector-java</artifactId>
16. <version>5.1.49</version>
17. </dependency>
18. </dependencies>
19. </plugin>
在 Maven 插件内引入后，可删除旧的生成文件后，通过命令 mvn mybatis-generate 生成新的文件。或者你也可以使用已经编写好的 make 命令，通过 make gen 来同时删除旧文件，并生成新文件。
注意
mybatis-generator.xml 中的属性 configuration.overwrite 仅可控制新生成的 Java 代码文件使用覆盖方式被写入，但 XML 映射文件仍会以追加方式写入。因此，推荐在 Mybaits Generator 生成新的文件前，先删除掉旧的文件。
Player.java 是使用 Mybatis Generator 生成出的数据实体类文件，为数据库表在程序内的映射。Player 类的每个属性都对应着 player 表的一个字段。
1. package com.pingcap.model;
3. public class Player {
4. private String id;
6. private Integer coins;
8. private Integer goods;
10. public Player(String id, Integer coins, Integer goods) {
11. this.id = id;
12. this.coins = coins;
13. this.goods = goods;
14. }
16. public Player() {
17. super();
18. }
20. public String getId() {
21. return id;
22. }
24. public void setId(String id) {
25. this.id = id;
26. }
28. public Integer getCoins() {
29. return coins;
30. }
32. public void setCoins(Integer coins) {
33. this.coins = coins;
34. }
36. public Integer getGoods() {
37. return goods;
38. }
40. public void setGoods(Integer goods) {
41. this.goods = goods;
42. }
43. }
PlayerMapper.java 是使用 Mybatis Generator 生成出的映射接口文件，它仅规定了接口，接口的实现类是由 Mybatis 来通过 XML 或注解自动生成的：
1. package com.pingcap.model;
3. import com.pingcap.model.Player;
5. public interface PlayerMapper {
6. int deleteByPrimaryKey(String id);
8. int insert(Player row);
10. int insertSelective(Player row);
12. Player selectByPrimaryKey(String id);
14. int updateByPrimaryKeySelective(Player row);
16. int updateByPrimaryKey(Player row);
17. }
PlayerMapper.xml 是使用 Mybatis Generator 生成出的映射 XML 文件，Mybatis 将使用这个文件自动生成 PlayerMapper 接口的实现类：
1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3. <mapper namespace="com.pingcap.model.PlayerMapper">
4. <resultMap id="BaseResultMap" type="com.pingcap.model.Player">
5. <constructor>
6. <idArg column="id" javaType="java.lang.String" jdbcType="VARCHAR" />
7. <arg column="coins" javaType="java.lang.Integer" jdbcType="INTEGER" />
8. <arg column="goods" javaType="java.lang.Integer" jdbcType="INTEGER" />
9. </constructor>
10. </resultMap>
11. <sql id="Base_Column_List">
12. id, coins, goods
13. </sql>
14. <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
15. select
16. <include refid="Base_Column_List" />
17. from player
18. where id = #{id,jdbcType=VARCHAR}
19. </select>
20. <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
21. delete from player
22. where id = #{id,jdbcType=VARCHAR}
23. </delete>
24. <insert id="insert" parameterType="com.pingcap.model.Player">
25. insert into player (id, coins, goods
26. )
27. values (#{id,jdbcType=VARCHAR}, #{coins,jdbcType=INTEGER}, #{goods,jdbcType=INTEGER}
28. )
29. </insert>
30. <insert id="insertSelective" parameterType="com.pingcap.model.Player">
31. insert into player
32. <trim prefix="(" suffix=")" suffixOverrides=",">
33. <if test="id != null">
34. id,
35. </if>
36. <if test="coins != null">
37. coins,
38. </if>
39. <if test="goods != null">
40. goods,
41. </if>
42. </trim>
43. <trim prefix="values (" suffix=")" suffixOverrides=",">
44. <if test="id != null">
45. #{id,jdbcType=VARCHAR},
46. </if>
47. <if test="coins != null">
48. #{coins,jdbcType=INTEGER},
49. </if>
50. <if test="goods != null">
51. #{goods,jdbcType=INTEGER},
52. </if>
53. </trim>
54. </insert>
55. <update id="updateByPrimaryKeySelective" parameterType="com.pingcap.model.Player">
56. update player
57. <set>
58. <if test="coins != null">
59. coins = #{coins,jdbcType=INTEGER},
60. </if>
61. <if test="goods != null">
62. goods = #{goods,jdbcType=INTEGER},
63. </if>
64. </set>
65. where id = #{id,jdbcType=VARCHAR}
66. </update>
67. <update id="updateByPrimaryKey" parameterType="com.pingcap.model.Player">
68. update player
69. set coins = #{coins,jdbcType=INTEGER},
70. goods = #{goods,jdbcType=INTEGER}
71. where id = #{id,jdbcType=VARCHAR}
72. </update>
73. </mapper>
由于 Mybatis Generator 需要逆向生成源码，因此，数据库中需先行有此表结构，可使用 dbinit.sql 生成表结构：
1. USE test;
2. DROP TABLE IF EXISTS player;
4. CREATE TABLE player (
5. `id` VARCHAR(36),
6. `coins` INTEGER,
7. `goods` INTEGER,
8. PRIMARY KEY (`id`)
9. );
额外拆分接口 PlayerMapperEx 继承 PlayerMapper，并且编写与之匹配的 PlayerMapperEx.xml。避免直接更改 PlayerMapper.java 和 PlayerMapper.xml。这是为了规避 Mybatis Generator 的反复生成，影响到自行编写的代码。
在 PlayerMapperEx.java 中定义自行增加的接口：
1. package com.pingcap.model;
3. import java.util.List;
5. public interface PlayerMapperEx extends PlayerMapper {
6. Player selectByPrimaryKeyWithLock(String id);
8. List<Player> selectByLimit(Integer limit);
10. Integer count();
11. }
在 PlayerMapperEx.xml 中定义映射规则：
1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3. <mapper namespace="com.pingcap.model.PlayerMapperEx">
4. <resultMap id="BaseResultMap" type="com.pingcap.model.Player">
5. <constructor>
6. <idArg column="id" javaType="java.lang.String" jdbcType="VARCHAR" />
7. <arg column="coins" javaType="java.lang.Integer" jdbcType="INTEGER" />
8. <arg column="goods" javaType="java.lang.Integer" jdbcType="INTEGER" />
9. </constructor>
10. </resultMap>
11. <sql id="Base_Column_List">
12. id, coins, goods
13. </sql>
15. <select id="selectByPrimaryKeyWithLock" parameterType="java.lang.String" resultMap="BaseResultMap">
16. select
17. <include refid="Base_Column_List" />
18. from player
19. where `id` = #{id,jdbcType=VARCHAR}
20. for update
21. </select>
23. <select id="selectByLimit" parameterType="java.lang.Integer" resultMap="BaseResultMap">
24. select
25. <include refid="Base_Column_List" />
26. from player
27. limit #{id,jdbcType=INTEGER}
28. </select>
30. <select id="count" resultType="java.lang.Integer">
31. select count(*) from player
32. </select>
34. </mapper>
PlayerDAO.java 是程序用来管理数据对象的类。其中 DAO 是 Data Access Object 的缩写。在其中定义了一系列数据的操作方法，用于数据的写入。
1. package com.pingcap.dao;
3. import com.pingcap.model.Player;
4. import com.pingcap.model.PlayerMapperEx;
5. import org.apache.ibatis.session.SqlSession;
6. import org.apache.ibatis.session.SqlSessionFactory;
8. import java.util.List;
9. import java.util.function.Function;
11. public class PlayerDAO {
12. public static class NotEnoughException extends RuntimeException {
13. public NotEnoughException(String message) {
14. super(message);
15. }
16. }
18. // Run SQL code in a way that automatically handles the
19. // transaction retry logic, so we don't have to duplicate it in
20. // various places.
21. public Object runTransaction(SqlSessionFactory sessionFactory, Function<PlayerMapperEx, Object> fn) {
22. Object resultObject = null;
23. SqlSession session = null;
25. try {
26. // open a session with autoCommit is false
27. session = sessionFactory.openSession(false);
29. // get player mapper
30. PlayerMapperEx playerMapperEx = session.getMapper(PlayerMapperEx.class);
32. resultObject = fn.apply(playerMapperEx);
33. session.commit();
34. System.out.println("APP: COMMIT;");
35. } catch (Exception e) {
36. if (e instanceof NotEnoughException) {
37. System.out.printf("APP: ROLLBACK BY LOGIC; \n%s\n", e.getMessage());
38. } else {
39. System.out.printf("APP: ROLLBACK BY ERROR; \n%s\n", e.getMessage());
40. }
42. if (session != null) {
43. session.rollback();
44. }
45. } finally {
46. if (session != null) {
47. session.close();
48. }
49. }
51. return resultObject;
52. }
54. public Function<PlayerMapperEx, Object> createPlayers(List<Player> players) {
55. return playerMapperEx -> {
56. Integer addedPlayerAmount = 0;
57. for (Player player: players) {
58. playerMapperEx.insert(player);
59. addedPlayerAmount ++;
60. }
61. System.out.printf("APP: createPlayers() --> %d\n", addedPlayerAmount);
62. return addedPlayerAmount;
63. };
64. }
66. public Function<PlayerMapperEx, Object> buyGoods(String sellId, String buyId, Integer amount, Integer price) {
67. return playerMapperEx -> {
68. Player sellPlayer = playerMapperEx.selectByPrimaryKeyWithLock(sellId);
69. Player buyPlayer = playerMapperEx.selectByPrimaryKeyWithLock(buyId);
71. if (buyPlayer == null || sellPlayer == null) {
72. throw new NotEnoughException("sell or buy player not exist");
73. }
75. if (buyPlayer.getCoins() < price || sellPlayer.getGoods() < amount) {
76. throw new NotEnoughException("coins or goods not enough, rollback");
77. }
79. int affectRows = 0;
80. buyPlayer.setGoods(buyPlayer.getGoods() + amount);
81. buyPlayer.setCoins(buyPlayer.getCoins() - price);
82. affectRows += playerMapperEx.updateByPrimaryKey(buyPlayer);
84. sellPlayer.setGoods(sellPlayer.getGoods() - amount);
85. sellPlayer.setCoins(sellPlayer.getCoins() + price);
86. affectRows += playerMapperEx.updateByPrimaryKey(sellPlayer);
88. System.out.printf("APP: buyGoods --> sell: %s, buy: %s, amount: %d, price: %d\n", sellId, buyId, amount, price);
89. return affectRows;
90. };
91. }
93. public Function<PlayerMapperEx, Object> getPlayerByID(String id) {
94. return playerMapperEx -> playerMapperEx.selectByPrimaryKey(id);
95. }
97. public Function<PlayerMapperEx, Object> printPlayers(Integer limit) {
98. return playerMapperEx -> {
99. List<Player> players = playerMapperEx.selectByLimit(limit);
101. for (Player player: players) {
102. System.out.println("\n[printPlayers]:\n" + player);
103. }
104. return 0;
105. };
106. }
108. public Function<PlayerMapperEx, Object> countPlayers() {
109. return PlayerMapperEx::count;
110. }
111. }
MybatisExample 是 plain-java-mybatis 这个示例程序的主类。其中定义了入口函数：
1. package com.pingcap;
3. import com.pingcap.dao.PlayerDAO;
4. import com.pingcap.model.Player;
5. import org.apache.ibatis.io.Resources;
6. import org.apache.ibatis.session.SqlSessionFactory;
7. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
9. import java.io.IOException;
10. import java.io.InputStream;
11. import java.util.Arrays;
12. import java.util.Collections;
14. public class MybatisExample {
15. public static void main( String[] args ) throws IOException {
16. // 1. Create a SqlSessionFactory based on our mybatis-config.xml configuration
17. // file, which defines how to connect to the database.
18. InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
19. SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
21. // 2. And then, create DAO to manager your data
22. PlayerDAO playerDAO = new PlayerDAO();
24. // 3. Run some simple examples.
26. // Create a player who has 1 coin and 1 goods.
27. playerDAO.runTransaction(sessionFactory, playerDAO.createPlayers(
28. Collections.singletonList(new Player("test", 1, 1))));
30. // Get a player.
31. Player testPlayer = (Player)playerDAO.runTransaction(sessionFactory, playerDAO.getPlayerByID("test"));
32. System.out.printf("PlayerDAO.getPlayer:\n => id: %s\n => coins: %s\n => goods: %s\n",
33. testPlayer.getId(), testPlayer.getCoins(), testPlayer.getGoods());
35. // Count players amount.
36. Integer count = (Integer)playerDAO.runTransaction(sessionFactory, playerDAO.countPlayers());
37. System.out.printf("PlayerDAO.countPlayers:\n => %d total players\n", count);
39. // Print 3 players.
40. playerDAO.runTransaction(sessionFactory, playerDAO.printPlayers(3));
42. // 4. Getting further.
44. // Player 1: id is "1", has only 100 coins.
45. // Player 2: id is "2", has 114514 coins, and 20 goods.
46. Player player1 = new Player("1", 100, 0);
47. Player player2 = new Player("2", 114514, 20);
49. // Create two players "by hand", using the INSERT statement on the backend.
50. int addedCount = (Integer)playerDAO.runTransaction(sessionFactory,
51. playerDAO.createPlayers(Arrays.asList(player1, player2)));
52. System.out.printf("PlayerDAO.createPlayers:\n => %d total inserted players\n", addedCount);
54. // Player 1 wants to buy 10 goods from player 2.
55. // It will cost 500 coins, but player 1 cannot afford it.
56. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will fail");
57. Integer updatedCount = (Integer)playerDAO.runTransaction(sessionFactory,
58. playerDAO.buyGoods(player2.getId(), player1.getId(), 10, 500));
59. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
61. // So player 1 has to reduce the incoming quantity to two.
62. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will success");
63. updatedCount = (Integer)playerDAO.runTransaction(sessionFactory,
64. playerDAO.buyGoods(player2.getId(), player1.getId(), 2, 100));
65. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
66. }
67. }
当前开源比较流行的 Java ORM 为 Hibernate，且 Hibernate 在版本 6.0.0.Beta2 及以后支持了 TiDB 方言。完美适配了 TiDB 的特性。因此，此处将以 6.0.0.Beta2 + 版本进行说明。
进入目录 plain-java-hibernate ：
1. cd plain-java-hibernate
目录结构如下所示：
1. .
2. ├── Makefile
3. ├── plain-java-hibernate.iml
4. ├── pom.xml
5. └── src
6. └── main
7. ├── java
8. │ └── com
9. │ └── pingcap
10. │ └── HibernateExample.java
11. └── resources
12. └── hibernate.cfg.xml
其中，hibernate.cfg.xml 为 Hibernate 配置文件，定义了：
1. <?xml version='1.0' encoding='utf-8'?>
2. <!DOCTYPE hibernate-configuration PUBLIC
3. "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
4. "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
5. <hibernate-configuration>
6. <session-factory>
8. <!-- Database connection settings -->
9. <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
10. <property name="hibernate.dialect">org.hibernate.dialect.TiDBDialect</property>
11. <property name="hibernate.connection.url">jdbc:mysql://localhost:4000/test</property>
12. <property name="hibernate.connection.username">root</property>
13. <property name="hibernate.connection.password"></property>
14. <property name="hibernate.connection.autocommit">false</property>
16. <!-- Required so a table can be created from the 'PlayerDAO' class -->
17. <property name="hibernate.hbm2ddl.auto">create-drop</property>
19. <!-- Optional: Show SQL output for debugging -->
20. <property name="hibernate.show_sql">true</property>
21. <property name="hibernate.format_sql">true</property>
22. </session-factory>
23. </hibernate-configuration>
HibernateExample.java 是 plain-java-hibernate 这个示例程序的主体。使用 Hibernate 时，相较于 JDBC，这里仅需写入配置文件地址，Hibernate 屏蔽了创建数据库连接时，不同数据库差异的细节。
PlayerDAO 是程序用来管理数据对象的类。其中 DAO 是 Data Access Object 的缩写。其中定义了一系列数据的操作方法，用来提供数据的写入能力。相较于 JDBC，Hibernate 封装了大量的操作，如对象映射、基本对象的 CRUD 等，极大的简化了代码量。
PlayerBean 是数据实体类，为数据库表在程序内的映射。PlayerBean 的每个属性都对应着 player 表的一个字段。相较于 JDBC，Hibernate 的 PlayerBean 实体类为了给 Hibernate 提供更多的信息，加入了注解，用来指示映射关系。
1. package com.pingcap;
3. import jakarta.persistence.Column;
4. import jakarta.persistence.Entity;
5. import jakarta.persistence.Id;
6. import jakarta.persistence.Table;
7. import org.hibernate.JDBCException;
8. import org.hibernate.Session;
9. import org.hibernate.SessionFactory;
10. import org.hibernate.Transaction;
11. import org.hibernate.cfg.Configuration;
12. import org.hibernate.query.NativeQuery;
13. import org.hibernate.query.Query;
15. import java.util.Arrays;
16. import java.util.Collections;
17. import java.util.List;
18. import java.util.function.Function;
20. @Entity
21. @Table(name = "player_hibernate")
22. class PlayerBean {
23. @Id
24. private String id;
25. @Column(name = "coins")
26. private Integer coins;
27. @Column(name = "goods")
28. private Integer goods;
30. public PlayerBean() {
31. }
33. public PlayerBean(String id, Integer coins, Integer goods) {
34. this.id = id;
35. this.coins = coins;
36. this.goods = goods;
37. }
39. public String getId() {
40. return id;
41. }
43. public void setId(String id) {
44. this.id = id;
45. }
47. public Integer getCoins() {
48. return coins;
49. }
51. public void setCoins(Integer coins) {
52. this.coins = coins;
53. }
55. public Integer getGoods() {
56. return goods;
57. }
59. public void setGoods(Integer goods) {
60. this.goods = goods;
61. }
63. @Override
64. public String toString() {
65. return String.format(" %-8s => %10s\n %-8s => %10s\n %-8s => %10s\n",
66. "id", this.id, "coins", this.coins, "goods", this.goods);
67. }
68. }
70. /**
71. * Main class for the basic Hibernate example.
72. **/
73. public class HibernateExample
74. {
75. public static class PlayerDAO {
76. public static class NotEnoughException extends RuntimeException {
77. public NotEnoughException(String message) {
78. super(message);
79. }
80. }
82. // Run SQL code in a way that automatically handles the
83. // transaction retry logic so we don't have to duplicate it in
84. // various places.
85. public Object runTransaction(Session session, Function<Session, Object> fn) {
86. Object resultObject = null;
88. Transaction txn = session.beginTransaction();
89. try {
90. resultObject = fn.apply(session);
91. txn.commit();
92. System.out.println("APP: COMMIT;");
93. } catch (JDBCException e) {
94. System.out.println("APP: ROLLBACK BY JDBC ERROR;");
95. txn.rollback();
96. } catch (NotEnoughException e) {
97. System.out.printf("APP: ROLLBACK BY LOGIC; %s", e.getMessage());
98. txn.rollback();
99. }
100. return resultObject;
101. }
103. public Function<Session, Object> createPlayers(List<PlayerBean> players) throws JDBCException {
104. return session -> {
105. Integer addedPlayerAmount = 0;
106. for (PlayerBean player: players) {
107. session.persist(player);
108. addedPlayerAmount ++;
109. }
110. System.out.printf("APP: createPlayers() --> %d\n", addedPlayerAmount);
111. return addedPlayerAmount;
112. };
113. }
115. public Function<Session, Object> buyGoods(String sellId, String buyId, Integer amount, Integer price) throws JDBCException {
116. return session -> {
117. PlayerBean sellPlayer = session.get(PlayerBean.class, sellId);
118. PlayerBean buyPlayer = session.get(PlayerBean.class, buyId);
120. if (buyPlayer == null || sellPlayer == null) {
121. throw new NotEnoughException("sell or buy player not exist");
122. }
124. if (buyPlayer.getCoins() < price || sellPlayer.getGoods() < amount) {
125. throw new NotEnoughException("coins or goods not enough, rollback");
126. }
128. buyPlayer.setGoods(buyPlayer.getGoods() + amount);
129. buyPlayer.setCoins(buyPlayer.getCoins() - price);
130. session.persist(buyPlayer);
132. sellPlayer.setGoods(sellPlayer.getGoods() - amount);
133. sellPlayer.setCoins(sellPlayer.getCoins() + price);
134. session.persist(sellPlayer);
136. System.out.printf("APP: buyGoods --> sell: %s, buy: %s, amount: %d, price: %d\n", sellId, buyId, amount, price);
137. return 0;
138. };
139. }
141. public Function<Session, Object> getPlayerByID(String id) throws JDBCException {
142. return session -> session.get(PlayerBean.class, id);
143. }
145. public Function<Session, Object> printPlayers(Integer limit) throws JDBCException {
146. return session -> {
147. NativeQuery<PlayerBean> limitQuery = session.createNativeQuery("SELECT * FROM player_hibernate LIMIT :limit", PlayerBean.class);
148. limitQuery.setParameter("limit", limit);
149. List<PlayerBean> players = limitQuery.getResultList();
151. for (PlayerBean player: players) {
152. System.out.println("\n[printPlayers]:\n" + player);
153. }
154. return 0;
155. };
156. }
158. public Function<Session, Object> countPlayers() throws JDBCException {
159. return session -> {
160. Query<Long> countQuery = session.createQuery("SELECT count(player_hibernate) FROM PlayerBean player_hibernate", Long.class);
161. return countQuery.getSingleResult();
162. };
163. }
164. }
166. public static void main(String[] args) {
167. // 1. Create a SessionFactory based on our hibernate.cfg.xml configuration
168. // file, which defines how to connect to the database.
169. SessionFactory sessionFactory
170. = new Configuration()
171. .configure("hibernate.cfg.xml")
172. .addAnnotatedClass(PlayerBean.class)
173. .buildSessionFactory();
175. try (Session session = sessionFactory.openSession()) {
176. // 2. And then, create DAO to manager your data.
177. PlayerDAO playerDAO = new PlayerDAO();
179. // 3. Run some simple examples.
181. // Create a player who has 1 coin and 1 goods.
182. playerDAO.runTransaction(session, playerDAO.createPlayers(Collections.singletonList(
183. new PlayerBean("test", 1, 1))));
185. // Get a player.
186. PlayerBean testPlayer = (PlayerBean)playerDAO.runTransaction(session, playerDAO.getPlayerByID("test"));
187. System.out.printf("PlayerDAO.getPlayer:\n => id: %s\n => coins: %s\n => goods: %s\n",
188. testPlayer.getId(), testPlayer.getCoins(), testPlayer.getGoods());
190. // Count players amount.
191. Long count = (Long)playerDAO.runTransaction(session, playerDAO.countPlayers());
192. System.out.printf("PlayerDAO.countPlayers:\n => %d total players\n", count);
194. // Print 3 players.
195. playerDAO.runTransaction(session, playerDAO.printPlayers(3));
197. // 4. Explore more.
199. // Player 1: id is "1", has only 100 coins.
200. // Player 2: id is "2", has 114514 coins, and 20 goods.
201. PlayerBean player1 = new PlayerBean("1", 100, 0);
202. PlayerBean player2 = new PlayerBean("2", 114514, 20);
204. // Create two players "by hand", using the INSERT statement on the backend.
205. int addedCount = (Integer)playerDAO.runTransaction(session,
206. playerDAO.createPlayers(Arrays.asList(player1, player2)));
207. System.out.printf("PlayerDAO.createPlayers:\n => %d total inserted players\n", addedCount);
209. // Player 1 wants to buy 10 goods from player 2.
210. // It will cost 500 coins, but player 1 cannot afford it.
211. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will fail");
212. Integer updatedCount = (Integer)playerDAO.runTransaction(session,
213. playerDAO.buyGoods(player2.getId(), player1.getId(), 10, 500));
214. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
216. // So player 1 has to reduce the incoming quantity to two.
217. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will success");
218. updatedCount = (Integer)playerDAO.runTransaction(session,
219. playerDAO.buyGoods(player2.getId(), player1.getId(), 2, 100));
220. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
221. } finally {
222. sessionFactory.close();
223. }
224. }
225. }
进入目录 plain-java-jdbc：
1. cd plain-java-jdbc
目录结构如下所示：
1. .
2. ├── Makefile
3. ├── plain-java-jdbc.iml
4. ├── pom.xml
5. └── src
6. └── main
7. ├── java
8. │ └── com
9. │ └── pingcap
10. │ └── JDBCExample.java
11. └── resources
12. └── dbinit.sql
其中，dbinit.sql 为数据表初始化语句：
1. USE test;
2. DROP TABLE IF EXISTS player;
4. CREATE TABLE player (
5. `id` VARCHAR(36),
6. `coins` INTEGER,
7. `goods` INTEGER,
8. PRIMARY KEY (`id`)
9. );
JDBCExample.java 是 plain-java-jdbc 这个示例程序的主体。因为 TiDB 与 MySQL 协议兼容，因此，需要初始化一个 MySQL 协议的数据源 MysqlDataSource，以此连接到 TiDB。并在其后，初始化 PlayerDAO，用来管理数据对象，进行增删改查等操作。
PlayerDAO 是程序用来管理数据对象的类。其中 DAO 是 Data Access Object 的缩写。在其中定义了一系列数据的操作方法，用来对提供数据的写入能力。
PlayerBean 是数据实体类，为数据库表在程序内的映射。PlayerBean 的每个属性都对应着 player 表的一个字段。
1. package com.pingcap;
3. import com.mysql.cj.jdbc.MysqlDataSource;
5. import java.sql.Connection;
6. import java.sql.PreparedStatement;
7. import java.sql.ResultSet;
8. import java.sql.SQLException;
9. import java.util.*;
11. /**
12. * Main class for the basic JDBC example.
13. **/
14. public class JDBCExample
15. {
16. public static class PlayerBean {
17. private String id;
18. private Integer coins;
19. private Integer goods;
21. public PlayerBean() {
22. }
24. public PlayerBean(String id, Integer coins, Integer goods) {
25. this.id = id;
26. this.coins = coins;
27. this.goods = goods;
28. }
30. public String getId() {
31. return id;
32. }
34. public void setId(String id) {
35. this.id = id;
36. }
38. public Integer getCoins() {
39. return coins;
40. }
42. public void setCoins(Integer coins) {
43. this.coins = coins;
44. }
46. public Integer getGoods() {
47. return goods;
48. }
50. public void setGoods(Integer goods) {
51. this.goods = goods;
52. }
54. @Override
55. public String toString() {
56. return String.format(" %-8s => %10s\n %-8s => %10s\n %-8s => %10s\n",
57. "id", this.id, "coins", this.coins, "goods", this.goods);
58. }
59. }
61. /**
62. * Data access object used by 'ExampleDataSource'.
63. * Example for CURD and bulk insert.
64. */
65. public static class PlayerDAO {
66. private final MysqlDataSource ds;
67. private final Random rand = new Random();
69. PlayerDAO(MysqlDataSource ds) {
70. this.ds = ds;
71. }
73. /**
74. * Create players by passing in a List of PlayerBean.
75. *
76. * @param players Will create players list
77. * @return The number of create accounts
78. */
79. public int createPlayers(List<PlayerBean> players){
80. int rows = 0;
82. Connection connection = null;
83. PreparedStatement preparedStatement = null;
84. try {
85. connection = ds.getConnection();
86. preparedStatement = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)");
87. } catch (SQLException e) {
88. System.out.printf("[createPlayers] ERROR: { state => %s, cause => %s, message => %s }\n",
89. e.getSQLState(), e.getCause(), e.getMessage());
90. e.printStackTrace();
92. return -1;
93. }
95. try {
96. for (PlayerBean player : players) {
97. preparedStatement.setString(1, player.getId());
98. preparedStatement.setInt(2, player.getCoins());
99. preparedStatement.setInt(3, player.getGoods());
101. preparedStatement.execute();
102. rows += preparedStatement.getUpdateCount();
103. }
104. } catch (SQLException e) {
105. System.out.printf("[createPlayers] ERROR: { state => %s, cause => %s, message => %s }\n",
106. e.getSQLState(), e.getCause(), e.getMessage());
107. e.printStackTrace();
108. } finally {
109. try {
110. connection.close();
111. } catch (SQLException e) {
112. e.printStackTrace();
113. }
114. }
116. System.out.printf("\n[createPlayers]:\n '%s'\n", preparedStatement);
117. return rows;
118. }
120. /**
121. * Buy goods and transfer funds between one player and another in one transaction.
122. * @param sellId Sell player id.
123. * @param buyId Buy player id.
124. * @param amount Goods amount, if sell player has not enough goods, the trade will break.
125. * @param price Price should pay, if buy player has not enough coins, the trade will break.
126. *
127. * @return The number of effected players.
128. */
129. public int buyGoods(String sellId, String buyId, Integer amount, Integer price) {
130. int effectPlayers = 0;
132. Connection connection = null;
133. try {
134. connection = ds.getConnection();
135. } catch (SQLException e) {
136. System.out.printf("[buyGoods] ERROR: { state => %s, cause => %s, message => %s }\n",
137. e.getSQLState(), e.getCause(), e.getMessage());
138. e.printStackTrace();
139. return effectPlayers;
140. }
142. try {
143. connection.setAutoCommit(false);
145. PreparedStatement playerQuery = connection.prepareStatement("SELECT * FROM player WHERE id=? OR id=? FOR UPDATE");
146. playerQuery.setString(1, sellId);
147. playerQuery.setString(2, buyId);
148. playerQuery.execute();
150. PlayerBean sellPlayer = null;
151. PlayerBean buyPlayer = null;
153. ResultSet playerQueryResultSet = playerQuery.getResultSet();
154. while (playerQueryResultSet.next()) {
155. PlayerBean player = new PlayerBean(
156. playerQueryResultSet.getString("id"),
157. playerQueryResultSet.getInt("coins"),
158. playerQueryResultSet.getInt("goods")
159. );
161. System.out.println("\n[buyGoods]:\n 'check goods and coins enough'");
162. System.out.println(player);
164. if (sellId.equals(player.getId())) {
165. sellPlayer = player;
166. } else {
167. buyPlayer = player;
168. }
169. }
171. if (sellPlayer == null || buyPlayer == null) {
172. throw new SQLException("player not exist.");
173. }
175. if (sellPlayer.getGoods().compareTo(amount) < 0) {
176. throw new SQLException(String.format("sell player %s goods not enough.", sellId));
177. }
179. if (buyPlayer.getCoins().compareTo(price) < 0) {
180. throw new SQLException(String.format("buy player %s coins not enough.", buyId));
181. }
183. PreparedStatement transfer = connection.prepareStatement("UPDATE player set goods = goods + ?, coins = coins + ? WHERE id=?");
184. transfer.setInt(1, -amount);
185. transfer.setInt(2, price);
186. transfer.setString(3, sellId);
187. transfer.execute();
188. effectPlayers += transfer.getUpdateCount();
190. transfer.setInt(1, amount);
191. transfer.setInt(2, -price);
192. transfer.setString(3, buyId);
193. transfer.execute();
194. effectPlayers += transfer.getUpdateCount();
196. connection.commit();
198. System.out.println("\n[buyGoods]:\n 'trade success'");
199. } catch (SQLException e) {
200. System.out.printf("[buyGoods] ERROR: { state => %s, cause => %s, message => %s }\n",
201. e.getSQLState(), e.getCause(), e.getMessage());
203. try {
204. System.out.println("[buyGoods] Rollback");
206. connection.rollback();
207. } catch (SQLException ex) {
208. // do nothing
209. }
210. } finally {
211. try {
212. connection.close();
213. } catch (SQLException e) {
214. // do nothing
215. }
216. }
218. return effectPlayers;
219. }
221. /**
222. * Get the player info by id.
223. *
224. * @param id Player id.
225. * @return The player of this id.
226. */
227. public PlayerBean getPlayer(String id) {
228. PlayerBean player = null;
230. try (Connection connection = ds.getConnection()) {
231. PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM player WHERE id = ?");
232. preparedStatement.setString(1, id);
233. preparedStatement.execute();
235. ResultSet res = preparedStatement.executeQuery();
236. if(!res.next()) {
237. System.out.printf("No players in the table with id %s", id);
238. } else {
239. player = new PlayerBean(res.getString("id"), res.getInt("coins"), res.getInt("goods"));
240. }
241. } catch (SQLException e) {
242. System.out.printf("PlayerDAO.getPlayer ERROR: { state => %s, cause => %s, message => %s }\n",
243. e.getSQLState(), e.getCause(), e.getMessage());
244. }
246. return player;
247. }
249. /**
250. * Insert randomized account data (id, coins, goods) using the JDBC fast path for
251. * bulk inserts. The fastest way to get data into TiDB is using the
252. * TiDB Lightning(https://docs.pingcap.com/tidb/stable/tidb-lightning-overview).
253. * However, if you must bulk insert from the application using INSERT SQL, the best
254. * option is the method shown here. It will require the following:
255. *
256. * Add `rewriteBatchedStatements=true` to your JDBC connection settings.
257. * Setting rewriteBatchedStatements to true now causes CallableStatements
258. * with batched arguments to be re-written in the form "CALL (...); CALL (...); ..."
259. * to send the batch in as few client/server round trips as possible.
260. * https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-3.html
261. *
262. * You can see the `rewriteBatchedStatements` param effect logic at
263. * implement function: `com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries`
264. *
265. * @param total Add players amount.
266. * @param batchSize Bulk insert size for per batch.
267. *
268. * @return The number of new accounts inserted.
269. */
270. public int bulkInsertRandomPlayers(Integer total, Integer batchSize) {
271. int totalNewPlayers = 0;
273. try (Connection connection = ds.getConnection()) {
274. // We're managing the commit lifecycle ourselves, so we can
275. // control the size of our batch inserts.
276. connection.setAutoCommit(false);
278. // In this example we are adding 500 rows to the database,
279. // but it could be any number. What's important is that
280. // the batch size is 128.
281. try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)")) {
282. for (int i=0; i<=(total/batchSize);i++) {
283. for (int j=0; j<batchSize; j++) {
284. String id = UUID.randomUUID().toString();
285. pstmt.setString(1, id);
286. pstmt.setInt(2, rand.nextInt(10000));
287. pstmt.setInt(3, rand.nextInt(10000));
288. pstmt.addBatch();
289. }
291. int[] count = pstmt.executeBatch();
292. totalNewPlayers += count.length;
293. System.out.printf("\nPlayerDAO.bulkInsertRandomPlayers:\n '%s'\n", pstmt);
294. System.out.printf(" => %s row(s) updated in this batch\n", count.length);
295. }
296. connection.commit();
297. } catch (SQLException e) {
298. System.out.printf("PlayerDAO.bulkInsertRandomPlayers ERROR: { state => %s, cause => %s, message => %s }\n",
299. e.getSQLState(), e.getCause(), e.getMessage());
300. }
301. } catch (SQLException e) {
302. System.out.printf("PlayerDAO.bulkInsertRandomPlayers ERROR: { state => %s, cause => %s, message => %s }\n",
303. e.getSQLState(), e.getCause(), e.getMessage());
304. }
305. return totalNewPlayers;
306. }
309. /**
310. * Print a subset of players from the data store by limit.
311. *
312. * @param limit Print max size.
313. */
314. public void printPlayers(Integer limit) {
315. try (Connection connection = ds.getConnection()) {
316. PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM player LIMIT ?");
317. preparedStatement.setInt(1, limit);
318. preparedStatement.execute();
320. ResultSet res = preparedStatement.executeQuery();
321. while (!res.next()) {
322. PlayerBean player = new PlayerBean(res.getString("id"),
323. res.getInt("coins"), res.getInt("goods"));
324. System.out.println("\n[printPlayers]:\n" + player);
325. }
326. } catch (SQLException e) {
327. System.out.printf("PlayerDAO.printPlayers ERROR: { state => %s, cause => %s, message => %s }\n",
328. e.getSQLState(), e.getCause(), e.getMessage());
329. }
330. }
333. /**
334. * Count players from the data store.
335. *
336. * @return All players count
337. */
338. public int countPlayers() {
339. int count = 0;
341. try (Connection connection = ds.getConnection()) {
342. PreparedStatement preparedStatement = connection.prepareStatement("SELECT count(*) FROM player");
343. preparedStatement.execute();
345. ResultSet res = preparedStatement.executeQuery();
346. if(res.next()) {
347. count = res.getInt(1);
348. }
349. } catch (SQLException e) {
350. System.out.printf("PlayerDAO.countPlayers ERROR: { state => %s, cause => %s, message => %s }\n",
351. e.getSQLState(), e.getCause(), e.getMessage());
352. }
354. return count;
355. }
356. }
358. public static void main(String[] args) {
359. // 1. Configure the example database connection.
361. // 1.1 Create a mysql data source instance.
362. MysqlDataSource mysqlDataSource = new MysqlDataSource();
364. // 1.2 Set server name, port, database name, username and password.
365. mysqlDataSource.setServerName("localhost");
366. mysqlDataSource.setPortNumber(4000);
367. mysqlDataSource.setDatabaseName("test");
368. mysqlDataSource.setUser("root");
369. mysqlDataSource.setPassword("");
371. // Or you can use jdbc string instead.
372. // mysqlDataSource.setURL("jdbc:mysql://{host}:{port}/test?user={user}&password={password}");
374. // 2. And then, create DAO to manager your data.
375. PlayerDAO dao = new PlayerDAO(mysqlDataSource);
377. // 3. Run some simple examples.
379. // Create a player, who has a coin and a goods..
380. dao.createPlayers(Collections.singletonList(new PlayerBean("test", 1, 1)));
382. // Get a player.
383. PlayerBean testPlayer = dao.getPlayer("test");
384. System.out.printf("PlayerDAO.getPlayer:\n => id: %s\n => coins: %s\n => goods: %s\n",
385. testPlayer.getId(), testPlayer.getCoins(), testPlayer.getGoods());
387. // Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch.
388. int addedCount = dao.bulkInsertRandomPlayers(1919, 114);
389. System.out.printf("PlayerDAO.bulkInsertRandomPlayers:\n => %d total inserted players\n", addedCount);
391. // Count players amount.
392. int count = dao.countPlayers();
393. System.out.printf("PlayerDAO.countPlayers:\n => %d total players\n", count);
395. // Print 3 players.
396. dao.printPlayers(3);
398. // 4. Explore more.
400. // Player 1: id is "1", has only 100 coins.
401. // Player 2: id is "2", has 114514 coins, and 20 goods.
402. PlayerBean player1 = new PlayerBean("1", 100, 0);
403. PlayerBean player2 = new PlayerBean("2", 114514, 20);
405. // Create two players "by hand", using the INSERT statement on the backend.
406. addedCount = dao.createPlayers(Arrays.asList(player1, player2));
407. System.out.printf("PlayerDAO.createPlayers:\n => %d total inserted players\n", addedCount);
409. // Player 1 wants to buy 10 goods from player 2.
410. // It will cost 500 coins, but player 1 cannot afford it.
411. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will fail");
412. int updatedCount = dao.buyGoods(player2.getId(), player1.getId(), 10, 500);
413. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
415. // So player 1 has to reduce the incoming quantity to two.
416. System.out.println("\nPlayerDAO.buyGoods:\n => this trade will success");
417. updatedCount = dao.buyGoods(player2.getId(), player1.getId(), 2, 100);
418. System.out.printf("PlayerDAO.buyGoods:\n => %d total update players\n", updatedCount);
419. }
420. }
第 3 步：运行代码
本节将逐步介绍代码的运行方法。
第 3 步第 1 部分：JDBC 表初始化
- 使用 Mybatis（推荐）
- 使用 Hibernate（推荐）
- 使用 JDBC
