sharding4.0分库分表

最新推荐文章于 2023-08-08 15:48:06 发布

只会写Null的菜鸡

最新推荐文章于 2023-08-08 15:48:06 发布

阅读量214

点赞数

分类专栏： java 文章标签：数据库 spring

本文链接：https://blog.csdn.net/qq_22650813/article/details/109690254

版权

java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Pom.xml文件

<!-- sharding jdbc 开始-->
<!-- for spring boot -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

<!-- for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<!-- sharding jdbc 结束-->

从当当网的1.X到apache的4.X都集成过，还是4.X的功能强大一点
datasource.properties文件

jdbc.driverClass_01_master=com.mysql.jdbc.Driver
jdbc.jdbcUrl_01_master=jdbc:mysql://10.1.11.59:3306/test01master?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.user_01_master=root
jdbc.password_01_master=root

jdbc.driverClass_02_master=com.mysql.jdbc.Driver
jdbc.jdbcUrl_02_master=jdbc:mysql://10.1.11.59:3306/test02master?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.user_02_master=root
jdbc.password_02_master=root

jdbc.driverClass_01_slave=com.mysql.jdbc.Driver
jdbc.jdbcUrl_01_slave=jdbc:mysql://10.1.11.59:3306/test01slave?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.user_01_slave=root
jdbc.password_01_slave=root

jdbc.driverClass_02_slave=com.mysql.jdbc.Driver
jdbc.jdbcUrl_02_slave=jdbc:mysql://10.1.11.59:3306/test02slave?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.user_02_slave=root
jdbc.password_02_slave=root

jdbc.driverClass_default=com.mysql.jdbc.Driver
jdbc.jdbcUrl_default=jdbc:mysql://10.1.11.59:3306/test?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.user_default=root
jdbc.password_default=root

spring-datasource.xml数据源配置

<?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:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 加载redis参数 -->
    <context:property-placeholder location="classpath:datasource.properties" ignore-unresolvable="true"/>
    <!-- 配置数据源 -->
    <bean id="dataSource_01_master" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass_01_master}" />
        <property name="jdbcUrl" value="${jdbc.jdbcUrl_01_master}" />
        <property name="user" value="${jdbc.user_01_master}" />
        <property name="password" value="${jdbc.password_01_master}" />
    </bean>

    <bean id="dataSource_01_slave" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass_01_slave}" />
        <property name="jdbcUrl" value="${jdbc.jdbcUrl_01_slave}" />
        <property name="user" value="${jdbc.user_01_slave}" />
        <property name="password" value="${jdbc.password_01_slave}" />
    </bean>

    <bean id="dataSource_02_master" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass_02_master}" />
        <property name="jdbcUrl" value="${jdbc.jdbcUrl_02_master}" />
        <property name="user" value="${jdbc.user_02_master}" />
        <property name="password" value="${jdbc.password_02_master}" />
    </bean>

    <bean id="dataSource_02_slave" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass_02_slave}" />
        <property name="jdbcUrl" value="${jdbc.jdbcUrl_02_slave}" />
        <property name="user" value="${jdbc.user_02_slave}" />
        <property name="password" value="${jdbc.password_02_slave}" />
    </bean>

    <bean id="dataSource_default" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass_default}" />
        <property name="jdbcUrl" value="${jdbc.jdbcUrl_default}" />
        <property name="user" value="${jdbc.user_default}" />
        <property name="password" value="${jdbc.password_default}" />
    </bean>
</beans>

spring-sharding.xml

