2016. 10. 28. (Fri)
L12JDBC
JDBC는 Java DataBase Connection이다.
이클립스에서 오라클 서버를 사용하기 위해 아래의 위치에
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib에 있는
ojdbc6.jar를 붙여넣는다. (각자의 설치 환경에 따라 위치는 다르다)
Jsp file을 생성하고 Oracle Server와 연동해서 회원 조회, 가입, 삭제, 수정 등을 실행해보자.
SQL Developer로 Member Table을 생성한다.
index.jsp
1 2 3 4 5 6 7 8 9 10 11 12 13 | <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>ojdbc를 배워보자</title> </head> <body> <h1>ojdbc로 Member table의 list 출력하자</h1> <h3><a href="./L01MemberList.jsp">Member List</a></h3> </body> </html> | cs |
예제 1. L01MemberList.jsp
쿼리문으로 Member 테이블을 조회한다.
테이블을 만들어서 출력하고 삭제 및 수정 버튼을 생성한다.
삭제 및 수정 버튼은 액션태그를 이용하여 각각 기능을 할 jsp file로 연결해준다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | <%@page import="java.sql.SQLException"%> <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.Statement"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>ojdbc로 Member List 출력</title> <% Connection conn = null; Statement stmt = null; ResultSet rs = null; String url = "jdbc:oracle:thin:@localhost:1521:xe"; /* 8080 포트에 사용되는 오라클은 다른 프로그램 */ String uid="jsp_lesson"; String pass = "1234"; String sql = "SELECT * FROM MEMBER order by num"; %> </head> <body> <table border="1"> <tr><th>num</th> <th>id</th> <th>pwd</th> <th>name</th> <th>email</th> <th>phone</th> <th>indate</th> <th>admin</th> <th>삭제</th> <th>수정</th> </tr> <% try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); //cmd창에서 slqplus system/oracle stmt = conn.createStatement(); //conn 객체가 쿼리를 실행할 준비 -> stmt가 가진다. rs = stmt.executeQuery(sql); //실행한 결과를 rs 객체가 담는다. while(rs.next()){ %> <tr><td><%=rs.getInt("num") %></td> <td><%=rs.getString("id") %></td> <td><%=rs.getString("pwd") %></td> <td><%=rs.getString("name") %></td> <td><%=rs.getString("email") %></td> <td><%=rs.getString("phone") %></td> <td><%=rs.getString("indate") %></td> <td><%=rs.getString("admin") %></td> <th><a href="./L02MemberDelete.jsp?num=<%=rs.getInt("num") %>">삭제</a></th> <th><a href="./L05MemberUpdateForm.jsp?num=<%=rs.getInt("num") %>">수정</a></th> </tr> <% } } catch (ClassNotFoundException e){e.printStackTrace(); } catch (SQLException e){e.printStackTrace(); } %> </table> <p><strong><a href="L03MemberAddForm.jsp">회원가입</a></strong></p> </body> </html> | cs |
예제 2. L02MemberDelete.jsp
앞의 MemberList page에서 삭제 버튼을 눌렀을 때 파라미터로 NUM값을 받아와서
쿼리문을 이용해서 Num값에 해당하는 내용들을 삭제하고 삭제가 완료되었다는 메시지를 띄운다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.Statement"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>Insert title here</title> </head> <%! Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String url = "jdbc:oracle:thin:@localhost:1521:xe"; String uid="jsp_lesson"; String pass = "1234"; String sql = "DELETE FROM MEMBER WHERE NUM=?"; %> <body> <% int num = Integer.parseInt(request.getParameter("num")); try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); pstmt.executeQuery(); } catch (Exception e){e.printStackTrace(); } %> <h3 style="color:red">삭제가 완료되었습니다.</h3> <h3><a href="./index.jsp">홈으로</a></h3> </body> </html> | cs |
예제 3. L03MemberAddForm.jsp
이번 예제는 회원가입을 진행하는 코드이다. 지금까지 Form Tag를 이용해서 정보를 입력받아
Insert 쿼리문을 진행하는 곳으로 넘겨주는 것을 많이 해봤지만, 이번에는 Num 값을 MAX(num)으로
검색하여 +1해서 자동으로 넣어주는 코드를 삽입했다.
SELECT MAX(num) as num FROM MEMBER 쿼리문을 실행하여 결과 값에 +1 하여 자동으로 카운팅 해준다.
아래의 예제를 살펴보자
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | <%@page import="java.sql.Connection"%> <%@page import="java.sql.Statement"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.DriverManager"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>회원가입 Form</title> </head> <%! Connection conn = null; Statement stmt = null; ResultSet rs = null; int num1 = 0; String url = "jdbc:oracle:thin:@localhost:1521:xe"; /* 8080 포트에 사용되는 오라클은 다른 프로그램 */ String uid="jsp_lesson"; String pass = "1234"; String sql = "SELECT MAX(num) as num FROM MEMBER"; %> <% try { response.setCharacterEncoding("utf-8"); Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if(rs.next()){ num1 = rs.getInt("num")+1; } } catch (Exception e){e.printStackTrace(); } %> <body> <h1>회원가입</h1> <% String login = request.getParameter("msg"); if(login != null){ %> <h3 style="color: red"><%=login %></h3> <% } %> <!-- id, num가 db 내용과 중복x --> <form action="L04MemberInsert.jsp" method="post"> <p> <label for="uNum">num :</label> <label><%=num1 %></label> <input type="hidden" id="uNum" name="num" value="<%=num1 %>"> <!-- Max(num)+1을 hidden--> </p> <p> <label for="uId">ID :</label> <input type="text" name="id" value=""> </p> <p> <label for="uPass">Password :</label> <input type="password" id="uPass" name="pwd" size="20" value=""> </p> <p> <label for="uName">이름 :</label> <input type="text" id="uName" name="name" size="20" value=""> </p> <p> <label for="uEmail">E-mail :</label> <input type="text" id="uEmail" name="email" size="20" value=""> </p> <p> <label for="uPhone">Phone :</label> <input type="text" id="uPhone" name="phone" size="20" value=""> </p> <p> <label for="admin">관리자 </label> <input type="radio" id="admin" name="admin" value="1" checked="checked"> <label for="nomal">일반 </label> <input type="radio" id="nomal" name="admin" value="0" > </p> <input type="submit" value="제출"> </form> </body> </html> | cs |
예제 4. L04MemberInsert.jsp
위의 예제에서 입력받은 내용을 가져와서 쿼리문을 통해 서버에 등록해주는 역할을 하는 곳이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>Insert title here</title> </head> <% Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; int insert = 0; int num = Integer.parseInt(request.getParameter("num")); String id = request.getParameter("id"); String pwd = request.getParameter("pwd"); String name = request.getParameter("name"); String phone = request.getParameter("phone"); String email = request.getParameter("email"); String admin = request.getParameter("admin"); %> <h3>id: <%=id %></h3> <% response.setCharacterEncoding("utf-8"); String url = "jdbc:oracle:thin:@localhost:1521:xe"; String uid="jsp_lesson"; String pass = "1234"; String sql = "INSERT INTO MEMBER (NUM, ID, PWD, NAME, EMAIL, PHONE, INDATE, ADMIN) VALUES (?, ?, ?, ?, ?, ?, CURRENT_DATE, ?)"; String id_sql = "SELECT ID FROM MEMBER WHERE ID=?"; %> <body> <% try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); pstmt = conn.prepareStatement(id_sql); pstmt.setString(1, id); rs = pstmt.executeQuery(); if(!rs.next()){ pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); pstmt.setString(2, id); pstmt.setString(3, pwd); pstmt.setString(4, name); pstmt.setString(5, email); pstmt.setString(6, phone); pstmt.setString(7, admin); insert = pstmt.executeUpdate(); } if(insert>0){ response.sendRedirect("./L01MemberList.jsp"); }else{ response.sendRedirect("./L03MemberAddForm.jsp?msg=already existed the same id"); } } catch (Exception e){e.printStackTrace(); }finally { try { if(rs!=null)rs.close(); if(pstmt!=null)pstmt.close(); if(conn!=null)conn.close(); } catch (Exception e){e.printStackTrace(); } } %> </body> </html> | cs |
예제 5. L05MemberUpdateForm.jsp
이번 예제는 회원가입 양식과 비슷하다.
MemberList에서 수정 값을 누르면 파라미터 값으로 num 값을 갖고 이번 예제로 넘어온다.
num 값을 이용해서 쿼리문을 실행하고 결과 값을 각 항목에 넣어준다!
num과 id는 수정이 불가능하게 하고 다른 항목을 수정하여 Form 태그를 이용해 다음 jsp file로 이동한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>Insert title here</title> </head> <%! Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String id,pwd,name,email,phone,admin; String url = "jdbc:oracle:thin:@localhost:1521:xe"; String uid="jsp_lesson"; String pass = "1234"; String sql = "SELECT * FROM MEMBER WHERE NUM=?"; %> <body> <% response.setCharacterEncoding("utf-8"); int num = Integer.parseInt(request.getParameter("num")); try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, num); rs = pstmt.executeQuery(); if(rs.next()){ id = rs.getString("id"); pwd = rs.getString("pwd"); name = rs.getString("name"); email = rs.getString("email"); phone = rs.getString("phone"); admin = rs.getString("admin"); } } catch (Exception e){e.printStackTrace(); } %> <body> <h1>회원정보 수정</h1> <form action="L06UpdateOk.jsp" method="post"> <p> <label for="uNum">num :</label> <label><%=num%></label> <input type="hidden" id="uNum" name="num" value="<%=num%>"> </p> <p> <label for="uId">ID :</label> <label><%=id%></label> <input type="hidden" name="id" value="<%=id%>"> </p> <p> <label for="uPass">Password :</label> <input type="password" id="uPass" name="pwd" size="20" value=""> </p> <p> <label for="uName">이름 :</label> <input type="text" id="uName" name="name" size="20" value="<%=name%>"> </p> <p> <label for="uEmail">E-mail :</label> <input type="text" id="uEmail" name="email" size="20" value="<%=email%>"> </p> <p> <label for="uPhone">Phone :</label> <input type="text" id="uPhone" name="phone" size="20" value="<%=phone%>"> </p> <p> <label for="admin">관리자 </label> <input type="radio" id="admin" name="admin" value="1" <% if(admin.equals("1")){ %>checked="checked"<%} %>> <label for="nomal">일반 </label> <input type="radio" id="nomal" name="admin" value="0" <% if(admin.equals("0")){ %>checked="checked"<%} %>> </p> <input type="submit" value="수정"> </form> </body> </html> | cs |
예제 6. L06UpdateOk.jsp
위의 예제에서 받아 온 내용을 쿼리문을 이용해 서버에 저장한다!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | <%@page import="java.sql.DriverManager"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.Connection"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>Insert title here</title> </head> <% response.setCharacterEncoding("utf-8"); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; int insert = 0; int num = Integer.parseInt(request.getParameter("num")); String id = request.getParameter("id"); String pwd = request.getParameter("pwd"); String name = request.getParameter("name"); String phone = request.getParameter("phone"); String email = request.getParameter("email"); String admin = request.getParameter("admin"); String url = "jdbc:oracle:thin:@localhost:1521:xe"; String uid="jsp_lesson"; String pass = "1234"; String sql = "Update member set pwd=?, name=?, email=?, phone=?, indate=CURRENT_DATE, admin=? where num=?"; %> <body> <% try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url,uid,pass); pstmt = conn.prepareStatement(sql); pstmt.setString(1, pwd); pstmt.setString(2, name); pstmt.setString(3, email); pstmt.setString(4, phone); pstmt.setString(5, admin); pstmt.setInt(6, num); insert = pstmt.executeUpdate(); if(insert>0){ response.sendRedirect("./L01MemberList.jsp"); }else{ response.sendRedirect("./L05MemberUpdateForm.jsp"); } } catch (Exception e){e.printStackTrace(); }finally { try { if(rs!=null)rs.close(); if(pstmt!=null)pstmt.close(); if(conn!=null)conn.close(); } catch (Exception e){e.printStackTrace(); } } %> </body> </html> | cs |
'IT story > JSP' 카테고리의 다른 글
| [JSP 강의] L14Model2 (0) | 2016.11.17 |
|---|---|
| [JSP 강의] L13JSTL (0) | 2016.11.17 |
| [Jsp 강의] L11ELtag (0) | 2016.10.27 |
| [Jsp 강의] L10Bean (0) | 2016.10.27 |
| [Jsp 강의] L09Cookie (0) | 2016.10.27 |