最近项目中遇到要调用其他数据库的情况,本来只使用一个MySQL数据库,但随着项目内容越来越多,逻辑越来越复杂。原来一个数据库已经不够用了,需要分库分表,所以决定扩充数据库,正好Spring可以灵活的扩充数据库。下面简单写一篇博文,记录下多数据库配置的过程。
1.项目结构如下图:
其中mkhl和ulab分别对应两个数据库模块,同时也对应两个不同的功能模块。
2.整个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>ULAB_UsersProvider</groupId>
- <artifactId>ULAB_UsersProvider</artifactId>
- <version>0.0.1-SNAPSHOT</version>
- <packaging>jar</packaging>
- <name>ULAB_UsersProvider</name>
- <url>http://maven.apache.org</url>
- <properties>
- <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
- <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
- <spring.version>4.0.0.RELEASE</spring.version>
- <jackson.version>2.5.0</jackson.version>
- </properties>
- <dependencies>
- <!-- junit -->
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>4.8.1</version>
- <scope>test</scope>
- </dependency>
- <dependency>
- <groupId>javax.activation</groupId>
- <artifactId>activation</artifactId>
- <version>1.1</version>
- </dependency>
- <dependency>
- <groupId>commons-lang</groupId>
- <artifactId>commons-lang</artifactId>
- <version>2.6</version>
- </dependency>
- <!-- spring -->
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-web</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-context</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-webmvc</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-aspects</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-context-support</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-tx</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-jdbc</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-orm</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-oxm</artifactId>
- <version>${spring.version}</version>
- </dependency>
- <!-- dubbo -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>dubbo</artifactId>
- <version>2.5.3</version>
- </dependency>
- <!-- zookeeper -->
- <dependency>
- <groupId>org.apache.zookeeper</groupId>
- <artifactId>zookeeper</artifactId>
- <version>3.4.6</version>
- </dependency>
- <dependency>
- <groupId>com.github.sgroschupf</groupId>
- <artifactId>zkclient</artifactId>
- <version>0.1</version>
- </dependency>
- <!-- mysql连接 -->
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>5.1.34</version>
- </dependency>
- <dependency>
- <groupId>commons-pool</groupId>
- <artifactId>commons-pool</artifactId>
- <version>1.6</version>
- </dependency>
- <dependency>
- <groupId>commons-dbcp</groupId>
- <artifactId>commons-dbcp</artifactId>
- <version>1.4</version>
- </dependency>
- <!-- oracle连接 -->
- <!-- <dependency>
- <groupId>com.oracle</groupId>
- <artifactId>ojdbc14</artifactId>
- <version>10.2.0.1.0</version>
- </dependency> -->
- <!-- SQL Server连接 -->
- <dependency>
- <groupId>com.microsoft.sqlserver</groupId>
- <artifactId>sqljdbc4</artifactId>
- <version>4.0</version>
- </dependency>
- <!-- 数据源 -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid</artifactId>
- <version>1.0.12</version>
- </dependency>
- <dependency>
- <groupId>org.aspectj</groupId>
- <artifactId>aspectjweaver</artifactId>
- <version>1.8.4</version>
- </dependency>
- <!-- ehcache -->
- <dependency>
- <groupId>net.sf.ehcache</groupId>
- <artifactId>ehcache-core</artifactId>
- <version>2.6.10</version>
- </dependency>
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis-ehcache</artifactId>
- <version>1.0.0</version>
- </dependency>
- <!-- log4j -->
- <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.17</version>
- </dependency>
- <dependency>
- <groupId>com.googlecode.log4jdbc</groupId>
- <artifactId>log4jdbc</artifactId>
- <version>1.2</version>
- <scope>runtime</scope>
- </dependency>
- <dependency>
- <groupId>net.bull.javamelody</groupId>
- <artifactId>javamelody-core</artifactId>
- <version>1.50.0</version>
- </dependency>
- <!-- servlet -->
- <!-- <dependency>
- <groupId>javax.servlet</groupId>
- <artifactId>servlet-api</artifactId>
- <version>2.5</version>
- </dependency>
- <dependency>
- <groupId>javax.servlet</groupId>
- <artifactId>jstl</artifactId>
- <version>1.2</version>
- </dependency> -->
- <!-- json -->
- <dependency>
- <groupId>org.codehaus.jackson</groupId>
- <artifactId>jackson-mapper-asl</artifactId>
- <version>1.9.13</version>
- </dependency>
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>fastjson</artifactId>
- <version>1.2.3</version>
- </dependency>
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-annotations</artifactId>
- <version>${jackson.version}</version>
- </dependency>
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-core</artifactId>
- <version>${jackson.version}</version>
- </dependency>
- <dependency>
- <groupId>com.fasterxml.jackson.core</groupId>
- <artifactId>jackson-databind</artifactId>
- <version>${jackson.version}</version>
- </dependency>
- <!-- mybatis核心包 -->
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis</artifactId>
- <version>3.2.4</version>
- </dependency>
- <!-- mybatis/spring包 -->
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis-spring</artifactId>
- <version>1.2.2</version>
- </dependency>
- <!-- 文件上传 -->
- <!-- <dependency>
- <groupId>commons-io</groupId>
- <artifactId>commons-io</artifactId>
- <version>2.4</version>
- </dependency>
- <dependency>
- <groupId>commons-fileupload</groupId>
- <artifactId>commons-fileupload</artifactId>
- <version>1.2.2</version>
- </dependency>
- <dependency>
- <groupId>jexcelapi</groupId>
- <artifactId>jxl</artifactId>
- <version>2.4.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.9</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>3.9</version>
- </dependency> -->
- <!-- <dependency>
- <groupId>redis.clients</groupId>
- <artifactId>jedis</artifactId>
- <version>2.0.0</version>
- <type>jar</type>
- </dependency>
- <dependency>
- <groupId>javax.xml.rpc</groupId>
- <artifactId>javax.xml.rpc-api</artifactId>
- <version>1.1.1</version>
- </dependency>
- <dependency>
- <groupId>wsdl4j</groupId>
- <artifactId>wsdl4j</artifactId>
- <version>1.6.3</version>
- </dependency>
- <dependency>
- <groupId>org.codehaus.xfire</groupId>
- <artifactId>xfire-all</artifactId>
- <version>1.2.6</version>
- </dependency> -->
- <!-- axis2 -->
- <!-- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-aar-maven-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-adb</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-adb-codegen</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-ant-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-clustering</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-codegen</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-integration</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-java2wsdl</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-java2wsdl-maven-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-jaxbri</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-jaxws</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-jaxws-integration</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-jibx</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-json</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-kernel</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-mar-maven-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-metadata</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-mtompolicy</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-repo-maven-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-resource-bundle</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-saaj</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-soapmonitor-servlet</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-spring</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-transport-http</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-transport-local</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-wsdl2code-maven-plugin</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>axis2-xmlbeans</artifactId>
- <version>1.6.2</version>
- </dependency>
- <dependency>
- <groupId>org.apache.axis2</groupId>
- <artifactId>org.apache.axis2.osgi</artifactId>
- <version>1.6.2</version>
- </dependency> -->
- <!-- Mybatis_pagehelper -->
- <dependency>
- <groupId>com.github.pagehelper</groupId>
- <artifactId>pagehelper</artifactId>
- <version>4.0.1</version>
- </dependency>
- </dependencies>
- <build>
- <finalName>ULAB_UsersProvider1.0</finalName>
- <plugins>
- <plugin>
- <groupId>org.apache.maven.plugins</groupId>
- <artifactId>maven-compiler-plugin</artifactId>
- <version>3.1</version>
- <configuration>
- <source>1.7</source>
- <target>1.7</target>
- </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>
- <filters>
- <filter>
- <artifact>*:*</artifact>
- <excludes>
- <exclude>META-INF/*.SF</exclude>
- <exclude>META-INF/*.DSA</exclude>
- <exclude>META-INF/*.RSA</exclude>
- </excludes>
- </filter>
- </filters>
- <transformers>
- <transformer
- implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
- <mainClass>com.ouc.ulab.main.UsersProvider</mainClass>
- </transformer>
- <transformer
- implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
- <resource>META-INF/spring.handlers</resource>
- </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>
3.接下来介绍与Spring和MyBatis相关的配置文件:
1)数据库配置文件:jdbc.properties
- #jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
- #jdbc.url=jdbc\:oracle\:thin\:@192.168.151.239\:1521\:orcl
- #jdbc.username=ULab
- #jdbc.password=123456
- #jdbc.validationQuery=SELECT 1 FROM DUAL
- jdbc.B.driverClassName=com.mysql.jdbc.Driver
- jdbc.B.url=jdbc\:mysql\://110.126.70.48\:3310/ulab?useUnicode\=true&characterEncoding\=UTF-8
- jdbc.B.username=mkhl
- jdbc.B.password=123456
- jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
- jdbc.url=jdbc\:sqlserver\://110.135.60.233\:1433;DatabaseName=OUCJiance
- jdbc.username=OUCUser
- jdbc.password=OUC,789
- jdbc.initialSize=1
- jdbc.maxActive=20
- jdbc.minIdle=0
- jdbc.maxWait=60000
- jdbc.validationQuery=SELECT 1
- jdbc.testOnBorrow=false
- jdbc.testOnReturn=false
- jdbc.testWhileIdle=true
- jdbc.timeBetweenEvictionRunsMillis=60000
- jdbc.minEvictableIdleTimeMillis=25200000
- jdbc.removeAbandoned=true
- jdbc.removeAbandonedTimeout=1800
- jdbc.logAbandoned=true
- jdbc.filters=stat
2)ehcache缓存配置:ehcache.xml
- <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ehcache.xsd" updateCheck="true" monitoring="autodetect"
- dynamicConfig="true">
- <diskStore path="java.io.tmpdir" />
- <defaultCache
- maxElementsInMemory="10000"
- eternal="false"
- timeToIdleSeconds="120"
- timeToLiveSeconds="120"
- overflowToDisk="true"
- diskSpoolBufferSizeMB="30"
- maxElementsOnDisk="10000000"
- diskExpiryThreadIntervalSeconds="120"
- memoryStoreEvictionPolicy="LRU"
- />
- <cache name="mavenCache"
- maxElementsInMemory="10000"
- eternal="false"
- timeToIdleSeconds="120"
- timeToLiveSeconds="120"
- overflowToDisk="true"
- diskSpoolBufferSizeMB="30"
- maxElementsOnDisk="10000000"
- diskExpiryThreadIntervalSeconds="120"
- memoryStoreEvictionPolicy="LRU"
- />
- </ehcache>
3)日志配置:log4j.properties
- log4j.rootLogger=INFO,stdout,logfile
- log4j.appender.stdout=org.apache.log4j.ConsoleAppender
- log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
- log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
- log4j.appender.logfile=org.apache.log4j.DailyRollingFileAppender
- log4j.appender.logfile.File=D:/log4j/U-LAB/usersProvide_log
- log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
- log4j.appender.logfile.DatePattern='_'yyyy-MM-dd'.log'
- log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} %-5p [%t] %c.%M(%L) - %m%n
- log4j.logger.com.ibatis=debug
- log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
- log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
- log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
- log4j.logger.java.sql.Connection=debug
- log4j.logger.java.sql.Statement=debug
- log4j.logger.java.sql.PreparedStatement=debug,stdout
- log4j.logger.jdbc.sqlonly=OFF
- log4j.logger.jdbc.sqltiming=OFF
- log4j.logger.jdbc.audit=OFF
- log4j.logger.jdbc.resultset=OFF
- log4j.logger.jdbc.connection=OFF
4)mybatis基础配置:mybatis-configure.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <configuration>
- <settings>
- <setting name="cacheEnabled" value="true" />
- <setting name="jdbcTypeForNull" value="NULL" />
- </settings>
- </configuration>
5)数据库连接池Druid配置:spring-druid.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:p="http://www.springframework.org/schema/p"
- xmlns:aop="http://www.springframework.org/schema/aop"
- xmlns:cache="http://www.springframework.org/schema/cache"
- xmlns:task="http://www.springframework.org/schema/task"
- xmlns:context="http://www.springframework.org/schema/context"
- xmlns:jee="http://www.springframework.org/schema/jee"
- xmlns:tx="http://www.springframework.org/schema/tx"
- xsi:schemaLocation="
- http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
- http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-4.0.xsd
- http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd
- 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/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
- http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
- <!-- 配置druid监控spring jdbc -->
- <bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor" />
- <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
- <property name="patterns">
- <list>
- <value>com.ouc.ulab.service.*</value>
- <value>com.ouc.mkhl.service.*</value>
- </list>
- </property>
- </bean>
- <aop:config>
- <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" />
- </aop:config>
- </beans>
6)MyBatis数据库相关的配置,双数据库配置主要内容在这里: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:p="http://www.springframework.org/schema/p"
- xmlns:aop="http://www.springframework.org/schema/aop" xmlns:cache="http://www.springframework.org/schema/cache"
- xmlns:task="http://www.springframework.org/schema/task" xmlns:context="http://www.springframework.org/schema/context"
- xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
- xsi:schemaLocation="
- http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
- http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-4.0.xsd
- http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd
- 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/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
- http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
- <!-- 配置数据源1:SQLServer -->
- <bean name="dataSource1" class="com.alibaba.druid.pool.DruidDataSource"
- init-method="init" destroy-method="close">
- <property name="driverClassName" value="${jdbc.driverClassName}" />
- <property name="url" value="${jdbc.url}" />
- <property name="username" value="${jdbc.username}" />
- <property name="password" value="${jdbc.password}" />
- <!-- 初始化连接大小 -->
- <property name="initialSize" value="${jdbc.initialSize}" />
- <!-- 连接池最大使用连接数量 -->
- <property name="maxActive" value="${jdbc.maxActive}" />
- <!-- 连接池最小空闲 -->
- <property name="minIdle" value="${jdbc.minIdle}" />
- <!-- 获取连接最大等待时间 -->
- <property name="maxWait" value="${jdbc.maxWait}" />
- <property name="validationQuery" value="${jdbc.validationQuery}" />
- <property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
- <property name="testOnReturn" value="${jdbc.testOnReturn}" />
- <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
- <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
- <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
- <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
- <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
- <!-- 打开removeAbandoned功能 -->
- <property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
- <!-- 1800秒,也就是30分钟 -->
- <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
- <!-- 关闭abanded连接时输出错误日志 -->
- <property name="logAbandoned" value="${jdbc.logAbandoned}" />
- <!-- 监控数据库 -->
- <property name="filters" value="${jdbc.filters}" />
- </bean>
- <!-- 配置数据源2:MySQL -->
- <bean name="dataSource2" class="com.alibaba.druid.pool.DruidDataSource"
- init-method="init" destroy-method="close">
- <property name="driverClassName" value="${jdbc.B.driverClassName}" />
- <property name="url" value="${jdbc.B.url}" />
- <property name="username" value="${jdbc.B.username}" />
- <property name="password" value="${jdbc.B.password}" />
- <!-- 初始化连接大小 -->
- <property name="initialSize" value="${jdbc.initialSize}" />
- <!-- 连接池最大使用连接数量 -->
- <property name="maxActive" value="${jdbc.maxActive}" />
- <!-- 连接池最小空闲 -->
- <property name="minIdle" value="${jdbc.minIdle}" />
- <!-- 获取连接最大等待时间 -->
- <property name="maxWait" value="${jdbc.maxWait}" />
- <property name="validationQuery" value="${jdbc.validationQuery}" />
- <property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
- <property name="testOnReturn" value="${jdbc.testOnReturn}" />
- <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
- <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
- <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
- <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
- <property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
- <!-- 打开removeAbandoned功能 -->
- <property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
- <!-- 1800秒,也就是30分钟 -->
- <property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
- <!-- 关闭abanded连接时输出错误日志 -->
- <property name="logAbandoned" value="${jdbc.logAbandoned}" />
- <!-- 监控数据库 -->
- <property name="filters" value="${jdbc.filters}" />
- </bean>
- <!-- myBatis文件 -->
- <bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="dataSource1" />
- <property name="mapperLocations" value="classpath:com/ouc/ulab/mapping/*.xml" />
- <property name="typeAliasesPackage" value="com.ouc.ulab.model" />
- <property name="plugins">
- <array>
- <bean class="com.github.pagehelper.PageHelper">
- <property name="properties">
- <value>
- dialect=sqlserver
- </value>
- </property>
- </bean>
- </array>
- </property>
- <property name="configLocation" value="classpath:mybatis-configure.xml" />
- </bean>
- <bean id="sqlSessionFactory2" class="org.mybatis.spring.SqlSessionFactoryBean">
- <property name="dataSource" ref="dataSource2" />
- <property name="mapperLocations" value="classpath:com/ouc/mkhl/mapping/*.xml" />
- <property name="typeAliasesPackage" value="com.ouc.mkhl.model" />
- <property name="configLocation" value="classpath:mybatis-configure.xml" />
- </bean>
- <bean id = "mapperScannerConfigurer1" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.ouc.ulab.dao" />
- <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory1" />
- </bean>
- <bean id = "mapperScannerConfigurer2" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
- <property name="basePackage" value="com.ouc.mkhl.dao" />
- <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory2" />
- </bean>
- <!-- 事务管理 -->
- <bean id="transactionManager"
- class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- <property name="dataSource" ref="dataSource1" />
- </bean>
- </beans>
7)Spring中Bean的注解扫描识别配置:spring.xml,此处为了发布Dubbo服务。
- <?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"
- xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
- xmlns:util="http://www.springframework.org/schema/util"
- xsi:schemaLocation="
- http://www.springframework.org/schema/beans
- http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
- http://www.springframework.org/schema/tx
- http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
- http://www.springframework.org/schema/aop
- http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
- http://www.springframework.org/schema/util
- http://www.springframework.org/schema/util/spring-util-3.1.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/context/spring-context-3.0.xsd">
- <aop:aspectj-autoproxy/>
- <!-- 自动扫描含有@Service将其注入为bean -->
- <context:component-scan base-package="com.ouc.ulab.service"/>
- <context:component-scan base-package="com.ouc.ulab.service.impl"/>
- <context:component-scan base-package="com.ouc.mkhl.service"/>
- <context:component-scan base-package="com.ouc.mkhl.service.impl"/>
- <context:annotation-config />
- <!--引入配置属性文件 -->
- <context:property-placeholder location="classpath:jdbc.properties"/>
- </beans>
8)发布Dubbo接口配置:spring-dubbo.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:dubbo="http://code.alibabatech.com/schema/dubbo"
- xsi:schemaLocation="http://www.springframework.org/schema/beans
- http://www.springframework.org/schema/beans/spring-beans.xsd
- http://code.alibabatech.com/schema/dubbo
- http://code.alibabatech.com/schema/dubbo/dubbo.xsd">
- <!-- 提供方应用信息,用于计算依赖关系 -->
- <dubbo:application name="ULAB_DubboProvider" />
- <!-- 使用multicast广播注册中心暴露服务地址 <dubbo:registry address="multicast://224.5.6.7:1234" /> -->
- <dubbo:registry id="localUsersRegistry" address="zookeeper://127.0.0.1:2181"></dubbo:registry>
- <!-- 用dubbo协议在21000端口暴露服务 -->
- <dubbo:protocol name="dubbo" port="21000" />
- <!-- 声明需要暴露的服务接口 -->
- <dubbo:service interface="com.ouc.ulab.service.VSupplierUsersService" ref="vSupplierUsersService" registry="localUsersRegistry" />
- <dubbo:service interface="com.ouc.mkhl.service.UsersEvaluateService" ref="usersEvaluateService" registry="localUsersRegistry" />
- </beans>
4.以下以ulab模块为例,介绍相关的功能包:dao,model,mapping,service,service.impl,main。
1)model数据库表实体类包:VSupplyUser类
- package com.ouc.ulab.model;
- import java.io.Serializable;
- public class VSupplyUser implements Serializable {
- private static final long serialVersionUID = 12L;
- private String supplycode;
- private String supplyname;
- private String supplypass;
- public String getSupplycode() {
- return supplycode;
- }
- public void setSupplycode(String supplycode) {
- this.supplycode = supplycode == null ? null : supplycode.trim();
- }
- public String getSupplyname() {
- return supplyname;
- }
- public void setSupplyname(String supplyname) {
- this.supplyname = supplyname == null ? null : supplyname.trim();
- }
- public String getSupplypass() {
- return supplypass;
- }
- public void setSupplypass(String supplypass) {
- this.supplypass = supplypass == null ? null : supplypass.trim();
- }
- }
2)数据接口Dao包:VSupplyUserMapper
- package com.ouc.ulab.dao;
- import java.util.List;
- import com.ouc.ulab.model.VSupplyUser;
- public interface VSupplyUserMapper {
- int insert(VSupplyUser record);
- int insertSelective(VSupplyUser record);
- public VSupplyUser getVSupplyUserByCode(String supplycode);
- public List <VSupplyUser> getAllVSupplyUser();
- }
3)SQL语句配置包:VSupplyUserMapper.xml
- <?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.ouc.ulab.dao.VSupplyUserMapper" >
- <resultMap id="BaseResultMap" type="VSupplyUser" >
- <result column="SupplyCode" property="supplycode" jdbcType="NVARCHAR" />
- <result column="SupplyName" property="supplyname" jdbcType="NVARCHAR" />
- <result column="SupplyPass" property="supplypass" jdbcType="NVARCHAR" />
- </resultMap>
- <select id="getVSupplyUserByCode" resultType="VSupplyUser" parameterType="java.lang.String" >
- select * from V_SupplyUser
- where SupplyCode = #{#{supplycode,jdbcType=NVARCHAR}
- </select>
- <select id="getAllVSupplyUser" resultMap="BaseResultMap" >
- select * from V_SupplyUser
- </select>
- <insert id="insert" parameterType="VSupplyUser" >
- insert into V_SupplyUser (SupplyCode, SupplyName, SupplyPass
- )
- values (#{supplycode,jdbcType=NVARCHAR}, #{supplyname,jdbcType=NVARCHAR}, #{supplypass,jdbcType=NVARCHAR}
- )
- </insert>
- <insert id="insertSelective" parameterType="VSupplyUser" >
- insert into V_SupplyUser
- <trim prefix="(" suffix=")" suffixOverrides="," >
- <if test="supplycode != null" >
- SupplyCode,
- </if>
- <if test="supplyname != null" >
- SupplyName,
- </if>
- <if test="supplypass != null" >
- SupplyPass,
- </if>
- </trim>
- <trim prefix="values (" suffix=")" suffixOverrides="," >
- <if test="supplycode != null" >
- #{supplycode,jdbcType=NVARCHAR},
- </if>
- <if test="supplyname != null" >
- #{supplyname,jdbcType=NVARCHAR},
- </if>
- <if test="supplypass != null" >
- #{supplypass,jdbcType=NVARCHAR},
- </if>
- </trim>
- </insert>
- </mapper>
4)Dubbo服务接口包:VSupplierUsersService
- package com.ouc.ulab.service;
- import java.util.List;
- import com.ouc.ulab.model.VSupplyUser;
- public interface VSupplierUsersService {
- public VSupplyUser getVSupplyUserByCode(String supplycode);
- public List <VSupplyUser> getAllVSupplyUser();
- }
5)Dubbo服务接口具体实现包:VSupplierUsersSerImpl
- package com.ouc.ulab.service.impl;
- import java.util.List;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- import com.ouc.ulab.dao.VSupplyUserMapper;
- import com.ouc.ulab.model.VSupplyUser;
- import com.ouc.ulab.service.VSupplierUsersService;
- @Service("vSupplierUsersService")
- public class VSupplierUsersSerImpl implements VSupplierUsersService{
- @Autowired VSupplyUserMapper vSuppUserMapper;
- @Override
- public VSupplyUser getVSupplyUserByCode(String supplycode){
- return vSuppUserMapper.getVSupplyUserByCode(supplycode);
- }
- @Override
- public List <VSupplyUser> getAllVSupplyUser(){
- return vSuppUserMapper.getAllVSupplyUser();
- }
- }
5.发布接口测试Main:UsersProvider
- package com.ouc.ulab.main;
- import java.util.ArrayList;
- import java.util.List;
- import org.apache.log4j.Logger;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.context.support.ClassPathXmlApplicationContext;
- import com.ouc.ulab.service.VSupplierUsersService;
- import com.ouc.ulab.model.VSupplyUser;
- import com.ouc.mkhl.service.UsersEvaluateService;
- import com.ouc.mkhl.model.PlanCount;
- public class UsersProvider {
- private static VSupplierUsersService vSuppUsersSer;
- private static UsersEvaluateService usersEvaluateSer;
- @Autowired
- public static void main(String[] args) throws Exception {
- try {
- @SuppressWarnings("resource")
- ClassPathXmlApplicationContext cxt = new ClassPathXmlApplicationContext(
- new String[] { "classpath:spring.xml",
- "classpath:spring-mybatis.xml",
- "classpath:spring-dubbo.xml" });
- cxt.start();
- vSuppUsersSer = (VSupplierUsersService) cxt
- .getBean("vSupplierUsersService");
- String supplyCode = "V98758";
- VSupplyUser user = vSuppUsersSer.getVSupplyUserByCode(supplyCode);
- System.out.println("供应商用户信息:");
- System.out.println("V码:" + user.getSupplycode());
- System.out.println("供应商名称:" + user.getSupplyname());
- System.out.println("供应商登录密码:" + user.getSupplypass());
- List<VSupplyUser> supplyUserList = new ArrayList<VSupplyUser>();
- supplyUserList = vSuppUsersSer.getAllVSupplyUser();
- System.out.println("供应商用户总量:"+supplyUserList.size());
- usersEvaluateSer = (UsersEvaluateService) cxt.getBean("usersEvaluateService");
- List<PlanCount> planCountList = new ArrayList<PlanCount>();
- planCountList = usersEvaluateSer.getPlanCountByYearAndVCode("2016", "V13187");
- System.out.println("问题方案统计结果:" + planCountList.size());
- PlanCount planCount = new PlanCount();
- planCount = usersEvaluateSer.getPlanCountByYearAndMonthAndVCode("2016", "V13187", 4);
- System.out.println("问题方案:" + planCount.getPlanNum() + planCount.getQuestionNum());
- System.in.read(); // 模拟开启
- } catch (Exception e) {
- e.printStackTrace();
- System.out.println(e.getMessage());
- Logger log = Logger.getLogger(UsersProvider.class);
- log.error(e.getMessage()); // 将异常输出到文件
- }
- }
- }
6.运行结果如下图: