背景
随着项目的强化,单一数据源不再满足需求。不同的业务需要操作不同的数据库。本篇采用spring-boot结合mybatis整合多数据源。
maven依赖
<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 http://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.1.0.RELEASE</version>
</parent>
<groupId>com.jx.aisino</groupId>
<artifactId>shzafkpt</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.6.5</version>
</dependency>
<!-- 资源池 -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle/ojdbc14 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.1.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<!-- Jackson Json处理工具包 -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<!-- Json -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.1</version>
</dependency>
<!-- Apache工具组件 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.5</version>
</dependency>
<!-- abel533 -->
<dependency>
<groupId>com.github.abel533</groupId>
<artifactId>mapper</artifactId>
<version>2.3.0</version>
</dependency>
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.1</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<scope>provided</scope>
</dependency>
<!-- 格式化对象,方便输出日志 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.41</version>
</dependency>
<dependency>
<groupId>javax.persistence</groupId>
<artifactId>persistence-api</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<!-- 资源文件拷贝插件 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<!-- java编译插件 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
项目结构
配置数据源
package com.aisino.shzafkpt.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.web.servlet.MultipartAutoConfiguration;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
@ComponentScan(basePackages = "com.aisino")
@PropertySource(value = {"classpath:jdbc.properties"})
@SpringBootApplication(exclude = {MultipartAutoConfiguration.class})
public class MainApplication extends SpringBootServletInitializer{
@Value("${db.username}")
private String username;
@Value("${db.password}")
private String passwd;
@Value("${url}")
private String jdbcUrl;
@Value("${hjwUsername}")
private String hjwUsername;
@Value("${hjwPassword}")
private String hjwPassword;
@Value("${hjwUrl}")
private String hjwUrl;
@Value("${nczdryUrl}")
private String nczdryUrl;
@Value("${nczdryUsername}")
private String nczdryUsername;
@Value("${nczdryPassword}")
private String nczdryPassword;
@Value("${lgUrl}")
private String lgUrl;
@Value("${lgUsername}")
private String lgUsername;
@Value("${lgPassword}")
private String lgPassword;
@Value("${driver}")
private String driverClassName;
@Value("${maxPoolSize}")
private Integer maxPoolSize;
@Value("${minIdle}")
private Integer minIdle;
@Value("${validationTimeout}")
private Integer validationTimeout;
@Value("${idleTimeout}")
private Integer idleTimeout;
@Bean(destroyMethod = "close",name = "dataSource")
@Primary
public DataSource dataSource() throws Exception {
HikariConfig configuration = new HikariConfig();
configuration.setJdbcUrl(jdbcUrl);
configuration.setDriverClassName(driverClassName);
configuration.setMaximumPoolSize(maxPoolSize);
configuration.setMinimumIdle(minIdle);
configuration.setValidationTimeout(validationTimeout);
configuration.setIdleTimeout(idleTimeout);
configuration.setConnectionTestQuery("select count(1) from tab_kh");
configuration.setUsername(username);
configuration.setPassword(passwd);
HikariDataSource dataSource = new HikariDataSource(configuration);
return dataSource;
}
@Bean(destroyMethod = "close",name = "hjwDataSource")
public DataSource dataSource4Lg() throws Exception {
HikariConfig configuration = new HikariConfig();
configuration.setJdbcUrl(hjwUrl);
configuration.setDriverClassName(driverClassName);
configuration.setMaximumPoolSize(maxPoolSize);
configuration.setMinimumIdle(minIdle);
configuration.setValidationTimeout(validationTimeout);
configuration.setIdleTimeout(idleTimeout);
configuration.setConnectionTestQuery("select 1+1 from dual");
configuration.setUsername(hjwUsername);
configuration.setPassword(hjwPassword);
HikariDataSource dataSource = new HikariDataSource(configuration);
return dataSource;
}
@Bean(destroyMethod = "close",name = "nczdryDataSource")
public DataSource dataSource4Nczdry() throws Exception {
HikariConfig configuration = new HikariConfig();
configuration.setJdbcUrl(nczdryUrl);
configuration.setDriverClassName(driverClassName);
configuration.setMaximumPoolSize(maxPoolSize);
configuration.setMinimumIdle(minIdle);
configuration.setValidationTimeout(validationTimeout);
configuration.setIdleTimeout(idleTimeout);
configuration.setConnectionTestQuery("select 1+1 from dual");
configuration.setUsername(nczdryUsername);
configuration.setPassword(nczdryPassword);
HikariDataSource dataSource = new HikariDataSource(configuration);
return dataSource;
}
@Bean(destroyMethod = "close",name = "nclgDataSource")
public DataSource dataSource4Nclg() throws Exception {
HikariConfig configuration = new HikariConfig();
configuration.setJdbcUrl(lgUrl);
configuration.setDriverClassName(driverClassName);
configuration.setMaximumPoolSize(maxPoolSize);
configuration.setMinimumIdle(minIdle);
configuration.setValidationTimeout(validationTimeout);
configuration.setIdleTimeout(idleTimeout);
configuration.setConnectionTestQuery("select 1+1 from dual");
configuration.setUsername(lgUsername);
configuration.setPassword(lgPassword);
HikariDataSource dataSource = new HikariDataSource(configuration);
return dataSource;
}
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) {
return builder.sources(MainApplication.class);
}
public static void main(String[] args) {
SpringApplication.run(MainApplication.class, args);
}
}
数据源1整合mybstis
package com.aisino.shzafkpt.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
@MapperScan(basePackages = "com.aisino.shzafkpt.mapper.trunk",sqlSessionTemplateRef = "defaultSqlSessionTemplate")
public class MyBatisConfig {
@Autowired
@Qualifier(value = "dataSource")
private DataSource dataSource;
@Bean(name = "defaultSqlSessionFactoryBean")
@Primary
public SqlSessionFactory defaultSqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = null;
try {
sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
// 设置mybatis的主配置文件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource mybatisConfigXml = resolver.getResource("classpath:mybatis/mybatis-config.xml");
sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
// 设置别名包
sqlSessionFactoryBean.setTypeAliasesPackage("com.aisino.shzafkpt.bean");
// 配置mapper的扫描,找到所有的mapper.xml映射文件
Resource[] resources = resolver.getResources("classpath:mybatis/mappers/*.xml");
sqlSessionFactoryBean.setMapperLocations(resources);
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactoryBean.getObject();
}
@Bean("defaultSqlSessionTemplate")
@Primary
public SqlSessionTemplate defaultSqlSessionTemplate() throws Exception{
SqlSessionTemplate template = new SqlSessionTemplate(defaultSqlSessionFactoryBean());
return template;
}
}
数据源2整合mybatis
package com.aisino.shzafkpt.config;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
@Configuration
@MapperScan(basePackages = "com.aisino.shzafkpt.mapper.hjw",sqlSessionTemplateRef = "hjwSqlSessionTemplate")
public class HjwMyBatisConfig {
@Autowired
@Qualifier(value = "hjwDataSource")
private DataSource hjwDataSource;
@Bean(name = "hjwSqlSessionFactoryBean")
public SqlSessionFactory hjwSqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = null;
try {
sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(hjwDataSource);
// 设置mybatis的主配置文件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource mybatisConfigXml = resolver.getResource("classpath:mybatis/mybatis-config.xml");
sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
// 设置别名包
sqlSessionFactoryBean.setTypeAliasesPackage("com.aisino.shzafkpt.model");
// 配置mapper的扫描,找到所有的mapper.xml映射文件
Resource[] resources = resolver.getResources("classpath:mybatis/hjws/*.xml");
sqlSessionFactoryBean.setMapperLocations(resources);
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactoryBean.getObject();
}
@Bean("hjwSqlSessionTemplate")
public SqlSessionTemplate defaultSqlSessionTemplate() throws Exception{
SqlSessionTemplate template = new SqlSessionTemplate(hjwSqlSessionFactoryBean());
return template;
}
}
注意修改mapper所在包及xml文件存放位置
通用Mapper
package com.aisino.shzafkpt.config;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@AutoConfigureAfter(MyBatisConfig.class)
public class MapperScannerConfig {
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setBasePackage("com.aisino.shzafkpt.mapper");
return mapperScannerConfigurer;
}
}