Offer to come, dig friends take it! I am participating in the 2022 Spring Recruit Punch card activity. Click here for details.

Introduction to the

ShardingSphere is an ecosystem of open source distributed database middleware solutions, which is composed of three independent products, Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned). They all provide standardized data sharding, distributed transaction and database governance functions, which can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, cloud native and so on.

ShardingSphere is positioned as relational database middleware, aiming at fully and reasonably utilizing the computing and storage capabilities of relational databases in distributed scenarios, rather than realizing a brand new relational database. It coexists with NoSQL and NewSQL rather than being mutually exclusive. NoSQL and NewSQL are highly recommended as being at the forefront of new technology exploration, looking to the future and embracing change. Conversely, you can look at things in a different way, looking to the future, focusing on what doesn’t change, and then grasping the essence of things. Relational databases still occupy a huge market today, and are the cornerstone of each company’s core business, and will be difficult to shake in the future. At present, we are more focused on incremental, rather than subversive, on the original foundation.

ShardingSphere has graduated from the Apache Incubator as an Apache Top-level project on April 16, 2020.

Quick start

Create a table

The important thing to note here is that the field does not have a keyword subclass, as long as possible.

create database `cloud-order`;

use `cloud-order`;

create database if not exists `cloud-order` default charset utf8mb4 collate utf8mb4_general_ci;

create table t_order_1 (
  `oid` bigint(20) unsigned not null auto_increment comment 'primary key id',
  `order_code` varchar(64) not null default ' ' comment 'Order Number'.primary key (`oid`),
  key `idx_order_code` (`order_code`)
) comment 'Order sheet';

create table t_order_2 (
  `oid` bigint(20) unsigned not null auto_increment comment 'primary key id',
  `order_code` varchar(64) not null default ' ' comment 'Order Number'.primary key (`oid`),
  key `idx_order_code` (`order_code`)
)comment 'Order sheet';

Copy the code

Add the dependent

Let me tell you the basic information of the project:

Spring boot 2.6.5
Spring cloud 2021.0.1
Mybatis – spring – the boot – starter 2.2.2
Sharding – JDBC – spring – the boot – starter 4.4.1

At present, it is a relatively new version, so there will be a lot of pits. The specific configuration is as follows (since my configuration is modified in the previous project, it is not concise enough for everyone to see) :

plugins {
    id 'java'
    id 'org.springframework.boot' version '2.6.5'
    id 'io.spring.dependency-management' version '1.0.11. RELEASE'
}

group 'io.zhengsh'
version = '0.0.1 - the SNAPSHOT'
sourceCompatibility = 1.8

repositories {
    mavenLocal()
    maven { url 'https://maven.aliyun.com/nexus/content/groups/public/' }
    mavenCentral()
}

ext {
    set('springCloudVersion'."2021.0.1")
}

configurations {
    compile.exclude module: 'spring-boot-starter-tomcat'
}


dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-undertow'
    implementation 'org.springframework.boot:spring-boot-starter-actuator'

    implementation 'org.springframework.cloud:spring-cloud-starter-netflix-eureka-client'
    implementation 'org.springframework.cloud:spring-cloud-starter-openfeign'
    implementation 'org.springframework.cloud:spring-cloud-starter-circuitbreaker-resilience4j'

    // mysql
    implementation 'mysql:mysql-connector-java'
    // mybatis
    implementation 'org. Mybatis. Spring. The boot: mybatis - spring - the boot - starter: 2.2.2'
    // shardingsphere
    implementation 'org. Apache. Shardingsphere: sharding - JDBC - spring - the boot - starter: 4.4.1'

    testImplementation 'org.springframework.boot:spring-boot-starter-test'

    compileOnly 'org. Projectlombok: lombok: 1.18.22'
    annotationProcessor 'org. Projectlombok: lombok: 1.18.22'

    testCompileOnly 'org. Projectlombok: lombok: 1.18.22'
    testAnnotationProcessor 'org. Projectlombok: lombok: 1.18.22'
}


dependencyManagement {
    imports {
        mavenBom "org.springframework.cloud:spring-cloud-dependencies:${springCloudVersion}"
    }
}


tasks.named('test') {
    useJUnitPlatform()
}

Copy the code

Add the YML configuration

Add the YML configuration, mainly with the following configuration

By setting up the data source, you configure which libraries are available
Set up the table, the table that participates in sharding
To set the strategy of sub-table, I used the snowflake algorithm to generate ID and specify the data location by taking the mode of module.

