springboot+mybatis+idea+gradle配置多数据源(MySQL+ORACLE)
1.应用场景
什么时候才用的到配置多数据源??当我们用不同数据库存放不同数据的时候。这个时候我们整个工程并不是只用一个数据库,所以,要配置多个,在具体的场景应用具体的数据源。
2.文件结构
3.主要思路
假设现在有两类人,需要登录,在不同的登录页面下,检测不同的数据库。一种人的信息存放在mysql,另一种存放在oracle,我们现在设置两个映射文件,分别存放不同的SQL语句,这很好理解吧,然后在application.properties里存放两个数据库的配置信息,准备实体类dto,准备一个controller,准备两个mapper接口,以及他们的service实现文件。现在重点来了,我们需要什么把他们串起来。你现在在login页面输入了信息,经过ajax传送到后台,后台调用service里的函数,这些函数,分别调用接口的函数来实现登录校验,将校验结果返回页面ajax,根据结果,跳转或者怎样。这就是我们的主要思路。但是我们想一下,谁来决定你在不同的login页面输入不同类人的信息所调用的不同数据库分别是哪个呢??我们将在DataSourceConfig里配置他。
4.build.gradle依赖
dependencies {
compile "org.springframework.boot:spring-boot-starter-web",
"org.springframework.boot:spring-boot-starter-tomcat",
"org.springframework.boot:spring-boot-starter-aop",
"org.springframework.boot:spring-boot-starter-thymeleaf",
"org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.1",
"org.apache.tomcat.embed:tomcat-embed-jasper",
"com.alibaba:druid-spring-boot-starter:1.1.2",
"org.springframework.boot:spring-boot-devtools",
"org.springframework.data:spring-data-redis:1.7.2.RELEASE",
"mysql:mysql-connector-java:8.0.16",
"log4j:log4j:1.2.17",
'com.google.guava:guava:18.0',
'com.google.code.gson:gson:2.4',
'org.apache.httpcomponents:httpclient:4.5.3',
'org.apache.httpcomponents:httpmime:4.5.3'
compile fileTree(dir: 'libs', include: '**/*.jar')
runtime('org.springframework.boot:spring-boot-devtools')
runtime files('libs / com.oracle:ojdbc6')
runtime('mysql:mysql-connector-java')
testCompile("org.springframework.boot:spring-boot-starter-test")
}
这里注意,由于个人的mysql版本问题,驱动版本的选择要十分谨慎,不然会出现很多错误,经过尝试,5.1.22的驱动可以较好的兼容。但由于我本地试8.0,所以下面就以用它写博客:
5.application.properties
#mysql
spring.datasource.test1.name = mysql
spring.datasource.test1.url = jdbc:mysql://127.0.0.1:3306/purchase?useUnicode=true&characterEncodingutf-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.test1.username = root
spring.datasource.test1.password = root
spring.datasource.test1.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.test1.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.test1.filters = stat,wall
spring.datasource.test1.max-active = 20
spring.datasource.test1.initial-size = 10
spring.datasource.test1.max-idle = 60000
spring.datasource.test1.min-idle = 1
#oracle
spring.datasource.test2.name=oracle
spring.datasource.test2.url=jdbc:oracle:thin:@47.96.141.116:1521:bank
spring.datasource.test2.username=bank
spring.datasource.test2.password= bank12
spring.datasource.test2.type = com.alibaba.druid.pool.DruidDataSource
spring.datasource.test2.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.test2.filters = stat,wall
spring.datasource.test2.max-active = 20
spring.datasource.test2.initial-size = 10
spring.datasource.test2.max-idle = 60000
spring.datasource.test2.min-idle = 1
由于两个数据源很多地方写法都是一样的,比如mapper,service等,所以我这里只写一个数据源的
6.mapper
package com.fansha.demo.domain.mapper1;
/**
* @Author: FanSha
* @Date: 2020/7/28 9:29
* @Version 1.0
* @Description:
* @Copyright: Copyright (c) 2019
* @Company: www.123start.cn
*/
public interface MerInfoMapper1 {
String selectMerNo();
}
7.Service
package com.fansha.demo.sevice.impl;
import com.fansha.demo.domain.mapper1.MerInfoMapper1;
import com.fansha.demo.domain.mapper2.MerInfoMapper2;
import com.fansha.demo.sevice.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* @Author: FanSha
* @Date: 2020/7/27 17:55
* @Version 1.0
* @Description:
* @Copyright: Copyright (c) 2019
* @Company: www.123start.cn
*/
@Service
public class TestServiceImpl implements TestService {
@Autowired(required = false)
private MerInfoMapper1 merInfoMapper1;
@Autowired(required = false)
private MerInfoMapper2 merInfoMapper2;
@Override
public String isTrue(){
String merNo1 = merInfoMapper1.selectMerNo();
String merNo2 = merInfoMapper2.selectMerNo();
if(merNo1.equals(merNo2)){
return "true";
}
return "false";
}
}
8.Controller
package com.fansha.demo.web.controller;
import com.fansha.demo.sevice.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
/**
* @Author: FanSha
* @Date: 2020/7/27 17:53
* @Version 1.0
* @Description:
* @Copyright: Copyright (c) 2019
* @Company: www.123start.cn
*/
@RestController
@RequestMapping("/test")
public class TestController {
@Resource
private TestService testService;
@RequestMapping(value = "/dbTwo",method = RequestMethod.GET,produces = "application/json")
public String test(){
String result = testService.isTrue();
return result;
}
}
9.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="com.fansha.demo.domain.mapper1.MerInfoMapper1">
<select id="selectMerNo" resultType="java.lang.String">
SELECT Mer_no FROM mer_info WHERE mer_name = '测试操作员跳槽'
</select>
</mapper>
10.重点!!DataSourceConfig
DataSource1Config
package com.fansha.demo.web.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @Author: FanSha
* @Date: 2020/7/27 17:44
* @Version 1.0
* @Description:
* @Copyright: Copyright (c) 2019
* @Company: www.123start.cn
*/
@Configuration
@MapperScan(basePackages = "com.fansha.demo.domain.mapper1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
//这里一定要注意,这个basePackages是你的mapper接口及service所在的包名,而下面的红线所标注的classpath是mapper.xml所在的位置,这个xml是配置文件,处在resources里,他的路径也要格外区分开。
public class DataSource1Config {
/**
* 生成数据源. @Primary 注解声明为默认数据源
*/
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test1")
//下面的注解作用就是从application.properties中读取以这个字符串开头的那些配置,设置为数据源的配置
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 创建 SqlSessionFactory
*/
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db1/*.xml"));
return bean.getObject();
}
/**
* 配置事务管理
*/
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
DataSource2Config
package com.fansha.demo.web.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* @Author: FanSha
* @Date: 2020/7/27 17:45
* @Version 1.0
* @Description:
* @Copyright: Copyright (c) 2019
* @Company: www.123start.cn
*/
@Configuration
@MapperScan(basePackages = "com.fansha.demo.domain.mapper2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
//这里一定要注意,这个basePackages是你的mapper接口及service所在的包名,而下面的红线所标注的classpath是mapper.xml所在的位置,这个xml是配置文件,处在resources里,他的路径也要格外区分开。
public class DataSource2Config {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
//下面的注解作用就是从application.properties中读取以这个字符串开头的那些配置,设置为数据源的配置
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/db2/*.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
11.总结
到这里就完成了,配置这个东西其实网上有很多但是,有很多细节都一笔带过,对于初学者来说可能有些难度,在这里我总结一下,配置这个比较坑的地方可能就是mysql驱动的版本的选择以及mapper文件和xml文件存放的位置上,祝大家好运吧。
参考:http://bbs.itheima.com/thread-452900-1-1.html