<./>.dev./hood

JSP 게시판 기본 예제 (preparedStatement) 전용

Server Side/JSP2017. 3. 4. 13:48

 

//////////////////////////////////


DBConnection.java
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;
        }
    }
}


/////////////////////////////////////////


index.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>
</head>
<body>
<ul>
    <li><a href="member/insert.html">회원가입</a></li>
    <li><a href="member/list.jsp">회원목록</a></li>
</ul>
</body>
</html>


/////////////////////////////////////////////


insert.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>


/////////////////////////////////////////////////////////


insert.jsp
<%@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>


////////////////////////////////////////////////////


list.jsp
<%@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>


////////////////////////////////////////////////////////


update.jsp
<%@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>



/////////////////////////////////////////////////



updateOk.jsp
<%@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>


////////////////////////////////////////////////


delete.jsp
<%@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>

 

참고 URL -

까먹음.

저작자표시 비영리 (새창열림)

'Server Side > JSP' 카테고리의 다른 글

JSP 에서 JSON String 변수 담기  (0) 2017.05.03
jsp에서 자기가 만든 class import 하기  (0) 2017.03.04
간단한 DB 연결 테스트 JSP 파일  (0) 2017.02.21
JSP 액션태그 -> EL 태그  (0) 2017.02.07
JSP, PHP 의 print_r 같은 기능  (0) 2017.01.20

티스토리툴바