注意:配置切换数据源和实现mybatis支持多种数据库为独立的配置,二者不相干
一、实现mybatis支持多种数据库
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis.xml"/>
<property name="mapperLocations">
<array>
//这里配置扫描不需要区分 扫描全部即可
<value>classpath*:com/**/mapper/*.xml</value>
</array>
</property>
<property name="databaseIdProvider" ref="databaseIdProvider" />
</bean>
<bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties" ref="vendorProperties"/>
</bean>
<bean id="vendorProperties"
class="org.springframework.beans.factory.config.PropertiesFactoryBean">
<property name="properties">
<props>
<prop key="Oracle">oracle</prop>
<prop key="MySQL">mysql</prop>
</props>
</property>
</bean>
mybatis中使用databaseId="#"即可自动根据当前数据库类型执行对应语句
<select id="getStatLog" parameterType="java.lang.String" resultType="java.util.HashMap" databaseId="mysql">
select rpt_type RPT_TYPE,rpt_id RPT_ID,rpt_name RPT_NAME,rpt_code RPT_CODE,sett_no SETT_NO,status STATUS,deal_date DEAL_DATE from tg_report_log where sett_no = #{cycle}
</select>
<select id="getStatLog" parameterType="java.lang.String" resultType="java.util.HashMap" databaseId="oracle">
select rpt_type RPT_TYPE,rpt_id RPT_ID,rpt_name RPT_NAME,rpt_code RPT_CODE,sett_no SETT_NO,status STATUS,deal_date DEAL_DATE from tg_report_log where sett_no = #{cycle}
</select>
二、springmvc+mytais配置多个数据源
定义区分各个数据源
public class DataSources {
//注意:这里的大小写要和xml文件对应的key值完全保持一致
public static final String MYSQL = "MYSQL";
public static final String ORACLE = "ORACLE";
}
获取当前配置要使用的数据源
public class DataSourceTypeManager {
private static final ThreadLocal<String> dataSourceTypes = new ThreadLocal<>();
public static String get() {
return dataSourceTypes.get();
}
public static void set(String dataSourceType) {
dataSourceTypes.set(dataSourceType);
}
public static void reset() {
dataSourceTypes.set(DataSources.MYSQL);
}
}
实现类 实现数据源切换
public class ThreadLocalRountingDataSource extends AbstractRoutingDataSource {
//这里的dbType可配置在jdbc.properties文件中 即可实现灵活切换数据源
public String dbType;
@Override
protected Object determineCurrentLookupKey() {
if(dbType.equalsIgnoreCase(DataSources.MYSQL)){
DataSourceTypeManager.set(DataSources.MYSQL);
}else if(dbType.equalsIgnoreCase(DataSources.ORACLE)){
DataSourceTypeManager.set(DataSources.ORACLE);
}else {
DataSourceTypeManager.set(DataSources.MYSQL);
}
return DataSourceTypeManager.get();
}
public String getDbType() {
return dbType;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
}
applicationContext.xml文件中配置多个数据源
<bean id="mysqlDatasource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="driverClassName" value="${jdbc.driverClassName}"></property>
</bean>
<bean id="oracleDatasource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value=""/>
<property name="username" value=""/>
<property name="password" value=""/>
<property name="driverClassName" value=""></property>
</bean>
配置自定义的数据源管理类 管理多个数据源 获取properties文件中配置的dbType 匹配对应数据源
<bean id="dataSource" class="com.asiainfo.datasource.ThreadLocalRountingDataSource">
<property name="dbType" value="${dbType}"></property>
<property name="targetDataSources" >
<map key-type="java.lang.String">
//这里的key值和上文类中的值大小写要完全一致
<entry value-ref="mysqlDatasource" key="MYSQL"></entry>
<entry value-ref="oracleDatasource" key="ORACLE"></entry>
</map>
</property>
//如果根据对应的key值没有找到对应的数据源 则默认使用
<property name="defaultTargetDataSource" ref="mysqlDatasource"></property>
</bean>
properties中定义当前要使用的数据源类型 和自定义类以及xml文件中的key值对应
dbType=MYSQL