spring+mybatis+druid数据源+sharding-jdbc分库分表
首先我们看下如何让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;
编写实体类UserEntity.java
-
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 +
-
'}';
-
}
-
}
编写mapper类UserMapper.java
-
package net.aty.spring.mapper;
-
-
-
import net.aty.spring.entity.UserEntity;
-
-
public interface UserMapper {
-
-
int insertOne(UserEntity user);
-
-
UserEntity selectByPk(int id);
-
}
编写mapper映射文件:UserMapper.xml
-
<?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>
编写mybatis配置文件:mybatis.xml
-
<?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>
配置druid数据源,以及整合mybatis的spring-mybatis.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"
-
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;
-
}
-
}
接下来我们只需要修改上面的spring-mybatis.xml,就可以使用分库分表了,不需要改动已有的代码。可以看到只需要修改数据源,就可以完成sharding-jdbc与mybatis、spring的集成,非常方便。
修改了这个数据源后,直接运行上面的测试代码,可以看到分库分表生效了。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>