Sharing-JDBC Data Sharding User Guide
Opened this issue · 0 comments
GeorgeCh2 commented
0. Introducing Maven Dependencies
<!-- for spring boot -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding.sphere.version}</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding.sphere.version}</version>
</dependency>Remove druid SpringBoot dependency
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.4</version>
</dependency>1. Rule Configuration based on Spring Boot
# DataSource Naming
spring.shardingsphere.datasource.names = data_source_0,data_source_1,data_source_2,data_source_3
# Maximum number of connections that can be opened per query (when the project is initialized, sharding-jdbc will load the meta information for all the tables in each datasource, so this number of connections needs to be adjusted to improve startup speed)
spring.shardingsphere.props.max.connections.size.per.query = 10
# Data source configuration
# Database connection pool name
data_source_0.type=com.alibaba.druid.pool.DruidDataSource
# Database driver class name
Database driver class name=spring.shardingsphere.datasource.data_source_0.driver-class-name=com.mysql.jdbc.
# database url
spring.shardingsphere.datasource.data_source_0.url=jdbc:mysql://xxx
# Database username
spring.shardingsphere.datasource.data_source_0.username = xxx
# Database password
spring.shardingsphere.datasource.data_source_0.password = xxx
# Other properties of the database connection pool
spring.shardingsphere.datasource.data_source_0.filters = mergeStat,com.btime.webser.monitor.druid.falconFilter
spring.shardingsphere.datasource.data_source_0.initial-size = 5
spring.shardingsphere.datasource.data_source_0.min-idle = 5
spring.shardingsphere.datasource.data_source_0.max-active = 100
spring.shardingsphere.datasource.data_source_0.validation-query = SELECT 1
spring.shardingsphere.datasource.data_source_0.test-while-idle = true
spring.shardingsphere.datasource.data_source_0.test-on-borrow = false
spring.shardingsphere.datasource.data_source_0.test-on-return = false
spring.shardingsphere.datasource.data_source_0.time-between-eviction-runs-millis = 60000
spring.shardingsphere.datasource.data_source_0.min-evictable-idle-time-millis = 300000
spring.shardingsphere.datasource.data_source_1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.data_source_1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.data_source_1.url = jdbc:mysql://xxx
spring.shardingsphere.datasource.data_source_1.username = xxx
spring.shardingsphere.datasource.data_source_1.password = xxx
spring.shardingsphere.datasource.data_source_1.initial-size = 5
spring.shardingsphere.datasource.data_source_1.min-idle = 5
spring.shardingsphere.datasource.data_source_1.max-active = 100
spring.shardingsphere.datasource.data_source_1.validation-query = SELECT 1
spring.shardingsphere.datasource.data_source_1.test-while-idle = true
spring.shardingsphere.datasource.data_source_1.test-on-borrow = false
spring.shardingsphere.datasource.data_source_1.test-on-return = false
spring.shardingsphere.datasource.data_source_1.time-between-eviction-runs-millis = 60000
spring.shardingsphere.datasource.data_source_1.min-evictable-idle-time-millis = 300000
spring.shardingsphere.datasource.data_source_2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.data_source_2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.data_source_2.url = jdbc:mysql://xxx
spring.shardingsphere.datasource.data_source_2.username = xxxx
spring.shardingsphere.datasource.data_source_2.password = xxx
spring.shardingsphere.datasource.data_source_2.initial-size = 5
spring.shardingsphere.datasource.data_source_2.min-idle = 5
spring.shardingsphere.datasource.data_source_2.max-active = 100
spring.shardingsphere.datasource.data_source_2.validation-query = SELECT 1
spring.shardingsphere.datasource.data_source_2.test-while-idle = true
spring.shardingsphere.datasource.data_source_2.test-on-borrow = false
spring.shardingsphere.datasource.data_source_2.test-on-return = false
spring.shardingsphere.datasource.data_source_2.time-between-eviction-runs-millis = 60000
spring.shardingsphere.datasource.data_source_2.min-evictable-idle-time-millis = 300000
spring.shardingsphere.datasource.data_source_3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.data_source_3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.data_source_3.url = jdbc:mysql://xxx
spring.shardingsphere.datasource.data_source_3.username = xxx
spring.shardingsphere.datasource.data_source_3.password = xxx
spring.shardingsphere.datasource.data_source_3.initial-size = 5
spring.shardingsphere.datasource.data_source_3.min-idle = 5
spring.shardingsphere.datasource.data_source_3.max-active = 100
spring.shardingsphere.datasource.data_source_3.validation-query = SELECT 1
spring.shardingsphere.datasource.data_source_3.test-while-idle = true
spring.shardingsphere.datasource.data_source_3.test-on-borrow = false
spring.shardingsphere.datasource.data_source_3.test-on-return = false
spring.shardingsphere.datasource.data_source_3.time-between-eviction-runs-millis = 60000
spring.shardingsphere.datasource.data_source_3.min-evictable-idle-time-millis = 300000
# default DataSource
spring.shardingsphere.sharding.default-data-source-name = data_source_0
# DataSource sharding configuration
spring.shardingsphere.sharding.tables.data_source.actual-data-nodes = data_source_$->{0..3}.data_source_$->{0..1023}
spring.shardingsphere.sharding.tables.data_source.database-strategy.inline.sharding-column = fid
spring.shardingsphere.sharding.tables.data_source.database-strategy.inline.algorithm-expression = data_source_$->{fid % 4}
spring.shardingsphere.sharding.tables.data_source.table-strategy.inline.sharding-column = fid
spring.shardingsphere.sharding.tables.data_source.table-strategy.inline.algorithm-expression = data_source_$->{(fid.intdiv(4)) % 1024}
spring.shardingsphere.sharding.tables.data_source_ext.actual-data-nodes = file_ext_$->{0..3}.data_source_ext_$->{0..1023}
spring.shardingsphere.sharding.tables.data_source_ext.database-strategy.inline.sharding-column = fid
spring.shardingsphere.sharding.tables.data_source_ext.database-strategy.inline.algorithm-expression = file_ext_$->{fid % 4}
spring.shardingsphere.sharding.tables.data_source_ext.table-strategy.inline.sharding-column = fid
spring.shardingsphere.sharding.tables.data_source_ext.table-strategy.inline.algorithm-expression = data_source_ext_$->{(fid.intdiv(4)) % 1024}
2. Cautions
- You need to configure the location of mybatis mapper.xml and config.xml.
#mybatis mybatis.config-location = classpath:mybatis/config.xml mybatis.mapper-locations = classpath:mybatis/mapper/*.xml
- The result of a groovy expression division is not an integer, you need to use intdiv() to round it up.
- on duplicate key update does not support #{name} format, need to replace it with values(name).
The official MySQL documentation explains this:
In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.