spring jdbctemplate配置多数据源

素颜马尾好姑娘i 2022-06-09 00:30 360阅读 0赞

其实现在使用spring jdbctemplate与数据库交互已经有点low了，现在主流都是mybatis，但是遇到了就分享一下吧，spring jdbctemplate是怎么配置多数据源于调用的呢？

一、所搭建的项目为maven工程，pom.xml如下

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
        <modelVersion>4.0.0</modelVersion>
        <groupId>com.ustcinfo</groupId>
        <artifactId>faultsheetsync</artifactId>
        <version>0.0.2-SNAPSHOT</version>
        <name>springjdbc</name>
        <description>spring jdbctemplate多数据源功能展示</description>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <slf4j.version>1.7.2</slf4j.version>
            <logback.version>1.0.13</logback.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>4.1.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context-support</artifactId>
                <version>4.1.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>4.1.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>proxool</groupId>
                <artifactId>proxool</artifactId>
                <version>0.9.1</version>
            </dependency>
    
    	<dependency>
    		<groupId>com.cloudhopper.proxool</groupId>
    		<artifactId>proxool-cglib</artifactId>
    		<version>0.9.1</version>
    	</dependency>
            
            <dependency>
                <groupId>com.oracle</groupId>
                <artifactId>ojdbc6</artifactId>
                <version>11.2.0.1.0</version>
            </dependency>
            
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
                <version>${slf4j.version}</version>
            </dependency>
            <dependency>
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-core</artifactId>
                <version>${logback.version}</version>
            </dependency>
            <dependency>
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-classic</artifactId>
                <version>${logback.version}</version>
            </dependency>
            <dependency>
                <groupId>org.quartz-scheduler</groupId>
                <artifactId>quartz</artifactId>
                <version>2.2.1</version>
            </dependency>
            <dependency>
                <groupId>org.quartz-scheduler</groupId>
                <artifactId>quartz-jobs</artifactId>
                <version>2.2.1</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-batch</artifactId>
                <version>1.0.0.RELEASE</version>
            </dependency>
        </dependencies>
        <build>
            <finalName>faultDeal</finalName>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>2.5.1</version>
                    <configuration>
                        <source>1.7</source>
                        <target>1.7</target>
                        <encoding>UTF-8</encoding>
                    </configuration>
                </plugin>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-shade-plugin</artifactId>
                    <version>1.4</version>
                    <executions>
                        <execution>
                            <phase>package</phase>
                            <goals>
                                <goal>shade</goal>
                            </goals>
                            <configuration>
                                <transformers>
                                    <transformer
                                            implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                        <resource>META-INF/spring.handlers</resource>
                                    </transformer>
                                    <transformer
                                            implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                        <mainClass>cn.com.ustcinfo.datadeal.main.FaultDealMain</mainClass>
                                    </transformer>
                                    <transformer
                                            implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                        <resource>META-INF/spring.schemas</resource>
                                    </transformer>
                                </transformers>
                            </configuration>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    </project>

二、代码目录结构，此处的多数据源配置涉及到部分，所以只分享部分代码。

![20170823114448988][]

三、appContext.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"
    	   xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
    	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">
    
    	
    	<context:property-placeholder location="classpath:config/jdbc.properties" />
    
    	
    	<bean id="gessDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
    		<property name="driver" value="${db.driverName}"/>
    		<property name="driverUrl" value="${db.url}"/>
    		<property name="user" value="${db.username}"/>
    		<property name="password" value="${db.password}"/>
    		<property name="alias" value="${db.alias}"/>
    		<property name="maximumActiveTime" value="${db.maxActiveTime}"/>
    		<property name="maximumConnectionCount" value="${db.maxConnCount}"/>
    		<property name="minimumConnectionCount" value="${db.minConnCount}"/>
    		<property name="houseKeepingTestSql" value="${db.testSql}"/>
    		<property name="trace" value="true"/>
    		<property name="testBeforeUse" value="true"/>
    	</bean>
    
    	
    	<bean id="fbDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
    		<property name="driver" value="${fb.driverName}"/>
    		<property name="driverUrl" value="${fb.url}"/>
    		<property name="user" value="${fb.username}"/>
    		<property name="password" value="${fb.password}"/>
    		<property name="alias" value="${fb.alias}"/>
    		<property name="maximumActiveTime" value="${fb.maxActiveTime}"/>
    		<property name="maximumConnectionCount" value="${fb.maxConnCount}"/>
    		<property name="minimumConnectionCount" value="${fb.minConnCount}"/>
    		<property name="houseKeepingTestSql" value="${fb.testSql}"/>
    		<property name="trace" value="true"/>
    		<property name="testBeforeUse" value="true"/>
    	</bean>
    	
    	
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="gessDataSource"/>
    	</bean>
    	<bean id="jdbcTempfb" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="fbDataSource"/>
    	</bean>
    
    	
    	<bean id="stdSheetDealDao" class="cn.com.ustcinfo.datadeal.dao.StdSheetDealDao">
    		<property name="jdbcTemplate" ref="jdbcTemplate"/>
    	</bean>
    
    	<bean id="fbSheetDealDao" class="cn.com.ustcinfo.datadeal.dao.FbSheetDealDao">
    		<property name="jdbcTempfb" ref="jdbcTempfb"/>
    	</bean>
    
    	
    	<bean id="txManagerOne" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="gessDataSource"/>
    	</bean>
    
    	
    	<import resource="scheduleContext.xml" />
    
    </beans>

