Tomcat和PostgreSQL协同工作
学习用（备忘）
的目的是？
使用应用服务器（Tomcat）通过数据源连接到PostgreSQL。
使用JSP使用数据源来显示PostgreSQL的表数据。
环境 –
Note: The provided Chinese characters already represent the phrase “环境,” which translates to “environment” in English.
虚拟机：VirtualBox6.1
操作系统：Red Hat Enterprise Linux 8.5 版本（Ootpa）
Java：openjdk 版本 “17.0.3” 2022-04-19 LTS
Tomcat：apache-tomcat-9.0.62
PosgreSQL：PostgreSQL 13.6
前提条件仅需要一种选择
・确保已准备好连接目标数据库（PostgreSQL）。
・能够从应用服务器（Tomcat）连接到数据库。
→建立 PostgreSQL13.6。
1. 安装JAVA
[root@tomcat ]#
mkdir -pm 777 /tmp/Installer/java/
dnf install --downloadonly java-17-openjdk-devel --downloaddir=/tmp/Installer/java/
cd /tmp/Installer/java/;pwd
dnf localinstall ./*
>インストール済み:
> java-17-openjdk-1:17.0.3.0.6-2.el8_5.x86_64 java-17-openjdk-devel-1:17.0.3.0.6-2.el8_5.x86_64
> java-17-openjdk-headless-1:17.0.3.0.6-2.el8_5.x86_64 ttmkfdir-3.0.9-54.el8.x86_64
> xorg-x11-fonts-Type1-7.5-19.el8.noarch
>
>完了しました!
・Java的升级（从版本8升级到版本17）
[root@tomcat ]#
alternatives --config java
> 選択 コマンド
>-----------------------------------------------
>*+ 1 java-1.8.0-openjdk.x86_64 (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.302.b08-3.el8.x86_64/jre/bin/java)
> 2 java-17-openjdk.x86_64 (/usr/lib/jvm/java-17-openjdk-17.0.3.0.6-2.el8_5.x86_64/bin/java)
>
>Enter を押して現在の選択 [+] を保持するか、選択番号を入力します:2
[root@tomcat ]#
java -version
>openjdk version "17.0.3" 2022-04-19 LTS
>OpenJDK Runtime Environment 21.9 (build 17.0.3+6-LTS)
>OpenJDK 64-Bit Server VM 21.9 (build 17.0.3+6-LTS, mixed mode, sharing)
2. 安装Tomcat
>ユーザ作成
[root@tomcat ]#
useradd -m tomcat
>資材をダウンロード
[root@tomcat ]#
mkdir -pm 777 /tmp/Installer/tomcat
cd /tmp/Installer/tomcat/;pwd
wget https://dlcdn.apache.org/tomcat/tomcat-9/v9.0.62/bin/apache-tomcat-9.0.62.tar.gz
ll -h /tmp/Installer/tomcat/
>解凍
[root@tomcat ]#
tar -xvf apache-tomcat-9.0.62.tar.gz -C /opt
chown -R tomcat: /opt/apache-tomcat-9.0.62
#シンボリックリンク作成
ln -sv /opt/{apache-tomcat-9.0.62,tomcat}
创建Tomcat服务
～～～～～～～～～～～～～～～～～～～～～～～
#変数定義
cat<<EOF>/etc/sysconfig/tomcat
JAVA_HOME="/usr/lib/jvm/jre"
CATALINA_HOME="/opt/tomcat"
CATALINA_BASE="/opt/tomcat"
CATALINA_OPTS="-server -Xmx128m -Xms128m -XX:MaxMetaspaceSize=128m"
EOF
#確認
cat /etc/sysconfig/tomcat
～～～～～～～～～～～～～～～～～～～～～～～
#サービス定義
cat<<EOF>/usr/lib/systemd/system/tomcat.service
[Unit]
Description=Apache Tomcat application server.
After=network.target
[Service]
Type=forking
User=tomcat
Group=tomcat
EnvironmentFile=/etc/sysconfig/tomcat
ExecStart=/opt/tomcat/bin/startup.sh
ExecStop=/opt/tomcat/bin/shutdown.sh
[Install]
WantedBy=multi-user.target
EOF
#確認
cat /usr/lib/systemd/system/tomcat.service
～～～～～～～～～～～～～～～～～～～～～～～
#サービス起動
systemctl daemon-reload
systemctl enable tomcat.service
systemctl start tomcat.service
systemctl status tomcat.service
>● tomcat.service - Apache Tomcat application server.
> Loaded: loaded (/usr/lib/systemd/system/tomcat.service; enabled; vendor preset: disabled)
> Active: active (running) since Mon 2022-05-02 23:18:34 JST; 11h ago
> Process: 64206 ExecStop=/opt/tomcat/bin/shutdown.sh (code=exited, status=0/SUCCESS)
> Process: 64231 ExecStart=/opt/tomcat/bin/startup.sh (code=exited, status=0/SUCCESS)
> Main PID: 64239 (java)
> Tasks: 31 (limit: 10474)
> Memory: 247.2M
> CGroup: /system.slice/tomcat.service
> 64239 /usr/lib/jvm/jre/bin/java -Djava.util.logging.config.file=/opt/tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava>
3. 配置sample.war文件
[root@tomcat ]#
cd /opt/tomcat/webapps;pwd
curl -O https://tomcat.apache.org/tomcat-9.0-doc/appdev/sample/sample.war
chown tomcat: /opt/tomcat/webapps/sample.war
ll -h /opt/tomcat/webapps
> drwxr-x---. 5 tomcat tomcat 86 5月 2 19:51 sample
> -rw-r--r--. 1 tomcat tomcat 4.5K 5月 2 19:51 sample.war
grep 'deploy' /opt/tomcat/logs/catalina.out
> 02-May-2022 19:51:51.759 情報 [Catalina-utility-1]org.apache.catalina.startup.HostConfig.deployWAR Web アプリケーションアーカイブ[/opt/apache-tomcat-9.0.62/webapps/sample.war] の配備は [217] ms で完了しました。
4. 配置PostgreSQL的JDBC驱动
[root@tomcat ]#
#jdbcドライバーダウンロード
mkdir -m 777 /tmp/Installer/postgres
cd /tmp/Installer/postgres/;pwd
curl -O https://jdbc.postgresql.org/download/postgresql-42.3.4.jar
ll /tmp/Installer/postgres/
chown tomcat: /tmp/Installer/postgres/*
#libフォルダにjdbcドライバーを移動
mv /tmp/Installer/postgres/postgresql-42.3.4.jar /opt/tomcat/lib/
ll -h /opt/tomcat/lib/ | grep postgresql
> -rw-r--r--. 1 tomcat tomcat 1016K 5月 2 22:03 postgresql-42.3.4.jar
5. 数据源设置
・上下文.xml 备份
[root@tomcat ]#
cp -p /opt/tomcat/conf/context.xml{,_\`date +%Y%m%d\`}
ll -h /opt/tomcat/conf/context.xml*
请对context.xml 进行编辑。
[root@tomcat ]#
cat /opt/tomcat/conf/context.xml
vi /opt/tomcat/conf/context.xml
～～～～～～～～～～～～～～～～～～～～～～～～～～～～
<Context>
<!-- Default set of monitored resources. If one of these changes, the -->
<!-- web application will be reloaded. -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<!-- PostgreSQL -->
<Resource name="jdbc/PostgreSQL"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
initialSize="2" maxActive="4" minIdle="1" maxIdle="2"
username="test_user01" password="password"
driverClassName="org.postgresql.Driver" url="jdbc:postgresql://<DB・IPアドレス>:5432/testdb"
validationQuery="SELECT 1" />
</Context>
～～～～～～～～～～～～～～～～～～～～～～～～～～～～
6. 重启Tomcat服务
[root@tomcat]#
systemctl restart tomcat.service
systemctl status tomcat.service
>● tomcat.service - Apache Tomcat application server.
> Loaded: loaded (/usr/lib/systemd/system/tomcat.service; enabled; vendor preset: disabled)
> Active: active (running) since Mon 2022-05-02 23:18:34 JST; 11h ago
> Process: 64206 ExecStop=/opt/tomcat/bin/shutdown.sh (code=exited, status=0/SUCCESS)
> Process: 64231 ExecStart=/opt/tomcat/bin/startup.sh (code=exited, status=0/SUCCESS)
> Main PID: 64239 (java)
> Tasks: 31 (limit: 10474)
> Memory: 247.2M
> CGroup: /system.slice/tomcat.service
> 64239 /usr/lib/jvm/jre/bin/java -Djava.util.logging.config.file=/opt/tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava>
7. 数据源连接确认
[root@tomcat]#
lsof -Pi:5432
> COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
> java 114747 tomcat 50u IPv6 797431 0t0 TCP tomcat9:56292-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 52u IPv6 797441 0t0 TCP tomcat9:56296-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 54u IPv6 797443 0t0 TCP tomcat9:56300-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 56u IPv6 797447 0t0 TCP tomcat9:56308-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 58u IPv6 797445 0t0 TCP tomcat9:56304-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 60u IPv6 797449 0t0 TCP tomcat9:56312-> <DB・IPアドレス>:5432 (ESTABLISHED)
> java 114747 tomcat 62u IPv6 797451 0t0 TCP tomcat9:56316-> <DB・IPアドレス>:5432 (ESTABLISHED)
8. 创建JSP页面
创建一个用于JSP测试的文件夹
[root@tomcat]#
mkdir -m 750 /opt/tomcat/webapps/testjsp
chown tomcat: /opt/tomcat/webapps/testjsp
ll -h /opt/tomcat/webapps/
> drwxr-x---. 2 tomcat tomcat 44 5月 3 01:47 testjsp
・创建JSP文件
[root@tomcat]#
vi /opt/tomcat/webapps/testjsp/test.jsp
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
<!DOCTYPE html>
<html>
<head>
<title>test.jsp</title>
</head>
<body>
<p><font size="7" color="#00ff00">JSPテストページ</font></p>
</body>
</html>
<%@page import="javax.sql.DataSource"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.*" %>
<%@page import="javax.naming.InitialContext"%>
<%@page contentType="text/html" pageEncoding="UTF-8" session="false" %>
<%
//コネクションを取得するjndi
String jndi = "java:comp/env/jdbc/PostgreSQL";
InitialContext context = null;
Connection connection = null;
Statement stmt = null;
//コネクション取得処理
try {
context = new InitialContext();
DataSource ds = (DataSource) context.lookup(jndi);
connection = ds.getConnection();
String sql = "SELECT * FROM test_t1 order by dt limit 3000;";
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet result = pstmt.executeQuery();
while ( result.next() ) {
// 表示などの処理
int id = result.getInt("id");
String data =result.getString("data");
String dt =result.getString("dt");
out.println("<p>");
out.println("id:" + id + ",data:" + data + ",time:"+ dt );
out.println("</p>");
}
}
finally {
if (context != null) {
try {
context.close();
}
catch (Exception e) {
}
}
if (connection != null) {
try {
connection.close();
}
catch (Exception e) {
}
}
}
}
%>
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
#ファイルのオーナ変更
[root@tomcat]#
chown tomcat: /opt/tomcat/webapps/testjsp/test.jsp
chmod 755 /opt/tomcat/webapps/testjsp/test.jsp
ll -h /opt/tomcat/webapps/testjsp/test.jsp
> -rwxr-xr-x. 1 tomcat tomcat 2.0K 5月 3 12:02 /opt/tomcat/webapps/testjsp/test.jsp
9. 访问测试页面
请提供相关链接
・Java Tomcat等应用服务器+数据库连接池设置示例
・【JavaServlet & jsp入门＃3】执行Select语句从PostgreSQL获取数据并显示在列表中
・基础设施看起来很难…一直回避的软弱程序员为了克服这个困难而努力构建了服务器。WebAP服务器篇
