一、在开发过程中可能我们会用到 多中数据源 有可能是mysql ,也有可能是sqlserver 这样的混合使用,还有就是主从模式或者需要连接不同的分库来支持业务。所以这些写一个简单支持处理多数据源的方式。
首先看项目结构
1、我们来看配置文件 application.yml
server:
tomcat:
uri-encoding: UTF-8
port: 8084
spring:
banner:
charset: utf-8
http:
encoding:
charset: UTF-8
enabled: true
force: true
messages:
encoding: UTF-8
datasource:
test1:
jdbcUrl: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true
username: root
password: 123
driver-class-name: com.mysql.cj.jdbc.Driver
test2:
jdbcUrl: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true
username: root
password: 123
driver-class-name: com.mysql.cj.jdbc.Driver
logging:
level:
com.base.mapper: debug
mybatis:
config-location: classpath:mybatis_config.xml
mapper-locations: classpath:mybatis/*.xml
type-aliases-package: org.learn.boot.datasource.entity
在配置文件我们需要配置两个数据库,其中 test1 位主库,在使用的过程中必须指定主库,不然会报错。
2、数据源配置
package org.learn.boot.datasource.datasource;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
/**
* ClassName: DataSource1Config
* Description: 数据源配置1 , 这个配置就是和spring 里面 的xml配置一样,只不过是通过java类 通过
* Date: 2019/3/24 16:55
* History:
* <version> 1.0
* @author lin
*/
@Configuration
@MapperScan(basePackages = "org.learn.boot.datasource.mapper.test1",sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
@Autowired
private DatasourceTest1 datasourceTest1;
/**
* @MapperScan 该注解指名扫描dao层,并且将dao层注入到指定的sqlSessionTemplate, 所以的@Bean都需要按照命令指定
* prefix(前缀) = "spring.datasource.test1" 表示配置文件中那个下面的所有属性
**/
/**
* @Description 1、创建dataSource,2、创建SqlSessionFactory ,3、再创建事务 ,4、最后包装到SqlSessionTemplate
* 这里注意下 如果使用 yml 文件,那么就不能通过 这种方式来 @ConfigurationProperties(prefix = "spring.datasource.test1") 获取数据源。
* 因为在这种方式下 会报错 ,找不到相应的配置。 如果是使用的properties 配置文件那么 就可以使用这种方式。
* 如果要使用 yml文件配置 方式,那么需要
* @param
* @return javax.sql.DataSource
* @exception
* @author lin
* @Date 17:28 2019/3/24
**/
@Bean(name = "test1DataSource")
// @ConfigurationProperties(prefix = "spring.datasource.test1")
@Primary
public DataSource testDateSource(){
return DataSourceBuilder.create().url(datasourceTest1.getJdbcUrl()).
username(datasourceTest1.getUsername()).
password(datasourceTest1.getPassword()).
driverClassName(datasourceTest1.getDriverClassName()).
build();
// return DataSourceBuilder.create().build();
}
@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/Bank1Mapper.xml"));
return bean.getObject();
}
@Bean("test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean("test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
这里注意注入是一层一层的注入
a、先注入dataSource,b、注入创建SqlSessionFactory , c、再创建事务test1TransactionManager ,d、最后包装到SqlSessionTemplate。并且需要知道分库的mapper文件地址。以及分库dao层代码。
@MapperScan(basePackages = "org.learn.boot.datasource.mapper.test1",sqlSessionTemplateRef = "test1SqlSessionTemplate")
这块的注解就是指明了扫描 dao 层,并且给 dao 层注入指定的 SqlSessionTemplate。所有@Bean都需要按照命名指定正确。
package org.learn.boot.datasource.datasource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
/**
* 读取 yml文件配置
* @ClassName: DatasourceConfig
* @Description:
* @Author: lin
* @Date: 2019/3/24 18:11
* History:
* @<version> 1.0
*/
@Component
@ConfigurationProperties(prefix = "spring.datasource.test1")
public class DatasourceTest1 {
/** 数据库连接url */
private String jdbcUrl;
/** 用户名 */
private String username;
/** 用户密码 */
private String password;
/** 数据库驱动 */
private String driverClassName;
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
}
3、第二个数据源
package org.learn.boot.datasource.datasource;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
/**
* ClassName: DataSource2Config
* Description: 数据源配置2
* Date: 2019/3/24 16:55
* History:
* <version> 1.0
* @author lin
*/
@Configuration
@MapperScan(basePackages = "com.base.mapper.test2",sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
@Autowired
private DatasourceTest2 datasourceTest2;
/**
* @Description 创建DataSource
* @param
* @return javax.sql.DataSource
* @exception
* @author lin
* @Date 18:12 2019/3/24
**/
@Bean(name = "test2DataSource")
// @Value("spring.datasource.test2")
public DataSource test2DataSource(){
return DataSourceBuilder.create().url(datasourceTest2.getJdbcUrl()).
username(datasourceTest2.getUsername()).
password(datasourceTest2.getPassword()).
driverClassName(datasourceTest2.getDriverClassName()).build();
// return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/Bank2Mapper.xml"));
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager test2TransactionManager(@Qualifier("test2DataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate test2SqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
package org.learn.boot.datasource.datasource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
/**
* @ClassName: DatasourceTest2
* @Description:
* @Author: lin
* @Date: 2019/3/24 18:15
* History:
* @<version> 1.0
*/
@Component
@ConfigurationProperties(prefix = "spring.datasource.test2")
public class DatasourceTest2 {
/** 数据库连接url */
private String jdbcUrl;
/** 用户名 */
private String username;
/** 用户密码 */
private String password;
/** 数据库驱动 */
private String driverClassName;
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
}
这里注意 获取yml的配置有三种 :a:使用 @Value注解,b.使用 @ConfigurationProperties注解来指定 配置文件中前缀。c.使用@Environment 注解来读取,这种方式使用的比较少。
4、上面的数据源编写好了之后 是dao层和 xml层。这两个分别编写在不同的包下面
Bank1Mapper 类
/**
* ClassName: Bank1Mapper
* Description: 测试多数据源配置
* Date: 2019/3/24 17:02
* History:
* <version> 1.0
* @author lin
*/
@Mapper
public interface Bank1Mapper {
/**
* 查询全部
* @Description 查询全部
* @param
* @return java.util.List<com.base.entity.Bank>
* @exception
* @author lin
* @Date 16:09 2019/3/21
**/
List<Bank> getAll();
/**
* 根据id查询
* @Description 根据id查询
* @param id
* @return com.base.entity.Bank
* @exception
* @author lin
* @Date 16:10 2019/3/21
**/
Bank getOne(Integer id);
/**
* 添加
* @Description
* @param bank
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void insert(Bank bank);
/**
* 更新
* @Description
* @param bank
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void update(Bank bank);
/**
* 删除
* @Description
* @param id
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void delete(Integer id);
}
Bank2Mapper 类
package org.learn.boot.datasource.mapper.test2;
import org.apache.ibatis.annotations.Mapper;
import org.learn.boot.datasource.entity.Bank;
import java.util.List;
/**
* ClassName: Bank2Mapper
* Description: 测试多数据源配置
* Date: 2019/3/24 17:03
* History:
* <version> 1.0
* @author lin
*/
@Mapper
public interface Bank2Mapper {
/**
* 查询全部
* @Description 查询全部
* @param
* @return java.util.List<com.base.entity.Bank>
* @exception
* @author lin
* @Date 16:09 2019/3/21
**/
List<Bank> getAll();
/**
* 根据id查询
* @Description 根据id查询
* @param id
* @return com.base.entity.Bank
* @exception
* @author lin
* @Date 16:10 2019/3/21
**/
Bank getOne(Integer id);
/**
* 添加
* @Description
* @param bank
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void insert(Bank bank);
/**
* 更新
* @Description
* @param bank
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void update(Bank bank);
/**
* 删除
* @Description
* @param id
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
void delete(Integer id);
}
实体类
/**
* ClassName: Bank
* Description: 实体类
* Date: 2019/3/21 15:50
* History:
* <version> 1.0
* @author lin
*/
@Data
public class Bank {
private Integer id;
private String code;
private String name;
private Date createTime;
}
5、xml的编写
Bank1Mapper.xml 配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.learn.boot.datasource.mapper.test1.Bank1Mapper" >
<resultMap id="BaseResultMap" type="org.learn.boot.datasource.entity.Bank">
<id column="id" property="id" javaType="INT"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="code" property="code" jdbcType="VARCHAR"/>
<result column="create_time" property="createTime" jdbcType="DATE"/>
</resultMap>
<sql id="Base_Column_List">
id, code, name, create_time
</sql>
<select id="getAll" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM c_bank
</select>
<select id="getOne" parameterType="java.lang.Integer" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM c_bank
WHERE id=#{id}
</select>
<insert id="insert" parameterType="org.learn.boot.datasource.entity.Bank">
INSERT INTO
c_bank
(name,code,create_time)
VALUES
(#{name},#{code},#{createTime})
</insert>
<update id="update" parameterType="org.learn.boot.datasource.entity.Bank">
UPDATE
c_bank
SET
<if test="name != null">name=#{name},</if>
<if test="code != null">code=#{code},</if>
WHERE
id=#{id}
</update>
<delete id="delete" parameterType="java.lang.Integer">
DELETE FROM
c_bank
WHERE
id=#{id}
</delete>
</mapper>
Bank2Mapper.xml 配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.learn.boot.datasource.mapper.test2.Bank2Mapper" >
<resultMap id="BaseResultMap" type="org.learn.boot.datasource.entity.Bank">
<id column="id" property="id" javaType="INT"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="code" property="code" jdbcType="VARCHAR"/>
<result column="create_time" property="createTime" jdbcType="DATE"/>
</resultMap>
<sql id="Base_Column_List">
id, code, name, create_time
</sql>
<select id="getAll" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM c_bank
</select>
<select id="getOne" parameterType="java.lang.Integer" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM c_bank
WHERE id=#{id}
</select>
<insert id="insert" parameterType="org.learn.boot.datasource.entity.Bank">
INSERT INTO
c_bank
(name,code,create_time)
VALUES
(#{name},#{code},#{createTime})
</insert>
<update id="update" parameterType="org.learn.boot.datasource.entity.Bank">
UPDATE
c_bank
SET
<if test="name != null">name=#{name},</if>
<if test="code != null">code=#{code},</if>
WHERE
id=#{id}
</update>
<delete id="delete" parameterType="java.lang.Integer">
DELETE FROM
c_bank
WHERE
id=#{id}
</delete>
</mapper>
6、编写controller来测试
package org.learn.boot.datasource.controller;
import org.learn.boot.datasource.entity.Bank;
import org.learn.boot.datasource.mapper.test1.Bank1Mapper;
import org.learn.boot.datasource.mapper.test2.Bank2Mapper;
import org.learn.boot.datasource.vo.BankVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* ClassName: BankController
* Description: mybatis xml 测试
* Date: 2019/3/21 16:52
* History:
* <version> 1.0
* @author lin
*/
@RestController
@RequestMapping(value = "bank")
public class BankController {
@Autowired
private Bank1Mapper bank1Mapper;
@Autowired
private Bank2Mapper bank2Mapper;
/**
* 查询全部
* @Description 查询全部
* @param
* @return java.util.List<com.base.entity.Bank>
* @exception
* @author lin
* @Date 16:09 2019/3/21
**/
@RequestMapping(value = "getBanks")
public List<Bank> getBanks(){
List<Bank> banks = bank1Mapper.getAll();
return banks;
}
@RequestMapping(value ="getById")
public Bank getById(Integer id){
Bank bank = bank2Mapper.getOne(id);
return bank;
}
/**
* 添加
* @Description
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
@RequestMapping(value = "add")
public void save(@RequestBody BankVo bankVo){
try {
Bank bank2 = new Bank();
bank2.setName(bankVo.getName());
bank2.setCode(bankVo.getCode());
Date time1;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
time1 = simpleDateFormat.parse(bankVo.getCreateTime());
bank2.setCreateTime(time1);
bank2Mapper.insert(bank2);
} catch (ParseException e) {
e.printStackTrace();
}
}
/**
* 更新
* @Description
* @param bank
* @return void
* @exception
* @author lin
* @Date 16:11 2019/3/21
**/
@RequestMapping(value = "update")
public void update(Bank bank){
bank2Mapper.update(bank);
}
@RequestMapping(value = "delete")
public void delete(@PathVariable("id") Integer id){
bank1Mapper.delete(id);
}
}
测试添加
查看数据库,可以看到数据库数据已经添加进去了。
参考:http://www.ityouknow.com/springboot/2016/11/25/spring-boot-multi-mybatis.html