연구실 과제/Dalle3
내가 혼자 surveyResult.jsp만들기(DB두번조회)
dongok218
2025. 3. 6. 14:57
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<html>
<head>
<title>설문조사 결과 보기</title>
<style>
.menu-container {
margin-top: 80px;
}
table {
border-collapse: collapse;
width: 80%;
margin: 20px auto;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
}
th, td {
border: 1px solid #ccc;
padding: 12px;
text-align: center;
font-family: Arial, sans-serif;
}
th {
background-color: #f2f2f2;
font-size: 16px;
font-weight: bold;
}
tr:nth-child(even) {
background-color: #f9f9f9;
}
tr:hover {
background-color: #f1f1f1;
}
h2 {
text-align: center;
color: #333;
font-family: Arial, sans-serif;
}
</style>
</head>
<body>
<%@ include file="../../menu.jsp" %>
<div class="container menu-container"></div>
<%
String DB_URL = "jdbc:mysql://117.17.142.133:3306/Dalle3";
String DB_USER = "root";
String DB_PASSWORD = "Skunivdilab1!";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
boolean hasData = false;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
String sql = "SELECT * FROM surveyTable";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
%>
<h2>설문조사 결과</h2>
<table>
<tr>
<th>id</th>
<th>user_id</th>
<th>gender</th>
<th>age</th>
<th>section_number</th>
<th>Q1</th>
<th>Q2</th>
<th>Q3</th>
<th>Q4</th>
<th>Q5</th>
</tr>
<%
while(rs.next()){
hasData = true;
Connection conn2 = null;
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try{
int userId = rs.getInt("user_id");
Class.forName("com.mysql.cj.jdbc.Driver");
conn2 = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
String sql2 = "SELECT gender, age FROM userTable WHERE = ?";
pstmt2 = conn.prepareStatement(sql2);
rs2 = pstmt2.executeQuery();
}
%>
<tr>
<td><%= rs.getInt("id") %></td>
<td><%= rs.getInt("user_id") %></td>
<td><%= rs2.getString("gender") %></td>
<td><%= rs2.getInt("age") %></td>
<td><%= rs.getString("section_number") %></td>
<td><%= rs.getInt("Q1") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q2") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q3") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q4") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q5") == 1 ? "O" : "X" %></td>
</tr>
<%
}
if(!hasData) {
%>
<tr>
<td colspan="8"><h2>설문조사 결과가 없습니다.</h2></td>
</tr>
<%
}
catch(Exception e){
out.println("<h2>오류 발생: " + e.getMessage() +"</h2>");
} finally{
if(rs2 != null) try {rs2.close();} catch(Exception e) {}
if(pstmt2 != null) try {pstmt2.close();} catch(Exception e) {}
if(conn2 != null) try {conn2.close();} catch(Exception e) {}
}
} catch(Exception e) {
out.println("<h2>오류 발생: " + e.getMessage() + "</h2>");
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(pstmt != null) try { pstmt.close(); } catch(Exception e) {}
if(conn != null) try { conn.close(); } catch(Exception e) {}
}
%>
</table>
</body>
</html>[내가 짠 코드]
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="UTF-8" %>
<%@ page import="java.sql.*" %>
<html>
<head>
<title>설문조사 결과 보기</title>
<style>
.menu-container {
margin-top: 80px;
}
table {
border-collapse: collapse;
width: 80%;
margin: 20px auto;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
}
th, td {
border: 1px solid #ccc;
padding: 12px;
text-align: center;
font-family: Arial, sans-serif;
}
th {
background-color: #f2f2f2;
font-size: 16px;
font-weight: bold;
}
tr:nth-child(even) {
background-color: #f9f9f9;
}
tr:hover {
background-color: #f1f1f1;
}
h2 {
text-align: center;
color: #333;
font-family: Arial, sans-serif;
}
</style>
</head>
<body>
<%@ include file="../../menu.jsp" %>
<div class="container menu-container"></div>
<%
String DB_URL = "jdbc:mysql://117.17.142.133:3306/Dalle3";
String DB_USER = "root";
String DB_PASSWORD = "Skunivdilab1!";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
boolean hasData = false;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
String sql = "SELECT * FROM surveyTable";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
%>
<h2>설문조사 결과</h2>
<table>
<tr>
<th>id</th>
<th>user_id</th>
<th>gender</th>
<th>age</th>
<th>section_number</th>
<th>Q1</th>
<th>Q2</th>
<th>Q3</th>
<th>Q4</th>
<th>Q5</th>
</tr>
<%
while(rs.next()){
hasData = true;
String gender = "";
int age = 0;
// 새 연결로 userTable 조회
Connection conn2 = null;
PreparedStatement pstmt2 = null;
ResultSet rs2 = null;
try {
int userId = rs.getInt("user_id");
conn2 = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
String sql2 = "SELECT gender, age FROM userTable WHERE id = ?";
pstmt2 = conn2.prepareStatement(sql2);
pstmt2.setInt(1, userId);
rs2 = pstmt2.executeQuery();
if(rs2.next()){
gender = rs2.getString("gender");
age = rs2.getInt("age");
}
} catch(Exception e) {
out.println("<h2>오류 발생 (user 정보): " + e.getMessage() + "</h2>");
} finally {
if(rs2 != null) try { rs2.close(); } catch(Exception e) {}
if(pstmt2 != null) try { pstmt2.close(); } catch(Exception e) {}
if(conn2 != null) try { conn2.close(); } catch(Exception e) {}
}
%>
<tr>
<td><%= rs.getInt("id") %></td>
<td><%= rs.getInt("user_id") %></td>
<td><%= gender %></td>
<td><%= age %></td>
<td><%= rs.getString("section_number") %></td>
<td><%= rs.getInt("Q1") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q2") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q3") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q4") == 1 ? "O" : "X" %></td>
<td><%= rs.getInt("Q5") == 1 ? "O" : "X" %></td>
</tr>
<%
}
if(!hasData) {
%>
<tr>
<td colspan="10"><h2>설문조사 결과가 없습니다.</h2></td>
</tr>
<%
}
} catch(Exception e) {
out.println("<h2>오류 발생: " + e.getMessage() + "</h2>");
} finally {
if(rs != null) try { rs.close(); } catch(Exception e) {}
if(pstmt != null) try { pstmt.close(); } catch(Exception e) {}
if(conn != null) try { conn.close(); } catch(Exception e) {}
}
%>
</table>
</body>
</html>[올바른 코드]