간단한 회원가입, 수정, 삭제, 조회 예제( JSP )
- 목록
create table MEMBERS
(
ID VARCHAR2(30) primary key,
PWD VARCHAR2(30),
EMAIL VARCHAR2(100),
PHONE VARCHAR2(100),
REGDATE DATE
)
package test.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public static Connection getCon() throws SQLException{
Connection con=null;
try{
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
con=DriverManager.getConnection(url, "scott", "tiger");
return con;
}catch(ClassNotFoundException ce){
System.out.println(ce.getMessage());
return null;
}
}
}
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<ul>
<li><a href="member/insert.html">회원가입</a></li>
<li><a href="member/list.jsp">회원목록</a></li>
</ul>
</body>
</html>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
<style type="text/css">
#regbox{ width : 300px; }
#regbox label{ display : block; width: 100px; float : left; }
</style>
</head>
<body>
<form method="post" action="insert.jsp">
<fieldset id="regbox">
<legend>회원가입</legend>
<label for="id">아이디</label>
<input type="text" name="id"/><br/>
<label for="pwd">비밀번호</label>
<input type="password" name="pwd"/><br/>
<label for="email">이메일</label>
<input type="text" name="email"/><br/>
<label for="phone">전화번호</label>
<input type="text" name="phone"/><br/>
<input type="submit" value="가입">
<input type="reset" value="취소"/>
</fieldset>
</form>
</body>
</html>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ page import="java.sql.Connection" %>
<%@page import="test.db.DBConnection"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String id=request.getParameter("id");
String pwd=request.getParameter("pwd");
String email=request.getParameter("email");
String phone=request.getParameter("phone");
//db에 저장하기
Connection con = null;
PreparedStatement pstmt = null;
String sql = "insert into members values(?,?,?,?, sysdate)";
int n=0;
try{
con = DBConnection.getCon();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.setString(2, pwd);
pstmt.setString(3, email);
pstmt.setString(4, phone);
n = pstmt.executeUpdate();
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
// 결과 응답하기
%>
<script type="text/javascript">
if(<%=n%> > 0){
alert("정상적으로 회원가입되었습니다.");
location.href="../index.html";//
}else{
alert("회원가입에 실패했습니다.");
history.go(-1);//이전페이지로 가기
}
</script>
</body>
</html>
<%@page import="test.db.DBConnection"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h2>회원목록</h2>
<table border="1" width="600">
<tr>
<td>아이디</td>
<td>비밀번호</td>
<td>이메일</td>
<td>전화번호</td>
<td>가입일</td>
<td>삭제</td>
<td>수정</td>
</tr>
<%
//db 에서 회원목록 얻어와 테이블에 출력하기.
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
try{
con = DBConnection.getCon();
String sql="select * from members";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String email = rs.getString("email");
String phone = rs.getString("phone");
Timestamp regdate = rs.getTimestamp("regdate");
%>
<tr>
<td><%=id %></td>
<td><%=pwd %></td>
<td><%=email %></td>
<td><%=phone %></td>
<td><%=regdate %></td>
<td><a href="delete.jsp?id=<%=id%>" >삭제</a></td>
<td><a href="update.jsp?id=<%=id%>" >수정</a></td>
</tr>
<%
}
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
%>
</table>
</body>
</html>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="test.db.DBConnection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String id=request.getParameter("id");
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
try{
con = DBConnection.getCon();
String sql="select * from members where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
String pwd = rs.getString("pwd");
String email = rs.getString("email");
String phone = rs.getString("phone");
Timestamp regdate = rs.getTimestamp("regdate");
%>
<form name='frm1' method='post' action='updateOk.jsp'>
<table>
<tr>
<td>아이디</td>
<td><%=id %><input type="hidden" name="id" value="<%=id%>"/></td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="text" name="pwd" value="<%=pwd %>"/></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email" value="<%=email %>"/></td>
</tr>
<tr>
<td>전화번호</td>
<td><input type="text" name="phone" value="<%=phone %>"/></td>
</tr>
<tr>
<td>등록일</td>
<td><%=regdate %></td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="button" name="btn1" value="저장" onclick="javascript:frm1.submit();"/>
<input type="button" name="btn2" value="목록" onclick="javascript:location.href='list.jsp';"/>
</td>
</tr>
</table>
</form>
<%
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(rs!=null) rs.close();
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
%>
<script type="text/javascript">
function update(){
document.frm1.submit();
}
function list(){
location.href="list.jsp";
}
</script>
</body>
</html>
<%@page import="java.sql.SQLException"%>
<%@page import="test.db.DBConnection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String id=request.getParameter("id");
String pwd=request.getParameter("pwd");
String email=request.getParameter("email");
String phone=request.getParameter("phone");
//db에 저장하기
Connection con = null;
PreparedStatement pstmt = null;
String sql = "update members set pwd=?, email=?, phone=? where id= ?";
int n=0;
try{
con = DBConnection.getCon();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, pwd);
pstmt.setString(2, email);
pstmt.setString(3, phone);
pstmt.setString(4, id);
n = pstmt.executeUpdate();
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
try{
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}
}
%>
<script type="text/javascript">
if(<%=n%>>0 ){
alert("수정되었습니다.");
location.href="list.jsp";
}else{
alert("수정 실패");
history.go(-1);
}
</script>
</body>
</html>
<%@page import="test.db.DBConnection"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
//삭제할 아이디
Connection con = null;
PreparedStatement pstmt = null;
String id = request.getParameter("id");
int n=0;
try{
con = DBConnection.getCon();
String sql="delete from members where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
n = pstmt.executeUpdate();
}catch(SQLException se){
System.out.println(se.getMessage());
}finally{
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
}
response.sendRedirect("list.jsp");
%>
</body>
</html>
