AWS에서 제공하는 서비스로 DB 생성
Database 생성
mysql> create database dyyeom;
mysql> use dyyeom
Table 생성
mysql> create table user(
-> NUM varchar(10) not null,
-> ID varchar(10) not null,
-> PW varchar(10) not null,
-> NAME varchar(10) not null,
-> ADDRESS varchar(10) not null
-> );
mysql> create table login(
-> ID varchar(20) not null,
-> PW varchar(20) not null
-> );
vi /opt/tomcat/conf/context.xml
Context 안에 Resource 추가
<Context>
<Resource name="jdbc/MyDB"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username="admin"
password="your_password"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://dyyeom-test-db.csuhqyt3lq7d.ap-northeast-2.rds.amazonaws.com/dyyeom"/>
<!-- 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 enable session persistence across Tomcat restarts -->
<!--
<Manager pathname="SESSIONS.ser" />
-->
</Context>
vi /opt/tomcat/webapps/ROOT/signup-process.jsp
<%@ page import="java.sql.*, javax.naming.*, javax.sql.DataSource, java.io.*" %>
<%
request.setCharacterEncoding("UTF-8");
String num = request.getParameter("num");
String id = request.getParameter("id");
String pw = request.getParameter("pw");
String name = request.getParameter("name");
String address = request.getParameter("address");
Connection conn = null;
PreparedStatement pstmt = null;
try {
// JNDI를 통해 데이터소스 가져오기
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/MyDB");
// 데이터베이스 연결
conn = ds.getConnection();
// SQL 쿼리 작성
String sql = "INSERT INTO user (NUM, ID, PW, NAME, ADDRESS) VALUES (?, ?, ?, ?, ?)";
// PreparedStatement를 사용하여 데이터베이스에 값 삽입
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, num);
pstmt.setString(2, id);
pstmt.setString(3, pw);
pstmt.setString(4, name);
pstmt.setString(5, address);
// 쿼리 실행
int result = pstmt.executeUpdate();
// 회원가입 성공 시 로그인 페이지로 리다이렉트
if (result > 0) {
response.sendRedirect("index.jsp"); // 회원가입 성공 후 login.jsp로 리다이렉트
} else {
out.println("회원가입 실패.");
}
} catch (Exception e) {
StringWriter sw = new StringWriter();
PrintWriter pwPrint = new PrintWriter(sw);
e.printStackTrace(pwPrint);
out.println("<pre>" + sw.toString() + "</pre>");
} finally {
// 리소스 해제
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) { out.println("Error closing pstmt"); }
if (conn != null) try { conn.close(); } catch (SQLException e) { out.println("Error closing conn"); }
}
%>
DB에 정상적으로 데이터 기입이 되었나 확인
AL 2023에 Mysql 설치
sudo wget https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm
sudo dnf install mysql80-community-release-el9-1.noarch.rpm -y
sudo dnf update -y
sudo dnf install mysql-community-client -y
sudo dnf install mysql-community-server -y
DB 접속
mysql -u admin -p -h dyyeom-test-db.csuhqyt3lq7d.ap-northeast-2.rds.amazonaws.com -P 3306
비밀번호 입력
use dyyeom
select * from user;
로그인 테스트
vi /opt/tomcat/webapps/ROOT/home.jsp
<!DOCTYPE html>
<html>
<head>
<title>Home Page</title>
</head>
<body>
<h1>Welcome to the Home Page</h1>
<p>You are successfully logged in.</p>
</body>
</html>
vi /opt/tomcat/webapps/ROOT/login-process.jsp
<%@ page import="java.sql.*, javax.naming.*, javax.sql.DataSource, java.io.*" %>
<%
String id = request.getParameter("id");
String pw = request.getParameter("pw");
Connection conn = null;
PreparedStatement pstmt = null;
PreparedStatement insertPstmt = null;
ResultSet rs = null;
try {
// 데이터베이스 연결
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/MyDB");
conn = ds.getConnection();
// ID와 PW가 일치하는지 확인
String sql = "SELECT * FROM user WHERE ID = ? AND PW = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.setString(2, pw);
rs = pstmt.executeQuery();
if (rs.next()) {
// 로그인 성공 시, login 테이블에 기록
String insertSql = "INSERT INTO login (ID, PW) VALUES (?, ?)";
insertPstmt = conn.prepareStatement(insertSql);
insertPstmt.setString(1, id);
insertPstmt.setString(2, pw);
insertPstmt.executeUpdate();
// 로그인 성공 후 home.jsp로 리다이렉트
response.sendRedirect("home.jsp");
} else {
out.println("Login failed. Please try again.");
}
} catch (Exception e) {
StringWriter sw = new StringWriter();
PrintWriter pwPrint = new PrintWriter(sw);
e.printStackTrace(pwPrint);
out.println("<pre>" + sw.toString() + "</pre>");
} finally {
// 리소스 해제
if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(new PrintWriter(out)); }
if (pstmt != null) try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(new PrintWriter(out)); }
if (insertPstmt != null) try { insertPstmt.close(); } catch (SQLException e) { e.printStackTrace(new PrintWriter(out)); }
if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(new PrintWriter(out)); }
}
%>
