Spring+Mybatis+多数据源(MySQL+Oracle)-白红宇

Spring+Mybatis+多数据源(MySQL+Oracle)

阅读量：2389 次

发布时间：2019-05-10

本文共 17328 字，大约阅读时间需要 57 分钟。

参考了

1、多数据源相关类

1.1 数据源路由器

AbstractRoutingDataSource 是spring提供的一个多数据源抽象类。

package cn.hadron.dao.datasource;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class DataSourceRouter extends AbstractRoutingDataSource {
        // 获取数据源名称     //spring会在使用事务的地方来调用此类的determineCurrentLookupKey()方法来获取数据源的key值。     protected Object determineCurrentLookupKey() {         return HandleDataSource.getDataSource();     }}

1.2 数据源处理类

DataSourceRouter 类中通过HandleDataSource.getDataSource()获取数据源的key值。此方法应该和线程绑定。

package cn.hadron.dao.datasource;/** * 线程相关的数据源处理类 * */public class HandleDataSource {
        // 数据源名称线程池     private static final ThreadLocal
   
     holder = new ThreadLocal
    
     ();     /**      * 设置数据源      * @param datasource 数据源名称      */     public static void setDataSource(String datasource) {          holder.set(datasource);     }     /**      * 获取数据源      * @return 数据源名称      */     public static String getDataSource() {          return holder.get();     }     /**      * 清空数据源      */     public static void clearDataSource() {          holder.remove();     }}

1.3 数据源注解类

对于spring来说，注解即简单方便且可读性也高。所以，我们也通过注解在service的方法前指定所用的数据源。我们先定义自己的注解类，其中value为数据源的key值。

package cn.hadron.dao.datasource;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target({ ElementType.TYPE, ElementType.METHOD })@Retention(RetentionPolicy.RUNTIME)public @interface DataSource {    //value为数据源的key值    String value();}

1.4 切换数据源

指定注解以后，我们可以通过AOP拦截所有service方法，在方法执行之前获取方法上的注解：即数据源的key值。

package cn.hadron.dao.datasource;import java.lang.reflect.Method;import java.text.MessageFormat;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.annotation.Pointcut;import org.aspectj.lang.reflect.MethodSignature;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.context.annotation.EnableAspectJAutoProxy;import org.springframework.core.annotation.Order;import org.springframework.stereotype.Component;import org.springframework.util.StringUtils;/** * 切换数据源(不同方法调用不同数据源) */@Aspect@Component@Order(1) //请注意：这里order一定要小于tx:annotation-driven的order，即先执行DataSourceAspect切面，再执行事务切面，才能获取到最终的数据源@EnableAspectJAutoProxy(proxyTargetClass = true)public class DataSourceAspect {
       static Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);    /**     * 切入点 service包及子孙包下的所有类     */    @Pointcut("execution(* cn.hadron.service..*.*(..))")    public void aspect() {    }    /**     * 配置前置通知,使用在方法aspect()上注册的切入点     */    @Before("aspect()")    public void before(JoinPoint point) {        Class
    target = point.getTarget().getClass();        MethodSignature signature = (MethodSignature) point.getSignature();        Method method = signature.getMethod() ;        DataSource dataSource = null ;        //从类初始化        dataSource = this.getDataSource(target, method) ;        //从接口初始化        if(dataSource == null){            for (Class
    clazz : target.getInterfaces()) {                dataSource = getDataSource(clazz, method);                if(dataSource != null){                    break ;//从某个接口中一旦发现注解，不再循环                }            }        }        if(dataSource != null && !StringUtils.isEmpty(dataSource.value()) ){            HandleDataSource.setDataSource(dataSource.value());        }    }    @After("aspect()")    public void after(JoinPoint point) {        //使用完记得清空        HandleDataSource.setDataSource(null);    }    /**     * 获取方法或类的注解对象DataSource     * @param target    类class     * @param method    方法     * @return DataSource     */    public DataSource getDataSource(Class
    target, Method method){        try {            //1.优先方法注解            Class
   [] types = method.getParameterTypes();            Method m = target.getMethod(method.getName(), types);            if (m != null && m.isAnnotationPresent(DataSource.class)) {                return m.getAnnotation(DataSource.class);            }            //2.其次类注解            if (target.isAnnotationPresent(DataSource.class)) {                return target.getAnnotation(DataSource.class);            }        } catch (Exception e) {            e.printStackTrace();            logger.error(MessageFormat.format("通过注解切换数据源时发生异常[class={0},method={1}]："                    , target.getName(), method.getName()),e)  ;        }        return null ;    }}

2 多数据源配置

2.1 jdbc.properties

#============================================================================# MySQL#============================================================================jdbc.mysql.driver=com.mysql.jdbc.Driverjdbc.mysql.url=jdbc:mysql://192.168.1.160:3306/testjdbc.mysql.username=rootjdbc.mysql.password=123456#============================================================================# Oracle#============================================================================jdbc.oracle.driver=oracle.jdbc.driver.OracleDriverjdbc.oracle.url=jdbc:oracle:thin:@192.168.1.62:1521:orajdbc.oracle.username=adminjdbc.oracle.password=123456#============================================================================# common setting#============================================================================jdbc.initialSize=5jdbc.minIdle=5jdbc.maxIdle=20jdbc.maxActive=100jdbc.maxWait=100000jdbc.defaultAutoCommit=falsejdbc.removeAbandoned=truejdbc.removeAbandonedTimeout=600jdbc.testWhileIdle=truejdbc.timeBetweenEvictionRunsMillis=60000jdbc.numTestsPerEvictionRun=20jdbc.minEvictableIdleTimeMillis=300000

2.2 applicationContext.xml

3、多数据源使用

3.1 dao

（1）MySQL

package cn.hadron.dao;import java.util.List;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.Update;import cn.hadron.bean.UserBean;public interface UserDao {
       /**     * 根据登录名和密码查询用户     * @return 找到返回User对象，没有找到返回null     * */    @Select("select * from users where username = #{username} and password = #{password}")    UserBean getUser(@Param("username") String username, @Param("password") String password);    @Select("select * from users where username = #{username}")    UserBean isExist(@Param("username") String username);    /**     * 根据ID查询用户     * @param id     * @return     */    @Select("select * from users where id = #{id}")    @Results({        @Result(id=true,column="id",property="id"),        @Result(column="username",property="username"),        @Result(column="password",property="password"),        @Result(column="age",property="age")    })    UserBean getUserById(@Param("id") Integer id);    /**     * 查询所有用户     */    @Select("select * from users")    List
   
     getAllUser();    @Insert("insert into users(username,password,age) values(#{username},#{password},#{age})")    @Options(useGeneratedKeys=true,keyProperty="id")    int saveUser(UserBean user);    @Update("update users set username = #{username},password = #{password},age = #{age} where id = #{id}")    void modifyUser(UserBean user);}

（2）Oracle

package cn.hadron.dao;import cn.hadron.bean.AttachmentBean;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import java.util.List;public interface AttachmentDao {    @Select("select ID,SID,SOURCEENTITY,TYPE,NAME,CONTENTSIZE,FILETYPE,CONTENT from attachment")    @Results({            @Result(column="ID",property="id"),            @Result(column="SID",property="sid"),            @Result(column="SOURCEENTITY",property="sourceEntity"),            @Result(column="TYPE",property="type"),            @Result(column="NAME",property="name"),            @Result(column="CONTENTSIZE",property="contentSize"),            @Result(column="FILETYPE",property="fileType"),            @Result(column="CONTENT",property="content"),    })    List
   
     getAttachmentList();}

3.2 service

（1）使用MySQL数据源

package cn.hadron.service.impl;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 cn.hadron.dao.UserDao;import cn.hadron.dao.datasource.DataSource;import cn.hadron.bean.UserBean;import cn.hadron.service.UserService;/** * User服务层接口实现类 * @Service("userService")用于将当前类注释为一个Spring的bean，名为userService * */@Transactional(propagation=Propagation.REQUIRED,isolation=Isolation.DEFAULT)@Service("userService")@DataSource("MYSQL")public class UserServiceImpl implements UserService {
       /**     * 自动注入UserDao     * */    @Autowired    private UserDao userDao;    /**     * UserService接口login方法实现     * @see { UserService }     * */    @Transactional(readOnly=true)    @Override    public UserBean login(String username, String password) {        System.out.println("UserServiceImpl.login:"+username+","+password);        return userDao.getUser(username, password);    }    @Transactional(readOnly=true)    @Override    public boolean isExist(String username) {        return null!=userDao.isExist(username);    }    @Override    public boolean addUser(UserBean user) {        return 1==userDao.saveUser(user);    }}

（2）使用Oracle数据源

package cn.hadron.service.impl;import cn.hadron.bean.AttachmentBean;import cn.hadron.dao.AttachmentDao;import cn.hadron.dao.datasource.DataSource;import cn.hadron.service.AttachmentService;import oracle.sql.BLOB;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 java.io.*;import java.sql.SQLException;import java.util.List;@Transactional(propagation=Propagation.REQUIRED,isolation=Isolation.DEFAULT)@Service("attachmentService")@DataSource("ORACLE")public class AttachmentServiceImpl implements AttachmentService {    @Autowired    private AttachmentDao attachmentDao;    @Override    public List
   
     getAttachmentList() {        return attachmentDao.getAttachmentList();    }}

3.3 controller

package cn.hadron.controller;import javax.servlet.http.HttpSession;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import cn.hadron.bean.UserBean;import cn.hadron.service.UserService;/** * 处理用户请求控制器 * */@Controller@RequestMapping(value = "/user")public class UserController {
       /**     * 自动注入UserService     */    @Autowired    @Qualifier("userService")    private UserService userService;    /**     * 处理/login请求     */    @RequestMapping(value="/login.do")    public String login(String username,String password,HttpSession session){        System.out.println("###login.do");        UserBean user=userService.login(username,password);        if(user!=null){
  //登录成功            // 登录成功，将user对象设置到HttpSession作用范围域            session.setAttribute("user", user);            System.out.println("登录成功！");            /**             * sendRedirect对浏览器做出的响应是重新发出对另外一个URL的访问请求，             * sendRedirect的调用者与被调用者使用各自的request和response对象，属于两个独立的访问请求和响应过程             */            return "redirect:/main.do";        }else{            //登录失败            System.out.println("登录失败！");            session.setAttribute("tip","登录失败！");            /**             * forward在服务器端内部将请求转发给另外一个资源，浏览器只知道发出了请求并得到了响应结果             * forward的调用者与被调用者之间共享相同的request对象和response对象，他们属于同一个访问请求和响应过程；             */            return "forward:/login";        }    }}

package cn.hadron.controller;import cn.hadron.bean.AttachmentBean;import cn.hadron.service.AttachmentService;import cn.hadron.service.UserService;import oracle.sql.BLOB;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import java.sql.SQLException;import java.util.List;@Controller@RequestMapping(value = "/a")public class AttachmentController {    @Autowired    @Qualifier("attachmentService")    private AttachmentService attachmentService;    @RequestMapping("testOracle.do")    public String testOracle() throws SQLException {        attachmentService.parseAttachmentList();        List
   
     list= attachmentService.getAttachmentList();        System.out.println("共有附件数："+list.size());        return "result";    }}

4、pom.xml


   
     
    
     4.0.0
      
    
     com.cntaiping.tpa
      
    
     datasource
      
    
     1.0-SNAPSHOT
      
    
     war
      
    
     datasource Maven Webapp
      
      
    
     http://www.example.com
      
        
     
      UTF-8
         
     
      1.8
         
     
      1.8
         
     
      1.8
         
     
      1.8
         
     
      4.3.16.RELEASE
         
     
      3.4.5
         
     
      1.3.1
       
      
        
           
      
       com.alibaba
            
      
       druid
            
      
       1.1.10
          
         
           
      
       org.aspectj
            
      
       aspectjweaver
            
      
       1.8.13
          
         
           
      
       org.mybatis
            
      
       mybatis
            
      
       3.2.4
          
         
           
      
       org.mybatis
            
      
       mybatis-spring
            
      
       1.2.2
          
         
           
      
       log4j
            
      
       log4j
            
      
       1.2.17
          
         
         
           
      
       org.springframework
            
      
       spring-core
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-beans
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-aop
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-context
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-context-support
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-jdbc
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-web
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-webmvc
            
      
       ${spring.version}
          
         
           
      
       org.springframework
            
      
       spring-tx
            
      
       ${spring.version}
          
         
           
      
       org.slf4j
            
      
       slf4j-log4j12
            
      
       1.7.6
          
         
         
           
      
       mysql
            
      
       mysql-connector-java
            
      
       5.1.5
          
         
         
           
      
       com.oracle
            
      
       ojdbc6
            
      
       11.2.0.3
            
      
       jar
          
         
         
           
      
       javax.servlet
            
      
       javax.servlet-api
            
      
       3.1.0
            
      
       provided
          
         
           
      
       junit
            
      
       junit
            
      
       4.9
            
      
       test
          
       
      
        
     
      datasource
         
     
            
              
                 
        
         maven-clean-plugin
                  
        
         3.0.0
                
               
               
                 
        
         maven-resources-plugin
                  
        
         3.0.2
                
               
                 
        
         maven-compiler-plugin
                  
        
         3.7.0
                
               
                 
        
         maven-surefire-plugin
                  
        
         2.20.1
                
               
                 
        
         maven-war-plugin
                  
        
         3.2.0
                
               
                 
        
         maven-install-plugin
                  
        
         2.5.2
                
               
                 
        
         maven-deploy-plugin
                  
        
         2.8.2