본문 바로가기
매일코딩/JSP

2.JSP - MYSQL JSP 연동 &리스트 뽑아오기

by 인생여희 2016. 11. 8.
반응형

MYSQL JSP 연동 &리스트 뽑아오기


MYSQL 홈페이지에서 mysql-connector-java-5.1.40 다운받아 압축을 푼뒤 lib 파일에 넣어준다. 


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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
   <%@page import="book.*"%> 
   <%@page import="java.util.ArrayList"%>
<!DOCTYPE  >
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
 
<h2> 도서목록</h2>
 
<table border="1"
 
    <tr>
                <th>번호</th>    
                <th>도서명</th>
                <th>저자</th>
                <th>가격</th>
                <th>수량</th>
    
    </tr>
 
 
<%
        BookDAO dao=new BookDAO();
        //dao.dbConn();   db 연결 확인 작업
        
         ArrayList<bookDTO>list=dao.bookList();
         for(bookDTO dto:list){
                     
%>
 
        <tr>
                <td><%=dto.getId() %></td>
                <td><%=dto.getTitle() %></td>
                <td><%=dto.getAuthor() %></td>
                <td><%=dto.getPrice() %></td>
                <td><%=dto.getQty() %></td>
        
        </tr>
 
 
 
<%
 
         } //for 문의 끝
%>
 </table>
 
</body>
</html>
cs



2. bookDTO   데이터 담는 그릇



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
package book;
 
 
// dto: data transfer object(데이터 전달 객체)
//vo , to 라고도 함
//자료 저장 클래스
public class bookDTO {
 
    private int id;
    private String title;
    private String author;
    private int price;
    private int qty;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getTitle() {
        return title;
    }
 
    public void setTitle(String title) {
        this.title = title;
    }
 
    public String getAuthor() {
        return author;
    }
 
    public void setAuthor(String author) {
        this.author = author;
    }
 
    public int getPrice() {
        return price;
    }
 
    public void setPrice(int price) {
        this.price = price;
    }
 
    public int getQty() {
        return qty;
    }
 
    public void setQty(int qty) {
        this.qty = qty;
    }
 
    // 기본생성자
    public bookDTO() {
        // TODO Auto-generated constructor stub
    }
 
    // 매개변수가 있는 생성자
    public bookDTO(String title, String author, int price, int qty) {
        super();
        this.title = title;
        this.author = author;
        this.price = price;
        this.qty = qty;
    }
 
    @Override
    public String toString() {
        return "bookDTO [id=" + id + ", title=" + title + ", author=" + author + ", price=" + price + ", qty=" + qty
                + "]";
    }
 
}
 
cs



3. BOOKDAO


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
96
97
98
package book;
 
// jdbc import
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
 
public class BookDAO {
 
    // db접속
    public Connection dbConn() {
 
        Connection conn = null// db접속 객체
 
        try {
            // mysql jdbc driver 로딩
            Class.forName("com.mysql.jdbc.Driver");
 
            // db연결 문자열 but 이방법은 보안에 취약하다. ..
            String url = "jdbc:mysql://localhost:3306/java";
            String id = "java"// mysql 접속아이디
            String pwd = "java1234"// mysql 접속 비번
 
            // db 접속
 
            conn = DriverManager.getConnection(url, id, pwd);
            System.out.println("db접속 성공");
        } catch (Exception e) {
            // db관련작업은 반드시 익셉션 처리
            System.out.println("db접속 실패");
            e.printStackTrace();
        }
        return conn;
 
    }
 
    // 북리스트
    public ArrayList<bookDTO> bookList() {
 
        ArrayList<bookDTO> list = new ArrayList<bookDTO>();
        Connection conn = null// DB접속 객체
        PreparedStatement pstmt = null// SQL실행객체
        ResultSet rs = null// 결과셋 처리 객체
 
        try {
            conn = dbConn(); // db연결 키
            String sql = "select * from book_table";
            pstmt = conn.prepareStatement(sql); // sql을 실행시키는 객체 만들어짐
            rs = pstmt.executeQuery(); // 실행 후 결과 값이 rs에 넘어옴
 
            while (rs.next()) {                    //결과셋.next(); 다음 레코드가 있으면 true
 
                bookDTO dto = new bookDTO();
                dto.setId(rs.getInt("id"));
                dto.setTitle(rs.getString("title"));
                dto.setAuthor(rs.getString("author"));
                dto.setPrice(rs.getInt("price"));
                dto.setQty(rs.getInt("qty"));
                
                //ArrayList에 추가
                list.add(dto);
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {        //오픈한 역순으로 닫기작업 실행
            
            //resultset= > statement=> connection
                
            try{
                if(rs!=null){rs.close();}
                
            }catch(Exception e2){
                e2.printStackTrace();
            }
            
            try{
                if(pstmt!=null){pstmt.close();}
                
            }catch(Exception e2){
                e2.printStackTrace();
            }
            
            try{
                if(conn!=null){conn.close();}
                
            }catch(Exception e2){
                e2.printStackTrace();
            }
            
        }
        return list;
    }
 
}
 
cs


반응형

댓글