在使用sharding-jdbc时,可以通过spring 进行整合,也可以不通过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;

不使用Spring 整合

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){
}
                    

规则配置包括数据源配置表规则配置分库策略分表策略组成。
这只是最简单的配置方式,实际使用可更加灵活,如:多分片键,分片策略直接和表规则配置绑定等。

Copyright © 2019-2020 2024-11-21 18:24:05