springboot 多数据源配置mysql+sqlserver
因项目需要多数据源配置,通过学习文档及网上资料完成多数据源配置,秉持好记性不如烂笔头原则特此文档记录。
第一次写,不足之处请指正。
1、项目目录如下:
红色框为重点关注内容。
2、pom.xml配置
<repositories>
<!-- Repositories sqljdbc4 -->
<repository>
<id>clojars</id>
<url>http://clojars.org/repo/</url>
</repository>
</repositories>
<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.3.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
注意:repository的clojars是sqljdbc4资源库,不能遗漏。
3.1、application.properties 文件配置
此处为适配多环境,添加application-local.properties文件。如需其他环境,可依次添加,使用时通过active指定环境即可。
server.port=8088
#出现错误时, 直接抛出异常
spring.mvc.throw-exception-if-no-handler-found=true
#不要为我们工程中的资源文件建立映射
spring.resources.add-mappings=false
# 本地环境
spring.profiles.active=local
3.2、application-local.properties文件配置
#mysql
spring.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mysql.jdbc-url=jdbc:mysql://localhost:3306/test_mysql?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
spring.datasource.mysql.username=root
spring.datasource.mysql.password=123456
#sqlserver
spring.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.sqlserver.jdbc-url=jdbc:sqlserver://localhost:1433;databaseName=test_sqlserver
spring.datasource.sqlserver.username=sa
spring.datasource.sqlserver.password=123456
4、MySqlConfig数据源配置
MySqlConfig此数据源为Primary数据源,其他数据源配置与此文件一样删除Primary即可。(必须有且只有一个primary数据源)
代码如下:
package net.wiseks.datasource;
import javax.sql.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.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;
@Configuration
@MapperScan(basePackages = {"net.wiseks.mysql"},sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MySqlConfig {
/**
* mysqlDataSource 注入到容器
* spring.datasource.mysql 取数据源配置文件中的前缀
* 设置优先数据源 Primary 必须设置
* @return mysql数据源
*/
@Bean(name = "mysqlDataSource")
@ConfigurationProperties(prefix = "spring.datasource.mysql")
@Primary
public DataSource mysqlDataSource() {
return DataSourceBuilder.create().build();
}
/**
*
* @param dataSource 使用mysql数据源
* @return mySqlSessionFactory
* @throws Exception
*/
@Bean(name="mysqlSqlSessionFactory")
@Primary
public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean=new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
/**
* 配置事务
* @param dataSource 使用mysql数据源
*/
@Bean(name="mysqlTransactionManager")
@Primary
public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
*
* @param sqlSessionFactory factory
*/
@Bean(name="mysqlSqlSessionTemplate")
@Primary
public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
编写查询测试类,对多数据配置进行测试。
5、dao示例
不同数据源dao、service放在不同包下面,如上图所示,此处只列出mysql的测试类。
@Component(value = "mobileMySqlMapper")
public interface MobileMySqlMapper {
/**
* 查询用户信息
* @return MobileUserInfo
*/
@Select(" select id,openId,telephone from mobile_user where status=0 ")
List<MobileUserInfo> getMobileUserInfo();
}
6、service示例
@Service
public class MobileMysqlService {
@Autowired
MobileMySqlMapper mobileMySqlMapper;
/**
* 查询用户信息
* @return MobileUserInfo
*/
public List<MobileUserInfo> getMobileUserInfo(){
return mobileMySqlMapper.getMobileUserInfo();
}
}
7、controller示例
package net.wiseks.controller;
import net.wiseks.common.Constant;
import net.wiseks.common.Result;
import net.wiseks.mysql.service.MobileMysqlService;
import net.wiseks.sqlserver.service.MobileSqlServerService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Controller
@RequestMapping(value = "mt")
@ComponentScan(basePackages = {"net.wiseks.mysql.service", "net.wiseks.sqlserver.service"})
public class MobileTvController extends BaseController {
private final Logger logger = LoggerFactory.getLogger(MobileTvController.class);
@Autowired
MobileMysqlService mobileMysqlService;
@Autowired
MobileSqlServerService mobileSqlServerService;
@RequestMapping("/test")
@ResponseBody
Result test() {
List list = mobileMysqlService.getMobileUserInfo();
List tag = mobileSqlServerService.getTagInfo();
Map<String,Object> res = new HashMap<>(2);
res.put("list",list);
res.put("tag",tag);
return writeResult(Constant.RESULT_SUCCESS, res);
}
}
8、测试结果。
如上,完成多数据源配置。
路漫漫其修远兮,吾将上下而求索。