spring-boot-dyna-datasource(spring-boot动态数据源)

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-multi-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><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</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.App</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 configurationPUBLIC "-//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.App）

package cn.janvi.ds;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.transaction.annotation.EnableTransactionManagement;@EnableTransactionManagement //开启事务管理；如果mybatis中service实现类中加入事务注解，需要此处添加该注解@SpringBootApplicationpublic class App {public static void main(String[] args) {SpringApplication.run(App.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 方法......        }  

数据源配置

package cn.janvi.ds.config;import java.util.HashMap;import java.util.Map;import java.util.Properties;import javax.sql.DataSource;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.SqlSessionFactoryBean;import org.mybatis.spring.annotation.MapperScan;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;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 org.springframework.context.annotation.Primary;import org.springframework.core.env.Environment;import org.springframework.core.io.ClassPathResource;import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.pool.DruidDataSourceFactory;/** * springboot集成mybatis的基本入口 1）创建数据源(如果采用的是默认的tomcat-jdbc数据源，则不需要) * 2）创建SqlSessionFactory 3）配置事务管理器，除非需要使用事务，否则不用配置 *  * 通过读取application.properties文件生成两个数据源（myTestDbDataSource、myTestDb2DataSource）使用以上生成的两个数据源构造动态数据源dataSource@Primary：指定在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@Autowire注解报错（一般用于多数据源的情况下）@Qualifier：指定名称的注入，当一个接口有多个实现类的时候使用（在本例中，有两个DataSource类型的实例，需要指定名称注入）@Bean：生成的bean实例的名称是方法名（例如上边的@Qualifier注解中使用的名称是前边两个数据源的方法名，而这两个数据源也是使用@Bean注解进行注入的）通过动态数据源构造SqlSessionFactory和事务管理器（如果不需要事务，后者可以去掉） *  *  */@Configuration@MapperScan(basePackages = "cn.janvi.ds.mapper")public class MyBatisConfig {      @Autowired    private Db1Properties db1Properties;    /**     * 数据源1，使用阿里巴巴的数据源com.alibaba.druid.pool.DruidDataSource     * @return     */    @Bean    public DataSource ds1() {        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    @ConfigurationProperties(prefix = "spring.datasource.db2") // application.yml中对应属性的前缀    public DataSource ds2() {        return DataSourceBuilder.create().build();    }    /**     * @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错     * @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入（例如有多个DataSource类型的实例）     */    @Bean    @Primary    public DynamicDataSource dataSource(@Qualifier("ds1") DataSource ds1,    @Qualifier("ds2") DataSource ds2) {        Map<Object, Object> targetDataSources = new HashMap<>();        targetDataSources.put("ds1", ds1);        targetDataSources.put("ds2", ds2);        DynamicDataSource dataSource = new DynamicDataSource();        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法        dataSource.setDefaultTargetDataSource(ds1);// 默认的datasource设置为ds1        return dataSource;    }   /**    * 根据数据源创建SqlSessionFactory    * @param ds1    * @param ds2    * @return    * @throws Exception    */    @Bean    public SqlSessionFactory sqlSessionFactory(@Qualifier("ds1") DataSource ds1,            @Qualifier("ds2") DataSource ds2) throws Exception{SqlSessionFactoryBean fb = new SqlSessionFactoryBean();fb.setDataSource(this.dataSource(ds1, ds2));// 下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话（只用注解就可以搞定），则不加        //fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));// 指定基包        //fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapperLocations")));fb.setConfigLocation(new ClassPathResource("mybatis/SqlMapConfig1.xml"));// 引入mybatis的配置文件，分页插件return fb.getObject();    }        /**     * 配置事务管理器     */    @Bean    public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {        return new DataSourceTransactionManager(dataSource);    }}

线程本地化用于保存当前线程使用的数据源名

package cn.janvi.ds.config;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/** *  * 定义一个ContextHolder, 用于保存当前线程使用的数据源名 * Company:www.linewell.com * @author panjianwei * @date 2017年8月29日上午7:12:12 */public class DataSourceContextHolder {public static final Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);/** * 默认数据源 */public static final String DEFAULT_DS = "ds1";private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();// 设置数据源名public static void setDB(String dbType) {log.debug("切换到{}数据源", dbType);contextHolder.set(dbType);}// 获取数据源名public static String getDB() {return (contextHolder.get());}// 清除数据源名public static void clearDB() {contextHolder.remove();}}

动态数据源

package cn.janvi.ds.config;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/** * 自定义一个javax.sql.DataSource接口的实现，这里只需要继承Spring为我们预先实现好的父类AbstractRoutingDataSource即可 * Company:www.linewell.com * @author panjianwei * @date 2017年8月29日上午7:13:27 */public class DynamicDataSource extends AbstractRoutingDataSource {private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);@Overrideprotected Object determineCurrentLookupKey() {log.debug("数据源为{}", DataSourceContextHolder.getDB());return DataSourceContextHolder.getDB();}}

自定义注解用于切换数据源（cn.janvi.ds.config.Ds）

package cn.janvi.ds.config;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** *  * 自定义注释@DS用于在编码时指定方法使用哪个数据源 * Company:www.linewell.com * @author panjianwei * @date 2017年8月29日上午7:20:04 */@Retention(RetentionPolicy.RUNTIME)@Target({ ElementType.METHOD })public @interface Ds {String value() default "ds1";}

使用AOP进行拦截切换数据源（cn.janvi.ds.config.DynamicDataSourceAspect）

package cn.janvi.ds.config;import java.lang.reflect.Method;import org.aspectj.lang.JoinPoint;import org.aspectj.lang.annotation.After;import org.aspectj.lang.annotation.Aspect;import org.aspectj.lang.annotation.Before;import org.aspectj.lang.reflect.MethodSignature;import org.springframework.stereotype.Component;/** *  * 编写AOP切面，实现切换逻辑： * Company:www.linewell.com * @author panjianwei * @date 2017年8月29日上午7:24:09 */@Aspect@Componentpublic class DynamicDataSourceAspect {@SuppressWarnings("rawtypes")@Before("@annotation(Ds)")public void beforeSwitchDS(JoinPoint point) {// 获得当前访问的classClass<?> className = point.getTarget().getClass();// 获得访问的方法名String methodName = point.getSignature().getName();// 得到方法的参数的类型Class[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes();// 设置默认数据源String dataSource = DataSourceContextHolder.DEFAULT_DS;try {// 得到访问的方法对象Method method = className.getMethod(methodName, argClass);// 判断是否存在@DS注解if (method.isAnnotationPresent(Ds.class)) {Ds annotation = method.getAnnotation(Ds.class);// 取出注解中的数据源名dataSource = annotation.value();}} catch (Exception e) {e.printStackTrace();}// 切换数据源DataSourceContextHolder.setDB(dataSource);}@After("@annotation(Ds)")public void afterSwitchDS(JoinPoint point) {DataSourceContextHolder.clearDB();}}

Mybatis 的那一套开发结构 Bean、Mapper、Service

Bean（使用mybatis的逆向工程 ）

package cn.janvi.ds.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 AND GETTER}

package cn.janvi.ds.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 AND GETTER
}

Mapper（使用mybatis的逆向工程生成）

package cn.janvi.ds.mapper;import java.util.List;import org.apache.ibatis.annotations.Param;import cn.janvi.ds.bean.Emp;import cn.janvi.ds.bean.EmpExample;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);}