<?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:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:bean="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://shardingsphere.apache.org/schema/shardingsphere/sharding http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

    <import resource="classpath:spring-datasource.xml"/>
    <bean id="userDatabaseShardingAlgorithm" class="com.ff.sharding.UserDatabaseShardingAlgorithm"/>
    <bean id="userItemDatabaseShardingAlgorithm" class="com.ff.sharding.UserItemDatabaseShardingAlgorithm"/>
    <!--数据库sharding策略-->
    <sharding:standard-strategy id="databaseStrategy" sharding-column="id"
                                precise-algorithm-ref="userDatabaseShardingAlgorithm"/>
    <sharding:standard-strategy id="itemdatabaseStrategy" sharding-column="user_id"
                                precise-algorithm-ref="userItemDatabaseShardingAlgorithm"/>
    <!--auth_user表sharding策略:无 -->

    <sharding:none-strategy id="noneStrategy"/>
    <!--    <bean:properties id="properties">-->
    <!--        <prop key="id">123</prop>-->
    <!--    </bean:properties>-->
    <!--默认有雪花算法和UUID，我这里是自定义的，我们公司用的redis分布式ID-->
    <sharding:key-generator id="messageKeyGenerator" type="SIMPLE" column="id"/>

    <!-- sharding数据源-->
    <sharding:data-source id="shardingDataSource">
        <!--default-data-source指定默认数据源, 即没有在<rdb:table-rules>申明的logic-table表,
        即不需要分库分表的表, 全部走默认数据源-->
        <sharding:sharding-rule
                data-source-names="dataSource_01_master,dataSource_01_slave,dataSource_02_master,dataSource_02_slave,dataSource_default"
                default-data-source-name="dataSource_default"
                default-database-strategy-ref="noneStrategy"
                default-table-strategy-ref="noneStrategy">
            <!-- 读写分离的路由 一主一从配置 strategy-ref  -->
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="dataSource_01" master-data-source-name="dataSource_01_master"
                                            slave-data-source-names="dataSource_01_slave"/>
                <sharding:master-slave-rule id="dataSource_02" master-data-source-name="dataSource_02_master"
                                            slave-data-source-names="dataSource_02_slave"/>
            </sharding:master-slave-rules>
            <!-- 读写分离配置 结束-->
            <sharding:table-rules>
                <!--auth_user只分库不分表, actual-tables的值一定要加上:sj_ds_${0..3}.,
                否则会遍历data-sources, 而sj_ds_default中并没有auth_user表 -->
                <sharding:table-rule logic-table="user" actual-data-nodes="dataSource_0${1..2}.user"
                                     database-strategy-ref="databaseStrategy" key-generator-ref="messageKeyGenerator"/>
                <sharding:table-rule logic-table="user_item" actual-data-nodes="dataSource_0${1..2}.user_item"
                                     database-strategy-ref="itemdatabaseStrategy"
                                     key-generator-ref="messageKeyGenerator"/>
            </sharding:table-rules>
            <sharding:binding-table-rules><!--绑定表的配置 -->
                <sharding:binding-table-rule logic-tables="user,user_item"/>
            </sharding:binding-table-rules>

            <sharding:broadcast-table-rules><!--广播表的配置 保存的时候每个表都保存-->
                <sharding:broadcast-table-rule table="user_base"/>
            </sharding:broadcast-table-rules>


        </sharding:sharding-rule>
        <sharding:props>
            <prop key="sql.show">true</prop>
            <prop key="executor.size">2</prop>
        </sharding:props>
    </sharding:data-source>

    <bean id="transactionMybatisManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="shardingDataSource"/>
    </bean>

    <!--使用注解事务管理 ，为添加mybatis添加 -->
    <tx:annotation-driven transaction-manager="transactionMybatisManager"/>

    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!---datasource交给sharding-jdbc托管-->
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="typeAliasesPackage" value="com.ff.entity"/>
        <property name="configLocation" value="classpath:mybatis-config.xml"></property>
    </bean>
    <!-- 映射配置器 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.ff.mapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
</beans>

自定义的ID生成类

package com.ff.sharding;

import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;

import java.util.Properties;

public class UidGeneratorShardingKeyGenerator implements ShardingKeyGenerator {

    public static Integer ID_START=18;

    public Comparable<?> generateKey() {

        return ID_START++;
    }

    public String getType() {
        return "SIMPLE";
    }

    public Properties getProperties() {
        Properties val = new Properties();
        val.setProperty("name", "22");
        return val;
    }

    public void setProperties(Properties properties) {

    }
}

分库取模类

package com.ff.sharding;


import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;


import java.util.Collection;

/**
 * @author wangzhenfei9
 * @version 1.0.0
 * @since 2018年05月14日
 */
@Slf4j
public class UserDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    private static final int SHARDING_NUMBER = 2;

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(String.valueOf(shardingValue.getValue() % SHARDING_NUMBER + 1))) {
                log.debug("the target database name: {}", each);
                return each;
            }
        }
        return "dataSource_default";
    }
}

由于用的自定义主键，必须用SPI
在resource \META-INF\services新建org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator文件内容为
com.ff.sharding.UidGeneratorShardingKeyGenerator
//注意，如果调用不到，打下包就好了
在这里插入图片描述

再不行就直接把文件放到target\classes\META-INF\services下面
如果插入广播表，每个库都会插入
在这里插入图片描述

查询的时候是从库
在这里插入图片描述

增删改是主库
在这里插入图片描述

不过查询所有的时候是主库查询

只会写Null的菜鸡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sharding4.0分库分表

Pom.xml文件<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1&
复制链接

扫一扫