当在项目中需要对多个库进行操作时,并且我们想在每次操作库的时候由程序动态来选择,那么我们可以采用Spring+mybatis配置为动态的数据源。在动态数据源中当需要对库进行操作的时候通过key值动态的来决定来操作那个库。
首先我们需要配置多个数据源,下面列举两个:
1、db_3u.xml 配置对3U的数据源
<?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.xsd" default-lazy-init="true">
<!-- 川航 -->
<bean id="dataSource3u" class="com.qunar.db.resource.RWDelegatorDataSource" destroy-method="close">
<constructor-arg index="0" type="java.lang.String" value="******"/>
<!-- DBA分配给业务的namespace -->
<constructor-arg index="1" type="java.lang.String" value="*****"/>
<!-- DBA分配给业务的MySQL用户名 -->
<constructor-arg index="2" type="java.lang.String" value="*******"/>
<!-- DBA分配给业务的MySQL密码-->
<constructor-arg index="3" type="java.lang.String" value="3u"/>
<!--如果将db_name替换成空串(""),则是连接实例 -->
<constructor-arg index="4" type="int" value="20"/>
<!-- 客户端MySQL连接池核心连接数,推荐:10-->
<constructor-arg index="5" type="int" value="50"/>
<!-- 客户端MySQL连接池最大连接数,推荐:20 -->
<constructor-arg index="6" type="java.lang.String" value="?useunicode=true&autoReconnect=true"/>
<constructor-arg index="7" type="boolean" value="true"/> <!-- autocommit=true or false, 默认是false -->
<!-- MySQL jdbc Url 参数-->
<constructor-arg index="8" ref="tomcatJdbcDataSourceFactory"/>
<!-- 数据源工厂实现类 -->
</bean>
</beans>
b_9Cxml 配置对9C的数据源
<?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.xsd" default-lazy-init="true">
<!-- 春秋 -->
<bean id="dataSource9c" class="com.qunar.db.resource.RWDelegatorDataSource" destroy-method="close">
<constructor-arg index="0" type="java.lang.String" value="8888"/>
<!-- DBA分配给业务的namespace -->
<constructor-arg index="1" type="java.lang.String" value="8888"/>
<!-- DBA分配给业务的MySQL用户名 -->
<constructor-arg index="2" type="java.lang.String" value="88888"/>
<!-- DBA分配给业务的MySQL密码-->
<constructor-arg index="3" type="java.lang.String" value="9c"/>
<!--如果将db_name替换成空串(""),则是连接实例 -->
<constructor-arg index="4" type="int" value="20"/>
<!-- 客户端MySQL连接池核心连接数,推荐:10-->
<constructor-arg index="5" type="int" value="50"/>
<!-- 客户端MySQL连接池最大连接数,推荐:20 -->
<constructor-arg index="6" type="java.lang.String" value="?useunicode=true&autoReconnect=true"/>
<constructor-arg index="7" type="boolean" value="true"/> <!-- autocommit=true or false, 默认是false -->
<!-- MySQL jdbc Url 参数-->
<constructor-arg index="8" ref="tomcatJdbcDataSourceFactory"/>
<!-- 数据源工厂实现类 -->
</bean>
</beans>
当然数据源的配置你可以更多。
2、接下来我们在db_context.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" 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/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
<span style="white-space:pre"> </span>
<import resource="classpath:db/db-cz.xml" />
<import resource="classpath:db/db-ca.xml" />
<import resource="classpath:db/db-9c.xml" />
<import resource="classpath:db/db-3u.xml" />
<import resource="classpath:db/db-hu.xml" />
<import resource="classpath:db/db-mf.xml" />
<import resource="classpath:db/db-zh.xml" />
<import resource="classpath:db/db-mu.xml" />
<import resource="classpath:db/db-gs.xml" />
<!--international-->
<import resource="classpath:db/international/db-ak.xml" />
<!-- 动态DataSource -->
<bean id="dynamicDataSource" class="com.****.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="dataSourceCz" key="cz" />
<entry value-ref="dataSourceCa" key="ca" />
<entry value-ref="dataSource9c" key="9c" />
<entry value-ref="dataSource3u" key="3u" />
<entry value-ref="dataSourceHu" key="hu" />
<entry value-ref="dataSourceMf" key="mf" />
<entry value-ref="dataSourceMu" key="mu" />
<entry value-ref="dataSourceZh" key="zh" />
<entry value-ref="dataSourceGs" key="gs" />
<!--international-->
<entry value-ref="dataSourceak" key="ak" />
</map>
</property>
</bean>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" mode="aspectj" />
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource" />
<property name="mapperLocations" value="classpath*:mappers/*.xml" />
<property name="typeAliasesPackage" value="com.qunar.flight.autoticket_domestic.proxy.model" />
<property name="typeHandlersPackage" value="com.qunar.flight.autoticket_domestic.proxy.mappers" />
</bean>
<bean name="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.qunar.flight.autoticket_domestic.proxy.dao" />
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
</beans>
其中需要说明的几点:
a、com.****.DynamicDataSource类中需要继承AbstractRoutingDataSource 重写determineCurrentLookupKey方法,对于这个方法返回的参数应该个db_context.xml文件配置的key对应,这样才能找到对应的数据源。比如现在返回的是3u,那么就会取dataSource3u这个数据源。理解了这点才能理解动态数据源到底是怎么动态起来的。
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return AppContext.getDatasourceProxyKey();
}
}
b、使用的是spring事务机制
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource" />
</bean>
3、然后需要一个sql的xml文件,这里以passager表为例
<?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.qunar.flight.autoticket_domestic.proxy.dao.PassengerDao">
<sql id="columns">
id as id, order_id as orderId, first_name as firstName, last_name as lastName, type as type,
birthday as birthday, gender as gender, country as country, id_type as idType, id_no as idNo,
ticket_no as ticketNo, ticket_status as ticketState, card_issue_place as cardIssuePlace,
passport_expire_date as passportExpireDate
</sql>
<select id="loadPassengers" resultType="PassengerModel">
select <include refid="columns" />
from passenger
where order_id = #{orderId}
</select>
<insert id="savePassenger" parameterType="PassengerModel" useGeneratedKeys="true" keyProperty="passenger.id">
INSERT INTO passenger
SET
order_id = #{passenger.orderId},
first_name = #{passenger.firstName},
last_name = #{passenger.lastName},
type = #{passenger.type},
birthday = #{passenger.birthday},
gender = #{passenger.gender},
<if test="passenger.country != null">
country = #{passenger.country},
</if>
<if test="passenger.cardIssuePlace != null">
card_issue_place = #{passenger.cardIssuePlace},
</if>
<if test="passenger.passportExpireDate != null">
passport_expire_date = #{passenger.passportExpireDate},
</if>
id_type = #{passenger.idType},
id_no = #{passenger.idNo}
</insert>
<update id="updatePassenger" parameterType="PassengerModel">
update passenger
set
ticket_no=#{passenger.ticketNo}
where id=#{passenger.id}
</update>
<select id="queryPassengerCount" resultType="Integer">
select count(*) from passenger where order_id=#{orderId}
</select>
</mapper>
最后需要一个PasseagerDao
public interface PassengerDao {
List<PassengerModel> loadPassengers(@Param("orderId") int orderId);
int savePassenger(@Param("passenger") PassengerModel passenger);
void updatePassenger(@Param("passenger") PassengerModel passenger);
int queryPassengerCount(@Param("orderId") int orderId);
}
到这里整个Spring加Mybatis的动态数据库配置就基本完成了,当调用PasseagerDao进行库操作时,根据AppContext.getDatasourceProxyKey()返回的值来确定采用哪个数据源。我们只需要改变返回值就行了。