wildfly23 - postgresql 13 연동

=======

여기에서 이어짐

https://dodomain93.tistory.com/148

postgresql 설치와 DB 계정과 테이블 생성

=======

 

1. JDBC Driver 파일 다운로드

https://jdbc.postgresql.org/download/

Download | pgJDBC

 

 

2. /sw/wildfly/modules/system/layers/base/com 경로에 postgresql/main 경로 생성

[app@test11 ~]$ mkdir -p /sw/wildfly/modules/system/layers/base/com/postgresql/main

 

3. 다운로드 받은 드라이버 파일을 ${JBOSS_HOME}/modules/system/layers/base/com/postgresql/main 경로에 위치

[app@test11 ~]$ mv ./postgresql-42.7.10.jar /sw/wildfly/modules/system/layers/base/com/postgresql/main

 

4. module.xml 작성

[app@test11 ~]$ vi /sw/wildfly/modules/system/layers/base/com/postgresql/main/module.xml

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.1" name="com.postgresql">
  <resources>
    <resource-root path="postgresql-42.7.10.jar"/>
  </resources>
  <dependencies>
    <module name="javaee.api"/>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

 

※ 최종결과

[app@test11 ~]$ tree /sw/wildfly/modules/system/layers/base/com/postgresql
/sw/wildfly/modules/system/layers/base/com/postgresql
└── main
    ├── module.xml
    └── postgresql-42.7.10.jar

1 directory, 2 files

 

5. wildfly 내에 JDBC 드라이버 추가

[app@test11 bin]$ ./jboss-cli.sh
=================================================
...
=================================================

[standalone@192.168.56.101:9990 /] /subsystem=datasources/jdbc-driver=postgreDriver:add(driver-name=postgreDriver,driver-module-name=com.postgresql)
{"outcome" => "success"}

 

6. 생성한 드라이버를 바탕으로 datasource 생성

[standalone@192.168.56.101:9990 /] data-source add --name=postgreDS --jndi-name=java:/postgreDS --driver-name=postgreDriver --connection-url=jdbc:postgresql://192.168.56.104:5432/appdb --user-name=appuser --password=1234! --check-valid-connection-sql="select 1" --validate-on-match=false --background-validation=true --background-validation-millis=60000 --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter

 

※ Connection URL 관련 정리

1️⃣ 전체 구조

jdbc:postgresql://HOST:PORT/DATABASE

 

구성 요소:

부분의미

jdbc	Java Database Connectivity
postgresql	사용할 DBMS
HOST	DB 서버 IP 또는 hostname
PORT	DB 포트
DATABASE	접속할 데이터베이스

---

2️⃣ 실제 예시 해석

jdbc:postgresql://192.168.56.104:5432/appdb

 

항목의미

jdbc	JDBC 프로토콜
postgresql	PostgreSQL 드라이버 사용
192.168.56.104	DB 서버 IP
5432	PostgreSQL 기본 포트
appdb	접속할 DB

 

7. connection test 페이지 호출

 

[standalone-ha.xml 상 내용]

                <datasource jndi-name="java:/postgreDS" pool-name="postgreDS">
                    <connection-url>jdbc:postgresql://192.168.56.104:5432/appdb</connection-url>
                    <driver>postgreDriver</driver>
                    <security>
                        <user-name>appuser</user-name>
                        <password>1234!</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>true</background-validation>
                        <background-validation-millis>60000</background-validation-millis>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
                    </validation>
                </datasource>
                <drivers>
                    <driver name="postgreDriver" module="com.postgresql"/>
                </drivers>

 

[테스트용 JSP]

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.sql.DataSource" %>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>PostgreSQL Member 조회</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 30px;
        }
        h2 {
            margin-bottom: 15px;
        }
        table {
            border-collapse: collapse;
            width: 900px;
        }
        th, td {
            border: 1px solid #999;
            padding: 8px 12px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        .error {
            color: red;
            font-weight: bold;
        }
        .info {
            margin-bottom: 15px;
            color: #444;
        }
    </style>
</head>
<body>
    <h2>member 테이블 조회</h2>
    <div class="info">JNDI: java:/postgreDS</div>

<%
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:/postgreDS");

        conn = ds.getConnection();

        String sql = "SELECT id, name, dept, email, created_at FROM member ORDER BY id";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
%>

    <table>
        <tr>
            <th>ID</th>
            <th>NAME</th>
            <th>DEPT</th>
            <th>EMAIL</th>
            <th>CREATED_AT</th>
        </tr>

<%
        while (rs.next()) {
%>
        <tr>
            <td><%= rs.getInt("id") %></td>
            <td><%= rs.getString("name") %></td>
            <td><%= rs.getString("dept") %></td>
            <td><%= rs.getString("email") %></td>
            <td><%= rs.getTimestamp("created_at") %></td>
        </tr>
<%
        }
%>
    </table>

<%
    } catch (Exception e) {
%>
    <p class="error">에러 발생: <%= e.getClass().getName() %> - <%= e.getMessage() %></p>
<%
        e.printStackTrace();
    } finally {
        try { if (rs != null) rs.close(); } catch (Exception e) {}
        try { if (pstmt != null) pstmt.close(); } catch (Exception e) {}
        try { if (conn != null) conn.close(); } catch (Exception e) {}
    }
%>

</body>
</html>