spring+Ibatis+oracle多数据源配置

Spring+iBatis+JOTM实现JTA事务JOTM是个开源的JTA事务管理组件，可以让程序脱离J2EE容器而获得分布式事务管理的能力。

测试过程如下：一、环境1、准备软件环境spring-framework-2.5.6.SEC01-with-dependencies.zipibatis-2.3.4ow2-jotm-dist-2.1.4-bin.tar.gzMySQL-5.1JDK1.52、创建数据库环境，注意数据库引擎为InnoDB，只有这样才能支持事务。

CREATE DATABASE IF NOT EXISTS testdb_a DEFAULT CHARACTER SET utf8;USE testdb_a;DROP TABLE IF EXISTS tab_a;CREATE TABLE tab_a (id bigint(20) NOT NULL,name varchar(60) DEFAULT NULL,address varchar(120) DEFAULT NULL,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE DATABASE IF NOT EXISTS testdb_b DEFAULT CHARACTER SET utf8;USE testdb_b;DROP TABLE IF EXISTS tab_b;CREATE TABLE tab_b (id bigint(20) NOT NULL,name varchar(60) DEFAULT NULL,address varchar(120) DEFAULT NULL,PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;二、建立项目testJOTM1、建立项目后，准备依赖的类库，结构如下：│spring-aop.jar│spring-beans.jar│spring-context-support.jar│spring-context.jar│spring-core.jar│spring-jdbc.jar│spring-jms.jar│spring-orm.jar│spring-test.jar│spring-tx.jar│spring-web.jar│spring-webmvc-portlet.jar│spring-webmvc-struts.jar│spring-webmvc.jar│aspectjrt.jar│aspectjweaver.jar│cglib-nodep-2.1_3.jar│asm-2.2.3.jar│log4j-1.2.15.jar│asm-commons-2.2.3.jar│asm-util-2.2.3.jar│aopalliance.jar│mysql-connector-java-5.1.6-bin.jar│├─ibatis│ibatis-2.3.4.726.jar│sql-map-2.dtd│sql-map-config-2.dtd│├─jotm│license.txt│xapool.jar│jotm-core.jar│jotm-standalone.jar│jotm-jms.jar│jotm-datasource.jar│ow2-jta-1.1-spec.jar│jotm-client.jar│├─jakarta-commons│commons-attributes-api.jar│commons-attributes-compiler.jar│commons-beanutils.jar│commons-codec.jar│commons-collections.jar│commons-dbcp.jar│commons-digester.jar│commons-discovery.jar│commons-fileupload.jar│commons-httpclient.jar│commons-io.jar│commons-lang.jar│commons-logging.jar│commons-pool.jar│commons-validator.jar│├─junit│junit-3.8.2.jar│junit-4.4.jar│license.txt│└─log4jlog4j-1.2.15.jar2、根据表建立entity和SQLMappublic class TabA implements Serializable {private Long id;private String name;private String address;//省略getter/setterpublic class TabB implements Serializable {private Long id;private String name;private String address;//省略getter/setterTabA.xml<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE sqlMap PUBLIC "-////DTD SQL Map 2.0//EN" "/dtd/sql-map-2.dtd" ><!-- 表名:tab_a --><sqlMap namespace="tab_a"><typeAlias alias="TabA" type="vasoft.stu.jtom.entity.TabA"/><resultMap id="result_base" class="TabA"><result property="id" column="id"/><result property="name" column="name"/><result property="address" column="address"/></resultMap><!-- 添加--><insert id="insert" parameterClass="TabA">insert into tab_a(id,name,address) values (#id#,#name#,#address#)<selectKey keyProperty="id" resultClass="long">select LAST_INSERT_ID()</selectKey></insert><!-- 更新--><update id="update" parameterClass="TabA">update tab_a setid = #id#,name = #name#,address = #address#where id = #id#</update><!-- 删除--><delete id="deleteById" parameterClass="long">delete from tab_awhere id = #value#</delete><!-- 根据ID获取--><select id="findById" parameterClass="long" resultMap="tab_a.result_base">select *from tab_awhere id = #value#</select></sqlMap>TabB.xml<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE sqlMap PUBLIC "-////DTD SQL Map 2.0//EN" "/dtd/sql-map-2.dtd" ><!-- 表名:tab_b --><sqlMap namespace="tab_b"><typeAlias alias="TabB" type="vasoft.stu.jtom.entity.TabB"/><resultMap id="result_base" class="TabB"><result property="id" column="id"/><result property="name" column="name"/><result property="address" column="address"/></resultMap><!-- 添加--><insert id="insert" parameterClass="TabB">insert into tab_b(id,name,address) values (#id#,#name#,#address#)<selectKey keyProperty="id" resultClass="long">select LAST_INSERT_ID()</selectKey></insert><!-- 更新--><update id="update" parameterClass="TabB">update tab_b setid = #id#,name = #name#,address = #address#where id = #id#</update><!-- 删除--><delete id="deleteById" parameterClass="long">delete from tab_bwhere id = #value#</delete><!-- 根据ID获取--><select id="findById" parameterClass="long" resultMap="tab_b.result_base"> select *from tab_bwhere id = #value#</select></sqlMap>/*** TabADAO** @author leizhimin 2009-6-25 12:39:19*/public interface TabADAO {/*** 保存一个TabA对象** @param tabA TabA对象* @return 返回保存后的对象*/TabA saveTabA(TabA tabA);/*** 更新一个TabA** @param tabA TabA对象* @return 返回更新后的对象*/TabA updateTabA(TabA tabA);/*** 删除指定标识的一个TabA** @param id TabA标识*/void deleteTabAById(Long id);/*** 获取指定标识的TabA** @param id TabA标识* @return 所查询到的TabA*/TabA findTabAById(Long id);}/*** TabADAO** @author leizhimin 2009-6-25 12:43:55*/public class TabADAOImpl extends SqlMapClientDaoSupport implements TabADAO {/*** 保存一个TabA对象** @param tabA TabA对象* @return 返回保存后的对象*/public TabA saveTabA(TabA tabA) {Long id = (Long) getSqlMapClientTemplate().insert("tab_a.insert", tabA);tabA.setId(id);return tabA;}/*** 更新一个TabA** @param tabA TabA对象* @return 返回更新后的对象*/public TabA updateTabA(TabA tabA) {getSqlMapClientTemplate().update("tab_a.update", tabA);return tabA;}/*** 删除指定标识的一个TabA** @param id TabA标识*/public void deleteTabAById(Long id) {getSqlMapClientTemplate().delete("tab_a.deleteById",id);}/*** 获取指定标识的TabA** @param id TabA标识* @return 所查询到的TabA*/public TabA findTabAById(Long id) {return (TabA) getSqlMapClientTemplate().queryForObject("tab_a.findById",id);}}B的DAO和A类似，就不写了。

iBatis多数据源的苦恼在仅使用ibatis时，多数据源简直就是梦魇，每多一个数据源就需要多一份sql-map-config配置文件。

采用spring的AbstractRoutingDataSource就可以简单的解决这个问题。

AbstractRoutingDataSource实现了javax.sql.DataSource接口，因此可以理解为一个虚拟的动态DataSource，在需要的时候根据上下文Context动态决定使用哪个数据源。

Spring+iBatis多数据源的配置下面是一个完整的配置：< beans xmlns="/schema/beans"xmlns:xsi="/2001/XMLSchema-instance"xmlns:aop="/schema/aop"xmlns:tx="/schema/tx"xmlns:jee="/schema/jee"xsi:schemaLocation="/schema/beans/schema/beans/spring-beans-2.5.xsd /schema/aop/schema/aop/spring-aop-2.5.xsd /schema/tx/schema/tx/spring-tx-2.5.xsd /schema/jee/schema/jee/spring-jee-2.5.xsd">< !-- ========================= GENERAL DEFINITIONS========================= -->< jee:jndi-lookup id="ds0" jndi-name="jdbc/ds0"/>< jee:jndi-lookup id="ds1" jndi-name="jdbc/ds1"/>< jee:jndi-lookup id="ds2" jndi-name="jdbc/ds2"/>< bean id="dataSource" class="com.xxx.xxx.util.DynamicDataSource"> < property name="targetDataSources">< map key-type="ng.String">< entry key="0" value-ref="ds0"/>< entry key="1" value-ref="ds1"/>< entry key="2" value-ref="ds2"/>< /map>< /property>< property name="defaultTargetDataSource" ref="1"/>< /bean>< !-- SqlMap setup for iBATIS Database Layer -->< bean id="sqlMapClient"class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">< property name="dataSource" ref="dataSource"/>< property name="configLocation"value="classpath:com/xxx/xxx/dao/sqlmap/sql-map-config.xml"/>< /bean>< bean id="testDAO" class="com.xxx.xxx.dao.impl.TestDAO">< property name="sqlMapClient" ref="sqlMapClient"/>< /bean>< bean id="testService"class="com.xxx.xxx.service.impl.TestService">< property name="testDAO" ref="testDAO"/>< /bean>< /beans>其核心是DynamicDataSource，代码如下package com.xxx.xxx.util;import org.apache.log4j.Logger;importorg.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicDataSource extends AbstractRoutingDataSource {static Logger log = Logger.getLogger("DynamicDataSource");@Overrideprotected Object determineCurrentLookupKey() {// TODO Auto-generated method stubreturn DbContextHolder.getDbType();}}上下文DbContextHolder为一线程安全的ThreadLocal，如下package com.xxx.xxx.util;public class DbContextHolder {private static final ThreadLocal contextHolder = new ThreadLocal();public static void setDbType(String dbType) {contextHolder.set(dbType);}public static String getDbType() {return (String) contextHolder.get();}public static void clearDbType() {contextHolder.remove();}}sql-map-config.xml如下< ?xml version="1.0" encoding="UTF-8" standalone="no"?>< !DOCTYPE sqlMapConfig PUBLIC "-////DTD SQL Map Config 2.0//EN""/dtd/sql-map-config-2.dtd">< sqlMapConfig>< sqlMap resource="com/xxx/xxx/dao/sqlmap/Object.xml"/>< /sqlMapConfig>这样在调用service之前只需要设置一下上下文即可调用相应的数据源，如下：DbContextHolder.setDbType("2");//execute services//.........dao如下package com.xxx.xxx.dao.impl;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.log4j.Logger;importorg.springframework.orm.ibatis.support.SqlMapClientDaoSupport;import com.xxx.xxx.vo.TestObj;public class TestDAO extends SqlMapClientDaoSupport implements ITestDAO {static Logger log = Logger.getLogger(TestDAO.class);public TestObj getTestObj(String objID) throws Exception {return (TestObj)getSqlMapClientTemplate().queryForObject("getTestObj", objID);}}以上就介绍了Spring+iBatis多数据源的配置方案。

SpringBoot+Mybatis-plus多数据源配置（MySQL、Sqlserver）前⾔：本章案例利⽤dynamic-datasource-spring-boot-starter集成多数据源，mybaits-plus采⽤3.3.0版本，主要讲述配置多数据源，其案例中也包含了逻辑删除、攻击SQL阻断解析器、p6spySQL性能分析打印、事务以及分页和乐观锁插件。

dynamic-datasource-spring-boot-starter 是⼀个基于springboot的快速集成多数据源的启动器，其⽀持 Jdk 1.7+, SpringBoot 1.4.x 1.5.x 2.0.x。

⼀、pom.xml<!--代码简化,⼯具相关 --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!--SQLServer 驱动--><dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>sqljdbc4</artifactId><version>4.0</version><scope>runtime</scope></dependency><!--mysql 驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.3.0</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>2.4.2</version></dependency><dependency><groupId>p6spy</groupId><artifactId>p6spy</artifactId><version>3.8.0</version></dependency>⼆、application.ymlserver:port: 8888servlet:context-path: /serverspring:application:name: springboot-manyDataSourcesdatasource:p6spy: truedynamic:datasource:master:driver-class-name: com.p6spy.engine.spy.P6SpyDriverurl: jdbc:p6spy:mysql://localhost:3306/datasourceName?characterEncoding=utf8&useSSL=false&serverTimezone=GMTusername: rootpassword: root# url: jdbc:mysql://localhost:3306/ datasourceName?characterEncoding=utf8&useSSL=false&serverTimezone=GMT# username: root# password: root# driver-class-name: com.mysql.cj.jdbc.Driverdb2:driver-class-name: com.p6spy.engine.spy.P6SpyDriverurl: jdbc:p6spy:sqlserver://localhost:1433;DatabaseName=datasourceNameusername: sapassword: root# url: jdbc:sqlserver://localhost:1433;DatabaseName= datasourceName# username: sa# password: root# driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver#⽇志logging:level:com.example.demo : debug三、spy.propertiesmodulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory# ⾃定义⽇志打印logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger#⽇志输出到控制台appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger# 使⽤⽇志系统记录 sql#appender=com.p6spy.engine.spy.appender.Slf4JLogger# 设置 p6spy driver 代理deregisterdrivers=true# 取消JDBC URL前缀useprefix=true# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.excludecategories=info,debug,result,commit,resultset# ⽇期格式dateformat=yyyy-MM-dd HH:mm:ss# 实际驱动可多个#driverlist=org.h2.Driver# 是否开启慢SQL记录outagedetection=true# 慢SQL记录标准 2 秒outagedetectioninterval=2四、MybatisPlusConfig@Configuration@EnableTransactionManagement//开启事务public class MybatisPlusConfig {/*** mybatisplus 分页插件* @return*/@Beanpublic PaginationInterceptor paginationInterceptor(){PaginationInterceptor paginationInterceptor = new PaginationInterceptor();List<ISqlParser> sqlParserList = new ArrayList<>();// 攻击 SQL 阻断解析器、加⼊解析链;防⽌⼩⽩或者恶意进⾏delete update 全表操作。

spring+ibatis多数据源<bean id="propertyConfigurer"class="org.springframework.beans.factory.config.PropertyPl aceholderConfigurer"><property name="locations"><list><value>WEB-INF/config/jdbc.properties </value></list></property></bean><bean id="dataSource"class="mons.dbcp.BasicDataSource"destroy-method="close"><property name="driverClassName"value="${jdbc.driverClassName}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${ername}" /> <property name="password" value="${jdbc.password}" /> </bean><!-- 数据源的加载 --><bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTra nsactionManager"><property name="dataSource" ref="dataSource" /></bean><bean id="sqlMapClient"class="org.springframework.orm.ibatis.SqlMapClientFactory Bean"><property name="configLocation"value="/WEB-INF/config/sqlMap-config.xml" /><property name="dataSource" ref="dataSource" /></bean>DAO层是：public class DepartmentDaoImp extends SqlMapClientDaoSupport implementsDepartmentDao {public int addDepartment(DepartmentInfo departmentInfo) {returngetSqlMapClientTemplate().update("Department.addDepartmen t", departmentInfo);}当然可以配置多个，sqlMapClient只是默认的，你可以自己配置多个呀<bean id="userDaoImp" class="erDaoImp"> <property name="sqlMapClient" ref="sqlMapClient" /><property name="sqlMapClient1" ref="sqlMapClient" /><property name="sqlMapClient2" ref="sqlMapClient2" /><property name="sqlMapClient3" ref="sqlMapClient3" /></bean>transactionManager也分成多个<bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTra nsactionManager"><property name="dataSource" ref="dataSource" /></bean><bean id="transactionManager1"......<bean id="transactionManager2"......然后再你的dao类里面声明(sqlMapClient是默认的无需声明) private SqlMapClient sqlMapClient1;private SqlMapClient sqlMapClient2;private SqlMapClient sqlMapClient3;get/set ......。