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

程裕强

发布于 2022-05-06 20:43:08

1.1K0

发布于 2022-05-06 20:43:08

文章被收录于专栏：大数据学习笔记大数据学习笔记

参考了https://www.jianshu.com/p/fddcc1a6b2d8

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<String> holder = new ThreadLocal<String>();

     /**
      * 设置数据源
      * @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.Driver
jdbc.mysql.url=jdbc:mysql://192.168.1.160:3306/test
jdbc.mysql.username=root
jdbc.mysql.password=123456

#============================================================================
# Oracle
#============================================================================
jdbc.oracle.driver=oracle.jdbc.driver.OracleDriver
jdbc.oracle.url=jdbc:oracle:thin:@192.168.1.62:1521:ora
jdbc.oracle.username=admin
jdbc.oracle.password=123456

#============================================================================
# common setting
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000

2.2 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://mybatis.org/schema/mybatis-spring 
        http://mybatis.org/schema/mybatis-spring.xsd">

    <context:property-placeholder location="classpath:jdbc.properties" />

    <!-- 2、配置数据库连接池 -->
    <bean id="mysql" class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <!--配置连接池属性 -->
        <property name="driverClassName" value="${jdbc.mysql.driver}" />
        <property name="url" value="${jdbc.mysql.url}" />
        <property name="username" value="${jdbc.mysql.username}" />
        <property name="password" value="${jdbc.mysql.password}" />
    </bean>

    <bean id="oracle" class="com.alibaba.druid.pool.DruidDataSource"
        init-method="init" destroy-method="close">
        <!--配置连接池属性 -->
        <property name="driverClassName" value="${jdbc.oracle.driver}" />
        <property name="url" value="${jdbc.oracle.url}" />
        <property name="username" value="${jdbc.oracle.username}" />
        <property name="password" value="${jdbc.oracle.password}" />
    </bean>

    <bean id="dataSource" class="cn.hadron.dao.datasource.DataSourceRouter">
        <description>多数据源路由</description>
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <entry key="MYSQL" value-ref="mysql" />
                <entry key="ORACLE" value-ref="oracle" />
            </map>
        </property>
        <!-- 默认数据源，如果未指定数据源 或者指定的数据源不存在的话 默认使用这个数据源 -->
        <property name="defaultTargetDataSource" ref="mysql" />
    </bean>
        <!--3、配置SqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
    </bean>
     <!-- mybatis:scan会扫描cn.hadron.dao包里的所有接口当作Spring的bean配置，之后可以进行依赖注入-->  
    <mybatis:scan base-package="cn.hadron.dao"/>   
    <!-- 扫描cn.hadron包下面的java文件，有Spring的相关注解的类，则把这些类注册为Spring的bean -->
    <context:component-scan base-package="cn.hadron"/>
    <!-- 配置事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <!-- 注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
    </bean>
    <!-- 配置基于注解的声明式事务 -->
    <tx:annotation-driven transaction-manager="transactionManager"
         proxy-target-class="true" order="2" />
    <!-- AOP自动代理功能 -->
    <aop:aspectj-autoproxy proxy-target-class="true" />
</beans>

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<UserBean> 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<AttachmentBean> 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<AttachmentBean> 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<AttachmentBean> list= attachmentService.getAttachmentList();
        System.out.println("共有附件数："+list.size());
        return "result";
    }
}

4、pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<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.cntaiping.tpa</groupId>
  <artifactId>datasource</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>datasource Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <spring.version>4.3.16.RELEASE</spring.version>
    <mybatis.version>3.4.5</mybatis.version>
    <mybatis-spring.version>1.3.1</mybatis-spring.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.13</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.4</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.2</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <!--spring -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context-support</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.5</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 -->
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc6</artifactId>
      <version>11.2.0.3</version>
      <type>jar</type>
    </dependency>
    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.9</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <finalName>datasource</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.7.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.20.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018-06-24，如有侵权请联系 cloudcommunity@tencent.com 删除

spring