Tomcat配置Oracle数据源

开发环境：Eclipse luna、tomcat 7、Oracle

配置Oracle datasource步骤

第一步：打开tomcat目录下的 context.xml文件，添加 Resource 标签内容

1<?xml version="1.0" encoding="UTF-8"?>2<!--3Licensed to the Apache Software Foundation (ASF) under one or more4contributor license agreements. See the NOTICE file distributed with5this work for additional information regarding copyright ownership.6The ASF licenses this file to You under the Apache License, Version 2.07(the "License"); you may not use this file except in compliance with8the License. You may obtain a copy of the License at910http://www.apache.org/licenses/LICENSE-2.01112Unless required by applicable law or agreed to in writing, software13distributed under the License is distributed on an "AS IS" BASIS,14WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either exPRess or implied.15See the License for the specific language governing permissions and16limitations under the License.17--><!--The contents of this file will be loaded for each webapplication--><Context>1819<!--Default set of monitored resources-->20<WatchedResource>WEB-INF/web.xml</WatchedResource>2122<!--Uncomment this to disablesessionpersistence across Tomcat restarts-->23<!--24<Manager pathname="" />25-->2627<!--Uncomment this to enable Comet connection tacking (provides events28on session expiration as well as webapp lifecycle)-->29<!--30<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />31-->3233<!--oracle datasource-->34<Resourcename="jdbc/orcl"auth="Container"type="javax.sql.DataSource"35maxActive="100"maxIdel="30"maxWait="1000"36username="scott"passWord="goodluck"driverClassName="oracle.jdbc.driver.OracleDriver"37url="jdbc:oracle:thin://localhost:1521/orcl?allowMultiQueries=true"/>3839</Context>

第二步：编辑工程下面的 web.xml文件，插入resource-ref标签内容

1<?xml version="1.0" encoding="UTF-8"?>2<web-appxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns="http://java.sun.com/xml/ns/javaee"xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"id="WebApp_ID"version="3.0">3<display-name>web01</display-name>4<welcome-file-list>5<welcome-file>index.html</welcome-file>6<welcome-file>index.htm</welcome-file>7<welcome-file>index.jsp</welcome-file>8<welcome-file>default.html</welcome-file>9<welcome-file>default.htm</welcome-file>10<welcome-file>default.jsp</welcome-file>11</welcome-file-list>1213<resource-ref>14<description>DB Connection</description>15<res-ref-name>jdbc/orcl</res-ref-name>16<res-type>javax.sql.DataSource</res-type>17<res-auth>Container</res-auth>18</resource-ref>1920</web-app>

第三步：数据库连接程序中添加三行代码

1Context sourceCtx =newInitialContext();2DataSource ds = (DataSource) sourceCtx.lookup("java:comp/env/jdbc/orcl");3conn = ds.getConnection();

第四步：测试程序

重构 Java Web MVC实例 数据库连接程序

1packagecom.test.dao;23importjava.sql.Connection;4importjava.sql.SQLException;56importjavax.naming.Context;7importjavax.naming.InitialContext;8importjavax.naming.NamingException;9importjavax.sql.DataSource;1011publicclassBaseDao {12Connection conn =null;1314publicBaseDao() {15}1617publicConnection dbConnection()throwsSQLException{18try{19Context sourceCtx =newInitialContext();20DataSource ds = (DataSource) sourceCtx.lookup("java:comp/env/jdbc/orcl");21conn =ds.getConnection();22System.out.println("----> Connection Success！");23}catch(NamingException e) {24e.printStackTrace();25}2627returnconn;28}2930publicvoiddbDisconnection()throwsSQLException{31conn.close();32System.out.println("----> Connection End！");33}34}

重构调用处的代码

1packagecom.test.service;23importjava.sql.Connection;4importjava.sql.PreparedStatement;5importjava.sql.ResultSet;6importjava.sql.SQLException;7importjava.util.ArrayList;89importcom.test.bean.EmpBean;10importcom.test.dao.BaseDao;1112publicclassEmpService {13intidx = 1;1415publicArrayList<EmpBean>getEmpList(EmpBean eb){1617Connection conn =null;18PreparedStatement pstmt =null;19ResultSet rs =null;2021ArrayList<EmpBean> empList =newArrayList<EmpBean>();2223BaseDao baseDao =newBaseDao();24try{25conn =baseDao.dbConnection();26}catch(SQLException e1) {27e1.printStackTrace();28}2930//3. 执行SQL语句31StringBuffer sqlBf =newStringBuffer();32sqlBf.setLength(0);3334sqlBf.append("SELECT EMPNO \n");35sqlBf.append(" , ENAME \n");36sqlBf.append(" , JOB \n");37sqlBf.append(" , MGR \n");38sqlBf.append(" , TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE \n");39sqlBf.append(" , SAL \n");40sqlBf.append(" , COMM \n");41sqlBf.append(" , DEPTNO \n");42sqlBf.append("FROM EMP \n");43sqlBf.append("WHERE ENAME LIKE UPPER(?) || '%' \n");4445System.out.println(sqlBf.toString());4647try{48pstmt =conn.prepareStatement(sqlBf.toString());49idx = 1;50pstmt.setString(idx++, eb.getEname());5152//4. 获取结果集53rs =pstmt.executeQuery();54while(rs.next()) {55EmpBean emp =newEmpBean();5657emp.setEmpNo(rs.getInt("empno"));58emp.setEname(rs.getString("ename"));59emp.setJob(rs.getString("job"));60emp.setMgr(rs.getInt("mgr"));61emp.setHiredate(rs.getString("hiredate"));62emp.setSal(rs.getDouble("sal"));63emp.setComm(rs.getDouble("comm"));64emp.setDeptno(rs.getInt("deptno"));6566empList.add(emp);67}68}catch(SQLException e) {69e.printStackTrace();70}7172try{73baseDao.dbDisconnection();74}catch(SQLException e) {75e.printStackTrace();76}77returnempList;78}79}

第五步：重新启动web工程测试已有程序是不是访问正常

参考文章：http://pengjianbo1.iteye.com/blog/503326