由于项目需要,需要为我的springMCV项目配置mysql+oracle两种数据源,运行时进行切换。由于技术水平有限,断断续续用了一周时间解决了这个小问题(比较懒),特来发篇博客,为以后需要的朋友提供参考。
首先,这个项目的多数据源配置建立在mysql已经搭建完成的基础上,这部分的源代码github上都有,网上也有不少教程,我就不作说明了,直接讲如何配置oracle。
oracle我用的版本是11g,具体版本是11.2.0.1.0。下载及安装,建表过程省略(详情请百度,建表推荐用pl/sql)
我的项目中,数据库的账号密码都是通过properties文件配置的,首先你需要配置好spring项目中的oracle的url,账号以及密码。
dataSource2.driver=oracle.jdbc.driver.OracleDriver
dataSource2.url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl
dataSource2.username=sys as sysdba
dataSource2.password=a123456
driver表示驱动名,username表示登录oracle的用户名,这里需要注意的是oracle非常注重数据库管理员的权限,因此在登录时需要指明登录者的身份,pl/sql以及navicat会让你选择以什么角色登录,而在spring项目中访问时,直接输入 用户名 + as sysdba 的形式作为用户名,密码就是先前安装oracle时设置的密码。
接下来配置spring的配置文件,来配置2个数据源,我主要是参考了网上的文章,大致原理是为不同数据源配置不同的sqlSessionFactory以及sqlSessionTemplate,为项目编写工具类,通过设置不同的键值切换数据源。
<bean id="mysqlDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- Filters -->
<!-- <property name="filters" value="config,stat" />
<property name="connectionProperties" value="config.decrypt=true" /> -->
<property name="filters" value="stat" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="1" />
<property name="minIdle" value="1" />
<property name="maxActive" value="20" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="false" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
</bean>
<!-- 数据源2 -->
<bean id="oracleDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="driverClassName" value="${dataSource2.driver}" />
<property name="url" value="${dataSource2.url}" />
<property name="username" value="${dataSource2.username}" />
<property name="password" value="${dataSource2.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="1" />
<property name="minIdle" value="1" />
<property name="maxActive" value="20" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="SELECT 'x' FROM DUAL" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="true" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="stat" />
</bean>
<bean id="oracleSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="oracleDataSource"/>
<property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/>
<!-- mapper和resultmap配置路径 -->
<property name="mapperLocations">
<list>
<!-- 表示在com.hoo目录下的任意包下的resultmap包目录中,以-resultmap.xml或-mapper.xml结尾所有文件 -->
<value>classpath*:/mapper/TestMapper.xml</value>
</list>
</property>
</bean>
<!-- 配置SqlSessionFactoryBean -->
<bean id="mysqlSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="mysqlDataSource"/>
<property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/>
<!-- mapper和resultmap配置路径 -->
<property name="mapperLocations">
<list>
<!-- 表示在com.hoo目录下的任意包下的resultmap包目录中,以-resultmap.xml或-mapper.xml结尾所有文件 (oracle和mysql扫描的配置和路径不一样,如果是公共的都扫描 这里要区分下,不然就报错 找不到对应的表、视图)-->
<value>classpath:com/zlzkj/app/mapper/*-mapper.xml</value>
</list>
</property>
</bean>
<!-- 配置自定义的SqlSessionTemplate模板,注入相关配置 -->
<bean id="sqlSessionTemplate" class="com.zlzkj.app.support.CustomSqlSessionTemplate">
<constructor-arg ref="mysqlSqlSessionFactory" />
<property name="targetSqlSessionFactorys">
<map>
<entry value-ref="oracleSqlSessionFactory" key="oracle"/>
<entry value-ref="mysqlSqlSessionFactory" key="mysql"/>
</map>
</property>
</bean>
<!-- 通过扫描的模式,扫描目录在com/hoo/任意目录下的mapper目录下,所有的mapper都需要继承SqlMapper接口的接口 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 注意注入sqlSessionTemplate -->
<property name="sqlSessionTemplateBeanName" value="sqlSessionTemplate"/>
<property name="basePackage" value="com.zlzkj.app.mapper,com.zlzkj.core.mybatis,com.zlzkj.app.omapper" />
</bean>
在设置oracle的mapper.xml映射文件时,注意要将文件放在resources文件夹下,我将xml和mapper.java放在同一文件下时总是报错invalid bound statement,最后是通过 点击打开链接 解决的,如果有知道原因的大神,希望不吝赐教。
另外,<constructor-arg ref="mysqlSqlSessionFactory" />这一句是配置我们这个项目的默认数据源,我的项目的菜单栏都是通过mysql访问获取的,如果项目本身搭建在mysql基础之上就需要将默认数据源设置成mysql,如果设置成oracle,你懂的,菜单栏的数据获取不到,sql语句就会报错了
到这为止,才仅仅是配置oracle的第一步。接下来,我们要尝试我们能与oracle进行连接了。
同mysql的jdbc驱动一样,我们需要将oracle的驱动加载到maven库中来,这个jar包你可以从官网下载,也可以从你已经安装的oracle文件夹的product\11.2.0\dbhome_1\jdbc文件夹下找到,不同的jar包对应不同的jdk版本,我用的是jdk7,对应的是ojdbc6.jar。我build path了一下这个jar包,并在pom.xml中进行配置,结果发现错误,依然没有找到这个jar包,update也没有作用。
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
</dependency>
最后发现,这个jar包,maven库并不包含,无法自己update出阿里(我的maven库是做项目时update出来的,没有自己安装独立的maven库),找到原因,解决就容易了。
首先,你需要安装独立的maven库,这个从官网下载即可,http://www.oracle.com/technetwork/indexes/downloads/index.html 这里是参考文章。
安装完成后别忘记配置环境变量(百度),oracle的jar包放在C:\Users\Administrator下,我之前也是随意放,后来发现报错,貌似放在各个盘的根目录下也可以,我是放在C盘下面的,这个自己尝试就好。
安装好以后,oracle的jar包连接驱动也算是配置好了,接下来是oracle的mapper类和sql映射文件,我是用过mybatis插件一键生成的,我的properties配置文件如下:
projectName=mchp
jdbc.connector=F\:\\app\\Administrator\\product\\11.2.0\\dbhome_1\\jdbc\\lib\\ojdbc6.jar
jdbc.driverClass=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:orcl
jdbc.username=sys as sysdba
jdbc.password=a123456
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<properties resource="generatorConfig1.properties" />
<classPathEntry location="${jdbc.connector}" />
<!-- 不生成example文件 -->
<context id="mybatis_model" targetRuntime="MyBatis3">
<!-- 自动转换字段名中的sql关键字 -->
<property name="autoDelimitKeywords" value="true" />
<!-- 删除注释 -->
<commentGenerator>
<property name="suppressAllComments" value="true" />
<property name="suppressDate" value="true" />
</commentGenerator>
<jdbcConnection driverClass="${jdbc.driverClass}"
connectionURL="${jdbc.url}"
userId="${jdbc.username}" password="${jdbc.password}">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver><span style="white-space:pre">
</span><pre name="code" class="html" style="line-height: 18px;"><span style="white-space:pre"> </span><!--这里是你生成的model文件的位置-->
<javaModelGenerator targetPackage="com.zlzkj.app.model"targetProject="${projectName}/src/main/java"><property name="enableSubPackages" value="true" /><property name="trimStrings" value="true" /></javaModelGenerator>
<span style="white-space:pre"> </span><!--这里是你生成的映射文件的位置-->
<sqlMapGenerator targetPackage="com.zlzkj.app.omapper"targetProject="${projectName}/src/main/java"></sqlMapGenerator>
<span style="white-space:pre"> </span><!--这里是你生成的映射文件的位置-->
<javaClientGenerator type="XMLMAPPER"targetPackage="com.zlzkj.app.omapper" targetProject="${projectName}/src/main/java"><property name="enableSubPackages" value="true" /></javaClientGenerator>
<span style="white-space:pre"> </span><!--这里是你自己的表名-->
<table tableName="z_test" domainObjectName="Test"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
selectByExampleQueryId="false">
</table>
</context>
</generatorConfiguration>
跟mysql的差不多,拷一个你可用的mysql配置文件,改一下驱动,url,地址,账号和密码就好(账号不要忘记加as sysdba),右键生成,如果生成成功,且和你oracle建的表一致,那说明你和oracle的对接成功了。
接下来也是我们文章的关键一步,配置数据源的切换,如何把我们配置的oracle真正使用起来
你需要写2个java工具类
package com.zlzkj.app.support;
import static java.lang.reflect.Proxy.newProxyInstance;
import static org.apache.ibatis.reflection.ExceptionUtil.unwrapThrowable;
import static org.mybatis.spring.SqlSessionUtils.closeSqlSession;
import static org.mybatis.spring.SqlSessionUtils.getSqlSession;
import static org.mybatis.spring.SqlSessionUtils.isSqlSessionTransactional;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.MyBatisExceptionTranslator;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.dao.support.PersistenceExceptionTranslator;
import org.springframework.util.Assert;
public class CustomSqlSessionTemplate extends SqlSessionTemplate {
private final SqlSessionFactory sqlSessionFactory;
private final ExecutorType executorType;
private final SqlSession sqlSessionProxy;
private final PersistenceExceptionTranslator exceptionTranslator;
private Map<Object, SqlSessionFactory> targetSqlSessionFactorys;
private SqlSessionFactory defaultTargetSqlSessionFactory;
public void setTargetSqlSessionFactorys(Map<Object, SqlSessionFactory> targetSqlSessionFactorys) {
this.targetSqlSessionFactorys = targetSqlSessionFactorys;
}
public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {
this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
}
public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
}
public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {
this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration()
.getEnvironment().getDataSource(), true));
}
public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
PersistenceExceptionTranslator exceptionTranslator) {
super(sqlSessionFactory, executorType, exceptionTranslator);
this.sqlSessionFactory = sqlSessionFactory;
this.executorType = executorType;
this.exceptionTranslator = exceptionTranslator;
this.sqlSessionProxy = (SqlSession) newProxyInstance(
SqlSessionFactory.class.getClassLoader(),
new Class[] { SqlSession.class },
new SqlSessionInterceptor());
this.defaultTargetSqlSessionFactory = sqlSessionFactory;
}
@Override
public SqlSessionFactory getSqlSessionFactory() {
SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys.get(CustomerContextHolder.getContextType());
if (targetSqlSessionFactory != null) {
return targetSqlSessionFactory;
} else if (defaultTargetSqlSessionFactory != null) {
return defaultTargetSqlSessionFactory;
} else {
Assert.notNull(targetSqlSessionFactorys, "Property 'targetSqlSessionFactorys' or 'defaultTargetSqlSessionFactory' are required");
Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactorys' are required");
}
return this.sqlSessionFactory;
}
@Override
public Configuration getConfiguration() {
return this.getSqlSessionFactory().getConfiguration();
}
public ExecutorType getExecutorType() {
return this.executorType;
}
public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {
return this.exceptionTranslator;
}
/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement) {
return this.sqlSessionProxy.<T> selectOne(statement);
}
/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement, Object parameter) {
return this.sqlSessionProxy.<T> selectOne(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, String mapKey) {
return this.sqlSessionProxy.<K, V> selectMap(statement, mapKey);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey);
}
/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey, rowBounds);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement) {
return this.sqlSessionProxy.<E> selectList(statement);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter) {
return this.sqlSessionProxy.<E> selectList(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
return this.sqlSessionProxy.<E> selectList(statement, parameter, rowBounds);
}
/**
* {@inheritDoc}
*/
public void select(String statement, ResultHandler handler) {
this.sqlSessionProxy.select(statement, handler);
}
/**
* {@inheritDoc}
*/
public void select(String statement, Object parameter, ResultHandler handler) {
this.sqlSessionProxy.select(statement, parameter, handler);
}
/**
* {@inheritDoc}
*/
public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
}
/**
* {@inheritDoc}
*/
public int insert(String statement) {
return this.sqlSessionProxy.insert(statement);
}
/**
* {@inheritDoc}
*/
public int insert(String statement, Object parameter) {
return this.sqlSessionProxy.insert(statement, parameter);
}
/**
* {@inheritDoc}
*/
public int update(String statement) {
return this.sqlSessionProxy.update(statement);
}
/**
* {@inheritDoc}
*/
public int update(String statement, Object parameter) {
return this.sqlSessionProxy.update(statement, parameter);
}
/**
* {@inheritDoc}
*/
public int delete(String statement) {
return this.sqlSessionProxy.delete(statement);
}
/**
* {@inheritDoc}
*/
public int delete(String statement, Object parameter) {
return this.sqlSessionProxy.delete(statement, parameter);
}
/**
* {@inheritDoc}
*/
public <T> T getMapper(Class<T> type) {
return getConfiguration().getMapper(type, this);
}
/**
* {@inheritDoc}
*/
public void commit() {
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void commit(boolean force) {
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void rollback() {
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void rollback(boolean force) {
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void close() {
throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
}
/**
* {@inheritDoc}
*/
public void clearCache() {
this.sqlSessionProxy.clearCache();
}
/**
* {@inheritDoc}
*/
public Connection getConnection() {
return this.sqlSessionProxy.getConnection();
}
/**
* {@inheritDoc}
* @since 1.0.2
*/
public List<BatchResult> flushStatements() {
return this.sqlSessionProxy.flushStatements();
}
/**
* Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
* unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to
* the {@code PersistenceExceptionTranslator}.
*/
private class SqlSessionInterceptor implements InvocationHandler {
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
final SqlSession sqlSession = getSqlSession(
CustomSqlSessionTemplate.this.getSqlSessionFactory(),
CustomSqlSessionTemplate.this.executorType,
CustomSqlSessionTemplate.this.exceptionTranslator);
try {
Object result = method.invoke(sqlSession, args);
if (!isSqlSessionTransactional(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory())) {
// force commit even on non-dirty sessions because some databases require
// a commit/rollback before calling close()
sqlSession.commit(true);
}
return result;
} catch (Throwable t) {
Throwable unwrapped = unwrapThrowable(t);
if (CustomSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
Throwable translated = CustomSqlSessionTemplate.this.exceptionTranslator
.translateExceptionIfPossible((PersistenceException) unwrapped);
if (translated != null) {
unwrapped = translated;
}
}
throw unwrapped;
} finally {
closeSqlSession(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory());
}
}
}
}
上述java类重写了sqlSessionTemplate
package com.zlzkj.app.support;
public abstract class CustomerContextHolder {
public final static String SESSION_FACTORY_MYSQL = "mysql";
public final static String SESSION_FACTORY_ORACLE = "oracle";
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setContextType(String contextType) {
contextHolder.set(contextType);
}
public static String getContextType() {
return contextHolder.get();
}
public static void clearContextType() {
contextHolder.remove();
}
}
这是一个抽象类,在service中调用切换数据源
最后在service中调用进行切换
CustomerContextHolder.setContextType(CustomerContextHolder.SESSION_FACTORY_ORACLE);
try {
Test test = mapper.selectByPrimaryKey((short)1);
System.out.println(">>>>>>"+test.getName());
} catch (Exception e) {
e.printStackTrace();
}
好了,如果数据能读出来,那就恭喜你,你配置多数据源成功了。
我的感悟:
1) oracle驱动需要手动安装maven库进行配置,单纯的build path以及update没有作用
2) oracle登录时对角色权限要求严格,不要忘记 用户名+as sysdba
3) 我使用mybatis生成的mapper以及xml放在java包下面时service中调用sql语句总是无法匹配到对应的xml,放在resources目录下才可以,当然别忘记配置spring的xml文件