java jdbc oracle rac_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链接配置。

单机配置

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"/>

RAC配置

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"/>

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

DataSource Welcome Page

p1p2

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("

");

out.println("

"+rst.getString("p1")+"");

out.println("

"+rst.getString("p2")+"");

out.println("

");

}

conn.close();

} catch (Exception e){

e.printStackTrace();

}

%>

web.xml

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">

Welcome to Tomcat

Welcome to Tomcat

Oracle Datasource example

jdbc/myoracle

javax.sql.DataSource

Container

启动测试

启动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

数据库监听日志

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 <=================== 刚刚访问的记录

......

至此，测试完全通过。