Shulou(Shulou.com)06/03 Report--

This article introduces the knowledge of "how to integrate multiple data sources with ShardingSphere jdbc". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Recently, there are several tables of a project, the order of magnitude is more than 10 million, and the technology stack is SpringBoot+Mybatis-plus+MySQL. If you use a single table, the query operation is very time-consuming, after some research, decided to use sub-table middleware: ShardingSphere.

ShardingSphere became a top-level project of the Apache Software Foundation in April this year. Currently, it supports data sharding, read-write separation, multiple data copies, data encryption, shadow library stress testing and other functions. It is also compatible with a variety of databases. Through the pluggable architecture, it is ideally unaware of the business code.

There are two mature products under ShardingSphere: sharding jdbc and sharding proxy

Sharding jdbc: can be understood as an enhanced version of the JDBC driver

Sharding proxy: a transparent database agent that can be seen as a virtual database service.

Integrated sharding jdbc

It's easy to integrate sharding jdbc alone, and for a better understanding, take the order table as an example.

1. Introduce dependency 4.1.0 org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${sharding-sphere.version} 2. Configure subtable rules spring: shardingsphere: datasource: names: sharding-order-system sharding-order-system: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/order_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&useTimezone=true username: root password: root props: # Log Display SQL sql.show: true sharding: tables: # order table: 20 order: # Real table order_0 actualDataNodes: sharding-order-system.order_$- > {0.19} # Sub-library policy databaseStrategy: none: # sub-table policy tableStrategy: Inline: shardingColumn: order_key # slicing algorithm line expression Need to comply with groovy syntax'& Integer.MAX_VALUE' bit operation to make hash value positive algorithmExpression: order_$- > {(order_key.hashCode () & Integer.MAX_VALUE)% 20} problem

Although the above has completed the sub-table of the order table (order), sharding jdbc does not support some syntax, which is generally stated in the official document, as shown in the following figure:

Like insert into... Select these grammars are not supported, and there are the same restrictions for statements that do not involve subtables. * * for example, there is a SQL:insert into user_temp select * from user; in the project. After integrating sharding jdbc, the execution of the SQL will report an error even if the user table is not configured with sub-tables.

In the official Q & A, it is mentioned that multiple data sources are used to deal with sharding and non-sharding respectively, sharding jdbc data sources are used for SQL of sub-tables, and ordinary data sources are used for SQL that does not involve sub-tables.

Integrated multiple data sources

We use the baomidou team's open source mybatis-plus in our project, and the team also opened up a multi-source component: dynamic-datasource-spring-boot-starter. After integration, you can switch data sources using @ DS annotation, which is very convenient.

1. Introduce dependency com.baomidou dynamic-datasource-spring-boot-starter 3.1.12. Multiple data source configuration

The core idea is to add sharding jdbc data sources to multiple data sources.

/ * dynamic data source configuration: * * Annotation using {@ link com.baomidou.dynamic.datasource.annotation.DS} Switch data source * * @ DS (DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME) * * @ author songyinyin * @ date 2020-7-27 15:19 * / @ Configuration@AutoConfigureBefore ({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class}) public class DataSourceConfiguration {/ * * subtable data source name * / private static final String SHARDING_DATA_SOURCE_NAME = "gits_sharding" / * dynamic data source configuration item * / @ Autowired private DynamicDataSourceProperties properties; / * shardingjdbc has four data sources, which need to be injected into different data sources according to the business * *

1. Name of desensitization without shard (default): shardingDataSource; *

two。 Master-slave data source: masterSlaveDataSource; *

3. Desensitized data source: encryptDataSource; *

4. Shadow data source: shadowDataSource * * / @ Lazy @ Resource (name = "shardingDataSource") AbstractDataSourceAdapter shardingDataSource; @ Bean public DynamicDataSourceProvider dynamicDataSourceProvider () {Map datasourceMap = properties.getDatasource (); return new AbstractDataSourceProvider () {@ Override public Map loadDataSources () {Map dataSourceMap = createDataSourceMap (datasourceMap) / / transfer the data sources managed by shardingjdbc to dynamic data source management dataSourceMap.put (SHARDING_DATA_SOURCE_NAME, shardingDataSource); return dataSourceMap;}} } / * set the dynamic data source as the preferred * when there are multiple data sources in spring, the preferred object is automatically injected * once set as the main data source, the native configuration of shardingjdbc can be supported * * @ return * / @ Primary @ Bean public DataSource dataSource (DynamicDataSourceProvider dynamicDataSourceProvider) {DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource () DataSource.setPrimary (properties.getPrimary ()); dataSource.setStrict (properties.getStrict ()); dataSource.setStrategy (properties.getStrategy ()); dataSource.setProvider (dynamicDataSourceProvider); dataSource.setP6spy (properties.getP6spy ()); dataSource.setSeata (properties.getSeata ()); return dataSource;}}

Sharding jdbc has four data sources:

No shard, desensitized name (default): shardingDataSource

Master-slave data source: masterSlaveDataSource

Desensitized data source: encryptDataSource

Shadow data source: shadowDataSource

Different data sources need to be injected according to different scenarios. This article takes a sub-table as an example, so shardingDataSource is put into multiple data sources (dataSourceMap).

3. Increase multiple data source configuration

In step 2, we specified the name of the shardingsphere data source as: gits_sharding

Spring: datasource: # dynamic data source configuration dynamic: datasource: master: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/gits?useUnicode=true&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true username: root password: root # specify default Data source name primary: master # Subtable configuration shardingsphere: datasource: names: sharding-order-system sharding-order-system: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://172.20.20.19:3306/order_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&useTimezone=true username: root password: root Props: # Log shows SQL sql.show: true sharding: tables: # order table sub-table: 20 order: # realistic table order_0 actualDataNodes: sharding-order-system.order_$- > {0.19} # sub-library policy databaseStrategy: none: # sub-table policy TableStrategy: inline: shardingColumn: order_key # slicing algorithm line expression Need to comply with groovy syntax'& Integer.MAX_VALUE' bit operation to make the hash value positive algorithmExpression: order_$- > {(order_key.hashCode () & Integer.MAX_VALUE)% 20}

Here, the default data source is specified as a normal data source.

4. Use

Add @ DS ("gits_sharding") to the service method that requires a split table to switch to the sharding jdbc data source.

@ Service@Slf4jpublic class OrderServiceImpl extends OrderService {@ Override @ DS ("gits_sharding") public List getOrderByUser (OrderQueryDTO dto) throws Exception {/ / omit some business codes.}} "how to integrate multiple data sources with ShardingSphere jdbc" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.