如何使用spring配合mybatis配置多个数据源并应用?

Java基础

浏览数:266

2019-6-8

  使用多数据源的场景应该是很多的,如操作同一台服务器上不同的数据库,或者多地机器上的相同或不相同数据库。

  虽然涉及到不同数据库时,我们也许可以通过跨库操作的方式,如 other.user 使用同一数据源来操作数据库,但是,这样明显使得应用很难扩展,单数据库将无法拆离。使用多数据源操作则可以解决这个问题。

  在spring中怎样使用多数据源?本文通过实践方式,让我配置多数据源有个参考。(不得不说,java中很大的一个难点就在于配置环境)

  从入口处更改,web.xml中添加引用spring配置文件:

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" version="3.0">
  <display-name>c</display-name>
  <filter>
    <filter-name>characterEncodingFilter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
      <param-name>encoding</param-name>
      <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>forceEncoding</param-name>
      <param-value>true</param-value>
    </init-param>
  </filter>
  <filter-mapping>
    <filter-name>characterEncodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>
  <servlet>
    <servlet-name>dispatcherServlet</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:applicationContext.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
   <!-- URL重写 -->
    <filter>
        <filter-name>UrlRewriteFilter</filter-name>
        <filter-class>org.tuckey.web.filters.urlrewrite.UrlRewriteFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>UrlRewriteFilter</filter-name>
        <url-pattern>/*</url-pattern>
        <dispatcher>REQUEST</dispatcher>
        <dispatcher>FORWARD</dispatcher>
    </filter-mapping>
    
  <servlet-mapping>
    <servlet-name>dispatcherServlet</servlet-name>
    <url-pattern>/api/*</url-pattern>
  </servlet-mapping>
  
  <display-name>c</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
  </welcome-file-list>
    
  <filter> 
  <filter-name>JAMonFilter</filter-name>  
  <filter-class>com.xx.core.web.filter.PageMonFilter</filter-class> 
  <init-param>
  <param-name>flag</param-name>
  <param-value>true</param-value>
  </init-param>
  </filter> 
  <filter-mapping> 
  <filter-name>JAMonFilter</filter-name> 
  <url-pattern>/*</url-pattern> 
  </filter-mapping>
  <servlet>
   <servlet-name>exceptions</servlet-name> 
   <jsp-file>/jamon/exceptions.jsp</jsp-file> 
   </servlet> 
   <servlet> 
</web-app>

2. 在application.xml中添加多数据源配置:

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

    <bean class="com.xx.c.common.utils.SpringContextsUtil" />
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
        destroy-method="close">
        <property name="url" value="${jdbc.url}" />
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="maxActive" value="${pool.maxPoolSize}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <!-- 超过时间限制是否回收 -->
        <property name="removeAbandoned" value="true" />
        <!-- 超时时间;单位为秒。180秒=3分钟 -->
        <property name="removeAbandonedTimeout" value="${pool.removeAbandonedTimeout}" />
        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="${pool.maxWait}" />
        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="${pool.timeBetweenEvictionRunsMillis}" />
        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="${pool.minEvictableIdleTimeMillis}" />
        <property name="validationQuery" value="${pool.validationQuery} " />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
    </bean>
    <bean id="dataSource_c" class="com.alibaba.druid.pool.DruidDataSource"
        destroy-method="close">
        <property name="url" value="${jdbc2.url}" />
        <property name="driverClassName" value="${jdbc2.driver}" />
        <property name="maxActive" value="${pool2.maxPoolSize}" />
        <property name="username" value="${jdbc2.username}" />
        <property name="password" value="${jdbc2.password}" />
        <property name="removeAbandoned" value="true" />
        <property name="removeAbandonedTimeout" value="${pool2.removeAbandonedTimeout}" />
        <property name="maxWait" value="${pool2.maxWait}" />
        <property name="timeBetweenEvictionRunsMillis" value="${pool2.timeBetweenEvictionRunsMillis}" />
        <property name="minEvictableIdleTimeMillis" value="${pool2.minEvictableIdleTimeMillis}" />
        <property name="validationQuery" value="${pool2.validationQuery} " />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />
    </bean>
    <context:component-scan base-package="com.xx.c">
        <context:exclude-filter type="annotation"
            expression="org.springframework.stereotype.Controller" />
    </context:component-scan>
    <context:component-scan base-package="com.xx.c.pojo.config" />
    <bean class="com.xx.framework.web.v1_0_0.ServerController" />

    <!-- spring的属性加载器,加载properties文件中的属性 -->
    <bean class="com.xx.zkc.property.PropertyPlaceholderConfigurer">
        <property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
        <property name="ignoreResourceNotFound" value="true" />
        <property name="locations">
            <list>
                <value>classpath*:/spring/conf.properties</value>
            </list>
        </property>
    </bean>
    
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
    </bean>
    <bean id="sqlSessionFactory_c" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource_c" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
    </bean>
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"
        scope="prototype">
        <constructor-arg index="0" ref="sqlSessionFactory" />
    </bean>
    <bean id="sqlSession_c" class="org.mybatis.spring.SqlSessionTemplate"
        scope="prototype">
        <constructor-arg index="0" ref="sqlSessionFactory_c" />
    </bean>


    <mongo:mongo id="mongo" replica-set="${mongodb.url}">
        <mongo:options connections-per-host="${mongo.connections.per.host}"
            threads-allowed-to-block-for-connection-multiplier="${mongo.threads.allowed.to.block.for.connection.multiplier}"
            connect-timeout="${mongo.connect.timeout}" max-wait-time="${mongo.max.wait.time}"
            auto-connect-retry="${mongo.auto.connect.retry}" socket-keep-alive="${mongo.socket.keep.alive}"
            socket-timeout="${mongo.socket.timeout}" slave-ok="${mongo.slave.ok}"
            write-number="${mongo.write.number}" write-timeout="${mongo.write.timeout}"
            write-fsync="${mongo.write.fsync}" />
    </mongo:mongo>
    <bean id="mappingContext"
        class="org.springframework.data.mongodb.core.mapping.MongoMappingContext" />
    <bean id="defaultMongoTypeMapper"
        class="org.springframework.data.mongodb.core.convert.DefaultMongoTypeMapper">
        <constructor-arg name="typeKey">
            <null />
        </constructor-arg>
    </bean>
    <bean id="mongoDbFactory"
        class="org.springframework.data.mongodb.core.SimpleMongoDbFactory">
        <constructor-arg ref="mongo" />
        <constructor-arg name="databaseName" value="${mongodb.databaseName}" />
    </bean>

    <bean id="mappingMongoConverter"
        class="org.springframework.data.mongodb.core.convert.MappingMongoConverter">
        <constructor-arg name="mappingContext" ref="mappingContext" />
        <property name="typeMapper" ref="defaultMongoTypeMapper" />
        <constructor-arg name="mongoDbFactory" ref="mongoDbFactory" />
    </bean>

    <bean id="mongodbReadPreference" class="com.mongodb.ReadPreference"
        factory-method="secondaryPreferred" />
    <bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
        <property name="readPreference" ref="mongodbReadPreference" />
        <constructor-arg name="mongoConverter" ref="mappingMongoConverter" />
        <constructor-arg name="mongoDbFactory" ref="mongoDbFactory" />
    </bean>

    <!-- 事务 控制 begin -->
    <bean name="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" />
    <!-- 事务 控制 end -->

    <!-- action begin -->
    <bean name='requestMappingHandlerMapping'
        class='com.xx.core.web.spring.annotation.PackageURLRequestMappingHandlerMapping'>
        <property name='packageBase' value='com.xx.c.action'></property>
        <property name="interceptors">
            <list>
                <bean class="com.xx.core.web.spring.interceptor.SessionInterceptor">
                    <property name="on" value="${system.check.permission}" />
                    <property name="sessionTimeout" value="${system.timeout}" />
                    <property name="userSessionDao" ref="userSessionDao" />
                    <property name="systemDao" ref="systemDao" />
                </bean>
            </list>
        </property>
    </bean>

    <!-- dao -->
    <import resource="dao.xml" />

    <!-- service -->
    <import resource="service.xml" />

    <!-- activemq -->
    <import resource="applicationContext-activemq.xml" />

    <!-- redis -->
    <import resource="applicationContext-redis.xml" />

    <!-- dubbo -->
    <import resource="applicationContext-dubbo.xml" />
    <import resource="applicationContext-dubbo-consumer.xml" />

    <import resource="classpath:spring/xx-c-kafka-consumer.xml" />
    
    <import resource="applicationContext-rabbitmq-producer.xml"/>
    
    <import resource="applicationContext-rabbitmq-consumer.xml"/>

    <bean
        class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter">
        <property name="messageConverters">
            <list>
                <bean
                    class="com.xx.core.web.spring.convert.JSONHttpMessageConverter">
                </bean>
                <bean
                    class="org.springframework.http.converter.StringHttpMessageConverter">
                </bean>
                <bean
                    class="org.springframework.http.converter.ByteArrayHttpMessageConverter">
                </bean>
                <bean
                    class="org.springframework.http.converter.xml.SourceHttpMessageConverter">
                </bean>
                <bean
                    class="org.springframework.http.converter.support.AllEncompassingFormHttpMessageConverter">
                </bean>
            </list>
        </property>
        <property name="customArgumentResolvers">
            <list>
                <bean class="com.xx.core.web.spring.bind.TIDArgumentResolver" />
                <bean
                    class="com.xx.core.web.spring.bind.RequestAttributeArgumentResolver" />
                <bean class="com.xx.core.web.spring.bind.ClientIPArgumentResolver" />
                <bean
                    class="com.xx.core.web.spring.bind.SessionUserIdArgumentResolver" />
            </list>
        </property>
    </bean>

    <bean id="dealException" class="com.xx.core.web.spring.interceptor.ExceptionAop"></bean>
    <aop:config>
        <!-- 对异常的集中处理 -->
        <aop:aspect id="exceptionAop" ref="dealException">
            <aop:pointcut id="exceptionPointCut"
                expression="execution(* com.xx.c.action.*.*.*(..))" />
            <aop:around pointcut-ref="exceptionPointCut" method="deal" />
        </aop:aspect>
    </aop:config>

    <bean
        class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
        <property name="beanNames">
            <list>
                <value>systemDictDao</value>
                <value>userDao</value>
                <value>infoDao</value>
            </list>
        </property>
        <property name="interceptorNames">
            <list>
                <value>jamonInterceptor</value>
            </list>
        </property>
    </bean>
    <bean id="jamonInterceptor"
        class="org.springframework.aop.interceptor.JamonPerformanceMonitorInterceptor"></bean>
     <bean id="multipartResolver"
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver"
        p:defaultEncoding="utf-8" p:maxUploadSize="104857600" />
    <context:component-scan base-package="com.xx.c.action"
        name-generator="com.xx.core.web.spring.annotation.FullPackageBeanNameGenerator" /> 
    <!-- action end -->
    <!-- Spring托管线程池 -->
    <bean id="threadPoolTaskExecutor"
        class="org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor">
        <!-- 线程池活跃的线程数 -->
        <property name="corePoolSize" value="50" />
        <!-- 线程池最大活跃的线程数 -->
        <property name="maxPoolSize" value="75" />
        <!-- 队列的最大容量 <property name ="queueCapacity" value ="2000" /> -->
        <!-- <property name="WaitForTasksToCompleteOnShutdown" value="true" /> -->
    </bean>
     
    <bean id="mapper" class="org.dozer.spring.DozerBeanMapperFactoryBean">
       <property name="mappingFiles">
            <list>        
                <value>classpath:/dozerBeanMapping.xml</value>
                <value>classpath:/dozerBeanMapping2.xml</value>
           </list>
       </property>
    </bean>    
    
</beans>

3. 在dao.xml中,添加对数据源的引用:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans     
        http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">
    <!-- import this -->
    <bean id="userDao" class="com.xx.c.dao.user.UserDaoImpl">
        <property name="sqlSession" ref="sqlSession" />
        <property name="mongoTemplate" ref ="mongoTemplate" />
    </bean>
    
    <bean id="infoDao" class="com.xx.c.dao.content.ConInfoDaoImpl">
        <property name="sqlSession" ref="sqlSession_c" />
    </bean>
</beans>

4. 在具体的sql当中实现数据操作:

<?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="com.xx.c.dao.user.UserDao">
    <resultMap id="userMap" type="User">
        <id column="user_id" property="userId" />
        <result column="username" property="userName" />
        <result column="sex" property="sex" />
    </resultMap>
    <resultMap id="userInfoMap" type="UserInfoBean">
        <id column="user_id" property="userId" />
        <result column="card_id" property="cardId" />
        <result column="username" property="phone" />
        <result column="id_card_address" property="idCardAddress" />
        <result column="reg_time" property="regTime" />
        <result column="logintime" property="logintime" />
        <result column="last_time" property="lastTime" />
        <result column="reg_ip" property="regIp" />
        <result column="up_ip" property="upIp" />
        <result column="last_ip" property="lastIp" />
        <result column="download_channel" property="downloadChannel" />

        <result column="guarantor1" property="guarantor1" />
        <result column="guarantor_phone1" property="guarantor_phone1" />
    </resultMap>

    <sql id="getUserCond">
        <where>
            <if test="userId != null">
                and u.user_id = #{userId}
            </if>
            <if test="userName != null and userName != ''">
                and u.username = #{userName}
            </if>
        </where>
    </sql>
    
    <select id="getUser" resultMap="userMap" parameterType="User">
        select u.* from users_info u
        <include refid="getUserCond" />
    </select>

    <insert id="addDebitCardBindChannel" parameterType="DebitCardBindChannelBean">
        insert into
        nzz (
        user_id,channel,
        card_no,request_id,status)
        values (
        #{userId},#{channel},#{cardNo},#{requestId},#{status})
    </insert>

    <update id="updateDebitCardBindChannel" parameterType="DebitCardBindChannelBean">
        update
        nzz
        set status=#{status}
        ,request_id=#{requestId}
        where
        user_id=#{userId} and card_no=#{cardNo}
        and channel=#{channel}
    </update>
</mapper>

5. 完善一下,使用别名,使sql写的时候更简短:

<?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>
<settings>  
        <setting name="logPrefix" value="dao."/>  
</settings> 
    <!-- 配置别名,以便在引用时简写 -->
    <typeAliases>
        <typeAlias alias="User" type="com.xx.c.pojo.user.User" />
        <typeAlias alias="CardBinBean" type="com.xx.c.pojo.user.CardBinBean"/>
        <typeAlias alias="UserCreditLogBean" type="com.xx.c.pojo.user.UserCreditLogBean"/>
        <typeAlias alias="InfoBean" type="com.xx.c.pojo.content.ContentInfo" /> 
    </typeAliases>

    <mappers>
        <mapper resource="com/xx/c/dao/impl/User.xml"/>
        <mapper resource="com/xx/c/dao/impl/Info.xml"/> 
    </mappers>

</configuration>

以上,就已经完成了一整个spring操作多数据源的mybatis了。

作者:等你归去来