spring:
  shardingsphere:
    # set data source
    datasource:
      names: o1
      o1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: JDBC: mysql: / / 127.0.0.1:3306 / cloud - the order? useUnicode=true&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: root123
    props:
      # print SQL
      sql.show: true
    sharding:
      default-data-source-name: o1
      tables:
        t_order:
          # specify the table to be divided
          # expression, actual data node: find this value based on the previous node, {0.. 1} for Groovy, $will be replaced with {0.. 1}, the database tables are t_order_1, t_order_2
          This configuration tells Sharding how many tables there are
          actual-data-nodes: o1.t_order_$->{1.. 2}
          key-generator:
            # specify primary key
            column: oid
						# Generation method: Snowflake algorithm
            type: snowflake
            props:
              worker:
                id: 1
          # table splitting strategy
          table-strategy:
            inline:
							# configure sharding's computed column
              sharding-column: oid
              Sharding column = sharding column = sharding column = sharding column = sharding column = sharding column
              algorithm-expression: t_order_$->{oid % 2 + 1}
Copy the code

Java code

1. Entity classes

public class OrderEntity {

    private Long oid;

    private String orderCode;

    // omit the get and set methods here
}
Copy the code

Mapper file

@Mapper
public interface OrderEntityMapper {


    @Insert("insert into o1.t_order (`order_code`) values(#{orderCode})")
    @Options(useGeneratedKeys = true, keyProperty = "oid", keyColumn = "oid")
    int insertSelective(OrderEntity record);

    @Select("select * from o1.t_order where oid = #{oid}")
    OrderEntity selectOne(@Param("oid") Long oid);
}
Copy the code

Unit testing

@Test
public void insertOrder(a) {
  for (int i = 0; i < 10; i++) {
    OrderEntity order = new OrderEntity();
    order.setOrderCode("OR000" + i);
    int row = orderEntityMapper.insertSelective(order);
    Assert.assertTrue(row > 0); }}@Test
public void selectOne(a) {
  OrderEntity order1 = orderEntityMapper.selectOne(
715721929377452033L);
  Assert.assertNotNull(order1);
}
Copy the code

The execution result

1. Insert data

2. Query results

Q&A

Health check error

The core is yML configuration. I met a problem before that the health check failed, so I couldn’t configure it, so I used Java Config to avoid this startup error

Add the Java Config configuration as follows:

@Configuration
public class DataSourceConfig {

    @Bean
    public HealthIndicator dbHealthIndicator(DataSource dataSource) {
        DataSourceHealthIndicator indicator = new DataSourceHealthIndicator(dataSource);
        indicator.setQuery("select 1");
        returnindicator; }}Copy the code

Null pointer exception

A null pointer was reported in MyBatis. Mysql > select * from table where id = 1;

### Cause: java.lang.NullPointerException
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)...75 common frames omitted
Caused by: java.lang.NullPointerException: null
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.createProjection(MySQLDMLVisitor.java:446)
// omit some exceptions
Copy the code

At first I thought it was the reason for the table, but later I checked the information on the Internet and found that there were keywords in the table. When viewing exception information, the top prompt is ignored:

line 1:176 mismatched input 'order' expecting {..... Ignore some keywords}Copy the code

Invoke Method mod() on null Object exception

Cannot invoke method mod() on null object] with root cause

### SQL: insert into o1.t_order (`order_code`, `code`) values(?, ?)
### Cause: java.lang.NullPointerException: Cannot invoke method mod(a) on null object
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy112.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
// omit some exceptions
Copy the code

Cause: The sharding key is inconsistent with the fields in the sharding policy

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

ShardingSphere Sharding-JDBC data Sharding practice

Introduction to the

Quick start

Create a table

Add the dependent

Add the YML configuration

Java code

The execution result

Q&A

Health check error

Null pointer exception

Invoke Method mod() on null Object exception

ShardingSphere Sharding-JDBC data Sharding practice

Introduction to the

Quick start

Create a table

Add the dependent

Add the YML configuration

Java code

The execution result

Q&A

Health check error

Null pointer exception

Invoke Method mod() on null Object exception

Related Posts

The highest award. LintCode joins hands with Alibaba Yuntianchi to hold online programming competition

Are you sure you know everything about Maven

Improved performance by optimizing Gunicorn configurations