将数据源划分为master和cluster,框架为springboot
1、maven引入对应的依赖。
<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.aspirecn.comparison</groupId>
<artifactId>oracle-mysql-comparison</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
<!-- Junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>12.1.0.1-atlassian-hosted</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>*.*</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</plugin>
</plugins>
</build>
</project>
以上重点关注druid和mysql、oracle驱动
2、为2个数据库分别写对应的配置类。**注意两个数据库对应的mapper类放的包一定要区分开。**下面的配置类中包含了分页的配置。
这是oracle的:
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {
// 精确到 cluster 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.comparison.oracle";
// static final String MAPPER_LOCATION = "classpath:mapper/cluster/*.xml";
static final String MAPPER_LOCATION = "classpath:com/comparison/oracle/*.xml";
@Value("${cluster.datasource.url}")
private String url;
@Value("${cluster.datasource.username}")
private String user;
@Value("${cluster.datasource.password}")
private String password;
@Value("${cluster.datasource.driverClassName}")
private String driverClass;
@Bean(name = "clusterDataSource")
public DataSource clusterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "clusterTransactionManager")
public DataSourceTransactionManager clusterTransactionManager() {
return new DataSourceTransactionManager(clusterDataSource());
}
@Bean(name = "clusterSqlSessionFactory")
public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(clusterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(ClusterDataSourceConfig.MAPPER_LOCATION));
PageInterceptor pageInterceptor = new PageInterceptor();
pageInterceptor.setDatabaseType("oracle");
sessionFactory.setPlugins(new Interceptor[]{pageInterceptor});
return sessionFactory.getObject();
}
}
这是mysql的
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
// 精确到 master 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.comparison.mysql";
static final String MAPPER_LOCATION = "classpath:com/comparison/mysql/*.xml";
@Value("${master.datasource.url}")
private String url;
@Value("${master.datasource.username}")
private String user;
@Value("${master.datasource.password}")
private String password;
@Value("${master.datasource.driverClassName}")
private String driverClass;
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(masterDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MasterDataSourceConfig.MAPPER_LOCATION));
PageInterceptor pageInterceptor = new PageInterceptor();
pageInterceptor.setDatabaseType("mysql");
sessionFactory.setPlugins(new Interceptor[]{pageInterceptor});
return sessionFactory.getObject();
}
}
分页配置:
import java.util.List;
/**
* 分页基本实现
* @param <T>
*/
public class Page<T> implements Pagination {
/** 页码 */
protected int pageNo;
/** 每页记录条数 */
protected int pageCount;
/** 总页数 */
protected int totalPage;
/** 总记录条数 */
protected int totalCount = -1;
/** 用于存放查询结果 */
protected List<T> resultList;
public Page() {}
public Page(Integer pageNo, int pageCount) {
if(pageNo==null){
pageNo=1;
}
if (pageNo <= 0) {
pageNo=1;
//throw new IllegalArgumentException("pageNo must be greater than 0.");
}
if (pageCount<=0 && pageCount>50) {
pageCount=50;
//throw new IllegalArgumentException("pageCount must be greater than 0.");
}
this.pageNo = pageNo;
this.pageCount = pageCount;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageNo() {
return pageNo;
}
public int getPageCount() {
return pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
if (totalCount < 0) { // 如果总数为负数, 表未设置
totalPage = 0;
} else { // 计算总页数
totalPage = (totalCount / pageCount) + (totalCount % pageCount == 0 ? 0 : 1);
}
}
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
public int getTotalPage() {
return totalPage;
}
}
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
* 分页拦截器,用于拦截需要进行分页查询的操作,然后对其进行分页处理
* 注意MyBatis 3.4.0 之后,StatementHandler的prepare方法做了修改,如下:
*
* @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class }) })
*/
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class,Integer.class }) })
//@Intercepts({@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class})})
public class PageInterceptor implements Interceptor {
private String databaseType;// 数据库类型,不同的数据库有不同的分页方法
/**
* 拦截后要执行的方法
*/
public Object intercept(Invocation invocation) throws Throwable {
RoutingStatementHandler handler = (RoutingStatementHandler) invocation
.getTarget();
StatementHandler delegate = (StatementHandler) ReflectUtil
.getFieldValue(handler, "delegate");
BoundSql boundSql = delegate.getBoundSql();
Object paramObj = boundSql.getParameterObject();
// 判断参数里是否有page对象
Pagination page = null;
if (paramObj instanceof Pagination) {
page = (Pagination) paramObj;
} else if (paramObj instanceof Map) {
for (Object arg : ((Map) paramObj).values()) {
if (arg instanceof Page<?>) {
page = (Pagination) arg;
break;
}
}
}
if (page != null) {
MappedStatement mappedStatement = (MappedStatement) ReflectUtil
.getFieldValue(delegate, "mappedStatement");
Connection connection = (Connection) invocation.getArgs()[0];
String sql = boundSql.getSql();
if (page.getTotalCount() < 0) { // 如果总数为负数表需要设置
this.setTotalRecord(paramObj, mappedStatement, connection, page);
}
// 获取分页Sql语句
String pageSql = this.getPageSql(page, sql);
// 利用反射设置当前BoundSql对应的sql属性为我们建立好的分页Sql语句
ReflectUtil.setFieldValue(boundSql, "sql", pageSql);
}
return invocation.proceed();
}
/**
* 拦截器对应的封装原始对象的方法
*/
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* 设置注册拦截器时设定的属性
*/
public void setProperties(Properties properties) {
this.databaseType = properties.getProperty("databaseType");
}
/**
* 根据page对象获取对应的分页查询Sql语句,这里只做了两种数据库类型,Mysql和Oracle 其它的数据库都 没有进行分页
*
* @param page 分页对象
* @param sql 原sql语句
* @return
*/
private String getPageSql(Pagination page, String sql) {
StringBuffer sqlBuffer = new StringBuffer(sql);
if ("mysql".equalsIgnoreCase(databaseType)) {
return getMysqlPageSql(page, sqlBuffer);
} else if ("oracle".equalsIgnoreCase(databaseType)) {
return getOraclePageSql(page, sqlBuffer);
}
return sqlBuffer.toString();
}
/**
* 获取Mysql数据库的分页查询语句
*
* @param page 分页对象
* @param sqlBuffer 包含原sql语句的StringBuffer对象
* @return Mysql数据库分页语句
*/
private String getMysqlPageSql(Pagination page, StringBuffer sqlBuffer) {
// 计算第一条记录的位置,Mysql中记录的位置是从0开始的。
int offset = (page.getPageNo() - 1) * page.getPageCount();
sqlBuffer.append(" limit ").append(offset).append(",")
.append(page.getPageCount());
return sqlBuffer.toString();
}
/**
* 获取Oracle数据库的分页查询语句
*
* @param page 分页对象
* @param sqlBuffer 包含原sql语句的StringBuffer对象
* @return Oracle数据库的分页查询语句
*/
private String getOraclePageSql(Pagination page, StringBuffer sqlBuffer) {
// 计算第一条记录的位置,Oracle分页是通过rownum进行的,而rownum是从1开始的
int offset = (page.getPageNo() - 1) * page.getPageCount() + 1;
sqlBuffer.insert(0, "select u.*, rownum row_id from (")
.append(") u where rownum < ")
.append(offset + page.getPageCount());
sqlBuffer.insert(0, "select * from (").append(") where row_id >= ")
.append(offset);
// 上面的Sql语句拼接之后大概是这个样子:
// select * from (select u.*, rownum r from (select * from t_user) u
// where rownum < 31) where r >= 16
return sqlBuffer.toString();
}
/**
* 给当前的参数对象page设置总记录数
*
* @param obj Mapper映射语句对应的参数对象
* @param mappedStatement Mapper映射语句
* @param connection 当前的数据库连接
*/
private void setTotalRecord(Object obj, MappedStatement mappedStatement,
Connection connection, Pagination page) {
BoundSql boundSql = mappedStatement.getBoundSql(obj);
String sql = boundSql.getSql();
String countSql = this.getCountSql(sql);
List<ParameterMapping> parameterMappings = boundSql
.getParameterMappings();
BoundSql countBoundSql = new BoundSql(
mappedStatement.getConfiguration(), countSql,
parameterMappings, obj);
ReflectUtil.setFieldValue(countBoundSql, "additionalParameters",
ReflectUtil.getFieldValue(boundSql, "additionalParameters"));
ReflectUtil.setFieldValue(countBoundSql, "metaParameters",
ReflectUtil.getFieldValue(boundSql, "metaParameters"));
ParameterHandler parameterHandler = new DefaultParameterHandler(
mappedStatement, obj, countBoundSql);
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = connection.prepareStatement(countSql);
parameterHandler.setParameters(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
int totalRecord = rs.getInt(1);
// 给当前的参数page对象设置总记录数
page.setTotalCount(totalRecord);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 根据原Sql语句获取对应的查询总记录数的Sql语句
*
* @param sql
* @return
*/
private String getCountSql(String sql) {
return "select count(1) from (" + sql + ") tmp_";
}
/**
* 利用反射进行操作的一个工具类
*/
private static class ReflectUtil {
/**
* 利用反射获取指定对象的指定属性
*
* @param obj 目标对象
* @param fieldName 目标属性
* @return 目标属性的值
*/
public static Object getFieldValue(Object obj, String fieldName) {
Object result = null;
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
field.setAccessible(true);
try {
result = field.get(obj);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return result;
}
/**
* 利用反射获取指定对象里面的指定属性
*
* @param obj 目标对象
* @param fieldName 目标属性
* @return 目标字段
*/
private static Field getField(Object obj, String fieldName) {
Field field = null;
for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz
.getSuperclass()) {
try {
field = clazz.getDeclaredField(fieldName);
break;
} catch (NoSuchFieldException e) {
// 这里不用做处理,子类没有该字段可能对应的父类有,都没有就返回null。
}
}
return field;
}
/**
* 利用反射设置指定对象的指定属性为指定的值
*
* @param obj 目标对象
* @param fieldName 目标属性
* @param fieldValue 目标值
*/
public static void setFieldValue(Object obj, String fieldName,
String fieldValue) {
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
try {
field.setAccessible(true);
field.set(obj, fieldValue);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 利用反射设置指定对象的指定属性为指定的值
*
* @param obj 目标对象
* @param fieldName 目标属性
* @param fieldValue 目标值
*/
public static void setFieldValue(Object obj, String fieldName,
Object fieldValue) {
Field field = ReflectUtil.getField(obj, fieldName);
if (field != null) {
try {
field.setAccessible(true);
field.set(obj, fieldValue);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
public String getDatabaseType() {
return databaseType;
}
public void setDatabaseType(String databaseType) {
this.databaseType = databaseType;
}
}
/**
* 分页,在分页挡截中使用
*
*/
public interface Pagination {
/**
* @return 当前页码
*/
int getPageNo();
/**
* @return 每页记录数
*/
int getPageCount();
/**
* @return 总记录数: 负数表尚未设置, 挡截器会使用count语句统计总数; 0或正整数表总数已设置, 挡截器将不会统计总数.
*/
int getTotalCount();
/**
* @param totalCount 设置记录总数
*/
void setTotalCount(int totalCount);
}
3、在springboot的配置文件中配置数据库连接地址等参数
master 数据源配置
master.datasource.url=jdbc:mysql://ip:3307/scapp?useUnicode=true&characterEncoding=utf8
master.datasource.username=xxx
master.datasource.password=xxx
master.datasource.driverClassName=com.mysql.cj.jdbc.Driver
cluster 数据源配置
cluster.datasource.url=jdbc:oracle:thin:@ip:1521:ora11g
cluster.datasource.username=xxx
cluster.datasource.password=xxx
cluster.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
4、完成以上步骤就可以在项目中使用双数据源了,使用上跟单数据源一样
@Autowired
private LxcUserMapper lxcUserMapper;