Tomcat配置Oracle數據源

2019-11-14 15:37:47

字體：大中小

來源：轉載

供稿：網友

開發環境：Eclipse luna、tomcat 7、Oracle

配置Oracle datasource步驟

第一步：打開tomcat目錄下的 context.xml 文件，添加 Resource 標簽內容

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <!-- 3   Licensed to the Apache Software Foundation (ASF) under one or more 4   contributor license agreements.  See the NOTICE file distributed with 5   this work for additional information regarding copyright ownership. 6   The ASF licenses this file to You under the Apache License, Version 2.0 7   (the "License"); you may not use this file except in compliance with 8   the License.  You may obtain a copy of the License at 9 10       http://www.apache.org/licenses/LICENSE-2.011 12   Unless required by applicable law or agreed to in writing, software13   distributed under the License is distributed on an "AS IS" BASIS,14   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either exPRess or implied.15   See the License for the specific language governing permissions and16   limitations under the License.17 --><!-- The contents of this file will be loaded for each web application --><Context>18 19     <!-- Default set of monitored resources -->20     <WatchedResource>WEB-INF/web.xml</WatchedResource>21 22     <!-- Uncomment this to disable session persistence across Tomcat restarts -->23     <!--24     <Manager pathname="" />25     -->26 27     <!-- Uncomment this to enable Comet connection tacking (provides events28          on session expiration as well as webapp lifecycle) -->29     <!--30     <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />31     -->32      33      <!-- oracle datasource -->      34      <Resource name="jdbc/orcl" auth="Container" type="javax.sql.DataSource"35            maxActive="100" maxIdel="30" maxWait="1000"36            username="scott" passWord="goodluck" driverClassName="oracle.jdbc.driver.OracleDriver"37            url="jdbc:oracle:thin://localhost:1521/orcl?allowMultiQueries=true"/>           38 39 </Context>

第二步：編輯工程下面的 web.xml文件，插入 resource-ref 標簽內容

 1 <?xml version="1.0" encoding="UTF-8"?> 2 <web-app xmlns: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>12   13   <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>  19   20 </web-app>

第三步：數據庫連接程序中添加三行代碼

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

第四步：測試程序

重構 Java Web MVC實例數據庫連接程序

 1 package com.test.dao; 2  3 import java.sql.Connection; 4 import java.sql.SQLException; 5  6 import javax.naming.Context; 7 import javax.naming.InitialContext; 8 import javax.naming.NamingException; 9 import javax.sql.DataSource;10 11 public class BaseDao {12     Connection conn = null;13 14     public BaseDao() {15     }16     17     public Connection dbConnection() throws SQLException{18         try {19             Context sourceCtx  = new InitialContext();20             DataSource ds = (DataSource) sourceCtx.lookup("java:comp/env/jdbc/orcl");21             conn = ds.getConnection();22             System.out.println("----> Connection Success！");23         } catch (NamingException e) {24             e.printStackTrace();25         }26         27         return conn;28     }29     30     public void dbDisconnection() throws SQLException{31         conn.close();32         System.out.println("----> Connection End！");33     }    34 }

重構調用處的代碼

 1 package com.test.service; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8  9 import com.test.bean.EmpBean;10 import com.test.dao.BaseDao;11 12 public class EmpService {13     int idx = 1;14         15     public ArrayList<EmpBean> getEmpList(EmpBean eb){16         17         Connection conn = null;18         PreparedStatement pstmt = null;19         ResultSet rs = null;20         21         ArrayList<EmpBean> empList = new ArrayList<EmpBean>();22 23         BaseDao baseDao = new BaseDao();24         try {25             conn = baseDao.dbConnection();26         } catch (SQLException e1) {27             e1.printStackTrace();28         }29 30         // 3. 執行SQL語句31         StringBuffer sqlBf = new StringBuffer();32         sqlBf.setLength(0);33         34         sqlBf.append("SELECT  EMPNO                                             /n");35         sqlBf.append("      , ENAME                                             /n");36         sqlBf.append("      , JOB                                               /n");37         sqlBf.append("      , MGR                                               /n");38         sqlBf.append("      , TO_CHAR(HIREDATE, 'YYYYMMDD')  HIREDATE           /n");39         sqlBf.append("      , SAL                                               /n");40         sqlBf.append("      , COMM                                              /n");41         sqlBf.append("      , DEPTNO                                            /n");42         sqlBf.append("FROM    EMP                                               /n");43         sqlBf.append("WHERE   ENAME LIKE UPPER(?) || '%'                        /n");44 45         System.out.println(sqlBf.toString());46         47         try {48             pstmt = conn.prepareStatement(sqlBf.toString());49             idx = 1;50             pstmt.setString(idx++, eb.getEname());51             52             // 4. 獲取結果集53             rs = pstmt.executeQuery();54             while (rs.next()) {55                 EmpBean emp = new EmpBean();56                 57                 emp.setEmpNo(rs.getInt("empno"));58                 emp.setEname(rs.getString("ename"));59                 emp.setJob(rs.getString("job"));60                 emp.setMgr(rs.getInt("mgr"));61                 emp.setHiredate(rs.getString("hiredate"));62                 emp.setSal(rs.getDouble("sal"));63                 emp.setComm(rs.getDouble("comm"));64                 emp.setDeptno(rs.getInt("deptno"));65                 66                 empList.add(emp);67             }            68         } catch (SQLException e) {69             e.printStackTrace();70         }71         72         try {73             baseDao.dbDisconnection();74         } catch (SQLException e) {75             e.printStackTrace();76         }77         return empList;78     }79 }