【springmvc+hibernate】动态切换数据源实现多租户数据分离

谁借莪１个温暖的怀抱￠ 2022-11-11 05:57 136阅读 0赞

## 需求 ##

我们为客户开发了移动端应用，基于数据安全性的考虑，需要将业务数据按照数据库隔离的方式进行设计。

## 数据库架构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXhpaXBob25l_size_16_color_FFFFFF_t_70]  
数据库设计说明

1.  master数据库用于基础数据的管理和综合业务查询  
    – 基础数据包括：用户、角色、权限等  
    —主要负责，你是谁，你从哪来，你要去哪的问题  
    –综合业务查询  
    —主要是通过视图的方式，将多个业务数据库中的数据进行整合
2.  hx和rl为各自的业务数据库

## 开发流程 ##

![在这里插入图片描述][20210319184016542.png]

## 解决问题 ##

**1. aop切片不生效，配置后无法动态的切换数据库**  
其原因在于系统中使用了spring和springmvc的配置文件  
按照加载顺序，spring的配置文件先加载，然后是springmvc的配置文件  
在包扫描时，spring中不能将controller包含进去，而springmvc需要将controller包含进去

spring的包扫描配置

<context:component-scan base-package="com.pms">
    		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
    	</context:component-scan>

springmvc的包扫描配置

<context:component-scan base-package="com.pms"
    		use-default-filters="false">
    		<context:include-filter type="annotation"
    			expression="org.springframework.stereotype.Controller" />
    	</context:component-scan>

1.  网上大部分比较全的xml配置，都是基于mybatis的，所以在改成hibernate配置文件时候查了好多资料，下面提供完整的hibernate.xml配置，里面的参数就自己写吧，主要也就是url

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:aop="http://www.springframework.org/schema/aop"
           xmlns:c="http://www.springframework.org/schema/c"
           xmlns:cache="http://www.springframework.org/schema/cache"
           xmlns:context="http://www.springframework.org/schema/context"
           xmlns:jdbc="http://www.springframework.org/schema/jdbc"
           xmlns:jee="http://www.springframework.org/schema/jee"
           xmlns:lang="http://www.springframework.org/schema/lang"
           xmlns:p="http://www.springframework.org/schema/p"
           xmlns:task="http://www.springframework.org/schema/task"
           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/aop http://www.springframework.org/schema/aop/spring-aop.xsd
    		http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd
    		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
    		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
    		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
    		http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
    		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
    		http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd
    		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
    		"
            default-autowire="byName">
        <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="${jdbc_url}"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            <property name="validationQuery" value="select 1"></property>
            <property name="testOnBorrow" value="true"></property>
        </bean>
    
        <bean id="childDataSource1" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="${jdbc_url1}"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            <property name="validationQuery" value="select 1"></property>
            <property name="testOnBorrow" value="true"></property>
        </bean>
    
        <bean id="childDataSource2" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="${jdbc_url2}"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            <property name="validationQuery" value="select 1"></property>
            <property name="testOnBorrow" value="true"></property>
        </bean>
        <bean id="dataSourceSwitcher" class="com.pms.multiDS.util.DataSourceSwitcher">
            <property name="defaultTargetDataSource" ref="masterDataSource"></property>
            <property name="targetDataSources">
                <map>
                    <entry key="master" value-ref="masterDataSource"/>
                    <entry key="haixin" value-ref="childDataSource1"/>
                    <entry key="rili" value-ref="childDataSource2"/>
                </map>
            </property>
        </bean>
    
        
        <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
            <property name="dataSource" ref="dataSourceSwitcher"></property>
            <property name="hibernateProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
                    <prop key="hibernate.show_sql">false</prop>
                </props>
            </property>
            
            <property name="packagesToScan">
                <list>
                    <value>com.pms.model.*</value>
                </list>
            </property>
        </bean>
    
        
        <tx:annotation-driven transaction-manager="transactionManager"/>
        <bean id="transactionManager"
              class="org.springframework.orm.hibernate5.HibernateTransactionManager">
            <property name="dataSource" ref="dataSourceSwitcher" />
        </bean>
    
        
        <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                <tx:method name="save*" propagation="REQUIRED" />
                <tx:method name="delete*" propagation="REQUIRED" />
                <tx:method name="check*" propagation="REQUIRED" />
                <tx:method name="query*" propagation="REQUIRED" read-only="true" />
            </tx:attributes>
        </tx:advice>
    
        
        <bean id="multiDataSourceAsp" class="com.pms.multiDS.aop.DataSourceAsp">
            <property name="mDataSourceSwitcher" ref="dataSourceSwitcher"/>
        </bean>
    </beans>

## 参考资料 ##

[spring 注解aop不生效][spring _aop]  
[SSH整合的Property ‘sessionFactory’ is required问题][SSH_Property _sessionFactory_ is required]  
[spring 动态切换数据源 多数据库][spring _]（主流程就是根据这位大佬写的博客做的）  
[大佬提供的源代码][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXhpaXBob25l_size_16_color_FFFFFF_t_70]: /images/20221022/98176559c82c475bae3dfc54a1b5f06f.png
[20210319184016542.png]: /images/20221022/06e8189020814dd39544cd9b63d08d00.png
[spring _aop]: https://blog.csdn.net/zhaoyachao123/article/details/73826278
[SSH_Property _sessionFactory_ is required]: https://blog.csdn.net/Aweng_ws/article/details/79639591
[spring _]: https://blog.csdn.net/laojiaqi/article/details/78964862
[Link 1]: https://github.com/lywhlao/multiDS