我的个人网站:
http://riun.xyz
当项目中要用到两个不同的数据库时,就需要配置两个数据源分别连接这两个库。有的同学可能会问,将所有的表都放在一个库下面不就行了,为什么要用两个库呢?
所有表都放在一个库下当然可以,但是实际项目中,业务逻辑复杂之后,可能一些业务相关的库在同一个表中,另一块业务相关的数据就是在另一个库里,业务需求就是需要连接不同的数据库,这你怎么办嘛。
示例:
数据库信息
两个数据库:data和demo
这里用data中的user表
和demo中的user表
做例子。
在项目中,由于两个表名都叫user,为了区分,demo中的user表对应javabean为Demo.java,data中的user表对应javabean为User.java
项目信息:
目录:
依赖
pom.xml
这里用的是SpringBoot2.2.2
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>hx.insist</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>多数据源</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--Swagger依赖-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!--fastjson依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.33</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置文件
application.properties(properties和yml、yaml均可)
配置多个数据源连接,配置mapper.xml的扫描路径
注意:
Spring1.0+配置多数据源是写spring.datasource.url 和spring.datasource.driverClassName。
Spring2.0+配置多数据源是写spring.datasource.jdbc-url和spring.datasource.driver-class-name。
名称不一样,如果还像1.0那样写会报jdbcUrl is required with driverClassName.错误!
server.port=9091
spring.datasource.one.jdbc-url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8
spring.datasource.one.username=root
spring.datasource.one.password=hanxu
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.two.jdbc-url=jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8
spring.datasource.two.username=root
spring.datasource.two.password=hanxu
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver
# mapper扫描
mybatis.mapper-locations=classpath:static/mybatis/mapper/*/*.xml
# 下划线转驼峰
mybatis.configuration.map-underscore-to-camel-case=true
多数据源配置信息
数据源1配置类:
package hx.insist.demo.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.context.properties.ConfigurationProperties;
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;
/*
* @Configuration :// 注册到springBoot容器中
* @MapperScan(basePackages = "com.dengwei.springdemo.test1", sqlSessionFactoryRef = "sqlSessionFactory1")
* 当前扫描包:com.dengwei.springdemo.test1下使用此数据源;sqlSessionFactoryRef:对应下面的sql会话工厂
* @Bean(name = "DataSource1") :给当前注入的bean对象取的名字
* @ConfigurationProperties(prefix = "spring.datasource.hikari.test1") application.properteis中对应属性的前缀
* @Primary : 设置默认数据源,当多个数据源时,不加会报错
*/
@Configuration
@MapperScan(basePackages = "hx.insist.demo.mapper.meituan", sqlSessionFactoryRef = "sqlSessionFactory1")
public class DataSource1Config {
/**
*
* @methodDesc: 功能描述:(配置test1数据库)
*/
@Bean(name = "DataSource1")
@ConfigurationProperties(prefix = "spring.datasource.one")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
/**
*
* @methodDesc: 功能描述:(test1 sql会话工厂)
* @returnType:@param dataSource
* @returnType:@throws Exception SqlSessionFactory
*/
@Bean(name = "sqlSessionFactory1")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("DataSource1") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//mybatis写配置文件(sql映射)需要加下面的代码
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:static/mybatis/mapper/DS1/*.xml"));
return bean.getObject();
}
/**
*
* @methodDesc: 功能描述:(test1 事物管理)
*/
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource1") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
数据源2配置类:
package hx.insist.demo.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.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;
/*
* @Configuration :// 注册到springBoot容器中
* @MapperScan(basePackages = "com.dengwei.springdemo.test2", sqlSessionFactoryRef = "SqlSessionFactory2")
* 当前扫描包:com.dengwei.springdemo.test2 下使用此数据源;sqlSessionFactoryRef:对应下面的sql回话工厂
* @Bean(name = "DataSource2") :给当前注入的bean对象取的名字
* @ConfigurationProperties(prefix = "spring.datasource.hikari.test2") application.properteis中对应属性的前缀
*
*/
@Configuration
@MapperScan(basePackages = "hx.insist.demo.mapper.hbxj", sqlSessionFactoryRef = "sqlSessionFactory2")
public class DataSource2Config {
/**
*
* @methodDesc: 功能描述:(配置test2数据库)
*/
@Bean(name = "DataSource2")
@ConfigurationProperties(prefix = "spring.datasource.two")
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
/**
*
* @methodDesc: 功能描述:(test2 sql会话工厂)
* @returnType:@param dataSource
* @returnType:@throws Exception SqlSessionFactory
*/
@Bean(name = "sqlSessionFactory2")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("DataSource2") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
//mybatis写配置文件(sql映射)需要加下面的代码
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:static/mybatis/mapper/DS2/*.xml"));
return bean.getObject();
}
/**
*
* @methodDesc: 功能描述:(test2 事物管理)
*/
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager testTransactionManager(@Qualifier("DataSource2") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
配置好数据源信息后,就可以写po、mapper、service、controller来测试了:
PO
Demo类:
package hx.insist.demo.domain.meituan.Po;
import com.alibaba.fastjson.annotation.JSONField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
/**
* @author: HanXu
* on 2019/12/18
* Class description:
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Demo {
private Long id;
private String emp;
private String name;
private String address;
private String tel;
private Integer salary;
//将时间戳数字自动转换为对应时间格式
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private Date createTime;
@JSONField(format="yyyy-MM-dd HH:mm:ss")
private Date updateTime;
}
Usre类:
package hx.insist.demo.domain.hbxj.Po;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author: HanXu
* on 2019/12/19
* Class description:
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String name;
private Integer age;
}
mapper
DemoMapper类:
package hx.insist.demo.mapper.meituan;
import hx.insist.demo.domain.meituan.Po.Demo;
import java.util.List;
/**
* @author: HanXu
* on 2019/12/18
* Class description:
*/
public interface DemoMapper {
List<Demo> getAll();
}
对应的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="hx.insist.demo.mapper.meituan.DemoMapper">
<resultMap id="BaseResultMap" type="hx.insist.demo.domain.meituan.Po.Demo">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="emp" jdbcType="VARCHAR" property="emp" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="address" jdbcType="VARCHAR" property="address" />
<result column="tel" jdbcType="VARCHAR" property="tel" />
<result column="salary" jdbcType="INTEGER" property="salary" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
</resultMap>
<select id="getAll" resultMap="BaseResultMap">
select * from user
</select>
</mapper>
UserMapper类:
package hx.insist.demo.mapper.hbxj;
import hx.insist.demo.domain.hbxj.Po.User;
import java.util.List;
/**
* @author: HanXu
* on 2019/12/19
* Class description:
*/
public interface UserMapper {
List<User> getAll();
}
对应的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="hx.insist.demo.mapper.hbxj.UserMapper">
<resultMap id="BaseResultMap" type="hx.insist.demo.domain.hbxj.Po.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
</resultMap>
<select id="getAll" resultMap="BaseResultMap">
select * from user
</select>
</mapper>
xml中mapper标签的namespace一定要和对应的mapper接口路径一致
service
DemoService.java:
package hx.insist.demo.service.meituan;
import hx.insist.demo.domain.meituan.Po.Demo;
import hx.insist.demo.mapper.meituan.DemoMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
* @author: HanXu
* on 2019/12/18
* Class description:
*/
@Service
public class DemoService {
@Resource
private DemoMapper demoMapper;
public List<Demo> getAll(){
return demoMapper.getAll();
}
}
UserService.java:
package hx.insist.demo.service.hbxj;
import hx.insist.demo.domain.hbxj.Po.User;
import hx.insist.demo.mapper.hbxj.UserMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/**
* @author: HanXu
* on 2019/12/19
* Class description:
*/
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public List<User> getAll(){
return userMapper.getAll();
}
}
controller
package hx.insist.demo.ui;
import hx.insist.demo.service.hbxj.UserService;
import hx.insist.demo.service.meituan.DemoService;
import io.swagger.annotations.Api;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author: HanXu
* on 2019/12/18
* Class description:
*/
@Api("用户操作")
@RestController
public class UserController {
@Autowired
private DemoService demoService;
@Autowired
private UserService userService;
@GetMapping("meituanDemos")
public ResponseEntity getAllDs1(){
return ResponseEntity.ok(demoService.getAll());
}
@GetMapping("hbxjUsers")
public ResponseEntity getAllDs2(){
return ResponseEntity.ok(userService.getAll());
}
}
启动类
启动类要配置mapper接口扫描
package hx.insist.demo;
import com.alibaba.fastjson.serializer.SerializeConfig;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.alibaba.fastjson.serializer.ToStringSerializer;
import com.alibaba.fastjson.support.config.FastJsonConfig;
import com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.http.HttpMessageConverters;
import org.springframework.context.annotation.Bean;
import org.springframework.http.converter.HttpMessageConverter;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
import java.math.BigInteger;
@EnableSwagger2
//@MapperScan({"hx.insist.demo.mapper.hbxj","hx.insist.demo.mapper.meituan"})
@MapperScan("hx.insist.demo.mapper")//这两个扫描路径都可以
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
/**
* 使用@Bean注解注入第三方的解析框架(fastJson)
*/
@Bean
public HttpMessageConverters fastHttpMessageConverters() {
FastJsonHttpMessageConverter converter = new FastJsonHttpMessageConverter();
//配置
FastJsonConfig fastJsonConfig = new FastJsonConfig();
fastJsonConfig.setSerializerFeatures(SerializerFeature.PrettyFormat);
//解决Long精度丢失问题
SerializeConfig serializeConfig = SerializeConfig.globalInstance;
serializeConfig.put(BigInteger.class, ToStringSerializer.instance);
serializeConfig.put(Long.class, ToStringSerializer.instance);
serializeConfig.put(Long.TYPE, ToStringSerializer.instance);
fastJsonConfig.setSerializeConfig(serializeConfig);
//配置setter
converter.setFastJsonConfig(fastJsonConfig);
HttpMessageConverter<?> converter2 = converter;
return new HttpMessageConverters(converter2);
}
}
测试
浏览器输入http://localhost:9091/swagger-ui.html进入swagger测试:
执行成功:
总结:
配置多数据源注意事项:
- 配置文件中多数据源用不同词缀分辨:spring.datasource.one、spring.datasource.two
- 配置文件中要配置mapper.xml扫描路径:mybatis.mapper-locations=classpath:static/mybatis/mapper//.xml
- SpringBoot2.0写法有所改变:spring.datasource.one.jdbc-url、spring.datasource.one.driver-class-name
- 多数据源配置类上要配置对应的mapper接口扫描路径、mapper.xml扫描路径和配置文件对应配置前缀
- 启动类要加mapper接口扫描
- mapper.xml文件中的mapper标签的namespace命名空间要和接口对应