多数据源的问题在实际项目中还是经常遇到的,例如读写分离的数据库、马甲类型服务器等。不多说上干货,特别说明下本人用的开发工具是idea。
创建Spring Boot项目
菜单 file–》new –》project–》spring initializer 填写自己的group、artifact之后 勾选web开启web属性 数据库选着mysql、mybatis
项目初始化完成,pom中加载分页组件、数据源后如下:
<?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.rongdong</groupId>
<artifactId>waistcoat</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>waistcoat</name>
<description></description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<mybatis.pagehelper.version>4.1.6</mybatis.pagehelper.version>
</properties>
<dependencies>
<!--起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!--内置tomact 可有可无-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<!--<scope>provided</scope>-->
</dependency>
<!--测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--数据库-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.11</version>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.0</version>
</dependency>
<!--json 工具-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-joda</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-parameter-names</artifactId>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>${mybatis.pagehelper.version}</version>
</dependency>
<!--基础工具-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!-- mybatis generator 自动生成代码插件 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
</plugins>
</build>
</project>
generator是自动生成mybatis 映射文件的,还不知道的可以百度下
在resouces下新建generator 文件夹 并新建generator.xml文件
内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
<classPathEntry location="G:\generator\mysql-connector-java-5.1.34.jar" />
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="true"/>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接URL,用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8"
userId="root"
password="root">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 生成模型的包名和位置-->
<javaModelGenerator targetPackage="com.rongdong.model.secondDataSource" targetProject="src">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成映射文件的包名和位置-->
<sqlMapGenerator targetPackage="main.resources.mapping.secondDataSource" targetProject="src">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- 生成DAO的包名和位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.rongdong.dao.secondDataSource" targetProject="src">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
<table tableName="user_info" domainObjectName="SecondUserInfo" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false" />
</context>
</generatorConfiguration>
习惯yml格式配置文件看着清晰点,将 application改为yml 的 多数据源配置如下:
server:
port: 8089
spring:
datasource:
primary:
jdbc-url: jdbc:mysql://127.0.0.1:3306/rongdong_waistcoat_db
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
second:
jdbc-url: jdbc:mysql://127.0.0.1:3306/rongdong_jtx_db
username: root
password:root
#mybatis:
# mapper-locations: classpath:mapping/*.xml #指明mapper的xml文件存放位置
# type-aliases-package: com.rongdong.model #指明和数据库映射的实体的所在包
#pagehelper 分页插件
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
然后就是配置问题,我的因为我的数据库中表名相同,但是部分字段不一样 因此我在dao层、model、mapping这块给细分成了 多个部分
然后新建DataSourceConfig 数据源配置类
/**
* 数据源配置类
*
* @author hsh
* @create 2018-03-21 19:06
**/
@Configuration
public class DataSourceConfig {
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix="spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondDataSource")
@ConfigurationProperties(prefix = "spring.datasource.second") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}
配置具体数据源
数据源A
/**
* Mybatis SqlSessionFactory多数据源配置
*
* @author hsh
* @create 2018-03-21 19:18
**/
@Configuration
@MapperScan(basePackages = {"com.rongdong.dao.priDataSource"}, sqlSessionFactoryRef = "sqlSessionFactory1")
public class MybatisDbAConfig {
public static final String MAPPER_LOCATION = "classpath:mapping/priDataSource/*.xml";
@Autowired
@Qualifier("primaryDataSource")
private DataSource ds1;
@Bean
public SqlSessionFactory sqlSessionFactory1() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds1); // 使用主数据源, 连接主库
Resource[] resource = new PathMatchingResourcePatternResolver().getResources(MybatisDbAConfig.MAPPER_LOCATION);
factoryBean.setMapperLocations(resource);
return factoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate1() throws Exception {
return new SqlSessionTemplate(sqlSessionFactory1());
}
}
数据源B
/**
* Mybatis SqlSessionFactory多数据源配置
*
* @author hsh
* @create 2018-03-21 19:48
**/
@Configuration
@MapperScan(basePackages = {"com.rongdong.dao.secondDataSource"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class MybatisDbBConfig {
public static final String MAPPER_LOCATION = "classpath:mapping/secondDataSource/*.xml";
@Autowired
@Qualifier("secondDataSource")
private DataSource ds2;
@Bean
public SqlSessionFactory sqlSessionFactory2() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds2);
Resource[] resource = new PathMatchingResourcePatternResolver().getResources(MybatisDbBConfig.MAPPER_LOCATION);
factoryBean.setMapperLocations(resource);
return factoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate2() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory2());
return template;
}
}
service 层具体调用了
/**
* 用户service实现
*
* @author hsh
* @create 2018-03-21 17:44
**/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserInfoMapper userInfoMapper;
@Autowired
private SecondUserInfoMapper secondUserInfoMapper;
@Override
public UserInfo getUserById(String userId, Integer dataSourceType) throws Exception {
if (dataSourceType == DataSourceTypeEnum.one.getIndex()) {
return userInfoMapper.selectByPrimaryKey(userId);
} else if (dataSourceType == DataSourceTypeEnum.two.getIndex()) {
UserInfo userInfoTar = new UserInfo();
SecondUserInfo secondUserInfo = secondUserInfoMapper.selectByPrimaryKey(userId);
BeanUtils.copyProperties(secondUserInfo, userInfoTar);
return userInfoTar;
}
return null;
}
}
controller代码
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping(value = "{id}/{datatype}", method = RequestMethod.GET)
public UserInfo getUserById(@PathVariable("id") String userId,@PathVariable("datatype") Integer dataType) throws Exception {
return userService.getUserById(userId, dataType);
}
}
调试
搞定!
1。提醒下spring boot 2.0之后多数据的配置url时候要用 jdbc-url替代url不然报错
jdbcUrl is required with driverClassName.
2。表名相同的情况下 dao层是不能名字一样的,因为默认是name实例化这个bean 会报错 因此实例化
以上就是最简单版本的Spring boot +Mybatis 实现多数据源,当然还有动态绑定数据源的,后期有时间在继续更新上来,欢迎大家指正!有问题的可以留言大家互相交流下!