Spring Boot integration with tidb Quick Start Demo

1. What is TIDB?

TiDB is an open-source distributed relational database independently designed and developed by PingCAP, which is a converged distributed database product that supports both online transaction processing and online analytical processing (HTAP), with horizontal scaling or scaling, financial-grade high availability, real-time HTAP, cloud-native distributed database, and compatibility with MySQL protocol MySQL ecosystem and other important features. The goal is to provide users with one-stop OLTP (Online Transactional Processing), OLAP (Online Analytical Processing), and HTAP solutions. TiDB is suitable for various application scenarios, such as high availability, high requirements for strong consistency, and large data scale.

Five core features

• One-click horizontal scaling benefits from the design of TiDB’s storage and compute separation architecture, which allows you to scale out or scale in compute and storage online on demand, and is transparent to application O&M personnel during the scale-out or scale-in process.
• Financial-grade high-availability data is stored in multiple copies, and the data replicas synchronize the transaction log through the Multi-Raft protocol, and the majority of the parties can only commit successful transactions, ensuring strong data consistency and not affecting the availability of data when a few replicas fail. Policies such as the geographic location and number of replicas can be configured as needed to meet the requirements of different disaster recovery levels.
• Real-time HTAP provides two storage engines: TiKV and TiFlash, which replicate data from TiKV in real time through the Multi-Raft Learner protocol to ensure strong data consistency between the row storage engine TiKV and the column storage engine TiFlash. TiKV and TiFlash can be deployed on different machines on demand to solve the problem of HTAP resource isolation.
• Cloud-native distributed databases are distributed databases designed for the cloud, and can be deployed in public clouds, private clouds, and hybrid clouds through TiDB Operator.
• Compatible with the MySQL protocol and MySQL ecosystem, MySQL protocol, common MySQL functions, and MySQL ecosystem, applications can be migrated from MySQL to TiDB without or modifying a small amount of code. Provides a variety of data migration tools to help applications easily migrate data.

Applicable scenarios

apply

1. If the MySQL business of the original business encounters a single-node capacity or performance bottleneck, you can consider using TiDB to seamlessly replace MySQL. TiDB provides the following features:

• Horizontal scaling of throughput, storage, and compute power
• Non-stop service when horizontally scaling
• Strong consistency distributed ACID transactions

1. In the case of large data volumes, complex MySQL queries are slow.
2. Under the large amount of data, the data grows rapidly, which is close to the limit of stand-alone processing, and you do not want to use sharding solutions such as database and table sharding or database middleware, which are highly intrusive and constraining.
3. Under the large amount of big data, there are requirements for high-concurrency real-time writes, real-time queries, and real-time statistical analysis.
4. There are requirements for distributed transactions, 100% strong consistency of data in multiple data centers, and high availability of auto-failover.

not applicable

1. TiDB is not used in scenarios that can be met by a single MySQL machine.
2. TiDB is usually not used in scenarios where the number of data records is less than 5000W, and TiDB is designed for large-scale data scenarios.
3. If your application has a small amount of data (less than 10 million rows of all data) and does not require high availability, strong consistency, or multi-data center replication, then TiDB is not suitable for you.

2. Tidb environment setup

pull images

docker pull xuxuclassmate/tidb

start tidb server

docker run --name tidb -d --privileged=true -p 4000:4000 xuxuclassmate/tidb

use mysql client to connect tidb

mysql -h 127.0.0.1 -P 4000 -u root

init data

CREATE DATABASE demo;
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON demo.* TO 'test'@'%';
FLUSH PRIVILEGES;
use mysql
update user set authentication_string = password('123456') where User = 'root';
FLUSH PRIVILEGES;
CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB ;
INSERT INTO demo.`user`(id, name, age)VALUES(1, 'jack', 18);
INSERT INTO demo.`user`(id, name, age)VALUES(2, 'alyssa', 19);

3. Code engineering

Purpose: To read table data in the tidb database

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
    <artifactId>tidb</artifactId>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>
</project>

entity

package com.et.tidb.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("user")
public class UserPO {
    @TableId(value = "id",type = IdType.AUTO)
    private int id;
    @TableField("name")
    private String name;
    @TableField("age")
    private int age;
}

mapper

package com.et.tidb.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.et.tidb.entity.UserPO;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper extends BaseMapper<UserPO> {
}

application.yaml

server:
  port: 8088
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://127.0.0.1:4000/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456

4. Testing

package com.et.tidb;
import com.et.tidb.entity.UserPO;
import com.et.tidb.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class MysqlTests {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testmysql(){
        for (UserPO row : userMapper.selectList(null)) {
            System.out.println(row.toString());
        }
    }
}

Run the test class, view the results, and see the output database records

5. References

• https://docs.pingcap.com/zh/tidb/dev
• http://www.liuhaihua.cn/archives/710643.html