공부/최범균jsp2.2 췝프로그래밍

[자바] JDBC 구조 기초 + statement & preparedStatement

앗뜨거 2014. 7. 1. 02:27
728x90

JDBC 프로그램의 일반적인 실행순서

1. JDBC 드라이버 로딩

2. 데이터베이스 커넥션 구함

3. 쿼리 실행을 위한 Statement 객체 생성

4. 쿼리실행

5. 쿼리실행 결과 사용

6. Statement 종료

7. 데이터베이스 커넥션 종료


예제

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
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import = "java.sql.*" %>
 
<!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>회원 목록</title>
</head>
<body>
MEMBER 테이블의 내용
<table width="100%" border="1">
<tr>
    <td>이름</td><td>아이디</td><td>이메일</td>
</tr>
<%
    //1. jdbc 드라이버 로딩
    Class.forName("com.mysql.jdbc.Driver");
 
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    
    try
    {
        String jdbcDriver = "jdbc:mysql://localhost:3306/chap12?" +
                "useUnicode=true&characterEncoding=euckr";
        String dbUser = "jspexam";
        String dbPass = "jspex";
 
        String query = "select * from MEMBER order by MEMBERID";
        
        //2.데이터베이스 커넥션 생성
        conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
        
        //3.Statements 생성 쿼리 실행을 위한
        stmt = conn.createStatement();
        
        //4.쿼리 실행
        rs = stmt.executeQuery(query);
        
        //5.쿼리 실행 결과 출력
        while(rs.next()){
%>
 
<tr>
    <td><%= rs.getString("NAME") %></td>
    <td><%= rs.getString("MEMBERID") %></td>
    <td><%= rs.getString("EMAIL") %></td>    
</tr>
<%
    }
    }
    catch(SQLException ex)
    {
        out.println(ex.getMessage());
        ex.printStackTrace();
    }
    finally
    {
        //6.사용한 Statement 종료
        if(rs != null) try {rs.close();} catch(SQLException ex){}
        if(stmt != null) try{stmt.close();}catch(SQLException ex){}
        
        //7.커넥션 종료
        if(conn != null) try{conn.close();}catch(SQLException ex){}
    }
%>
</table>
</body>
</html>


자세한 내용은 최번균의 jsp2.2 웹프로그래밍 Chapter12  315 Page에 나와있다 




statement & preparedStatement


1. Statement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + num 
Statement stmt = conn.credateStatement(); 
ResultSet rst = stmt.executeQuerey(sqlstr);

2. PreparedStatement 

String sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? " 
PreparedStatement stmt = conn.prepareStatement(sqlstr); 
pstmt.setInt(1, num); 
ResultSet rst = pstmt.executeQuerey(); 

저작자표시 (새창열림)