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

最新推荐文章于 2024-03-22 06:00:00 发布

锡塔

最新推荐文章于 2024-03-22 06:00:00 发布

阅读量2k

点赞数

分类专栏： spring-boot 文章标签： spring datasource spring-boot java ee mybatis

本文链接：https://blog.csdn.net/j_a_n_v_i/article/details/77764826

版权

spring-boot 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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 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.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实现类中加入事务注解，需要此处添加该注解
@SpringBootApplication
public 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);

	@Override
	protected 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
@Component
public class DynamicDataSourceAspect {

	@SuppressWarnings("rawtypes")
	@Before("@annotation(Ds)")
	public void beforeSwitchDS(JoinPoint point) {

		// 获得当前访问的class
		Class<?> 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;

@Service
public class EmpServiceImpl implements EmpService {

	@Autowired
	private EmpMapper empMapper;
	
	/**
	 * 使用@Ds注解，指定使用的数据源2
	 */
	@Ds("ds2")
	@Override
	public 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;

@Service
public class ItemsServiceImpl implements ItemsService {

	@Autowired
	private ItemsMapper itemsMapper;
	
	@Override
	public List<Items> findAll() {
		return this.itemsMapper.selectByExample(null);
	}
        // 没有使用注解则说明此方法使用的是默认数据源1
	@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(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);
	}

}

编写测试类

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 {


	@Autowired
	private ItemsService itemsService;

	/**
	 * 测试数据源1 - 查询所有Items
	 * @throws SQLException
	 */
	@Test
	public void testDynamicDs1() throws SQLException {
		List<Items> findAll = this.itemsService.findAll();
		
		System.out.println(JSON.toJSONString(findAll));
	}
	
	/**
	 * 测试数据源1 - 分页查询Items
	 * @throws SQLException
	 */
	@Test
	public void testDynamicDs1Page() throws SQLException {
		PageInfo<Items> findByPage = this.itemsService.findByPage(1, 2);
		
		System.out.println(JSON.toJSONString(findByPage.getList()));
	}
	
	
	@Test
	public 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);
	}
	
	
	@Autowired
	private EmpService empService;
	
	
	/**
	 * 测试数据源2 - 查询所有Emp
	 * @throws SQLException
	 */
	@Test
	public 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

锡塔

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
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
复制链接

扫一扫