spring-boot 如何配置多个数据源
本示例工程使用maven来构建
pom配置文件
<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>
<groupId>com.linewell</groupId>
<artifactId>spring-boot-double-datasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<oracle.version>10.2.0.5.0</oracle.version>
<fastjson.version>1.2.24</fastjson.version>
<druid.version>1.0.9</druid.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>1.5.2.RELEASE</version>
<scope>import</scope>
<type>pom</type>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<!-- spring-boot的web依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- spring-boot的测试依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- spring-boot 的jdbc依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- oracle 驱动包 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>${oracle.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<!-- mybatis 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.0</version>
<exclusions>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-parent</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.github.miemiedev</groupId>
<artifactId>mybatis-paginator</artifactId>
<version>1.2.17</version>
<exclusions>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-parent</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- springboot编译插件 springboot:run -->
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>1.5.2.RELEASE</version>
<configuration>
<mainClass>cn.janvi.ds.Application</mainClass>
<layout>ZIP</layout>
</configuration>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
spring-boot的配置文件(src/main/resources/application.yml)
spring:
datasource:
db1:
url: jdbc:mysql://127.0.0.1/mybatis?characterEncoding=utf-8
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
db2:
url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
username: scott
password: tiger
driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis配置文件(src/main/resources/mybatis/SqlMapConfig1.xml),这个配置文件只用于数据源1的分页测试
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 设置数据库类型可选值为[mysql,mariadb,sqlite,oracle,hsqldb,postgresql]六种数据库 -->
<property name="dialect" value="mysql" />
</plugin>
</plugins>
</configuration>
spring-boot的启动类(cn.janvi.ds.Application)
package cn.janvi.ds;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
*
* spring-boot 配置多个数据源 Company:www.linewell.com
*
* @author panjianwei
* @date 2017年8月28日上午11:05:59
*
* 1. 首先要将spring
* boot自带的DataSourceAutoConfiguration禁掉,因为它会读取application.properties文件的
* spring.datasource.*属性并自动配置单数据源。在@SpringBootApplication注解中添加exclude属性即可;
*/
@EnableTransactionManagement //开启事务管理;如果mybatis中service实现类中加入事务注解,需要此处添加该注解
@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
数据源1的属性映射类(cn.janvi.ds.config.Db1Properties)
package cn.janvi.ds.config;
import java.io.Serializable;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
@Component
@ConfigurationProperties(prefix = "spring.datasource.db1")
public class Db1Properties implements Serializable{
private static final long serialVersionUID = 283848210567696024L;
public String url;
public String username;
public String password;
public String driverClassName;
public Integer maxActive;
public Integer initialSize;
public Integer minIdle;
public Integer maxWait;
public Integer maxPoolPreparedStatementPerConnectionSize;
public Integer timeBetweenEvictionRunsMillis;
public Integer minEvictableIdleTimeMillis;
public Boolean poolPreparedStatements;
// 省略Setter 和 Getter 方法......
}
数据源2的属性映射类(cn.janvi.ds.config.Db2Properties)
package cn.janvi.ds.config;
import java.io.Serializable;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
@Component
@ConfigurationProperties(prefix = "spring.datasource.db2")
public class Db2Properties implements Serializable{
private static final long serialVersionUID = 283848210567696024L;
public String url;
public String username;
public String password;
public String driverClassName;
public Integer maxActive;
public Integer initialSize;
public Integer minIdle;
public Integer maxWait;
public Integer maxPoolPreparedStatementPerConnectionSize;
public Integer timeBetweenEvictionRunsMillis;
public Integer minEvictableIdleTimeMillis;
public Boolean poolPreparedStatements;
// 省略Setter 和 Getter 方法......
}
手动配置数据源(cn.janvi.ds.config.DataSourceConfig)
package cn.janvi.ds.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
/**
*
* 手动创建数据源
* Company:www.linewell.com
* @author panjianwei
* @date 2017年8月28日上午11:20:55
*/
@Configuration
public class DataSourceConfig {
@Autowired
private Db1Properties db1Properties;
/**
* 数据源1,使用阿里巴巴的数据源com.alibaba.druid.pool.DruidDataSource
* @return
*/
@Bean(name = "ds1")
public DataSource dataSource1() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(db1Properties.url);
dataSource.setUsername(db1Properties.username);
dataSource.setPassword(db1Properties.password);
dataSource.setDriverClassName(db1Properties.driverClassName);
return dataSource;
}
/**
* 数据源2,使用默认的数据源org.apache.tomcat.jdbc.pool.DataSource
* @return
*/
@Bean(name = "ds2")
@ConfigurationProperties(prefix = "spring.datasource.db2") // application.yml中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}
分别配置各个数据的SqlSessionFactory、SqlSessionTemplate、DataSourceTransactionManager
针对数据源1的配置(cn.janvi.ds.config.MyBatisDb1Config):
package cn.janvi.ds.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.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
*
* 配置数据源1的SqlSessionFactory、SqlSessionTemplate、DataSourceTransactionManager
* Company:www.linewell.com
* @author panjianwei
* @date 2017年8月28日下午3:00:01
*/
@Configuration
@EnableTransactionManagement //开启事物管理
@MapperScan(basePackages = {"cn.janvi.ds.db1.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory1")
public class MyBatisDb1Config {
@Autowired
@Qualifier("ds1")
private DataSource ds1;
/**
* 创建SqlSessionFactory工厂bean,引入mybatis的分页插件
* @return
* @throws Exception
*/
@Bean
public SqlSessionFactory sqlSessionFactory1() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setConfigLocation(new ClassPathResource("mybatis/SqlMapConfig1.xml"));
factoryBean.setDataSource(ds1);
return factoryBean.getObject();
}
/**
* 创建 SqlSessionTemplate
* @return
* @throws Exception
*/
@Bean
public SqlSessionTemplate sqlSessionTemplate1() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory1());
return template;
}
/**
* 创建事务管理器
* @return
*/
@Bean
public DataSourceTransactionManager dataSourceTransactionManager1() {
return new DataSourceTransactionManager(ds1);
}
}
针对数据源2的配置(cn.janvi.ds.config.MyBatisDb2Config):
package cn.janvi.ds.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.support.PathMatchingResourcePatternResolver;
/**
* cn.janvi.ds.db2.mapper包下面的Mapper会自动连接db2库
* Title:MyBatisDb2Config
* Company:www.linewell.com
* @author panjianwei
* @date 2017年8月28日下午1:08:58
*/
@Configuration
@MapperScan(basePackages = { "cn.janvi.ds.db2.mapper" }, sqlSessionFactoryRef = "sqlSessionFactory2")
public class MyBatisDb2Config {
@Autowired
@Qualifier("ds2")
private DataSource ds2;
@Bean
public SqlSessionFactory sqlSessionFactory2() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds2);
PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();
factoryBean.setMapperLocations(pathMatchingResourcePatternResolver.getResources("classpath:/cn/janvi/ds/db2/mapper/*.xml"));
return factoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate2() throws Exception {
SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory2());
return template;
}
}
分别创建针对数据源1的Bean、Mapper、Service
cn.janvi.ds.db1.bean.Items(使用mybatis的逆向工程生成)
package cn.janvi.ds.db1.bean;
import java.util.Date;
public class Items {
private Integer id;
private String name;
private Float price;
private String pic;
private Date createtime;
private String detail;
// 省略 Setter 和 Getter 方法......
}
cn.janvi.ds.db1.mapper.ItemsMapper(使用mybatis的逆向工程生成)
package cn.janvi.ds.db1.mapper;
import cn.janvi.ds.db1.bean.Items;
import cn.janvi.ds.db1.bean.ItemsExample;
import java.util.List;
import org.apache.ibatis.annotations.Param;
public interface ItemsMapper {
int countByExample(ItemsExample example);
int deleteByExample(ItemsExample example);
int deleteByPrimaryKey(Integer id);
int insert(Items record);
int insertSelective(Items record);
List<Items> selectByExampleWithBLOBs(ItemsExample example);
List<Items> selectByExample(ItemsExample example);
Items selectByPrimaryKey(Integer id);
int updateByExampleSelective(@Param("record") Items record, @Param("example") ItemsExample example);
int updateByExampleWithBLOBs(@Param("record") Items record, @Param("example") ItemsExample example);
int updateByExample(@Param("record") Items record, @Param("example") ItemsExample example);
int updateByPrimaryKeySelective(Items record);
int updateByPrimaryKeyWithBLOBs(Items record);
int updateByPrimaryKey(Items record);
}
cn.janvi.ds.db1.mapper.ItemsMapper.xml(使用mybatis的逆向工程生成)
<?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="cn.janvi.ds.db1.mapper.ItemsMapper" >
<resultMap id="BaseResultMap" type="cn.janvi.ds.db1.bean.Items" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="price" property="price" jdbcType="REAL" />
<result column="pic" property="pic" jdbcType="VARCHAR" />
<result column="createtime" property="createtime" jdbcType="TIMESTAMP" />
</resultMap>
<resultMap id="ResultMapWithBLOBs" type="cn.janvi.ds.db1.bean.Items" extends="BaseResultMap" >
<result column="detail" property="detail" jdbcType="LONGVARCHAR" />
</resultMap>
<sql id="Example_Where_Clause" >
<where >
<foreach collection="oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
<sql id="Update_By_Example_Where_Clause" >
<where >
<foreach collection="example.oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
<sql id="Base_Column_List" >
id, name, price, pic, createtime
</sql>
<sql id="Blob_Column_List" >
detail
</sql>
<select id="selectByExampleWithBLOBs" resultMap="ResultMapWithBLOBs" parameterType="cn.janvi.ds.db1.bean.ItemsExample" >
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />
from items
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
<select id="selectByExample" resultMap="BaseResultMap" parameterType="cn.janvi.ds.db1.bean.ItemsExample" >
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from items
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
<select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />
from items
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from items
where id = #{id,jdbcType=INTEGER}
</delete>
<delete id="deleteByExample" parameterType="cn.janvi.ds.db1.bean.ItemsExample" >
delete from items
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
</delete>
<insert id="insert" parameterType="cn.janvi.ds.db1.bean.Items" >
insert into items (id, name, price,
pic, createtime, detail
)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{price,jdbcType=REAL},
#{pic,jdbcType=VARCHAR}, #{createtime,jdbcType=TIMESTAMP}, #{detail,jdbcType=LONGVARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="cn.janvi.ds.db1.bean.Items" >
insert into items
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="price != null" >
price,
</if>
<if test="pic != null" >
pic,
</if>
<if test="createtime != null" >
createtime,
</if>
<if test="detail != null" >
detail,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="price != null" >
#{price,jdbcType=REAL},
</if>
<if test="pic != null" >
#{pic,jdbcType=VARCHAR},
</if>
<if test="createtime != null" >
#{createtime,jdbcType=TIMESTAMP},
</if>
<if test="detail != null" >
#{detail,jdbcType=LONGVARCHAR},
</if>
</trim>
</insert>
<select id="countByExample" parameterType="cn.janvi.ds.db1.bean.ItemsExample" resultType="java.lang.Integer" >
select count(*) from items
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
</select>
<update id="updateByExampleSelective" parameterType="map" >
update items
<set >
<if test="record.id != null" >
id = #{record.id,jdbcType=INTEGER},
</if>
<if test="record.name != null" >
name = #{record.name,jdbcType=VARCHAR},
</if>
<if test="record.price != null" >
price = #{record.price,jdbcType=REAL},
</if>
<if test="record.pic != null" >
pic = #{record.pic,jdbcType=VARCHAR},
</if>
<if test="record.createtime != null" >
createtime = #{record.createtime,jdbcType=TIMESTAMP},
</if>
<if test="record.detail != null" >
detail = #{record.detail,jdbcType=LONGVARCHAR},
</if>
</set>
<if test="_parameter != null" >
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>
<update id="updateByExampleWithBLOBs" parameterType="map" >
update items
set id = #{record.id,jdbcType=INTEGER},
name = #{record.name,jdbcType=VARCHAR},
price = #{record.price,jdbcType=REAL},
pic = #{record.pic,jdbcType=VARCHAR},
createtime = #{record.createtime,jdbcType=TIMESTAMP},
detail = #{record.detail,jdbcType=LONGVARCHAR}
<if test="_parameter != null" >
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>
<update id="updateByExample" parameterType="map" >
update items
set id = #{record.id,jdbcType=INTEGER},
name = #{record.name,jdbcType=VARCHAR},
price = #{record.price,jdbcType=REAL},
pic = #{record.pic,jdbcType=VARCHAR},
createtime = #{record.createtime,jdbcType=TIMESTAMP}
<if test="_parameter != null" >
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>
<update id="updateByPrimaryKeySelective" parameterType="cn.janvi.ds.db1.bean.Items" >
update items
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="price != null" >
price = #{price,jdbcType=REAL},
</if>
<if test="pic != null" >
pic = #{pic,jdbcType=VARCHAR},
</if>
<if test="createtime != null" >
createtime = #{createtime,jdbcType=TIMESTAMP},
</if>
<if test="detail != null" >
detail = #{detail,jdbcType=LONGVARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKeyWithBLOBs" parameterType="cn.janvi.ds.db1.bean.Items" >
update items
set name = #{name,jdbcType=VARCHAR},
price = #{price,jdbcType=REAL},
pic = #{pic,jdbcType=VARCHAR},
createtime = #{createtime,jdbcType=TIMESTAMP},
detail = #{detail,jdbcType=LONGVARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="cn.janvi.ds.db1.bean.Items" >
update items
set name = #{name,jdbcType=VARCHAR},
price = #{price,jdbcType=REAL},
pic = #{pic,jdbcType=VARCHAR},
createtime = #{createtime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
cn.janvi.ds.db1.service.ItemsService(service接口)
package cn.janvi.ds.db1.service;
import java.util.List;
import com.github.pagehelper.PageInfo;
import cn.janvi.ds.db1.bean.Items;
public interface ItemsService {
List<Items> findAll();
PageInfo<Items> findByPage(int page,int rows);
int add(Items items);
}
cn.janvi.ds.db1.service.impl.ItemsServiceImpl(ItemsService接口实现类)
package cn.janvi.ds.db1.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import cn.janvi.ds.db1.bean.Items;
import cn.janvi.ds.db1.mapper.ItemsMapper;
import cn.janvi.ds.db1.service.ItemsService;
@Service
public class ItemsServiceImpl implements ItemsService {
/**
* 注入ItemsMapper
*/
@Autowired
private ItemsMapper itemsMapper;
@Override
public List<Items> findAll() {
return this.itemsMapper.selectByExample(null);
}
@Override
public PageInfo<Items> findByPage(int page, int rows) {
PageHelper.startPage(page,rows);
List<Items> selectByExample = this.itemsMapper.selectByExample(null);
PageInfo<Items> pageInfo = new PageInfo<Items>(selectByExample);
return pageInfo;
}
/**
* @Transactional 用于开启事务
*/
@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
@Override
public int add(Items items) {
// 模拟出错
int a = 1/0;
return this.itemsMapper.insert(items);
}
}
分别创建针对数据源2的Bean、Mapper、Service
cn.janvi.ds.db2.bean.Emp(使用mybatis逆向工程生成)
package cn.janvi.ds.db2.bean;
import java.math.BigDecimal;
import java.util.Date;
public class Emp {
private Short empno;
private String ename;
private String job;
private Short mgr;
private Date hiredate;
private BigDecimal sal;
private BigDecimal comm;
private Short deptno;
// 省略 Setter 和 Getter 方法 .....
}
cn.janvi.ds.db2.mapper.EmpMapper(使用mybatis逆向工程生成)
package cn.janvi.ds.db2.mapper;
import cn.janvi.ds.db2.bean.Emp;
import cn.janvi.ds.db2.bean.EmpExample;
import java.util.List;
import org.apache.ibatis.annotations.Param;
public interface EmpMapper {
int countByExample(EmpExample example);
int deleteByExample(EmpExample example);
int deleteByPrimaryKey(Short empno);
int insert(Emp record);
int insertSelective(Emp record);
List<Emp> selectByExample(EmpExample example);
Emp selectByPrimaryKey(Short empno);
int updateByExampleSelective(@Param("record") Emp record, @Param("example") EmpExample example);
int updateByExample(@Param("record") Emp record, @Param("example") EmpExample example);
int updateByPrimaryKeySelective(Emp record);
int updateByPrimaryKey(Emp record);
}
cn.janvi.ds.db2.mapper.EmpMapper.xml(使用mybatis逆向工程生成)
<?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="cn.janvi.ds.db2.mapper.EmpMapper" >
<resultMap id="BaseResultMap" type="cn.janvi.ds.db2.bean.Emp" >
<id column="EMPNO" property="empno" jdbcType="DECIMAL" />
<result column="ENAME" property="ename" jdbcType="VARCHAR" />
<result column="JOB" property="job" jdbcType="VARCHAR" />
<result column="MGR" property="mgr" jdbcType="DECIMAL" />
<result column="HIREDATE" property="hiredate" jdbcType="DATE" />
<result column="SAL" property="sal" jdbcType="DECIMAL" />
<result column="COMM" property="comm" jdbcType="DECIMAL" />
<result column="DEPTNO" property="deptno" jdbcType="DECIMAL" />
</resultMap>
<sql id="Example_Where_Clause" >
<where >
<foreach collection="oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
<sql id="Update_By_Example_Where_Clause" >
<where >
<foreach collection="example.oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
<sql id="Base_Column_List" >
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
</sql>
<select id="selectByExample" resultMap="BaseResultMap" parameterType="cn.janvi.ds.db2.bean.EmpExample" >
select
<if test="distinct" >
distinct
</if>
<include refid="Base_Column_List" />
from EMP
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Short" >
select
<include refid="Base_Column_List" />
from EMP
where EMPNO = #{empno,jdbcType=DECIMAL}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Short" >
delete from EMP
where EMPNO = #{empno,jdbcType=DECIMAL}
</delete>
<delete id="deleteByExample" parameterType="cn.janvi.ds.db2.bean.EmpExample" >
delete from EMP
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
</delete>
<insert id="insert" parameterType="cn.janvi.ds.db2.bean.Emp" >
insert into EMP (EMPNO, ENAME, JOB,
MGR, HIREDATE, SAL, COMM,
DEPTNO)
values (#{empno,jdbcType=DECIMAL}, #{ename,jdbcType=VARCHAR}, #{job,jdbcType=VARCHAR},
#{mgr,jdbcType=DECIMAL}, #{hiredate,jdbcType=DATE}, #{sal,jdbcType=DECIMAL}, #{comm,jdbcType=DECIMAL},
#{deptno,jdbcType=DECIMAL})
</insert>
<insert id="insertSelective" parameterType="cn.janvi.ds.db2.bean.Emp" >
insert into EMP
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="empno != null" >
EMPNO,
</if>
<if test="ename != null" >
ENAME,
</if>
<if test="job != null" >
JOB,
</if>
<if test="mgr != null" >
MGR,
</if>
<if test="hiredate != null" >
HIREDATE,
</if>
<if test="sal != null" >
SAL,
</if>
<if test="comm != null" >
COMM,
</if>
<if test="deptno != null" >
DEPTNO,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="empno != null" >
#{empno,jdbcType=DECIMAL},
</if>
<if test="ename != null" >
#{ename,jdbcType=VARCHAR},
</if>
<if test="job != null" >
#{job,jdbcType=VARCHAR},
</if>
<if test="mgr != null" >
#{mgr,jdbcType=DECIMAL},
</if>
<if test="hiredate != null" >
#{hiredate,jdbcType=DATE},
</if>
<if test="sal != null" >
#{sal,jdbcType=DECIMAL},
</if>
<if test="comm != null" >
#{comm,jdbcType=DECIMAL},
</if>
<if test="deptno != null" >
#{deptno,jdbcType=DECIMAL},
</if>
</trim>
</insert>
<select id="countByExample" parameterType="cn.janvi.ds.db2.bean.EmpExample" resultType="java.lang.Integer" >
select count(*) from EMP
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
</select>
<update id="updateByExampleSelective" parameterType="map" >
update EMP
<set >
<if test="record.empno != null" >
EMPNO = #{record.empno,jdbcType=DECIMAL},
</if>
<if test="record.ename != null" >
ENAME = #{record.ename,jdbcType=VARCHAR},
</if>
<if test="record.job != null" >
JOB = #{record.job,jdbcType=VARCHAR},
</if>
<if test="record.mgr != null" >
MGR = #{record.mgr,jdbcType=DECIMAL},
</if>
<if test="record.hiredate != null" >
HIREDATE = #{record.hiredate,jdbcType=DATE},
</if>
<if test="record.sal != null" >
SAL = #{record.sal,jdbcType=DECIMAL},
</if>
<if test="record.comm != null" >
COMM = #{record.comm,jdbcType=DECIMAL},
</if>
<if test="record.deptno != null" >
DEPTNO = #{record.deptno,jdbcType=DECIMAL},
</if>
</set>
<if test="_parameter != null" >
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>
<update id="updateByExample" parameterType="map" >
update EMP
set EMPNO = #{record.empno,jdbcType=DECIMAL},
ENAME = #{record.ename,jdbcType=VARCHAR},
JOB = #{record.job,jdbcType=VARCHAR},
MGR = #{record.mgr,jdbcType=DECIMAL},
HIREDATE = #{record.hiredate,jdbcType=DATE},
SAL = #{record.sal,jdbcType=DECIMAL},
COMM = #{record.comm,jdbcType=DECIMAL},
DEPTNO = #{record.deptno,jdbcType=DECIMAL}
<if test="_parameter != null" >
<include refid="Update_By_Example_Where_Clause" />
</if>
</update>
<update id="updateByPrimaryKeySelective" parameterType="cn.janvi.ds.db2.bean.Emp" >
update EMP
<set >
<if test="ename != null" >
ENAME = #{ename,jdbcType=VARCHAR},
</if>
<if test="job != null" >
JOB = #{job,jdbcType=VARCHAR},
</if>
<if test="mgr != null" >
MGR = #{mgr,jdbcType=DECIMAL},
</if>
<if test="hiredate != null" >
HIREDATE = #{hiredate,jdbcType=DATE},
</if>
<if test="sal != null" >
SAL = #{sal,jdbcType=DECIMAL},
</if>
<if test="comm != null" >
COMM = #{comm,jdbcType=DECIMAL},
</if>
<if test="deptno != null" >
DEPTNO = #{deptno,jdbcType=DECIMAL},
</if>
</set>
where EMPNO = #{empno,jdbcType=DECIMAL}
</update>
<update id="updateByPrimaryKey" parameterType="cn.janvi.ds.db2.bean.Emp" >
update EMP
set ENAME = #{ename,jdbcType=VARCHAR},
JOB = #{job,jdbcType=VARCHAR},
MGR = #{mgr,jdbcType=DECIMAL},
HIREDATE = #{hiredate,jdbcType=DATE},
SAL = #{sal,jdbcType=DECIMAL},
COMM = #{comm,jdbcType=DECIMAL},
DEPTNO = #{deptno,jdbcType=DECIMAL}
where EMPNO = #{empno,jdbcType=DECIMAL}
</update>
</mapper>
cn.janvi.ds.db2.EmpService 接口
package cn.janvi.ds.db2.service;
import java.util.List;
import cn.janvi.ds.db2.bean.Emp;
public interface EmpService {
List<Emp> findAll();
}
cn.janvi.ds.db2.EmpServiceImpl 实现类
package cn.janvi.ds.db2.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import cn.janvi.ds.db2.bean.Emp;
import cn.janvi.ds.db2.mapper.EmpMapper;
import cn.janvi.ds.db2.service.EmpService;
@Service
public class EmpServiceImpl implements EmpService {
@Autowired
private EmpMapper empMapper ;
@Override
public List<Emp> findAll() {
return this.empMapper.selectByExample(null);
}
}
编写测试类
package cn.janvi.test;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.alibaba.fastjson.JSON;
import com.github.pagehelper.PageInfo;
import cn.janvi.ds.Application;
import cn.janvi.ds.config.Db1Properties;
import cn.janvi.ds.config.Db2Properties;
import cn.janvi.ds.db1.bean.Items;
import cn.janvi.ds.db1.service.ItemsService;
import cn.janvi.ds.db2.service.EmpService;
@RunWith(SpringRunner.class)
@SpringBootTest(classes={Application.class})
public class TT {
/**
* 测试将 application.yml 配置文件映射到java类
*/
@Autowired
private Db1Properties db1Properties;
@Autowired
private Db2Properties db2Properties;
@Test
public void testDbProperties() {
System.out.println(JSON.toJSONString(db1Properties));
System.out.println(JSON.toJSONString(db2Properties));
}
/**
* 测试不同的数据源
*/
@Autowired
private DataSource ds1;
@Autowired
private DataSource ds2;
@Test
public void testDataSource() {
System.out.println("ds1数据源的类型:"+ds1.getClass());
System.out.println("ds2数据源的类型:"+ds2.getClass());
}
/**
* 测试使用数据源1,分页查询
*/
@Autowired
private ItemsService itemsService;
@Test
public void testPageHelper() {
PageInfo<Items> pageInfo = itemsService.findByPage(1, 1);
List<Items> list = pageInfo.getList();
System.out.println(JSON.toJSONString(list));
}
/**
* 测试事务管理
*/
@Test
public void testDynamicDs1Add() {
Items items = new Items();
items.setId(40022);
items.setName("DELL 灵越");
items.setCreatetime(new Date());
items.setDetail("LENOVO - XiaoXin 700-15ISK");
items.setPic("http://image.janvi/xx.png");
items.setPrice(4799.0F);
int result = this.itemsService.add(items);
System.out.println(result);
}
/**
* 测试使用数据源2,查询功能
*/
@Autowired
private EmpService empService ;
@Test
public void testEmpService(){
System.out.println(JSON.toJSONString(empService.findAll()));
}
}
源码下载地址(百度云)链接:http://pan.baidu.com/s/1bp2H1FL 密码:xk00