数据库分库分表插件使用
数据库分库分表插件shardingsphere
shardingsphere简介
Apache ShardingSphere(Incubator) 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。
Share-JDBC简介
定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。
适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。
sharejdbc配置
<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:spi="http://shardingsphere.apache.org/schema/shardingsphere/spi"xmlns:bean="http://www.springframework.org/schema/util"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsdhttp://shardingsphere.apache.org/schema/shardingsphere/shardinghttp://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsdhttp://shardingsphere.apache.org/schema/shardingsphere/spihttp://shardingsphere.apache.org/schema/shardingsphere/spi/spi.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd"><bean id="demo_ds_0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.jdbc.Driver"/><property name="jdbcUrl" value="jdbc:mysql://192.168.50.65:3306/ds0?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/><property name="username" value="root"/><property name="password" value="root"/></bean><bean id="demo_ds_1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.jdbc.Driver"/><property name="jdbcUrl" value="jdbc:mysql://192.168.50.65:3306/ds1?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/><property name="username" value="root"/><property name="password" value="root"/></bean><spi:sharding-algorithm id="databaseStrategyShardingAlgorithm" type="INLINE" props-ref="databaseStrategyShardingAlgorithmProp" /><spi:sharding-algorithm id="tableStrategyShardingAlgorithm" type="INLINE" props-ref="tableStrategyShardingAlgorithmProp" /><bean:properties id="databaseStrategyShardingAlgorithmProp"><prop key="algorithm.expression"><![CDATA[demo_ds_$->{user_id % 2}]]></prop></bean:properties><bean:properties id="tableStrategyShardingAlgorithmProp"><prop key="algorithm.expression"><![CDATA[t_user_$->{user_id % 3}]]></prop></bean:properties><bean:properties id="properties"><prop key="worker.id">123</prop></bean:properties><sharding:standard-strategy id="databaseStrategy" sharding-column="user_id" algorithm-ref="databaseStrategyShardingAlgorithm"/><sharding:standard-strategy id="tableStrategy" sharding-column="user_id" algorithm-ref="tableStrategyShardingAlgorithm"/><spi:key-generate-algorithm id="snowflakeAlgorithm" type="SNOWFLAKE" props-ref="properties"/><sharding:key-generator id="orderKeyGenerator" column="user_id" algorithm-ref="snowflakeAlgorithm" /><sharding:data-source id="shardingDataSource"><sharding:sharding-rule data-source-names="demo_ds_0, demo_ds_1"><sharding:table-rules><sharding:table-rule logic-table="t_user" actual-data-nodes="demo_ds_$->{0..1}.t_user_$->{0..2}" database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy" key-generator-ref="orderKeyGenerator" /></sharding:table-rules><sharding:binding-table-rules><sharding:binding-table-rule logic-tables="t_user"/></sharding:binding-table-rules></sharding:sharding-rule></sharding:data-source><bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="shardingDataSource" /></bean><tx:annotation-driven /><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="shardingDataSource"/><property name="mapperLocations" value="classpath*:META-INF/mappers/*.xml"/></bean><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"><property name="basePackage" value="cn.com.immortals.ds.share"/><property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/><property name="annotationClass" value="cn.com.immortals.ds.share.RepositoryImpl" /></bean></beans>
配置解析
配置时需要配置物理数据源，物理数据源是与数据库直接链接，使用任意数据连接即可
配置分库算法，<spi:sharding-algorithm id="databaseStrategyShardingAlgorithm" type="INLINE" props-ref="databaseStrategyShardingAlgorithmProp" /> 制定分库条件，
配置分表算法<sharding:standard-strategy id="tableStrategy" sharding-column="user_id" algorithm-ref="tableStrategyShardingAlgorithm"/> 制定分表的条件表达式
配置逻辑数据源 shareDataSource
配置ORM框架，使用上没有区别