Springboot如何同时装配两个相同类型数据库
目录
- 同时装配两个相同类型数据库
- 配置连接两个或多个数据库
同时装配两个相同类型数据库
1.配置文件：
spring:
profiles:
active: dev
datasource:
primary:
jdbc-url: jdbc:sqlserver://localhost:1111;DatabaseName=DB1
driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: root
secondary:
jdbc-url: jdbc:sqlserver://localhost:1111;DatabaseName=DB2
driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: root
2.配置类：
①主配置类：DataSourceConfigPrimary
@Configuration
@MapperScan(basePackages = "com.message.dao.primary", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class DataSourceConfigPrimary {
// 将这个对象放入Spring容器中
@Bean(name = "primaryDataSource")
// 表示这个数据源是默认数据源
@Primary
// 读取application.properties中的配置参数映射成为一个对象
// prefix表示参数的前缀
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource getDateSourcePrimary()
{
return DataSourceBuilder.create().build();
}
@Bean(name = "primarySqlSessionFactory")
// 表示这个数据源是默认数据源
@Primary
// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource datasource)
throws Exception
{
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
// 设置mybatis的xml所在位置
new PathMatchingResourcePatternResolver().getResources("classpath:mapper/primary/*.xml"));
return bean.getObject();
}
@Bean("primarySqlSessionTemplate")
// 表示这个数据源是默认数据源
@Primary
public SqlSessionTemplate primarySqlSessionTemplate(
@Qualifier("primarySqlSessionFactory") SqlSessionFactory sessionFactory)
{
return new SqlSessionTemplate(sessionFactory);
}
}
②次配置类：DataSourceConfigSecondary
@Configuration
@MapperScan(basePackages = "com.message.dao.secondary", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class DataSourceConfigSecondary {
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource getDateSource2()
{
return DataSourceBuilder.create().build();
}
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource datasource)
throws Exception
{
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mapper/secondary/*.xml"));
return bean.getObject();
}
@Bean("secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(
@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sessionFactory)
{
return new SqlSessionTemplate(sessionFactory);
}
}
3.扫描XML
4.启动类：
@SpringBootApplication(scanBasePackages = {"com.lalal.*"})
public class MessageApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(MessageApplication.class, args);
}
@Bean
public RestTemplate restTemplate(){
return new RestTemplate();
}
}
配置连接两个或多个数据库
背景：
项目中需要从两个不同的数据库查询数据，之前实现方法是：springboot配置连接一个数据源，另一个使用jdbc代码连接。
为了改进，现在使用SpringBoot配置连接两个数据源
实现效果：
一个SpringBoot项目，同时连接两个数据库：比如一个是pgsql数据库，一个是oracle数据库
（啥数据库都一样，连接两个同为oracle的数据库，或两个不同的数据库，只需要更改对应的driver-class-name和jdbc-url等即可）
注意：连接什么数据库，要引入对应数据库的包
实现步骤：
1、修改application.yml，添加一个数据库连接配置
（我这里是yml格式，后缀为properties格式是一样的
server:
port: 7101
spring:
jpa:
show-sql: true
datasource:
test1:
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://127.0.0.1:5432/test #测试数据库
username: root
password: root
test2:
driver-class-name: oracle.jdbc.driver.OracleDriver
jdbc-url: jdbc:oracle:thin:@127.0.0.1:8888:orcl #测试数据库
username: root
password: root
特别注意：
（1）使用test1、test2区分两个数据库连接
（2）url改为：jdbc-url
2、使用代码进行数据源注入，和扫描dao层路径（以前是在yml文件里配置mybatis扫描dao的路径）
新建config包，包含数据库1和数据库2的配置文件
（1）第一个数据库作为主数据库，项目启动默认连接此数据库
DataSource1Config.java
package com.test.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
@MapperScan(basePackages = "com.test.dao.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSource1Config {
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test1")
@Primary
public DataSource testDataSource() {
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:test1/*.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);
}
}
特别注意：
（1）主数据库都有 @Primary注解，从数据库都没有
（2）第二个数据库作为从数据库
DataSource2Config.java
package com.test.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
@MapperScan(basePackages = "com.test.dao.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class DataSource2Config {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
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:test2/*.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);
}
}
3、 在dao文件夹下，新建test1和test2两个包，分别放两个不同数据库的dao层文件
（1）TestDao1.java
@Component
public interface TestDao1 {
List<DailyActivityDataMiddle> selectDailyActivity();
}
（2）TestDao2.java
@Component
public interface TestDao2 {
List<MovieShowTest> selectDailyActivity();
}
4、 在resource下新建test1和test2两个文件夹，分别放入对应dao层的xml文件
（我原来项目的dao的xml文件在resource目录下，你们在自己的项目对应目录下即可）
注意dao的java文件和dao的xml文件名字要一致
（1）TestDao1.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.test.dao.test1.TestDao1">
<select id="selectDailyActivity" resultType="com.test.pojo.DailyActivityDataMiddle">
SELECT * FROM daily_activity_data_middle
</select>
</mapper>
（2）TestDao2.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.test.dao.test2.TestDao2">
<select id="selectDailyActivity" resultType="com.test.pojo.MovieShowTest">
SELECT * FROM movieshowtest
</select>
</mapper>
5、测试
在controller文件里，注入两个数据库的dao，分别查询数据
@RestController
public class TestController extends BaseController{
@Autowired
private PropertiesUtils propertiesUtils;
@Autowired
private TestDao1 testDao1;
@Autowired
private TestDao2 testDao2;
@RequestMapping(value = {"/test/test1"},method = RequestMethod.POST)
public Result<JSONObject> DataStatistics (@RequestBody JSONObject body) throws Exception {
Result<JSONObject> result = new Result<>(ICommon.SUCCESS, propertiesUtils.get(ICommon.SUCCESS));
JSONObject object = new JSONObject();
object.put("data",testDao1.selectDailyActivity());
result.setResult(object);
return result;
}
@RequestMapping(value = {"/test/test2"},method = RequestMethod.POST)
public Result<JSONObject> DataStatisticsaa (@RequestBody JSONObject body) throws Exception {
Result<JSONObject> result = new Result<>(ICommon.SUCCESS, propertiesUtils.get(ICommon.SUCCESS));
JSONObject object = new JSONObject();
object.put("data",testDao2.selectDailyActivity());
result.setResult(object);
return result;
}
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持自由互联。
【转自：美国服务器】