보라코딩
Day65_230330_ JSP (CRUD_회원정보) 본문
저번시간에 이어서..
delete.jsp
디렉티브(지시어) include문
<%@ include file="footer.jsp" %>
전역변수, 지역변수
회원정보
DB
main.jsp
script는 src로 연결
include 해서 menu 연결
(자주 사용하기 때문에 따로 파일로 만들어두었다.)
menu.jspf
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<form>
<table border>
<thead>
<tr>
<th colspan = "2">회원정보</th>
</tr>
</thead>
<tbody>
<tr>
<th>아이디</th>
<td><input type = "text" name="id"></td>
</tr>
<tr>
<th>암호</th>
<td><input type = "password" name="pwd"></td>
</tr>
<tr>
<th>이름</th>
<td><input type = "text" name="name"></td>
</tr>
<tr>
<th>나이</th>
<td><input type = "number" name="age" value="0"></td>
</tr>
<tr>
<th>주소</th>
<td><input type = "text" name="address"></td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="2">
<input type = "button" value = "전체검색" onclick="sel_all(this.form)">
<input type = "button" value = "검색(ID)" onclick="sel_one(this.form)">
<input type = "button" value = "입력" onclick="add_go(this.form)">
<input type = "button" value = "삭제(ID)" onclick="del_go(this.form)">
<input type = "reset" value = "초기화">
</td>
</tr>
</tfoot>
</table>
</form>
event.js
function sel_all(frm){ // 전체 데이터 조회 작업 요청
frm.action = "selectAll.jsp";
frm.submit();
}
function sel_one(frm){ // ID 검색 작업 요청
if(frm.id.value.trim() == ""){
alert("ID 입력하시죠.");
frm.id.value = "";
frm.id.focus();
return false;
}
frm.action = "selectOne.jsp";
frm.submit();
}
function add_go(frm){ //입력 요청
if(frm.id.value.trim() == ""){
alert("ID 입력하시죠.");
frm.id.value = "";
frm.id.focus();
return false;
}
frm.action = "insertMember.jsp";
frm.submit();
}
function del_go(frm){ //삭제 요청(ID 사용)
if(frm.id.value.trim() == ""){
alert("ID 입력하시죠.");
frm.id.value = "";
frm.id.focus();
return false;
}
frm.action = "deleteMember.jsp";
frm.submit();
}
JdbcUtil.java
package com.mystudy.common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JdbcUtil {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:xe";
private static final String USER = "mystudy";
private static final String PASSWORD = "mystudypw";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void close(Connection conn, PreparedStatement pstmt) {
try {
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
try {
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
close(conn, pstmt);
}
}
selectAll.jsp
<%@page import="com.mystudy.common.JdbcUtil"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%-- DB에서 전체데이터 조회(검색)해서 화면 출력 --%>
<%
//final String DRIVER = "oracle.jdbc.OracleDriver";
//final String URL = "jdbc:oracle:thin:@localhost:1521:xe";
//final String USER = "mystudy";
//final String PASSWORD = "mystudypw";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 전체인원 저장
int count = 0;
try {
//1. JDBC 드라이버 로딩
//Class.forName(DRIVER);
//2. DB연결 <- Connection 객체 생성 <- DriverManager
conn = JdbcUtil.getConnection();
//conn = DriverManager.getConnection(URL, USER, PASSWORD);
String sql = " select IDX, ID, PWD, NAME, AGE, NVL(ADDRESS,'주소없음') AS ADDRESS, TO_CHAR(REG, 'YYYY/MM/DD') AS REG "
+ " from member2 order by IDX";
//3. Statement 문 실행(SQL 문 실행)
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>전체검색</title>
<script src="includee/event.js" type="text/javascript"> </script>
</head>
<body>
<%@ include file="includee/menu.jspf" %>
<h1>전체 데이터 검색</h1>
<table border>
<thead>
<tr>
<th>번호</th>
<th>아이디</th>
<th>패스워드</th>
<th>이름</th>
<th>나이</th>
<th>주소</th>
<th>날짜</th>
</tr>
</thead>
<tbody>
<%
while (rs.next()){
%>
<tr>
<td><%=rs.getInt("IDX")%></td>
<td><%=rs.getString("ID")%></td>
<td><%=rs.getString("PWD")%></td>
<td><%=rs.getString("NAME")%></td>
<td><%=rs.getInt("AGE")%></td>
<td><%=rs.getString("ADDRESS")%></td>
<td><%=rs.getString("REG")%></td>
<%
}
%>
</tr>
</tbody>
</table>
</body>
</html>
<%
//전체 데이터 건수 확인
rs = pstmt.executeQuery("select count(*) AS CNT from member2");
if(rs.next()){
count = rs.getInt("CNT");
}
} catch (Exception e){
e.printStackTrace();
} finally {
JdbcUtil.close(conn, pstmt, rs);
}
%>
<h4>총 데이터 건수 : <%=count%></h4>
selectOne.jsp
<%@page import="com.mystudy.common.JdbcUtil"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%-- 전달받은 데이터 중 ID값을 사용해서 DB 데이터 조회 후 화면에 표시
없으면 : <데이터가 없습니다> 메시지 표시
--%>
<%
String id = request.getParameter("id");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// DB연결
conn = JdbcUtil.getConnection();
String sql = " select IDX, ID, PWD, NAME, AGE, "
+ " NVL(ADDRESS,'주소없음') AS ADDRESS, "
+ " TO_CHAR(REG, 'YYYY/MM/DD') AS REG "
+ " from member2 where ID = ? "
+ " order by IDX ";
//3. Statement 문 실행(SQL 문 실행)
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,id);
rs = pstmt.executeQuery();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Template.jsp</title>
<script src="includee/event.js" type="text/javascript"> </script>
</head>
<body>
<%@ include file="includee/menu.jspf" %>
<h1>전체 데이터 검색</h1>
<table border>
<thead>
<tr>
<th>번호</th>
<th>아이디</th>
<th>패스워드</th>
<th>이름</th>
<th>나이</th>
<th>주소</th>
<th>날짜</th>
</tr>
</thead>
<tbody>
<%
if (rs.next()){
%>
<tr>
<td><%=rs.getInt("IDX")%></td>
<td><%=rs.getString("ID")%></td>
<td><%=rs.getString("PWD")%></td>
<td><%=rs.getString("NAME")%></td>
<td><%=rs.getInt("AGE")%></td>
<td><%=rs.getString("ADDRESS")%></td>
<td><%=rs.getString("REG")%></td>
<%
} else {
out.print("< 데이터가 없습니다. >");
}
%>
</tbody>
</table>
</body>
</html>
<%
//전체 데이터 건수 확인
} catch (Exception e){
e.printStackTrace();
} finally {
JdbcUtil.close(conn, pstmt, rs);
}
%>
insertMember.jsp
<%@page import="com.mystudy.common.JdbcUtil"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%-- 전달받은 데이터를 DB에 입력하고 전체데이터 보기 페이지로 이동 --%>
<%
String id = request.getParameter("id");
String pwd = request.getParameter("pwd");
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
String address = request.getParameter("address");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// DB연결
conn = JdbcUtil.getConnection();
String sql = " insert into MEMBER2(IDX, ID, PWD, NAME, AGE, ADDRESS) "
+ " values (MEMBER2_SEQ.NEXTVAL,?,?,?,?,?) ";
//3. Statement 문 실행(SQL 문 실행)
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,id);
pstmt.setString(2,pwd);
pstmt.setString(3,name);
pstmt.setInt(4,age);
pstmt.setString(5,address);
int result = pstmt.executeUpdate();
// 페이지 전환처리
if (result > 0) {
System.out.println("insert result : " + result);
%>
<script>
alert("등록완료!");
location.href = "main.jsp";
</script>
<%
}
} catch (Exception e){
e.printStackTrace();
} finally {
JdbcUtil.close(conn, pstmt, rs);
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>insertMember.jsp</title>
</head>
<body>
</body>
</html>
deleteMember.jsp
<%@page import="com.mystudy.common.JdbcUtil"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%-- 전달받은 데이터(ID) 사용해서 DB에서 삭제하고 전체데이터 보기 페이지로 이동
화면 필요 없음
--%>
<%
String id = request.getParameter("id");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// DB연결
conn = JdbcUtil.getConnection();
String sql = " delete from member2 where id = ? ";
//3. Statement 문 실행(SQL 문 실행)
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,id);
int result = pstmt.executeUpdate();
// 페이지 전환처리
if (result > 0) {
System.out.println("delete result : " + result);
%>
<script>
alert("삭제완료!");
location.href = "main.jsp";
</script>
<%
}
} catch (Exception e){
e.printStackTrace();
} finally {
JdbcUtil.close(conn, pstmt, rs);
}
%>
'코딩 > Servlet, JSP, MyBatis' 카테고리의 다른 글
| Day67_230403_ JSP (EL, JSTL) (0) | 2023.04.03 |
|---|---|
| Day66_230331_ JSP (action tag, EL tag, useBean, setProperty) (0) | 2023.03.31 |
| Day64_230329_ JSP(JDBC CRUD) (0) | 2023.03.29 |
| Day63_230328_ JSP (session_로그인_장바구니, JDBC) (0) | 2023.03.28 |
| Day62_230327_ JSP (1) | 2023.03.27 |
