具体项目代码见github
项目结构
yml配置
server:
port: 8091
spring:
datasource:
comm:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://****/*?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username: *
password: *
commMapperLocations: classpath:com/asiainfo/comm/**/mapper/*.xml
configLocation: classpath:mybatis/mybatis-config.xml
settle:
driver-class-name: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@****:1521:*
username: *
password: *
settleMapperLocations: classpath:com/asiainfo/settle/**/mapper/*.xml
configLocation: classpath:mybatis/mybatis-config.xml
logging:
level:
root: info
com.asiainfo: debug
启动类(无需配置,会自动扫描同级包及子包下的组件)
@SpringBootApplication
public class SpringbootDatasourceApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootDatasourceApplication.class, args);
}
}
数据源配置类(不同数据源扫描不同包)
注意:多个同类型bean要使用@Primary指定优先选择,并指定名称name进行区分
主数据源
@Configuration
@MapperScan(basePackages = {"com.asiainfo.comm.**.dao"},sqlSessionFactoryRef = "commSqlSessionFactory")
public class CommDruidDataSourceConfig {
@Value("${spring.datasource.comm.commMapperLocations}")
private String commMapperLocations ;
@Value("${spring.datasource.comm.configLocation}")
private String configLocation;
@ConfigurationProperties(prefix = "spring.datasource.comm")
@Bean(name = "commDataSource")
@Primary
public DataSource commDataSource() {
return new DruidDataSource();
}
@Bean("commJdbcTemplate")
@Primary
public JdbcTemplate template(@Qualifier(value = "commDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
/**
* 自动识别使用的数据库类型
* 在mapper.xml中databaseId的值就是跟这里对应,
* 如果没有databaseId选择则说明该sql适用所有数据库
* */
@Bean(name = "commDatabaseIdProvider")
@Primary
public DatabaseIdProvider getDatabaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties properties = new Properties();
properties.setProperty("Oracle", "oracle");
properties.setProperty("MySQL", "mysql");
properties.setProperty("H2", "h2");
databaseIdProvider.setProperties(properties);
return databaseIdProvider;
}
/**
* SqlSessionFactory配置
*
* @return
* @throws Exception
*/
@Bean(name = "commSqlSessionFactory")
@Primary
public SqlSessionFactory commSqlSessionFactory(@Qualifier("commDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//自动识别 支持多种数据库关键代码
sqlSessionFactoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
// 配置mapper文件位置
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(commMapperLocations));
sqlSessionFactoryBean.setConfigLocation(resolver.getResource(configLocation));
return sqlSessionFactoryBean.getObject();
}
/**
* 配置事物管理器
*
* @return
*/
@Bean(name = "commTransactionManager")
@Primary
public DataSourceTransactionManager commTransactionManager(@Qualifier("commDataSource") DataSource dataSource) {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
从数据源
@Configuration
@MapperScan(basePackages = {"com.asiainfo.settle.**.dao"},sqlSessionFactoryRef = "settleSqlSessionFactory")
public class SettleDruidDataSourceConfig {
@Value("${spring.datasource.settle.settleMapperLocations}")
private String settleMapperLocations;
@Value("${spring.datasource.settle.configLocation}")
private String configLocation;
@ConfigurationProperties(prefix = "spring.datasource.settle")
@Bean(name = "settleDataSource")
public DataSource settleDataSource() {
return new DruidDataSource();
}
@Bean("settleJdbcTemplate")
@Primary
public JdbcTemplate template(@Qualifier(value = "settleDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
/**
* 自动识别使用的数据库类型
* 在mapper.xml中databaseId的值就是跟这里对应,
* 如果没有databaseId选择则说明该sql适用所有数据库
* */
@Bean(name = "settleDatabaseIdProvider")
public DatabaseIdProvider getDatabaseIdProvider() {
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties properties = new Properties();
properties.setProperty("Oracle", "oracle");
properties.setProperty("MySQL", "mysql");
properties.setProperty("H2", "h2");
databaseIdProvider.setProperties(properties);
return databaseIdProvider;
}
/**
* SqlSessionFactory配置
*
* @return
* @throws Exception
*/
@Bean(name = "settleSqlSessionFactory")
public SqlSessionFactory settleSqlSessionFactory(@Qualifier("settleDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//自动识别 支持多种数据库关键代码
sqlSessionFactoryBean.setDatabaseIdProvider(getDatabaseIdProvider());
// 配置mapper文件位置
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(settleMapperLocations));
sqlSessionFactoryBean.setConfigLocation(resolver.getResource(configLocation));
return sqlSessionFactoryBean.getObject();
}
/**
* 配置事物管理器
*
* @return
*/
@Bean(name = "settleTransactionManager")
public DataSourceTransactionManager settleTransactionManager(@Qualifier("settleDataSource") DataSource dataSource) {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
Mapper文件中
使用databaseId配置支持多种数据库
<mapper namespace="com.asiainfo.comm.dao.TestDao1">
<select id="getData" resultType="java.util.HashMap" databaseId="mysql">
select * from stt_privilege
</select>
<select id="getData" resultType="java.util.HashMap" databaseId="oracle">
select * from stt_privilege
</select>
</mapper>
附记
项目搭建时出现启动报错,数据源配置类中无法读取yml中数据源配置,排查发现yml没有输出到target文件夹classes目录下
原因:pom文件中使用了includes标签(注释2),指定resources目录下编译输出包含xml文件,但未指定yml文件(如下),可去除此配置,默认包含所有。注释1部分去除会导致src/java目录下xml文件未编译输出到target下classes目录下,造成Invalid bound statement异常,如果mapper文件时放置在resource目录下则不需要此设置,具体问题要根据项目结构进行配置解决
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<!--注释1-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<!--注释2-->
<!--<resource>-->
<!--<directory>src/main/resources</directory>-->
<!--<includes>-->
<!--<include>**/*.xml</include>-->
<!--</includes>-->
<!--<filtering>false</filtering>-->
<!--</resource>-->
</resources>
</build>
另有时指定了xml文件目录时却未生效,如下
spring:
datasource:
comm:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://*:8454/*?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username: *
password: *
commMapperLocations: classpath:com/asiainfo/comm/**/mapper/*.xml
configLocation: classpath:mybatis/mybatis-config.xml
解决:此时可指定为classpath*