链接两种数据库 有一个需求,要从SQL 2005 中的读出某个字段,然后插入到MYSQL表中的某个字段,两种数据库怎么链接,百度说配置多个数据源,但是实际操作的时候怎么改变呢,谁能给个DEMO ,谢谢了。数据库MySQLSQL 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 简单的说:sqlserver 的变量命名 --->sqlServer,mysql 的变量命名 ---> mySql,变量不一样,怎么会混淆? 给你个参考环境:1、spring+mybatis步骤:1、设置多个数据源:我用的是c3p0<!-- 配置c3p0数据源 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driverClass}" /> <property name="jdbcUrl" value="${jdbc.url}" /> <property name="user" value="${jdbc.user}" /> <property name="password" value="${jdbc.password}" /> <property name="minPoolSize" value="${jdbc.minPoolSize}" /> <property name="maxPoolSize" value="${jdbc.maxPoolSize}" /> <property name="maxIdleTime" value="${jdbc.maxIdleTime}" /> <property name="acquireIncrement" value="${jdbc.acquireIncrement}" /> <property name="maxStatements" value="${jdbc.maxStatements}" /> <property name="initialPoolSize" value="${jdbc.initialPoolSize}" /> <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}" /> <property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" /> <property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" /> <property name="testConnectionOnCheckin" value="${jdbc.testConnectionOnCheckin}" /> </bean> <bean id="drdc_dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${dwa.jdbc.driverClass}" /> <property name="jdbcUrl" value="${dwa.jdbc.url}" /> <property name="user" value="${dwa.jdbc.user}" /> <property name="password" value="${dwa.jdbc.password}" /> <property name="minPoolSize" value="${dwa.jdbc.minPoolSize}" /> <property name="maxPoolSize" value="${dwa.jdbc.maxPoolSize}" /> <property name="maxIdleTime" value="${dwa.jdbc.maxIdleTime}" /> <property name="acquireIncrement" value="${dwa.jdbc.acquireIncrement}" /> <property name="maxStatements" value="${dwa.jdbc.maxStatements}" /> <property name="initialPoolSize" value="${dwa.jdbc.initialPoolSize}" /> <property name="idleConnectionTestPeriod" value="${dwa.jdbc.idleConnectionTestPeriod}" /> <property name="acquireRetryAttempts" value="${dwa.jdbc.acquireRetryAttempts}" /> <property name="checkoutTimeout" value="${dwa.jdbc.checkoutTimeout}" /> <property name="testConnectionOnCheckin" value="${dwa.jdbc.testConnectionOnCheckin}" /> </bean>2、设置一个动态的进行处理<bean id="dynamic_dataSource" class="com.fencer.datacenter.route.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="dataSource" value-ref="dataSource" /> <entry key="drdc_dataSource" value-ref="drdc_dataSource"/> </map> </property> <property name="defaultTargetDataSource" ref="dataSource" /> </bean>3、配置关系<!-- 配置mybitasSqlSessionFactoryBean --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="classpath:mybatis-config.xml"></property> <property name="dataSource" ref="dynamic_dataSource" /> </bean>完整的配置文件:里面的路径改成你自己的<context:annotation-config/> <!-- 开启annotation的注解扫描,通配符方式扫描 --> <context:component-scan base-package="com.fencer.*" /> <!-- 数据库配置文件位置 --> <bean id="propertyConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:jdbc.properties</value> </list> </property> </bean> <!-- 自动扫描 mybatis mapper接口 <bean name="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name="basePackage" value="mappers"></property> <property name="sqlSessionFactory" ref="sqlSessionFactory"></property> </bean> --> <!-- 配置c3p0数据源 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driverClass}" /> <property name="jdbcUrl" value="${jdbc.url}" /> <property name="user" value="${jdbc.user}" /> <property name="password" value="${jdbc.password}" /> <property name="minPoolSize" value="${jdbc.minPoolSize}" /> <property name="maxPoolSize" value="${jdbc.maxPoolSize}" /> <property name="maxIdleTime" value="${jdbc.maxIdleTime}" /> <property name="acquireIncrement" value="${jdbc.acquireIncrement}" /> <property name="maxStatements" value="${jdbc.maxStatements}" /> <property name="initialPoolSize" value="${jdbc.initialPoolSize}" /> <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}" /> <property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}" /> <property name="checkoutTimeout" value="${jdbc.checkoutTimeout}" /> <property name="testConnectionOnCheckin" value="${jdbc.testConnectionOnCheckin}" /> </bean> <!-- 自动扫描 mybatis mapper接口 <bean name="mapperScannerConfigurer1" class="org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name="basePackage" value="mappers"></property> <property name="sqlSessionFactory" ref="sqlSessionFactory1"></property> </bean> --> <bean id="drdc_dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${dwa.jdbc.driverClass}" /> <property name="jdbcUrl" value="${dwa.jdbc.url}" /> <property name="user" value="${dwa.jdbc.user}" /> <property name="password" value="${dwa.jdbc.password}" /> <property name="minPoolSize" value="${dwa.jdbc.minPoolSize}" /> <property name="maxPoolSize" value="${dwa.jdbc.maxPoolSize}" /> <property name="maxIdleTime" value="${dwa.jdbc.maxIdleTime}" /> <property name="acquireIncrement" value="${dwa.jdbc.acquireIncrement}" /> <property name="maxStatements" value="${dwa.jdbc.maxStatements}" /> <property name="initialPoolSize" value="${dwa.jdbc.initialPoolSize}" /> <property name="idleConnectionTestPeriod" value="${dwa.jdbc.idleConnectionTestPeriod}" /> <property name="acquireRetryAttempts" value="${dwa.jdbc.acquireRetryAttempts}" /> <property name="checkoutTimeout" value="${dwa.jdbc.checkoutTimeout}" /> <property name="testConnectionOnCheckin" value="${dwa.jdbc.testConnectionOnCheckin}" /> </bean> <bean id="dynamic_dataSource" class="com.fencer.datacenter.route.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="dataSource" value-ref="dataSource" /> <entry key="drdc_dataSource" value-ref="drdc_dataSource"/> </map> </property> <property name="defaultTargetDataSource" ref="dataSource" /> </bean> <!-- 配置mybitasSqlSessionFactoryBean --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="classpath:mybatis-config.xml"></property> <property name="dataSource" ref="dynamic_dataSource" /> </bean> <!-- 配置SqlSessionTemplate --> <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"> <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean> <!-- 事务配置 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <!-- 多数据源配置 --> <aop:config proxy-target-class="true"> <aop:pointcut id="dsRoutePointcut" expression="execution(* com.fencer.datacenter.hdc.service.*.impl.*ServiceImpl.*(..))" /> <aop:advisor advice-ref="routeInterceptor" pointcut-ref="dsRoutePointcut" order="1"/> </aop:config> <bean id="routeInterceptor" class="com.fencer.datacenter.route.DataSourceRouteInterceptor"> </bean> <!-- 开启cglib代理 --> <aop:config proxy-target-class="true"> <aop:pointcut id="servicePointcut" expression="execution(* com.fencer.datacenter.hdc.service.*.impl.*ServiceImpl.*(..))" /> <aop:advisor advice-ref="txAdvice" pointcut-ref="servicePointcut" order="2"/> </aop:config> <!-- 使用annotation注解方式配置事务 --> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="*" propagation="REQUIRED" /> </tx:attributes> </tx:advice>几个类:public class DynamicDataSource extends AbstractRoutingDataSource { /* (non-Javadoc) * @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey() */ @Override protected Object determineCurrentLookupKey() {// System.out.println("DataSourceRouteContext.getDataSourceKey()=" + DataSourceRouteContext.getDataSourceKey()); return DataSourceRouteContext.getDataSourceKey(); }}public class DataSourceRouteInterceptor implements MethodInterceptor { public Object invoke(MethodInvocation invocation) throws Throwable {// System.out.println("in....."); Method interceptedMethod = invocation.getMethod();// System.out.println(interceptedMethod.getName()); DataSource dataSource = interceptedMethod.getAnnotation(DataSource.class); String dsKeyHis = DataSourceRouteContext.getDataSourceKey(); if(dataSource != null) { String dsKey = dataSource.value(); DataSourceRouteContext.setDataSourceKey(dsKey); } try { return invocation.proceed(); } finally { DataSourceRouteContext.setDataSourceKey(dsKeyHis); } }} 在service实现类里面,在需要使用非默认数据源的方法上添加@DataSource("数据源名称")来指定要使用的数据源。暂时只支持一个方法使用一个数据源的情况。 TxDataSource是支持事务的数据源,在使用到多个数据库的时候就有用了,因为它提供两阶段提交(Two Phrase Commit),支持分布式事务。而DataSource是一个非事务性的数据源,在操作多个数据库时就没法进行事务管理了。然如果你的应用中只对一个数据库操作的话,用DataSource相对来说还要好,效率要好一些。这个貌似一个思路 奇怪!Tomcat启动成功,访问却一直404 有没有用过apache camel框架的? jasperreport 客户端打印 对一张表更新出异常了 大家帮帮忙 在线等帮助 如何通过超链接执行js脚本 封装在JAVABEAN里就得不到name值 请问谁有Oracle 10g XDK (它是关于jaxb的工具)? jdom里如何增加一个子节点? Struts+EJB调试出错,谢谢了 为什么我的jboss运行后需要密码和用户名? 【求助,在线等】Java连oracle报sid错误 web service 客户端 调用不了远程服务
sqlserver 的变量命名 --->sqlServer,
mysql 的变量命名 ---> mySql,变量不一样,怎么会混淆?
环境:
1、spring+mybatis步骤:
1、设置多个数据源:我用的是c3p0<!-- 配置c3p0数据源 -->
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${jdbc.driverClass}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.user}" />
<property name="password" value="${jdbc.password}" />
<property name="minPoolSize" value="${jdbc.minPoolSize}" />
<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
<property name="maxIdleTime" value="${jdbc.maxIdleTime}" />
<property name="acquireIncrement"
value="${jdbc.acquireIncrement}" />
<property name="maxStatements" value="${jdbc.maxStatements}" />
<property name="initialPoolSize"
value="${jdbc.initialPoolSize}" />
<property name="idleConnectionTestPeriod"
value="${jdbc.idleConnectionTestPeriod}" />
<property name="acquireRetryAttempts"
value="${jdbc.acquireRetryAttempts}" />
<property name="checkoutTimeout"
value="${jdbc.checkoutTimeout}" />
<property name="testConnectionOnCheckin"
value="${jdbc.testConnectionOnCheckin}" />
</bean>
<bean id="drdc_dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${dwa.jdbc.driverClass}" />
<property name="jdbcUrl" value="${dwa.jdbc.url}" />
<property name="user" value="${dwa.jdbc.user}" />
<property name="password" value="${dwa.jdbc.password}" />
<property name="minPoolSize" value="${dwa.jdbc.minPoolSize}" />
<property name="maxPoolSize" value="${dwa.jdbc.maxPoolSize}" />
<property name="maxIdleTime" value="${dwa.jdbc.maxIdleTime}" />
<property name="acquireIncrement"
value="${dwa.jdbc.acquireIncrement}" />
<property name="maxStatements" value="${dwa.jdbc.maxStatements}" />
<property name="initialPoolSize"
value="${dwa.jdbc.initialPoolSize}" />
<property name="idleConnectionTestPeriod"
value="${dwa.jdbc.idleConnectionTestPeriod}" />
<property name="acquireRetryAttempts"
value="${dwa.jdbc.acquireRetryAttempts}" />
<property name="checkoutTimeout"
value="${dwa.jdbc.checkoutTimeout}" />
<property name="testConnectionOnCheckin"
value="${dwa.jdbc.testConnectionOnCheckin}" />
</bean>
2、设置一个动态的进行处理
<bean id="dynamic_dataSource" class="com.fencer.datacenter.route.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource" value-ref="dataSource" />
<entry key="drdc_dataSource" value-ref="drdc_dataSource"/>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource" />
</bean>
3、配置关系<!-- 配置mybitasSqlSessionFactoryBean -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis-config.xml"></property>
<property name="dataSource" ref="dynamic_dataSource" />
</bean>
完整的配置文件:里面的路径改成你自己的<context:annotation-config/>
<!-- 开启annotation的注解扫描,通配符方式扫描 -->
<context:component-scan base-package="com.fencer.*" />
<!-- 数据库配置文件位置 -->
<bean id="propertyConfig"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:jdbc.properties</value>
</list>
</property>
</bean>
<!-- 自动扫描 mybatis mapper接口
<bean name="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer" >
<property name="basePackage" value="mappers"></property>
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
</bean>
-->
<!-- 配置c3p0数据源 -->
<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${jdbc.driverClass}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.user}" />
<property name="password" value="${jdbc.password}" />
<property name="minPoolSize" value="${jdbc.minPoolSize}" />
<property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
<property name="maxIdleTime" value="${jdbc.maxIdleTime}" />
<property name="acquireIncrement"
value="${jdbc.acquireIncrement}" />
<property name="maxStatements" value="${jdbc.maxStatements}" />
<property name="initialPoolSize"
value="${jdbc.initialPoolSize}" />
<property name="idleConnectionTestPeriod"
value="${jdbc.idleConnectionTestPeriod}" />
<property name="acquireRetryAttempts"
value="${jdbc.acquireRetryAttempts}" />
<property name="checkoutTimeout"
value="${jdbc.checkoutTimeout}" />
<property name="testConnectionOnCheckin"
value="${jdbc.testConnectionOnCheckin}" />
</bean>
<!-- 自动扫描 mybatis mapper接口
<bean name="mapperScannerConfigurer1" class="org.mybatis.spring.mapper.MapperScannerConfigurer" >
<property name="basePackage" value="mappers"></property>
<property name="sqlSessionFactory" ref="sqlSessionFactory1"></property>
</bean>
-->
<bean id="drdc_dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${dwa.jdbc.driverClass}" />
<property name="jdbcUrl" value="${dwa.jdbc.url}" />
<property name="user" value="${dwa.jdbc.user}" />
<property name="password" value="${dwa.jdbc.password}" />
<property name="minPoolSize" value="${dwa.jdbc.minPoolSize}" />
<property name="maxPoolSize" value="${dwa.jdbc.maxPoolSize}" />
<property name="maxIdleTime" value="${dwa.jdbc.maxIdleTime}" />
<property name="acquireIncrement"
value="${dwa.jdbc.acquireIncrement}" />
<property name="maxStatements" value="${dwa.jdbc.maxStatements}" />
<property name="initialPoolSize"
value="${dwa.jdbc.initialPoolSize}" />
<property name="idleConnectionTestPeriod"
value="${dwa.jdbc.idleConnectionTestPeriod}" />
<property name="acquireRetryAttempts"
value="${dwa.jdbc.acquireRetryAttempts}" />
<property name="checkoutTimeout"
value="${dwa.jdbc.checkoutTimeout}" />
<property name="testConnectionOnCheckin"
value="${dwa.jdbc.testConnectionOnCheckin}" />
</bean>
<bean id="dynamic_dataSource" class="com.fencer.datacenter.route.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource" value-ref="dataSource" />
<entry key="drdc_dataSource" value-ref="drdc_dataSource"/>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource" />
</bean>
<!-- 配置mybitasSqlSessionFactoryBean -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis-config.xml"></property>
<property name="dataSource" ref="dynamic_dataSource" />
</bean>
<!-- 配置SqlSessionTemplate -->
<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
<!-- 事务配置 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 多数据源配置 -->
<aop:config proxy-target-class="true">
<aop:pointcut id="dsRoutePointcut"
expression="execution(* com.fencer.datacenter.hdc.service.*.impl.*ServiceImpl.*(..))" />
<aop:advisor advice-ref="routeInterceptor" pointcut-ref="dsRoutePointcut" order="1"/>
</aop:config>
<bean id="routeInterceptor" class="com.fencer.datacenter.route.DataSourceRouteInterceptor">
</bean>
<!-- 开启cglib代理 -->
<aop:config proxy-target-class="true">
<aop:pointcut id="servicePointcut" expression="execution(* com.fencer.datacenter.hdc.service.*.impl.*ServiceImpl.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="servicePointcut" order="2"/>
</aop:config>
<!-- 使用annotation注解方式配置事务 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="*" propagation="REQUIRED" />
</tx:attributes>
</tx:advice>
几个类:public class DynamicDataSource extends AbstractRoutingDataSource { /* (non-Javadoc)
* @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
*/
@Override
protected Object determineCurrentLookupKey() {
// System.out.println("DataSourceRouteContext.getDataSourceKey()=" + DataSourceRouteContext.getDataSourceKey());
return DataSourceRouteContext.getDataSourceKey();
}}public class DataSourceRouteInterceptor implements MethodInterceptor {
public Object invoke(MethodInvocation invocation) throws Throwable {
// System.out.println("in.....");
Method interceptedMethod = invocation.getMethod();
// System.out.println(interceptedMethod.getName());
DataSource dataSource = interceptedMethod.getAnnotation(DataSource.class);
String dsKeyHis = DataSourceRouteContext.getDataSourceKey();
if(dataSource != null) {
String dsKey = dataSource.value();
DataSourceRouteContext.setDataSourceKey(dsKey);
}
try {
return invocation.proceed();
} finally {
DataSourceRouteContext.setDataSourceKey(dsKeyHis);
}
}}