spring+mybatis+druid数据源+sharding-jdbc分库分表
来源：互联网 发布：非典生化武器知乎 编辑：程序博客网 时间：2024/06/02 04:13
首先我们看下如何让spring与mybatis集成，我使用的是mysql数据库，建库建表语句如下：
drop database if exists demodb00;CREATE database demodb00 DEFAULT CHARACTER SET utf8;CREATE TABLE demodb00.user ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(100) DEFAULT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY id_UNIQUE (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
package net.aty.spring.entity;public class UserEntity { private int id; private String name; private int age; public UserEntity() { } public UserEntity(int id, String name, int age) { this.id = id; this.name = name; this.age = age; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getId() { return id; } public void setId(int id) { this.id = id; } @Override public String toString() { return "UserEntity{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; }}package net.aty.spring.mapper;import net.aty.spring.entity.UserEntity;public interface UserMapper { int insertOne(UserEntity user); UserEntity selectByPk(int id);}<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="net.aty.spring.mapper.UserMapper"> <insert id="insertOne" parameterType="net.aty.spring.entity.UserEntity"> INSERT INTO user(id, name,age) VALUE(#{id},#{name},#{age}) </insert> <select id="selectByPk" resultType="net.aty.spring.entity.UserEntity" parameterType="java.lang.Integer"> select * from user where id=#{id} </select></mapper><?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <!-- 配置mybatis的缓存，延迟加载等等一系列属性 --> <settings> <!-- 全局映射器启用缓存 --> <setting name="cacheEnabled" value="true"/> <!-- 查询时，关闭关联对象即时加载以提高性能 --> <setting name="lazyLoadingEnabled" value="false"/> <!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 --> <setting name="aggressiveLazyLoading" value="true"/> <!-- 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果 --> <setting name="multipleResultSetsEnabled" value="true"/> <!-- 允许使用列标签代替列名 --> <setting name="useColumnLabel" value="true"/> <!-- 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖 --> <!-- <setting name="useGeneratedKeys" value="true" /> --> <!-- 给予被嵌套的resultMap以字段-属性的映射支持 --> <setting name="autoMappingBehavior" value="FULL"/> <!-- 对于批量更新操作缓存SQL以提高性能 --> <setting name="defaultExecutorType" value="REUSE"/> <!-- 数据库超过25000秒仍未响应则超时 --> <setting name="defaultStatementTimeout" value="25000"/> </settings></configuration>
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"><!--配置数据源--> <bean id="dataSourceResource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="username" value="root"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb00"/> <property name="password" value="root"/> <property name="maxActive" value="10"/> <property name="initialSize" value="1"/> <property name="maxWait" value="60000"/> <property name="minIdle" value="1"/> <property name="timeBetweenEvictionRunsMillis" value="60000"/> <property name="minEvictableIdleTimeMillis" value="300000"/> <property name="validationQuery" value="SELECT 'x'"/> <property name="testWhileIdle" value="true"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/> <property name="maxOpenPreparedStatements" value="100"/> <property name="proxyFilters"> <list> <ref bean="statFilter"/> <ref bean="logFilter"/> </list> </property> </bean> <bean id="statFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter"> <property name="statementExecutableSqlLogEnable" value="false"/> <property name="dataSourceLogEnabled" value="false"/> </bean> <bean id="logFilter" class="com.alibaba.druid.filter.stat.StatFilter"> <property name="slowSqlMillis" value="50"/> <property name="logSlowSql" value="false"/> <property name="mergeSql" value="true"/> </bean><!--整合spring与mybatis--> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSourceResource"/> <property name="configLocation" value="classpath:/META-INF/mybatis.xml"></property> <property name="typeAliasesPackage" value="net.aty.spring.mybatis.entity"></property> <property name="mapperLocations" value="classpath*:/META-INF/mybatis/*.xml"></property> </bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="net.aty.spring.mapper"></property> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean></beans>
package net.aty.spring;import net.aty.spring.entity.UserEntity;import net.aty.spring.mapper.UserMapper;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;public class Main { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("META-INF/spring-mybatis.xml"); UserMapper mapper = context.getBean(UserMapper.class); mapper.insertOne(new UserEntity(1, "xx", 11)); mapper.insertOne(new UserEntity(2, "xxzzz", 11)); System.out.println(mapper.selectByPk(1)); System.out.println(mapper.selectByPk(2)); }}
上面我们完成了spring与mybatis的整合，随着业务的变化，数据越来越多，我们需要分库分表。我们项目使用的当当中间件：sharding-jdbc，github地址是：https://github.com/dangdangdotcom/sharding-jdbc。假设我们分为demodb00和demodb01这2个数据库，每个库分user_0和user_1这2张表。
sharding-jdbc.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:rdb="http://www.dangdang.com/schema/ddframe/rdb" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.dangdang.com/schema/ddframe/rdb http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd"> <bean id="statFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter"> <property name="statementExecutableSqlLogEnable" value="false"/> <property name="dataSourceLogEnabled" value="false"/> </bean> <bean id="logFilter" class="com.alibaba.druid.filter.stat.StatFilter"> <property name="slowSqlMillis" value="50"/> <property name="logSlowSql" value="false"/> <property name="mergeSql" value="true"/> </bean> <bean id="master0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="username" value="root"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb00"/> <property name="password" value="root"/> <property name="maxActive" value="10"/> <property name="initialSize" value="1"/> <property name="maxWait" value="60000"/> <property name="minIdle" value="1"/> <property name="timeBetweenEvictionRunsMillis" value="60000"/> <property name="minEvictableIdleTimeMillis" value="300000"/> <property name="validationQuery" value="SELECT 'x'"/> <property name="testWhileIdle" value="true"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/> <property name="maxOpenPreparedStatements" value="100"/> <property name="proxyFilters"> <list> <ref bean="statFilter"/> <ref bean="logFilter"/> </list> </property> </bean> <bean id="master1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="username" value="root"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/demodb01"/> <property name="password" value="root"/> <property name="maxActive" value="10"/> <property name="initialSize" value="1"/> <property name="maxWait" value="60000"/> <property name="minIdle" value="1"/> <property name="timeBetweenEvictionRunsMillis" value="60000"/> <property name="minEvictableIdleTimeMillis" value="300000"/> <property name="validationQuery" value="SELECT 'x'"/> <property name="testWhileIdle" value="true"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> <property name="poolPreparedStatements" value="true"/> <property name="maxPoolPreparedStatementPerConnectionSize" value="50"/> <property name="maxOpenPreparedStatements" value="100"/> <property name="proxyFilters"> <list> <ref bean="statFilter"/> <ref bean="logFilter"/> </list> </property> </bean> <rdb:master-slave-data-source id="rbb_0" master-data-source-ref="master0" slave-data-sources-ref="master0"/> <rdb:master-slave-data-source id="rbb_1" master-data-source-ref="master1" slave-data-sources-ref="master1"/> <rdb:strategy id="idDbSharding" sharding-columns="id" algorithm-class="net.aty.spring.DbAlgorithm"/> <rdb:strategy id="idTbSharding" sharding-columns="id" algorithm-class="net.aty.spring.TbAlgorithm"/> <rdb:data-source id="wholeDataSource"> <rdb:sharding-rule data-sources="rbb_0,rbb_1"> <rdb:table-rules> <rdb:table-rule logic-table="user" actual-tables="user_${0..1}" database-strategy="idDbSharding" table-strategy="idTbSharding"/> </rdb:table-rules> </rdb:sharding-rule> </rdb:data-source></beans>这个文件中定义了2个库的数据源，以及我们的分库分表规则，我是按照id分库分表的：
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;import java.util.Collection;public class DbAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer> { @Override public String doEqualSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) { int id = shardingValue.getValue(); int index = id % 2; for (String each : collection) { if (each.endsWith(index + "")) { return each; } } throw new UnsupportedOperationException(); } @Override public Collection<String> doInSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) { return null; } @Override public Collection<String> doBetweenSharding(Collection<String> collection, ShardingValue<Integer> shardingValue) { return null; }}import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;import java.util.Collection;public class TbAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> { @Override public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { int id = shardingValue.getValue(); int index = id % 2; for (String each : availableTargetNames) { if (each.endsWith(index + "")) { return each; } } throw new UnsupportedOperationException(); } @Override public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { return null; } @Override public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { return null; }}
修改了这个数据源后，直接运行上面的测试代码，可以看到分库分表生效了。id=1的记录插入了rbb_1这个数据源，user_1这张表；id=2的记录插入了rbb_0这个数据源，user_0这张表。
至此就完成了spring与mybatis以及sharding-jdbc分库分表的集成，最后给出项目使用的pom配置：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>net.aty.mybatis</groupId> <artifactId>mybatis-demo</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>mybatis-demo</name> <url>http://maven.apache.org</url> <properties> <spring.version>4.3.3.RELEASE</spring.version> </properties> <dependencies> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.21</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.26</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.26</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.0</version> </dependency> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>1.3.3</version> </dependency> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-config-spring</artifactId> <version>1.3.3</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>2.5.1</version> <configuration> <encoding>UTF-8</encoding> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build></project> 1 0
- spring+mybatis+druid数据源+sharding-jdbc分库分表
- spring+mybatis+sharding-jdbc
- 学习sharding-jdbc 之spring+mybatis+sharding-jdbc整合
- Spring-mvc整合mybatis-oracle11g-druid数据源
- Spring-mvc整合mybatis-oracle11g-druid数据源
- spring+mybatis使用druid配置数据源
- Spring boot+MyBatis+Sharding jdbc配置
- sharding-jdbc集成spring+mybatis分表分库
- Druid简介（Spring Boot + Mybatis + Druid数据源【自己定制】）
- Druid简介（Spring Boot + Mybatis + Druid数据源【官方start】）
- sharding-jdbc整合mybatis
- 学习sharding-jdbc（二）之spring+mybatis+sharding-jdbc整合
- spring+mybatis dubbo 整合 sharding-jdbc分库分表存日志
- spring 配置druid数据源
- Spring Boot : 集成mybatis、pagehelper插件、开启事务、druid数据源配置、mybatis-generator(十一)
- spring boot 配置druid数据源
- spring-boot 集成Druid数据源
- Spring Boot 配置Druid数据源
- 图的深度遍历
- easyui textbox 绑定oninput事件
- 模拟SPI实现和调试流程
- 音视频处理-JM源码分析
- Egit笔记（一）
- spring+mybatis+druid数据源+sharding-jdbc分库分表
- ShaderForge实例01——圆
- POJ 2442Sequence
- Fragment中newInstance()方法的必要性
- c语言的左右法则
- 2016. 11.22 输入一个整数，用字符串输出
- 数据结构实验：连通分量个数
- 与我联系
- 文本输入的常用特殊符号快捷键
