Sharing-Jdbc Implementing mysql Subdatabase and Tables

Brief introduction

The database sub-table is different from the read-write separation. The sub-table is to build the same table in multiple libraries and different tables in the same library. It can be found and implemented by random or hash methods. Read-write separation is to solve the problem of insufficient read-write performance of database. The master library is used for write operation, slave library is used for read operation, and binglog is used to synchronize the data between the master and the slave database.
The realization of database sub-database and sub-table can be realized by itself or by mycat and sharding-jdbc.

Interpolation Concept

(1) OLTP (on-line transaction processing) and OLAP (On-line Analysis Processing) for OLAP and OLTP online transaction processing. OLTP is the main application of traditional relational database, mainly basic and daily transaction processing, such as bank transactions. OLAP is the main application of data warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and understandable query results.
(2) The self-increasing ID of distributed database is not self-increasing. Paging queries in distributed databases need to be implemented with insertion time.
(3) explain command, which shows how mysql uses indexes to process select statements and join tables. It can help to select better indexes and write more optimized query statements. Just add it before the select statement.

Introduction to Sharing-Jdbc

Sharing-jdbc is an intermediate price for client proxy of Dangdang Open Source Network. sharding-jdbc includes sub-database, fragmentation and read-write separation functions. There is no intrusive code for the application, almost no change, compatible with the mainstream orm framework, mainstream database connection pool. At present, apache's incubation project sharding Sphere is developing rapidly. Sharing-jdbc realizes read-write separation and cannot realize data synchronization between master and slave Libraries

Sharing-Jdbc use (Spring boot)

(1) Create sharding-jdbc project and database ds_master_0, ds_master_1, ds_master_0_slave_0, ds_master_1_slave_0

create table order0
(
    id bigint(11) not null comment 'Primary key ID' primary key,
    user_id  bigint(11) null comment 'user ID',
    order_id bigint(11) null comment 'Order ID'
);
create table order1
(
    id bigint(11) not null comment 'Primary key ID' primary key,
    user_id  bigint(11) null comment 'user ID',
    order_id bigint(11) null comment 'Order ID'
);

(2) Adding dependencies

    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>3.0.0</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.13</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>

(3) Configuration file

spring.application.name=sharding-jdbc

#mybatis
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*Mapper.xml

#When registration encounters whether the same name is allowed to be registered, it is invalid in the configuration center.
spring.main.allow-bean-definition-overriding=true

#All master-slave Libraries
sharding.jdbc.datasource.names=dsmaster0,dsmaster1,dsmaster0slave0,dsmaster1slave0

#dsmaster0
sharding.jdbc.datasource.dsmaster0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.dsmaster0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.dsmaster0.url=jdbc:mysql://ailijie.top:3306/ds_master_0?useSSL=false
sharding.jdbc.datasource.dsmaster0.username=root
sharding.jdbc.datasource.dsmaster0.password=

#slave for ds_master_0
sharding.jdbc.datasource.dsmaster0slave0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.dsmaster0slave0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.dsmaster0slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_0_slave_0?useSSL=false
sharding.jdbc.datasource.dsmaster0slave0.username=root
sharding.jdbc.datasource.dsmaster0slave0.password=

#dsmaster1
sharding.jdbc.datasource.dsmaster1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.dsmaster1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.dsmaster1.url=jdbc:mysql://ailijie.top:3306/ds_master_1?useSSL=false
sharding.jdbc.datasource.dsmaster1.username=root
sharding.jdbc.datasource.dsmaster1.password=

#slave for ds_master_1
sharding.jdbc.datasource.dsmaster1slave0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.dsmaster1slave0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.dsmaster1slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_1_slave_0?useSSL=false
sharding.jdbc.datasource.dsmaster1slave0.username=root
sharding.jdbc.datasource.dsmaster1slave0.password=

#Subdatabase Rules
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=dsmaster${user_id % 2}

#Subtable Rules
sharding.jdbc.config.sharding.tables.order.actual-data-nodes=dsmaster${0..1}.order${0..1}
sharding.jdbc.config.sharding.tables.order.table-strategy.inline.shardingColumn=order_id
sharding.jdbc.config.sharding.tables.order.table-strategy.inline.algorithmExpression=order${order_id % 2}
#Using id as distributed primary key
sharding.jdbc.config.sharding.tables.order.key-generator-column-name=user_id

#Mapping relationship between logical master-slave library name and actual master-slave Library
#sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=dsmaster0
#Separate by commas
#sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=dsmaster0
#sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.masterDataSourceName=dsmaster1
#sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.slaveDataSourceNames=dsmaster1slave0

(5) Entity class Order

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Order implements Serializable {
    private static final long serialVersionUID = 427226138907372838L;
    private Long id;

    private Integer userId;
    private Integer orderId;
}

(4)controller external interface

@Slf4j
@RequestMapping("sharding")
@RestController
public class ShardingController {

   @Autowired
   private OrderMapper orderMapper;

   @RequestMapping
   public String helloShardin(){
      return "hello Sharding-jdbc";
   }

   @RequestMapping("insert")
   public void insert(@RequestParam Integer orderId, @RequestParam Integer userId) {
      Order order = Order.builder()
            .orderId(orderId).userId(userId).build();
      orderMapper.insert(order);
      Long id = order.getId();
      log.info("Generated Key--id:" + id);
   }

   @RequestMapping("queryAll")
   public void findAll() {
       List<Order> orders = orderMapper.queryAll();
       log.info("user:{}", orders);
       log.info("user:{}",orders.size());
   }

   @RequestMapping("getById")
   public void getById(@RequestParam Long id) {
      Order order = orderMapper.queryById(id);
      log.info("user:{}", order);
   }

   @RequestMapping("getByUserId")
   public void getByUserId(@RequestParam Long userId) {
      List<Order> orders = orderMapper.queryByUserId(userId);
      log.info("user:{}", orders);
   }

   @RequestMapping("deleteById")
   public void deleteById(@RequestParam Long id) {
        orderMapper.deleteById(id);
        log.info("user:{}", id);
   }
 }

Sharing-jdbc is not intrusive and does not affect business code.
Springboot configuration file specification can be used for configuration.

If you have any questions, please leave a message.

Personal Blog Address Welcome to visit