MySql教程

使用jta和atomikos配置多数据源(oracle和mysql)

本文主要是介绍使用jta和atomikos配置多数据源(oracle和mysql),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

pom.xml 文件

     <!-- 分布式事务 jta+ atomikos -->
        <!-- 多个数据源的情况下,事务进行管理 -->
        <dependency>
            <groupId>com.atomikos</groupId>
            <artifactId>transactions-jdbc</artifactId>
            <version>4.0.6</version>
        </dependency>
        <dependency>
            <groupId>javax.transaction</groupId>
            <artifactId>jta</artifactId>
            <version>1.1</version>
        </dependency>
    
    <!-- 数据库连接池版本 推荐使用1.0.9的版本-->
    <druid.version>1.0.9</druid.version>

spring-dataSource.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:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    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/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
    
    <!-- 引入配置文件 数据库-->
    <bean id="propertyConfigurer"
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:config/db.properties" />
    </bean>
    
    <!-- 配置数据druid数据源,密码进行加密处理-->
    <!-- <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> -->
    <bean id="dataSource" class="com.alibaba.druid.pool.xa.DruidXADataSource" destroy-method="close" init-method="init">
        <property name="url"                  value="${db.core.url}" />
        <property name="username"             value="${db.core.username}" />
        <property name="password"             value="${db.core.password}" />
        <!-- 密码解密 -->
        <property name="connectionProperties" value="config.decrypt=true;config.decrypt.key=${db.core.publickey}" />
        <property name="proxyFilters">
            <list><ref bean="log-filter" /></list>
        </property>
        
        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${druid.initialSize}" />
        <property name="minIdle"     value="${druid.minIdle}" />
        <property name="maxActive"   value="${druid.maxActive}" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait"                       value="${druid.maxWait}" />
        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />

        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis"    value="${druid.minEvictableIdleTimeMillis}" />

        <property name="validationQuery" value="${druid.validationQuery}" />
        <property name="testWhileIdle"   value="${druid.testWhileIdle}" />
        <property name="testOnBorrow"    value="${druid.testOnBorrow}" />
        <property name="testOnReturn"    value="${druid.testOnReturn}" />

        <!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
        <!-- 
        <property name="poolPreparedStatements"                     value="${druid.poolPreparedStatements}" />
        <property name="maxPoolPreparedStatementPerConnectionSize"    value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
        -->
         
        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned"         value="${druid.removeAbandoned}" /> 
        <!-- 1800秒,也就是30分钟 -->
        <property name="removeAbandonedTimeout"  value="${druid.removeAbandonedTimeout}" /> 
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned"            value="${druid.logAbandoned}" />
        
        <!-- 配置监控统计拦截的filters -->
        <property name="filters"                 value="${druid.filters}" />
    </bean>

    <!-- 配置数据druid多数据源(高斯数据库)密码进行加密处理 -->
    <!-- <bean id="postgresqlDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> -->
    <bean id="postgresqlDataSource" class="com.alibaba.druid.pool.xa.DruidXADataSource" destroy-method="close" init-method="init">
        <property name="driverClassName"     value="${dbpg.core.driver}" />
        <property name="url"                  value="${dbpg.core.url}" />
        <property name="username"             value="${dbpg.core.username}" />
        <property name="password"             value="${dbpg.core.password}" />
        <!-- 密码解密 -->
        <property name="connectionProperties" value="config.decrypt=true;config.decrypt.key=${dbpg.core.publickey}" />
        <property name="proxyFilters">
            <list><ref bean="log-filter" /></list>
        </property>
        
        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="${druid.initialSize}" />
        <property name="minIdle"     value="${druid.minIdle}" />
        <property name="maxActive"   value="${druid.maxActive}" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait"                       value="${druid.maxWait}" />
        <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />

        <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis"    value="${druid.minEvictableIdleTimeMillis}" />

        <property name="validationQuery" value="${druid.validationQuery}" />
        <property name="testWhileIdle"   value="${druid.testWhileIdle}" />
        <property name="testOnBorrow"    value="${druid.testOnBorrow}" />
        <property name="testOnReturn"    value="${druid.testOnReturn}" />

        <!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
        <!-- 
        <property name="poolPreparedStatements"                     value="${druid.poolPreparedStatements}" />
        <property name="maxPoolPreparedStatementPerConnectionSize"    value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
        -->
         
        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned"         value="${druid.removeAbandoned}" /> 
        <!-- 1800秒,也就是30分钟 -->
        <property name="removeAbandonedTimeout"  value="${druid.removeAbandonedTimeout}" /> 
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned"            value="${druid.logAbandoned}" />
        
        <!-- 配置监控统计拦截的filters -->
        <property name="filters"                 value="${druid.filters}" />
    </bean>


    <!-- druid日志输出filter -->
    <bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">
         <property name="statementExecutableSqlLogEnable" value="true" />
         <property name="resultSetLogEnabled" value="false" />
         <property name="statementLogEnabled" value="false" />
         <property name="connectionLogEnabled" value="false" />
         <property name="dataSourceLogEnabled" value="false" />
    </bean>
    
  <!-- Oracle库datasource数据源 -->
    <bean id="oracleDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean"
        destroy-method="close" init-method="init">
        <property name="uniqueResourceName" value="ads" />
        <property name="xaDataSource" ref="dataSource" />
        
        <property name="maintenanceInterval" value="28000" />
        <!-- <property name="testQuery" value="SELECT 1" /> -->
        <!-- <property name="minPoolSize" value="5" />
         <property name="maxPoolSize" value="20" />
         <property name="borrowConnectionTimeout" value="60" />  -->
         
    </bean>  
    
    <!-- 高斯库datasource数据源 -->
    <bean id="dataSourcePostgresql" class="com.atomikos.jdbc.AtomikosDataSourceBean"
        destroy-method="close" init-method="init">
        <property name="uniqueResourceName" value="bds" />
        <property name="xaDataSource" ref="postgresqlDataSource" />
        <property name="maintenanceInterval" value="28000" />
        <!-- <property name="testQuery" value="SELECT 1" /> -->
        
        <!-- <property name="minPoolSize" value="5" />
         <property name="maxPoolSize" value="20" />
         <property name="borrowConnectionTimeout" value="60" />  -->
        
    </bean> 
    
    <!-- atomitos事务管理器 -->
    <bean id="atomitosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager"
        init-method="init" destroy-method="close">
        <property name="forceShutdown" value="true" />
    </bean>

    <!-- atomitos事务 -->
    <bean id="atomikosTransaction" class="com.atomikos.icatch.jta.UserTransactionImp">
        <property name="transactionTimeout" value="600" />
    </bean>

    <!-- Spring JTA事务管理器 -->
    <bean id="transactionManager"
        class="org.springframework.transaction.jta.JtaTransactionManager">
        <property name="transactionManager" ref="atomitosTransactionManager" />
        <property name="userTransaction" ref="atomikosTransaction" />
        <property name="allowCustomIsolationLevels" value="true" />
        <property name="globalRollbackOnParticipationFailure" value="true" />
    </bean>
    
    <!-- 开启事务注解驱动 -->
     <tx:annotation-driven transaction-manager="transactionManager" />
     
    <!-- interceptor事务拦截器 -->
    <bean id="transactionInterceptor"
        class="org.springframework.transaction.interceptor.TransactionInterceptor">
        <property name="transactionManager" ref="transactionManager" />
        <property name="transactionAttributes">
            <props>
                <prop key="*">PROPAGATION_REQUIRED,-Exception</prop>
            </props>
        </property>
    </bean>
    
    <!-- proxy代理所有Mapper结尾的bean实例 -->
    <bean
        class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
        <property name="beanNames">
            <list>
                <value>*Mapper</value>
            </list>
        </property>
        <property name="interceptorNames">
            <list>
                <value>transactionInterceptor</value>
            </list>
        </property>
    </bean>
    
    
    
    <!-- 开启事务注解驱动 -->
    <!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->
    

    <!-- 事务管理器 -->
    <!-- <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
        <property name="DataSource" ref="postgresqlDataSource" />
    </bean> -->
    
    <!-- 事务管理器 高斯数据源事务-->
    <!-- <bean id="transactionManagerPG" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        高斯数据源事务
        <property name="dataSource" ref="postgresqlDataSource" />
    </bean> -->

