Tomcat JDBC 连接池配置(Oracle RAC)

环境

Oracle服务器环境

  • 系统:CentOS Linux release 7.6.1810
  • Oracle:11.2.0.4 RAC
  • IP:192.168.10.160/161
  • Oracle Servicename:zydb

Tomcat服务器环境

  • 系统:CentOS Linux release 7.6.1810
  • IP:192.168.20.43
  • tomcat:Apache Tomcat/9.0.34
  • JDK:openjdk version "1.8.0_181"
  • ojdbc:ojdbc7.jar
  • CATALINA_BASE:/usr/local/tomcat/apache-tomcat-9.0.34

Oracle 配置

准备测试用户和测试表

create user v_test identified by v_test;
create table V_TEST.TB1
(
  p1 INTEGER,
  p2 INTEGER not null
);
insert into tb1 values (1,678);

Tomcat 配置

导入ojbc7.jar

将ojbc7.jar导入至目录$CATALINA_BASE/lib。(不同的jdk使用的ojdc版本不同,需注意)

[root@zsdb lib]# pwd
/usr/local/tomcat/apache-tomcat-9.0.34/lib
[root@zsdb lib]# ll
总用量 13952
.......
-rw-r----- 1 root root 2989263 4月   3 20:04 ecj-4.15.jar
-rw-r----- 1 root root   89666 4月   3 20:04 el-api.jar
-rw-r----- 1 root root  170856 4月   3 20:04 jasper-el.jar
-rw-r----- 1 root root  562942 4月   3 20:04 jasper.jar
-rw-r----- 1 root root   27959 4月   3 20:04 jaspic-api.jar
-rw-r----- 1 root root   63220 4月   3 20:04 jsp-api.jar
-rw-r--r-- 1 root root 3397734 8月   6 14:10 ojdbc7.jar         <==============此文件
-rw-r----- 1 root root  283288 4月   3 20:04 servlet-api.jar
......

配置 context.xml

调整$CATALINA_BASE/conf/context.xml 文件,添加jdbc链接配置。

  • 单机配置
<Context path="/testwar" reloadable="true" docBase="/usr/local/tomcat/apache-tomcat-9.0.34/webapps/testwar" workDir="/usr/local/tomcat/apache-tomcat-9.0.34/webapps/testwar/work" >
    <Resource name="jdbc/myoracle" auth="Container"
          type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@192.168.10.158:1521:zydb1"
          username="v_test" password="v_test" maxActive="20" maxIdle="10"
          maxWait="-1"/> 
</Context>
  • RAC配置
<Context path="/testwar" reloadable="true" docBase="/usr/local/tomcat/apache-tomcat-9.0.34/webapps/testwar" workDir="/usr/local/tomcat/apache-tomcat-9.0.34/webapps/testwar/work" >
    <Resource name="jdbc/myoracle" auth="Container"
          type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE = yes)(FAILOVER = yes)(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.160)(PORT = 1522))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.161)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = zydb)))"
          username="v_test" password="v_test" maxActive="20" maxIdle="10"
          maxWait="-1"/> 
</Context>

war包准备

在$CATALINA_BASE/webapps目录创建testwar文件夹,包含如下目录、文件

  • testwar
[root@zsdb webapps]# pwd
/usr/local/tomcat/apache-tomcat-9.0.34/webapps
[root@zsdb webapps]# tree -L 2 testwar
testwar           <==========文件夹
├── test.jsp      <==========文件
├── WEB-INF       <==========文件夹
│?? └── web.xml   <==========文件
└── work          <==========文件夹
    └── org 
  • test.jsp
<%@ page contentType="text/html; charset=UTF-8" %>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%@ page import="java.util.Hashtable,javax.sql.DataSource "%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataSource Welcome Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>

<body>
<table cellspacing="0" border=1>
    <thead>
        <tr>
            <th>p1</th>
            <th>p2</th>
        </tr>
    </thead>
    <tbody>
    <%
        try {
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/myoracle");
              Connection conn = ds.getConnection();
              Statement stmt = conn.createStatement();
             ResultSet rst = stmt.executeQuery(" select p1, p2 from tb1");
              while (rst.next()) {
                  out.println("<tr>");
                out.println("<td>"+rst.getString("p1")+"</td>");
                out.println("<td>"+rst.getString("p2")+"</td>");
                out.println("</tr>");
            }
            conn.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    %>
    </tbody>
    </table>
</body>
</html>
  • web.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.
-->
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                      http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
  version="4.0"
  metadata-complete="true">

  <display-name>Welcome to Tomcat</display-name>
  <description>
     Welcome to Tomcat
  </description>
  <resource-ref>
        <description>Oracle Datasource example</description>
        <res-ref-name>jdbc/myoracle</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

启动测试

启动tomcat程序。

[root@zsdb bin]# pwd
/usr/local/tomcat/apache-tomcat-9.0.34/bin
[root@zsdb bin]# ./startup.sh 

通过页面访问http://192.168.20.43:8080/testwar/test.jsp
Tomcat JDBC 连接池配置(Oracle RAC)
数据库监听日志

  • listener.log
(VERSION=186647552)) * status * 0
10-AUG-2020 16:28:38 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=dbrac1)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER_ORACLE)(VERSION=186647552)) * status * 0
Thu Aug 06 16:28:39 2020
10-AUG-2020 16:28:39 * service_update * zydb1 * 0
10-AUG-2020 16:28:47 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVER=DEDICATED)(SERVICE_NAME=zydb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.43)(PORT=44521)) * establish * zydb * 0             <=================== 刚刚访问的记录
......

至此,测试完全通过。

惊喜链接:https://www.cnblogs.com/monjeo/p/9330464.html

Tomcat JDBC 连接池配置(Oracle RAC)

上一篇:垂死挣扎还是涅槃重生 -- Delphi XE5 公布会归来感想


下一篇:mysql 5.7升级8.0 ERROR 1726 (HY000): Storage engine