ps:用到的jdbc.properties内容如下，数据库信息自己填写。

db.driverName=oracle.jdbc.driver.OracleDriver
    
    #db.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =168.65.80.65)(PORT = 8765))(ADDRESS = (PROTOCOL = TCP)(HOST = 168.65.80.66)(PORT = 8765))(LOAD_BALANCE = yes)(FAILOVER = ON)(CONNECT_DATA =(SERVICE_NAME = gzzc)))
    db.url=jdbc:oracle:thin:@178.65.80.65:1521:gzzc
    db.username=*
    db.password=*
    db.alias=*
    db.maxActiveTime=3600000
    db.maxConnCount=50
    db.minConnCount=1
    db.testSql=select 'x' from DUAL
    
    hibernate.dialect=org.hibernate.dialect.OracleDialect
    hibernate.show_sql=true
    
    fb.driverName=oracle.jdbc.driver.OracleDriver
    #fb.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 189.25.36.89)(PORT = 8765))(ADDRESS = (PROTOCOL = TCP)(HOST = 189.25.36.90)(PORT = 8765))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = gzsj)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 60)(DELAY = 5))))
    #test
    fb.url=jdbc:oracle:thin:@189.25.36.99:8765:gzsj
    fb.username=*
    fb.password=*
    fb.alias=*
    fb.maxActiveTime=3600000
    fb.maxConnCount=30
    fb.minConnCount=1
    fb.testSql=select 'x' from DUAL
    
    #ps this is a example

四、CommonServiceContextLoad.java加载类

package cn.com.ustcinfo.datadeal.common;
    
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.FileSystemXmlApplicationContext;
    
    /**
     * 加载配置文件工具类
     *
     */
    public final class CommonServiceContextLoad {
    
        private static ApplicationContext ctx = null;
    
        public static ApplicationContext init() {
            if (ctx == null) {
                ctx = new FileSystemXmlApplicationContext("classpath:appContext.xml");
            }
            return ctx;
        }
    
        public static Object getBean(String beanName) {
            return init().getBean(beanName);
        }
        
        /**
         *传入bean id
         * @param beanName 获取到对应bean之后不需要强制转换
         * @return
         */
    //    public static <T> T getBean(Class<T> clazz) {
    //    	return init().getBean(clazz);
    //    }
    }

五、BeanProvider.java获取bean类

package cn.com.ustcinfo.datadeal.common;
    
    import org.springframework.transaction.PlatformTransactionManager;
    
    import cn.com.ustcinfo.datadeal.dao.StdSheetDealDao;
    import cn.com.ustcinfo.datadeal.dao.FbSheetDealDao;
    
    /**
     * 取Spring管理的bean实例
     *
     */
    public class BeanProvider {
    
        // 获取stdSheetDealDao
        public static StdSheetDealDao getStdSheetDealDao() {
            return (StdSheetDealDao) CommonServiceContextLoad.getBean("stdSheetDealDao");
        }
    
        // 获取fbSheetDealDao
        public static FbSheetDealDao getFbSheetDealDao() {
            return (FbSheetDealDao) CommonServiceContextLoad.getBean("fbSheetDealDao");
        }
    
        // 事务
        public static PlatformTransactionManager getTransactionObj() {
            return (PlatformTransactionManager) CommonServiceContextLoad.getBean("txManagerOne");
        }
    
    }

六、选用数据源，在service层调用相应dao即可

（1）BeanProvider.getFbSheetDealDao()获取到bean为“fbDataSource”数据源

（2）BeanProvider.getStdSheetDealDao()获取bean为“gessDataSource”数据源

（3）利用得到的dao对象去调用相应的crud方法即可完成对数据的操作

[20170823114448988]: /images/20220609/9bb251c9c37844bf88d1ddd1dcfbb63a.png