tomcat里配置多数据源(数据库连接池) jndi 和项目连接 ssh框架

以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&amp;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&amp;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         auth="Container"
37         factory="org.apache.naming.factory.BeanFactory" 
38         type="com.mchange.v2.c3p0.ComboPooledDataSource"
39         driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
40         idleConnectionTestPeriod="60"
41         maxPoolSize="50" 
42         minPoolSize="2"
43         acquireIncrement="2" 
44         jdbcUrl="jdbc:sqlserver://192.168.1.51:1433;DatabaseName=demo"
45         user="sa" 
46         password="p@ssw0rd"/>
47 </Context>

在web.xml获得

<?xml version="1.0" encoding="UTF-8"?>
 2 <web-app version="2.5" 
 3     xmlns="http://java.sun.com/xml/ns/javaee" 
 4     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 5     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
 6     http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
 7   <welcome-file-list>
 8     <welcome-file>index.jsp</welcome-file>
 9   </welcome-file-list>
10   
11   <!-- 
12       JNDI配置的资源引用：
13   • res-ref-name：表示引用资源的名称
14   • res-type：此资源对应的类型为javax.sql.DataSource
15   • res-auth：容器授权管理
16    -->
17    <!--Oracle数据库JNDI数据源引用 -->
18   <resource-ref>
19       <description>Oracle DB Connection</description>
20       <res-ref-name>jdbc/OracleDataSource</res-ref-name>
21       <res-type>javax.sql.DataSource</res-type>
22       <res-auth>Container</res-auth>
23  </resource-ref>
24   
25   <!--MySQL数据库JNDI数据 -->
26   <resource-ref>
27       <description>MySQL DB Connection</description>
28       <res-ref-name>jdbc/MysqlDataSource</res-ref-name>
29       <res-type>javax.sql.DataSource</res-type>
30       <res-auth>Container</res-auth>
31   </resource-ref>

32   
33   <!--SQLServer数据库JNDI数据源引用 -->
34   <resource-ref>
35       <description>SQLServer DB Connection</description>
36       <res-ref-name>jdbc/SqlServerDataSource</res-ref-name>
37       <res-type>javax.sql.DataSource</res-type>
38       <res-auth>Container</res-auth>
39   </resource-ref>
40   
41 </web-app>

附赠JdbcTemplate的一些用法

1. <span style="font-size:24px;">package cn.itcast.jdbc.spring;  
2.   
3. import java.util.Date;  
4.   
5. import org.springframework.jdbc.core.BeanPropertyRowMapper;  
6. import org.springframework.jdbc.core.JdbcTemplate;  
7.   
8. import cn.itcast.jdbc.JdbcUtils;  
9. import cn.itcast.jdbc.domain.User;  
10.   
11. public class JdbcTemplateCreate {  
12.   
13.     //获取数据源(设置为static 是因为该jdbc多次被调用)  
14.     static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());  
15.       
16.     public static void main(String[] args) {  
17.         // TODO Auto-generated method stub  
18.         //这里为测试CRUD代码块  
19.         User user = new User();  
20. //      user.setName("小Q");  
21. //      user.setBirthday(new Date());  
22. //      user.setMoney(3000);  
23. //      text1(user);  
24.         user.setId(10);  
25. //      user.setName("BB");  
26. //      delete(user);  
27.         user  = query(8);  
28.         if (user == null) {  
29.             System.out.println("查询失败");  
30.         }else {  
31.             System.out.println("查询成功");  
32.         }  
33.     }  
34.       
35.     //插入，增加  
36.     public static void insert(User user) {  
37.         String sql = "insert into user(name,birthday,money)values(?,?,?)";  
38.         Object args[] = {user.getName(),user.getBirthday(),user.getMoney()};  
39.         int temp = jdbc.update(sql, args);  
40.         if (temp > 0) {  
41.             System.out.println("插入成功！");  
42.         }else{  
43.             System.out.println("插入失败");  
44.         }  
45.     }  
46.       
47.     //删除  
48.     public static void delete(int id) {  
49.         String sql = "delete from user where id = ?";  
50.         Object args[] = new Object[]{id};  
51.         int temp = jdbc.update(sql,args);  
52.         if (temp > 0) {  
53.             System.out.println("删除成功");  
54.         }else {  
55.             System.out.println("删除失败");  
56.         }  
57.     }  
58.       
59.     //更新  
60.     public static void update(User user) {  
61.         String sql = "update user set name = ? where id = ?";  
62.         Object args[] = new Object[]{user.getName(),user.getId()};  
63.         int temp = jdbc.update(sql,args);  
64.         if (temp > 0) {  
65.             System.out.println("更新成功");  
66.         }else {  
67.             System.out.println("更新失败");  
68.         }  
69.           
70.     }  
71.       
72.     //查询  
73.     public static User query(int id) {  
74.         String sql = "select * from user where id = ?";  
75.         Object args[] = new Object[]{id};  
76.         Object  user = jdbc.queryForObject(sql,args,new BeanPropertyRowMapper(User.class));  
77.         return (User)user;  
78.     }  
79.   
80. }  
81. </span>  

附赠一个多数据源博客：http://blog.csdn.net/wangpeng047/article/details/8866239