웹 프로그래밍 16(JSP,DB)-PreparedStatement
*PreparedStatement
-DB 사용을 위해 조금 더 중복을 줄이기 위한 객체
-보통 db를 사용할때 필요한 객체는 다음과 같다.
Connection
Statement
ResultSet
하지만 Statement를 사용할 시에 중복되는 경우가 많다. 다음 예를 살펴보자
-Statement 예제
사용자 4명의 개인정보를 DB에 입력할려고 한다.
|
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 |
Connection conn = Drivermanager.getConnection(url,uid,upw); Statement st = conn.createStatement(); ResultSet rs = st.executeUpdate("insert into member valuse('"+001+"','"+아무개1+"','"+010-1234-1234+"')"; issuccess(rs.executeQuery()); ResultSet rs = st.executeUpdate("insert into member valuse('"+002+"','"+아무개2+"','"+010-4321-3333+"')"; issuccess(rs.executeQuery()); ResultSet rs = st.executeUpdate("insert into member valuse('"+003+"','"+아무개3+"','"+010-1111-4444+"')"; issuccess(rs.executeQuery()); ResultSet rs = st.executeUpdate("insert into member valuse('"+004+"','"+아무개4+"','"+010-2222-5555+"')"; issuccess(rs.executeQuery());
public void issuccess(int k) { int i=k; if(i == 1) { System.out.println("insert success"); } else { System.out.println("insert fail"); } } |
ResultSet 객체에 Statament 갱신값을 계속 적어주어야 한다.
소스 보기도 힘들고 효율이 많이 떨어지는것을 볼 수 있다.
-PreparedStatement를 사용해보자
|
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 |
Connection conn = Drivermanager.getConnection(url,uid,upw); String query = "insert into member (id,pw,name,phone) values(?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, "aaa"); ps.setString(2, "123"); ps.setString(3, "일번"); ps.setString(4, "011-1111-1234"); issuccess(ps.executeUpdate());
ps.setString(1, "bbb"); ps.setString(2, "123"); ps.setString(3, "이번"); ps.setString(4, "011-2222-4567"); issuccess(ps.executeUpdate());
ps.setString(1, "ccc"); ps.setString(2, "123"); ps.setString(3, "삼번"); ps.setString(4, "011-3333-8910"); issuccess(ps.executeUpdate());
ps.setString(1, "ddd"); ps.setString(2, "123"); ps.setString(3, "사번"); ps.setString(4, "011-4444-1112"); issuccess(ps.executeUpdate()); public void issuccess(int k) { int i=k; if(i == 1) { System.out.println("insert success"); } else { System.out.println("insert fail"); } } |
Statement보다 훨씬 보기 쉽고 입력하기 간편하다는것을 볼 수 있다.
===========================소스=================================
-실전예제
그럼 실제로 데이터를 넣어보자
-memberDatainsert.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 |
<%@page import="java.sql.*"%> <%@ 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> <%! String name; String id; String pw; String phone;
Connection conn = null; PreparedStatement ps = null; public void issuccess(int i) { if(i == 1) { System.out.println("insert success"); } else { System.out.println("insert fail"); } } %> <% try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:clouddb","testid","password"); String query = "insert into member (id,pw,name,phone) values(?,?,?,?)"; ps = conn.prepareStatement(query);
ps.setString(1, "aaa"); ps.setString(2, "123"); ps.setString(3, "apple"); ps.setString(4, "011-1111-1234"); issuccess(ps.executeUpdate());
ps.setString(1, "bbb"); ps.setString(2, "123"); ps.setString(3, "grape"); ps.setString(4, "011-2222-4567"); issuccess(ps.executeUpdate());
ps.setString(1, "ccc"); ps.setString(2, "123"); ps.setString(3, "dog"); ps.setString(4, "011-3333-8910"); issuccess(ps.executeUpdate());
ps.setString(1, "ddd"); ps.setString(2, "123"); ps.setString(3, "cat"); ps.setString(4, "011-4444-1112"); issuccess(ps.executeUpdate()); } catch(Exception e) { e.printStackTrace(); } finally { try{ if(conn != null) conn.close(); if(ps != null) ps.close(); }catch(Exception e){ e.printStackTrace(); } } %> </body> </html> |
-출력결과
-Console
-Oracle db