</beans>

spring-mybatis.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:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:p="http://www.springframework.org/schema/p"
    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/aop 
    http://www.springframework.org/schema/aop/spring-aop-4.0.xsd">

    <!-- spring与mybatis整合配置,扫描所有dao -->
    <!-- 根据实际情况增加 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" p:basePackage="com.**.**.dao" p:sqlSessionFactoryBeanName="sqlSessionFactory" />
    <!-- 高斯库加载所有dao -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" p:basePackage="com.**.**.daopg" p:sqlSessionFactoryBeanName="sqlSessionFactoryPG" />
    
    
        <!-- scanner批量创建bean -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.**.**.daopg" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryPG" />
        </bean>

    <bean id="globalConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig">
        <!--逻辑删除 0 是默认未删除  1 是删除-->
        <property name="dbConfig">
            <bean class="com.baomidou.mybatisplus.core.config.GlobalConfig$DbConfig">
                <property name="logicDeleteValue" value="1"/>
                <property name="logicNotDeleteValue" value="0"/>
            </bean>
        </property>
    </bean>

<!--    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> -->
    <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
        <property name="globalConfig" ref="globalConfig"/>
        <!--dataSource属性指定要用到的连接池 -->
        <property name="dataSource" ref="oracleDataSource" />
        <!--configLocation属性指定mybatis的核心配置文件 -->
        <property name="configLocation" value="classpath:spring/mybatis-config.xml" />
        <property name="mapperLocations">
            <array>
                <value>classpath*:com/**/**/dao/mapper/*.xml</value>
            </array>
        </property>

        <!-- mybatis物理分页插件 -->
        <property name="plugins">
            <array>
                <!-- 权限插件 add by zhaoyunhua 20200513-->
                <!-- enabled:是否启用true|false -->
                <!-- ignoreroles:忽略得角色列表,逗号分隔(R001系统管理角色) -->
                <!-- debugable:是否调试模式,输出更多日志true|false -->
                <!-- 
                正式环境推荐使用:enabled=true,ignoreroles=R001,debugable=false
                 -->
                <bean class="com.actuary.utils.mybatis.PermissionHelper">
                    <property name="properties">
                        <value>
                            enabled=true
                            ignoreroles=R001
                            debugable=false
                        </value>
                    </property>
                </bean>
                <bean class="com.github.pagehelper.PageHelper">
                    <property name="properties">
                        <value>
                            offsetAsPageNum=true
                            rowBoundsWithCount=true
                            pageSizeZero=true
                            reasonable=true
                            supportMethodsArguments=false
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>
    
     <!-- sql会话模版 -->
    <!-- <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
        <constructor-arg ref="sqlSessionFactory"/>
        <constructor-arg ref="sqlSessionFactory"/>
    </bean> -->
    
    <!-- 配置高斯数据库 sqlSessionFactoryPG -->
    <bean id="sqlSessionFactoryPG" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
        <property name="globalConfig" ref="globalConfig"/>
        <!--dataSource属性指定要用到的连接池 -->
        <property name="dataSource" ref="dataSourcePostgresql" />
        <!--configLocation属性指定mybatis的核心配置文件 -->
        <property name="configLocation" value="classpath:spring/mybatis-config.xml" />
        <property name="mapperLocations">
            <array>
                <value>classpath*:com/**/**/daopg/mapper/*.xml</value>
            </array>
        </property>

        <!-- mybatis物理分页插件 -->
        <property name="plugins">
            <array>
                <!-- 权限插件 add by zhaoyunhua 20200513-->
                <!-- enabled:是否启用true|false -->
                <!-- ignoreroles:忽略得角色列表,逗号分隔(R001系统管理角色) -->
                <!-- debugable:是否调试模式,输出更多日志true|false -->
                <!-- 
                正式环境推荐使用:enabled=true,ignoreroles=R001,debugable=false
                 -->
                <bean class="com.actuary.utils.mybatis.PermissionHelper">
                    <property name="properties">
                        <value>
                            enabled=true
                            ignoreroles=R001
                            debugable=false
                        </value>
                    </property>
                </bean>
                <bean class="com.github.pagehelper.PageHelper">
                    <property name="properties">
                        <value>
                            offsetAsPageNum=true
                            rowBoundsWithCount=true
                            pageSizeZero=true
                            reasonable=true
                            supportMethodsArguments=false
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>


   
</beans>
######durid数据源
#db.core.url=jdbc:oracle:thin:@192.168.1.133:1521:utf8db
db.core.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl1
#db.core.username=username
#db.core.password=YbKBbfbRSBUXsBooZthhtOZ9cFMdxpKTfkIYPiaEYlL60sdsCwja3Yb6AhdmzTKOZemmL/R2IhBPkfFze2JDyw==
#db.core.publickey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAID/knRsgdpUE1u1eSp1Sz5MYWmHFi3tTX+dpdoYfX2Izz32cdKYx3gOpPaNHdgYurccjVxP4SHj2cSsKnunLdMCAwEAAQ==
db.core.username=username
db.core.password=X9fsOexysXatYqn8jMJqStH39S59u2puixOajW9JdPgq/I6m0TA8J/XnqXWEknxzxbzSxdUs0L/DTVSkVxXo1g==
db.core.publickey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALDtpiwe4riGVTipIFK3ai6dcNx65gFTtXoByt+kcHseqxblzZxMfwO8nZsbFQ6KOxZgIfFFpy1viJy9tOGiQI8CAwEAAQ==


######durid多数据源(高斯库)
#dbpg.core.driver=org.postgresql.Driver
dbpg.core.driver=com.mysql.jdbc.Driver
#db2.core.url=jdbc:postgresql:@9.16.98.105:25308:postgres
#dbpg.core.url=jdbc:postgresql://9.16.98.105:25308/postgres
dbpg.core.url=jdbc:mysql://localhost:3306/mybatis
#db.core.username=abc
#db.core.password=YbKBbfbRSBUXsBooZthhtOZ9cFMdxpKTfkIYPiaEYlL60sdsCwja3Yb6AhdmzTKOZemmL/R2IhBPkfFze2JDyw==
#db.core.publickey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAID/knRsgdpUE1u1eSp1Sz5MYWmHFi3tTX+dpdoYfX2Izz32cdKYx3gOpPaNHdgYurccjVxP4SHj2cSsKnunLdMCAwEAAQ==
dbpg.core.username=root
dbpg.core.password=vPY0JPHPcPB/E7jqLd+I+JGgrZH55NyHQf5ZGlicmVoHUUo/ucSHIvV8iEoyxRmd/QJXohF6JLlj7zfMez7T7g==
dbpg.core.publickey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBANSthDqkg3th6mRtw2FP4SS6WjF9X6PD27qF6vMhK9eO0ObuxT/hXgt7VIb0IuxV1ZTC3/ONexum0tTbthnnqjkCAwEAAQ==

#dbpg.core.username=abc
#dbpg.core.password=LsCt/K4DFAwWpgOV6CczSBWFyYs6YzSYEZ9VCbXQmr1U50LIc9zKAEgTMufg+TUprIJIGPwgJpdc+kfKT32+8Q==
#dbpg.core.publickey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAM02pyMsQAwUfHiZMKfpbHtc4jUBksh0AXFOti4o3hVVWqBL0d50O5OxidVd/QFcfIJDgGKyfIXX439uCiUl1mUCAwEAAQ==


#druid datasource config
druid.initialSize=10
druid.minIdle=1
druid.maxActive=200
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.validationQuery=SELECT 'x' FROM dual
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.removeAbandoned=true
druid.removeAbandonedTimeout=1800
druid.logAbandoned=true
druid.poolPreparedStatements=false
druid.maxPoolPreparedStatementPerConnectionSize=20
druid.filters=config,mergeStat,log4j

 

这篇关于使用jta和atomikos配置多数据源(oracle和mysql)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!