ShardingSphere 4.x User Manual-Sharding-JDBC-Manual

Navigation

This chapter will introduce the use of Sharding-JDBC. Please refer to Example for more details.

Sharding

Not Use Spring

Introduce Maven Dependency

Rule Configuration Based on Java

Sharding database and table of Sharding-JDBC configure descriptions according to rules. The following example is the configuration of two databases plus two tables, whose databases take module and split according to order_id, tables take module and split according to order_id.

Rule Configuration Based on Yaml

Or use Yaml to configure, similar to configurations above:

shardingRule:
tables:
t_order:
actualDataNodes: ds${0..1}.t_order${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order${order_id % 2}
t_order_item:
actualDataNodes: ds${0..1}.t_order_item${0..1}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds${user_id % 2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item${order_id % 2}
DataSource dataSource = ShardingDataSourceFactory.createDataSource(yamlFile);

Use Native JDBC

Configure objects with ShardingDataSourceFactory or YamlShardingDataSourceFactory to get ShardingDataSource, which is realized by standard JDBC DataSource. Or choose native JDBC to develop through DataSource; or use JPA, MyBatis and other ORM tools. Take native JDBC for example:

Using Spring

Introduce Maven Dependency

<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>

Rule Configuration Based on Spring Boot

spring.shardingsphere.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=
spring.shardingsphere.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

Rule Configuration Based on Spring Boot + JNDI

If you plan to use Sharding-JDBC in Application Server (such as Tomcat) with Spring boot + JNDI, spring.shardingsphere.datasource.${datasourceName}.jndiName can be used as an alternative to series of configuration of datasource. For example:

spring.shardingsphere.datasource.ds0.jndi-name=java:comp/env/jdbc/ds0
spring.shardingsphere.datasource.ds1.jndi-name=jdbc/ds1
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

Rule Configuration Based on Spring Name Space

Use DataSource in Spring

Inject DataSource to use; or configure DataSource in JPA, Hibernate or MyBatis to use.

Rule configurations include data source configuration, table rule configuration, database sharding strategy and table sharding strategy. All these are the simplest configuration methods, and the practical use can be more flexible, like multiple sharding keys, table rule configuration bound directly with sharding strategies, etc. For more detailed configurations, please refer to Configuration Manual.

Read-write Split

Not Use Spring

Introduce Maven Dependency

Rule Configuration Based on Java

Rule Configuration Based on Yaml

Or configure by yaml, similar to the configuration method above:

masterSlaveRule:
name: ds_ms
masterDataSourceName: ds_master
slaveDataSourceNames: [ds_slave0, ds_slave1]

props:
sql.show: true
DataSource dataSource = YamlMasterSlaveDataSourceFactory.createDataSource(yamlFile);

Use Native JDBC

Configure objects with YamlMasterSlaveDataSourceFactory to get MasterSlaveDataSource, which is realized by standard JDBC DataSource interface. Or choose native JDBC to develop through DataSource; or use JPA, MyBatis and other ORM tools. Take native JDBC for example:

Use Spring

Introduce Maven Dependency

<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>

Rule Configuration Based on Spring Boot

spring.shardingsphere.datasource.ds-master.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.ds-master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-master.url=jdbc:mysql://localhost:3306/master
spring.shardingsphere.datasource.ds-master.username=root
spring.shardingsphere.datasource.ds-master.password=
spring.shardingsphere.datasource.ds-slave0.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.ds-slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-slave0.url=jdbc:mysql://localhost:3306/slave0
spring.shardingsphere.datasource.ds-slave0.username=root
spring.shardingsphere.datasource.ds-slave0.password=
spring.shardingsphere.datasource.ds-slave1.type=org.apache.commons.dbcp.BasicDataSource
spring.shardingsphere.datasource.ds-slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-slave1.url=jdbc:mysql://localhost:3306/slave1
spring.shardingsphere.datasource.ds-slave1.username=root
spring.shardingsphere.datasource.ds-slave1.password=
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1
spring.shardingsphere.props.sql.show=true

Rule Configuration Based on Spring Boot + JNDI

If you plan to use Sharding-JDBC in Application Server (such as Tomcat) with Spring boot + JNDI, spring.shardingsphere.datasource.${datasourceName}.jndiName can be used as an alternative to series of configuration of datasource. For example:

spring.shardingsphere.datasource.master.jndi-name=java:comp/env/jdbc/master
spring.shardingsphere.datasource.slave0.jndi-name=jdbc/slave0
spring.shardingsphere.datasource.slave1.jndi-name=jdbc/slave1
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1
spring.shardingsphere.props.sql.show=true

Rule Configuration Based on Spring Name Space

Use DataSource in Spring

Inject DataSource to use; or configure DataSource in JPA, Hibernate or MyBatis to use.

For more detailed configurations, please refer to Configuration Manual.

Open-sourced distributed database middleware for data sharding, read-write splitting and BASE transaction. https://github.com/sharding-sphere/sharding-sphere