在使用sharding-jdbc时,可以通过spring 进行整合,也可以不通过spring直接使用:
1:通过pom文件引入sharding-jdbc
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>3.0.0.M1</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>3.0.0.M1</version>
</dependency>
2:与spring 进行整合
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.io/schema/shardingsphere/sharding
http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd">
<!--第一个数据源-->
<bean id="ds_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_0" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<!--第二个数据源-->
<bean id="ds_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/ds_1" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
<!--根据用户id对2取模求佘对数据库分片-->
<sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds_$->{user_id % 2}" />
<sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order_$->{order_id % 2}" />
<sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_id" algorithm-expression="t_order_item_$->{order_id % 2}" />
<!--该daSorce 可以和其它的ORM框架一起使用,将ORM中的Factory 注入该DataSource即可-->
<!--也可以在程序中直接获得该DataSource-->
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="ds_0,ds_1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" actual-data-nodes="ds_$->{0..1}.t_order_$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderTableStrategy" />
<sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds_$->{0..1}.t_order_item_$->{0..1}" database-strategy-ref="databaseStrategy" table-strategy-ref="orderItemTableStrategy" />
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
3:获取DataSource 对象
直接在Spring 中使用注解即可获得。
@Resource
private DataSource dataSource;
1:通过pom文件引入sharding-jdbc
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>3.0.0.M1</version>
</dependency>
2:JAVA程序直接调用sharding-jdbc的DataSource
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第一个数据源
BasicDataSource dataSource1 = new BasicDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/ds_0");
dataSource1.setUsername("root");
dataSource1.setPassword("");
dataSourceMap.put("ds_0", dataSource1);
// 配置第二个数据源
BasicDataSource dataSource2 = new BasicDataSource();
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setUrl("jdbc:mysql://localhost:3306/ds_1");
dataSource2.setUsername("root");
dataSource2.setPassword("");
dataSourceMap.put("ds_1", dataSource2);
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");
orderTableRuleConfig.setActualDataNodes("ds_${0..1}.t_order_${0..1}");
// 配置分库 + 分表策略
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds_ $ { user_id % 2}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$ { order_id % 2}"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
// 省略配置order_item表规则...
// ...
// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?";
try {
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql));
preparedStatement.setInt(1, 10);
preparedStatement.setInt(2, 1001);
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
}
}catch(Exception e){
}
规则配置包括数据源配置
、表规则配置
、分库策略
和分表策略
组成。
这只是最简单的配置方式,实际使用可更加灵活,如:多分片键,分片策略直接和表规则配置绑定等。