SpringBoot连接两个或多个数据库(SpringBoot+SqlServer)

共 5632字,需浏览 12分钟

 ·

2021-12-02 14:48

场景

同事要用SqlServer写一个小项目,但是有个问题就是他们的数据需要存到多个数据库中,其中两个库的名字是account和game,不知道怎么处理切换,然后我这边写了个测试暂时没有问题,求大神指教

SpringBoot配置文件

#连接sqlserver数据库
spring.datasource.account.jdbc-url=jdbc:sqlserver://172.16.10.2:1433;DatabaseName=account
spring.datasource.account.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.account.username=sa
spring.datasource.account.password=plo#@!


spring.datasource.game.jdbc-url=jdbc:sqlserver://172.16.10.2:1433;DatabaseName=game
spring.datasource.game.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.game.username=sa
spring.datasource.game.password=plo#@!

自定义配置文件

package org.geemp.game.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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
* @author JackRen
* @date 2021-12-01 14:14
* @description:
*/


@Configuration
@MapperScan(basePackages= {"org.geemp.game.account"},sqlSessionFactoryRef="gameSqlSessionFactory")
public class DataSourceAccountConfig {

@Bean(name="accountDataSource")
@ConfigurationProperties(prefix="spring.datasource.account")
public DataSource accountDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name="accountSqlSessionFactory")
@Primary
public SqlSessionFactory accountSqlSessionFactory(@Qualifier("accountDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean=new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name="accountTransactionManager")//配置事务
@Primary
public DataSourceTransactionManager accountTransactionManager(@Qualifier("accountDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name="accountSqlSessionTemplate")
@Primary
public SqlSessionTemplate accountSqlSessionTemplate(@Qualifier("accountSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}

}
package org.geemp.game.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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
* @author JackRen
* @date 2021-12-01 14:15
* @description:
*/


@Configuration
@MapperScan(basePackages= {"org.geemp.game.gm"},sqlSessionFactoryRef="gameSqlSessionFactory")
public class DataSourceGameConfig {

@Bean(name="gameDataSource")
@ConfigurationProperties(prefix="spring.datasource.game")
public DataSource gameDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name="gameSqlSessionFactory")
public SqlSessionFactory gameSqlSessionFactory(@Qualifier("gameDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean=new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name="gameTransactionManager")//配置事务
public DataSourceTransactionManager gameTransactionManager(@Qualifier("gameDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name="gameSqlSessionTemplate")
public SqlSessionTemplate gameSqlSessionTemplate(@Qualifier("gameSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}

}

在配置类扫描的包下进行数据交互


我们以game为例:

mapper:

package org.geemp.game.gm.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
* @author JackRen
* @date 2021-12-01 14:43
* @description:
*/

public interface GameMapper {

@Select("select CT_INGOT,CT_GOLD,CT_POINT from chartable where CT_USERID = #{CT_USERID} and CT_NAME=#{CT_NAME}")
List> obtainDataBy(@Param("CT_USERID") String CT_USERID,@Param("CT_NAME") String CT_NAME);
}

service:

package org.geemp.game.gm.service;

import org.geemp.game.gm.mapper.GameMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
* @author JackRen
* @date 2021-04-27 13:31
* @description:
*/

@Service
public class GameDBServiceImpl {

@Autowired
private GameMapper gameMapper;

public List> obtainDataBy(String CT_USERID, String CT_NAME){
List> dataList= gameMapper.obtainDataBy(CT_USERID,CT_NAME);
return dataList;
}

}

同样的,account和game是一样的写法,篇幅限制不再粘贴,到这里就完成了,可以启动测试!


浏览 179
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报