以mysql和oracle数据库为例;
我项目以mysql为主。但需要去一个oracle数据库里查询数据,所以只有mysql里表的实体类,但没有oracle数据库实体类,所以配置mysql的数据源有实体类直接把数据源放到session工厂里用hibernate和数据库交互(session工厂是hibernate特有的,但hibernate是通过实体类的映射和数据库联系的),oracle数据源我选择用spring的<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
tomcat里context.xml配置文件里:
<?xml version='1.0' encoding='utf-8'?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<!-- Uncomment this to enable Comet connection tacking (provides events
on session expiration as well as webapp lifecycle) -->
<!--
<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
-->
<!---->
<Resource
name="jdbc/ims4"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
maxPoolSize="100"
minPoolSize="50"
acquireIncrement="5"
maxIdleTime = "1800"
acquireRetryAttempts = "100"
acquireRetryDelay = "1000"
testConnectionOnCheckin = "true"
automaticTestTable = "c3p0TestTable"
idleConnectionTestPeriod = "18000"
checkoutTimeout="3000"
factory="org.apache.naming.factory.BeanFactory"
user="xxx"
password="xxx"
driverClass="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://ip:端口/数据库?characterEncoding=utf8&autoReconnect=true"
/>
<!-- BO1
<Resource
name="jdbc/oracle"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
maxPoolSize="100"
minPoolSize="50"
acquireIncrement="5"
maxIdleTime = "1800"
acquireRetryAttempts = "100"
acquireRetryDelay = "1000"
testConnectionOnCheckin = "true"
automaticTestTable = "c3p0TestTable"
idleConnectionTestPeriod = "18000"
checkoutTimeout="3000"
factory="org.apache.naming.factory.BeanFactory"
user="xxx"
password="xxx"
driverClass="com.mysql.jdbc.Driver"
jdbcUrl="jdbc:mysql://ip:端口/数据库?characterEncoding=utf8&autoReconnect=true"
/> -->
<!-- BO2 -->
<Resource
name="jdbc/oracle"
auth="Container"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
idleConnectionTestPeriod="60"
maxPoolSize="50"
minPoolSize="2"
acquireIncrement="2"
user="xxx"
password="xxx"
driverClass="oracle.jdbc.driver.OracleDriver"
jdbcUrl="jdbc:oracle:thin:@ip:端口:数据库"
/>
</Context>
项目里接收:db.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- BO实验区 -->
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/ims4</value>
</property>
</bean>
<bean id="dataSource2" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/oracle</value>
</property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dataSource2" />
</property>
</bean>
<!-- BO试验区 -->
</beans>
mysql数据源直接用hibernate实现就可以(省略)
oracle数据源:
public static JdbcTemplate getJdbcTemplate() {
JdbcTemplate jdbcTemplate=(JdbcTemplate) getApplicationContext().getBean("jdbcTemplate");
return jdbcTemplate;
}
JdbcTemplate 的 queryForMap(sql)和queryForInt(sql);方法必须要有返回值,包装一下如下:
public static Map getJdbcTemplateMap(String sql){
try{
return getJdbcTemplate().queryForMap(sql);
}catch (EmptyResultDataAccessException e) {
return null;
}
}
附赠其他数据库的数据源配置:
<Context> 2 <!-- 使用C3P0配置针对MySQL数据库的JNDI数据源 --> 3 <Resource 4 name="jdbc/MysqlDataSource" 5 auth="Container" 6 factory="org.apache.naming.factory.BeanFactory" 7 type="com.mchange.v2.c3p0.ComboPooledDataSource" 8 driverClass="com.mysql.jdbc.Driver" 9 idleConnectionTestPeriod="60" 10 maxPoolSize="50" 11 minPoolSize="2" 12 acquireIncrement="2" 13 user="root" 14 password="root" 15 jdbcUrl="jdbc:mysql://192.168.1.144:3306/leadtest"/> 16 17 <!-- 使用C3P0配置针对Oracle数据库的JNDI数据源 --> 18 <Resource 19 name="jdbc/OracleDataSource" 20 auth="Container" 21 factory="org.apache.naming.factory.BeanFactory" 22 type="com.mchange.v2.c3p0.ComboPooledDataSource" 23 driverClass="oracle.jdbc.OracleDriver" 24 idleConnectionTestPeriod="60" 25 maxPoolSize="50" 26 minPoolSize="2" 27 acquireIncrement="2" 28 jdbcUrl="jdbc:oracle:thin:@192.168.1.229:1521:lead" 29 user="lead_oams" 30 password="p"/> 31 32 33 <!--使用C3P0配置针对SQLServer数据库的JNDI数据源--> 34 <Resource 35 name="jdbc/SqlServerDataSource" 36