package cn.janvi.ds.mapper;import java.util.List;import org.apache.ibatis.annotations.Param;import cn.janvi.ds.bean.Items;import cn.janvi.ds.bean.ItemsExample;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);}

Mapper对应的.xml文件

EmpMapper.xml

<?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.mapper.EmpMapper" >  <resultMap id="BaseResultMap" type="cn.janvi.ds.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.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.bean.EmpExample" >    delete from EMP    <if test="_parameter != null" >      <include refid="Example_Where_Clause" />    </if>  </delete>  <insert id="insert" parameterType="cn.janvi.ds.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.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.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.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.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>

ItemsMapper.xml

<?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.mapper.ItemsMapper" >  <resultMap id="BaseResultMap" type="cn.janvi.ds.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.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.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.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.bean.ItemsExample" >    delete from items    <if test="_parameter != null" >      <include refid="Example_Where_Clause" />    </if>  </delete>  <insert id="insert" parameterType="cn.janvi.ds.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.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.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.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.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.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>

service 接口

package cn.janvi.ds.service;import java.util.List;import cn.janvi.ds.bean.Emp;public interface EmpService {List<Emp> findAll();}

package cn.janvi.ds.service;import java.util.List;import com.github.pagehelper.PageInfo;import cn.janvi.ds.bean.Items;public interface ItemsService {List<Items> findAll();PageInfo<Items> findByPage(int page,int rows);int add(Items items);}

