歡迎光臨
每天分享高質量文章

【死磕Sharding-jdbc】—基於ssm

點擊上方“Java技術驛站”,選擇“置頂公眾號”。

有內涵、有價值的文章第一時間送達!

本篇文章講解如何在ssm(spring、springmvc、mybatis)結構的程式上集成sharding-jdbc(版本為1.5.4.1)進行分庫分表; 假設分庫分表行為如下:

  • 將authuser表分到4個庫(user0~user_3)中;

  • 其他表不進行分庫分表,保留在default_db庫中;

1. POM配置

以spring配置檔案為例,新增如下POM配置:

  1.    com.dangdang

  •    sharding-jdbc-core

  •    1.5.4.1

  •    com.dangdang

  •    sharding-jdbc-config-spring

  •    1.5.4.1

  • 此次集成sharding-jdbc以1.5.4.1版本為例,如果是2.x版本的sharding-jdbc,那麼需要將坐標 com.dangdang

    修改為 io.shardingjdbc;另外,如果是yaml配置,那麼需要將坐標 sharding-jdbc-config-spring修改為 sharding-jdbc-config-yaml

    2. 配置資料源

    spring-datasource.xml配置所有需要的資料源如下--authuser分庫分表後需要的4個庫user0~user3,以及不分庫分表的預設庫defaultdb:

    1. xml version="1.0" encoding="UTF-8"?>

    2. xmlns="http://www.springframework.org/schema/beans"

    3.       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    4.       xsi:schemaLocation="http://www.springframework.org/schema/beans

    5.        http://www.springframework.org/schema/beans/spring-beans.xsd">

    6.    

    7.     id="sj_ds_0" class="com.alibaba.druid.pool.DruidDataSource"

    8.          init-method="init" destroy-method="close">

    9.         name="url" value="${sj_user_0.url}" />

    10.         name="username" value="${sj_user_0.username}" />

    11.         name="password" value="${sj_user_0.password}" />

    12.        

    13.    

  •    

  •     id="sj_ds_1" class="com.alibaba.druid.pool.DruidDataSource"

  •          init-method="init" destroy-method="close">

  •         name="url" value="${sj_user_1.url}" />

  •         name="username" value="${sj_user_1.username}" />

  •         name="password" value="${sj_user_1.password}" />

  •        

  •    

  •    

  •     id="sj_ds_2" class="com.alibaba.druid.pool.DruidDataSource"

  •          init-method="init" destroy-method="close">

  •         name="url" value="${sj_user_2.url}" />

  •         name="username" value="${sj_user_2.username}" />

  •         name="password" value="${sj_user_2.password}" />

  •        

  •    

  •    

  •     id="sj_ds_3" class="com.alibaba.druid.pool.DruidDataSource"

  •          init-method="init" destroy-method="close">

  •         name="url" value="${sj_user_3.url}" />

  •         name="username" value="${sj_user_3.username}" />

  •         name="password" value="${sj_user_3.password}" />

  •        

  •    

  •    

  •     id="sj_ds_default" class="com.alibaba.druid.pool.DruidDataSource"

  •          init-method="init" destroy-method="close">

  •         name="url" value="${sj_default.url}" />

  •         name="username" value="${sj_default.username}" />

  •         name="password" value="${sj_default.password}" />

  •        

  •    

  • properties配置檔案內容如下:

    1. sj_user_0.driver=com.mysql.jdbc.Driver

    2. sj_user_0.url=jdbc:mysql://localhost:3306/user_0

    3. sj_user_0.username=root

    4. sj_user_0.password=RootAfei_1

    5. sj_user_1.driver=com.mysql.jdbc.Driver

    6. sj_user_1.url=jdbc:mysql://localhost:3306/user_1

    7. sj_user_1.username=root

    8. sj_user_1.password=RootAfei_1

    9. sj_user_2.driver=com.mysql.jdbc.Driver

    10. sj_user_2.url=jdbc:mysql://localhost:3306/user_2

    11. sj_user_2.username=root

    12. sj_user_2.password=RootAfei_1

    13. sj_user_3.driver=com.mysql.jdbc.Driver

    14. sj_user_3.url=jdbc:mysql://localhost:3306/user_3

    15. sj_user_3.username=root

    16. sj_user_3.password=RootAfei_1

    17. sj_default.driver=com.mysql.jdbc.Driver

    18. sj_default.url=jdbc:mysql://localhost:3306/default_db

    19. sj_default.username=root

    20. sj_default.password=RootAfei_1

    3. 集成sharding資料源

    spring-sharding.xml配置如下:

    1. xml version="1.0" encoding="UTF-8"?>

    2. xmlns="http://www.springframework.org/schema/beans"

    3.       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    4.       xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"

    5.       xsi:schemaLocation="http://www.springframework.org/schema/beans

    6.                        http://www.springframework.org/schema/beans/spring-beans.xsd

    7.                        http://www.dangdang.com/schema/ddframe/rdb

    8.                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">

    9.    

    10.     id="databaseStrategy" sharding-columns="id"

    11.                  algorithm-class="com.crt.fin.ospsso.service.shardingjdbc.AuthUserDatabaseShardingAlgorithm" />

    12.    

    13.    

    14.     id="shardingDataSource">

    15.        

    16.         data-sources="sj_ds_0,sj_ds_1,sj_ds_2,sj_ds_3,sj_ds_default"

    17.                           default-data-source="sj_ds_default">

    18.            

    19.                

    20.                 logic-table="auth_user" actual-tables="sj_ds_${0..3}.auth_user"

    21.                                database-strategy="databaseStrategy"/>

    22.            

  •             sharding-columns="none" algorithm-class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.NoneDatabaseShardingAlgorithm"/>

  •             sharding-columns="none" algorithm-class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.NoneTableShardingAlgorithm"/>

  •        

  •        

  •             key="sql.show">true

  •             key="executor.size">2

  •        

  •    

  •    

  •     id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

  •        

  •         name="dataSource" ref="shardingDataSource"/>

  •         name="mapperLocations" value="classpath*:mybatis/*Mapper.xml"/>

  •    

  •     class="org.mybatis.spring.mapper.MapperScannerConfigurer">

  •         name="basePackage" value="com.crt.fin.ospsso.dal.mapper"/>

  •         name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>

  •    

  • 說明:spring-sharding.xml配置的分庫分表規則:authuser表分到id為sjds${0..3}的四個庫中,表名保持不變;其他表在id為sjds_default庫中,不分庫也不分表;集成sharding-jdbc的核心就是將SqlSessionFactoryBean需要的dataSource屬性修改為 shardingDataSource,把資料源交給sharding-jdbc處理;

    分庫邏輯 AuthUserDatabaseShardingAlgorithm的代碼很簡單,原始碼如下:

    1. /**

    2. * @author wangzhenfei9

    3. * @version 1.0.0

    4. * @since 2018年02月08日

    5. */

    6. public class AuthUserDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

    7.    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    8.    private static final int SHARDING_NUMBER = 4;

    9.    @Override

    10.    public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {

    11.        for (String each : availableTargetNames) {

    12.            if (each.endsWith(shardingValue.getValue() % SHARDING_NUMBER + "")) {

    13.                logger.debug("the target database name: {}", each);

    14.                return each;

    15.            }

    16.        }

    17.        throw new UnsupportedOperationException();

    18.    }

    19.    @Override

    20.    public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {

    21.        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

    22.        Collection<Long> values = shardingValue.getValues();

    23.        for (Long value : values) {

    24.            for (String each : availableTargetNames) {

    25.                if (each.endsWith(value % SHARDING_NUMBER + "")) {

    26.                    result.add(each);

    27.                }

    28.            }

    29.        }

    30.        return result;

    31.    }

    32.    @Override

    33.    public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {

    34.        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());

    35.        Range<Long> range = shardingValue.getValueRange();

    36.        for (Long value = range.lowerEndpoint(); value <= range.upperEndpoint(); value++) {

    37.            for (String each : availableTargetNames) {

    38.                if (each.endsWith(value % SHARDING_NUMBER + "")) {

    39.                    result.add(each);

    40.                }

    41.            }

    42.        }

    43.        return result;

    44.    }

    45. }

    這段代碼參考sharding-jdbc原始碼中 DatabaseShardingAlgorithm.java接口的實現即可,例如 ModuloDatabaseShardingAlgorithm.java

    4. 註意事項

    無法識別sharding-jdbc分庫分表規則inline-expression問題,例如: logic-table="auth_user"actual-tables="sj_ds_${0..3}.auth_user"database-strategy="databaseStrategy"/>

    1. 根本原因: 根本原因是spring把 ${}當做占位符, ${0..3}這種運算式,spring會嘗試去properties檔案中找key為 0..3的屬性。但是這裡是sharding-jdbc分庫分表規則的inline運算式,需要spring忽略這種行為。否則會丟擲異常: java.lang.IllegalArgumentException: Could not resolve placeholder '0..3' in value "sjds${0..3}.auth_user"

    2. 解決辦法: 配置: name="ignoreUnresolvablePlaceholders"value="true"/> 或者: ****** ignore-unresolvable="true"/>

    5. Main測試

    Main.java用來測試分庫分表是否OK,其原始碼如下:

    1. /**

    2. * @author wangzhenfei9

    3. * @version 1.0.0

    4. * @since 2018年02月08日

    5. */

    6. public class Main {

    7.    public static void main(String[] args) {

    8.        ApplicationContext context = new ClassPathXmlApplicationContext(

    9.                "/META-INF/spring/spring-*.xml");

    10.        // auth_user有進行分庫,

    11.        AuthUserMapper authUserMapper = context.getBean(AuthUserMapper.class);

    12.        AuthUser authUser = authUserMapper.selectByPrimaryKey(7L);

    13.        System.out.println("-----> The auth user: "+JSON.toJSONString(authUser));

    14.        // user_permission沒有分庫分表

    15.        UserPermissionMapper userPermissionMapper = context.getBean(UserPermissionMapper.class);

    16.        UserPermission userPermission = userPermissionMapper.selectPermissionByUsername("wangzhenfei", "FINANCE_WALLET");

    17.        System.out.println("-----< The user permission: "+JSON.toJSONString(userPermission));

    18.    }

    19. }

    AuthUserMapper.selectByPrimaryKey()和UserPermissionMapper.selectPermissionByUsername()的代碼和沒有分庫分表的代碼完全一樣;

    6. 遺留問題

    Main方法測試,或者啟動服務後的呼叫測試都沒有問題,但是通過junit測試用例訪問就會丟擲異常,作為一個待解決的遺留問題:

    1. org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: expected single matching bean but found 6: sj_ds_0,sj_ds_1,sj_ds_2,sj_ds_3,sj_ds_default,shardingDataSource

    END

    赞(0)

    分享創造快樂

    © 2022 知識星球   网站地图