sevice接口实现类

package cn.janvi.ds.service.impl;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import cn.janvi.ds.bean.Emp;import cn.janvi.ds.config.Ds;import cn.janvi.ds.mapper.EmpMapper;import cn.janvi.ds.service.EmpService;@Servicepublic class EmpServiceImpl implements EmpService {@Autowiredprivate EmpMapper empMapper;/** * 使用@Ds注解，指定使用的数据源2 */@Ds("ds2")@Overridepublic List<Emp> findAll() {return this.empMapper.selectByExample(null);}}

package cn.janvi.ds.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.bean.Items;import cn.janvi.ds.mapper.ItemsMapper;import cn.janvi.ds.service.ItemsService;@Servicepublic class ItemsServiceImpl implements ItemsService {@Autowiredprivate ItemsMapper itemsMapper;@Overridepublic List<Items> findAll() {return this.itemsMapper.selectByExample(null);}        // 没有使用注解则说明此方法使用的是默认数据源1@Overridepublic 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(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)@Overridepublic int add(Items items) {// 模拟出错int a = 1/0;return this.itemsMapper.insert(items);}}

编写测试类

package cn.janvi.test;import java.sql.SQLException;import java.util.Date;import java.util.List;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.App;import cn.janvi.ds.bean.Emp;import cn.janvi.ds.bean.Items;import cn.janvi.ds.service.EmpService;import cn.janvi.ds.service.ItemsService;@RunWith(SpringRunner.class)@SpringBootTest(classes={App.class})public class TT {@Autowiredprivate ItemsService itemsService;/** * 测试数据源1 - 查询所有Items * @throws SQLException */@Testpublic void testDynamicDs1() throws SQLException {List<Items> findAll = this.itemsService.findAll();System.out.println(JSON.toJSONString(findAll));}/** * 测试数据源1 - 分页查询Items * @throws SQLException */@Testpublic void testDynamicDs1Page() throws SQLException {PageInfo<Items> findByPage = this.itemsService.findByPage(1, 2);System.out.println(JSON.toJSONString(findByPage.getList()));}@Testpublic void testDynamicDs1Add() {Items items = new Items();items.setId(4001);items.setName("联想小新");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);}@Autowiredprivate EmpService empService;/** * 测试数据源2 - 查询所有Emp * @throws SQLException */@Testpublic void testDynamicDs2(){List<Emp> findAll = this.empService.findAll();System.out.println(JSON.toJSONString(findAll));}}

数据源1使用mysql数据库，创建脚本：

CREATE TABLE `items` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(32) NOT NULL COMMENT '商品名称',  `price` float(10,1) NOT NULL COMMENT '商品定价',  `detail` text COMMENT '商品描述',  `pic` varchar(64) default NULL COMMENT '商品图片',  `createtime` datetime NOT NULL COMMENT '生产日期',  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

数据源2使用oracle默认提供的scott账户

源码下载地址（百度云）链接：http://pan.baidu.com/s/1hs9YQva 密码：93rz