본문 바로가기
풀스택/css

그날그날 정리 1 jdbc dao와 vo

by woohyun22 2019. 1. 21.

Main(test)에서는 보통 입력값과 그에 해당하는  함수 호출 후, 리스트 출력이 이루어진다.


vo에서는 getset과 보여줄 String 값전달 및 출력을 나타냄


dao에서는 관련 sql구문들이 함수 호출시 실행되며 getconnection, getlist 등을 구현


보통 이클립스의 jdbc는 아래의 예시를 기본으로 쓰나 저번에 한 것중 jdbc 가장 기본을 참고


이 부분은 그 jdbc sql문들의 기본을 활용하여 dao와 vo사용


dao와 vo에 대한 확실한 정의와 정리도 정리해서 쓸 것.


Main.java


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
package com.douzon.jdbc.bookshop;
 
import java.util.List;
import java.util.Scanner;
 
import com.douzon.jdbc.bookshop.dao.BookDao;
import com.douzon.jdbc.bookshop.vo.BookVo;
 
public class MainApp {
    public static void main(String[] args) {
        displayBookInfo();
        
        Scanner scanner = new Scanner(System.in);
        System.out.print("대여 하고 싶은 책의 번호를 입력하세요:");
        long no = scanner.nextLong();
        scanner.close();
        
        rent(no);
        displayBookInfo();
    }
    
    private static void rent(long no) {
        new BookDao().update(no, "대여중");
    }
    
    private static void displayBookInfo() {
        System.out.println("*****도서 정보 출력하기******");
 
        List<BookVo> list = new BookDao().getList();
        for(BookVo vo:list) {
            System.out.println("[" + vo.getNo() + "]책제목:" + 
vo.getTitle() + ", 저자:" + vo.getAuthorName() + ", 대여유무:" + vo.getStatus() );
        }
    }    
}
cs


BookDao.java


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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
package com.douzon.jdbc.bookshop.dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
import com.douzon.jdbc.bookshop.vo.BookVo;
 
public class BookDao {
    public boolean update(BookVo bookVo) {
        // 책정보 수정
        return false;
    }
    
    public boolean update(long no, String status) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            conn = getConnection();
            String sql =
                " update book " + 
                "    set status=?" +
                "  where no=?";
            pstmt = conn.prepareStatement(sql);
 
            pstmt.setString(1, status);
            pstmt.setLong(2, no);
            
            int count = pstmt.executeUpdate();
            result = count == 1;
            
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return result;
    }
    
    public boolean insert(BookVo bookVo) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            conn = getConnection();
            String sql =
                " insert " +
                "   into book(title, author_no)" + 
                " values (?, ?)";
            pstmt = conn.prepareStatement(sql);
 
            pstmt.setString(1, bookVo.getTitle());
            pstmt.setLong(2, bookVo.getAuthorNo());
            
            int count = pstmt.executeUpdate();
            result = count == 1;
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    
    public List<BookVo> getList(){
        List<BookVo> list = new ArrayList<BookVo>();
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection();
            stmt = conn.createStatement();
 
            String sql = 
                "   select a.no, a.title, a.status, b.name" + 
                "     from book a, author b" + 
                "    where a.author_no = b.no" + 
                " order by a.no asc";
            rs = stmt.executeQuery(sql);
            
            while(rs.next()) {
                long no = rs.getLong(1);
                String title = rs.getString(2);
                String status = rs.getString(3);
                String authorName = rs.getString(4);
                
                BookVo vo = new BookVo();
                vo.setNo(no);
                vo.setTitle(title);
                vo.setStatus(status);
                vo.setAuthorName(authorName);
                
                list.add(vo);
            }
            
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
                if(stmt != null) {
                    stmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return list;        
    }
 
    private Connection getConnection() throws SQLException {
        Connection conn = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url = "jdbc:mysql://127.0.0.1:3306/webdb";
            conn = DriverManager.getConnection(url, "webdb", "webdb");
        } catch (ClassNotFoundException e) {
            System.out.println("드라이버 로딩 실패:" + e);
        }
        
        return conn;
    }
}
 
cs


BookVo.java


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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
package com.douzon.jdbc.bookshop.dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
import com.douzon.jdbc.bookshop.vo.BookVo;
 
public class BookDao {
    public boolean update(BookVo bookVo) {
        // 책정보 수정
        return false;
    }
    
    public boolean update(long no, String status) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            conn = getConnection();
            String sql =
                " update book " + 
                "    set status=?" +
                "  where no=?";
            pstmt = conn.prepareStatement(sql);
 
            pstmt.setString(1, status);
            pstmt.setLong(2, no);
            
            int count = pstmt.executeUpdate();
            result = count == 1;
            
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return result;
    }
    
    public boolean insert(BookVo bookVo) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            conn = getConnection();
            String sql =
                " insert " +
                "   into book(title, author_no)" + 
                " values (?, ?)";
            pstmt = conn.prepareStatement(sql);
 
            pstmt.setString(1, bookVo.getTitle());
            pstmt.setLong(2, bookVo.getAuthorNo());
            
            int count = pstmt.executeUpdate();
            result = count == 1;
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    
    public List<BookVo> getList(){
        List<BookVo> list = new ArrayList<BookVo>();
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection();
            stmt = conn.createStatement();
 
            String sql = 
                "   select a.no, a.title, a.status, b.name" + 
                "     from book a, author b" + 
                "    where a.author_no = b.no" + 
                " order by a.no asc";
            rs = stmt.executeQuery(sql);
            
            while(rs.next()) {
                long no = rs.getLong(1);
                String title = rs.getString(2);
                String status = rs.getString(3);
                String authorName = rs.getString(4);
                
                BookVo vo = new BookVo();
                vo.setNo(no);
                vo.setTitle(title);
                vo.setStatus(status);
                vo.setAuthorName(authorName);
                
                list.add(vo);
            }
            
        } catch (SQLException e) {
            System.out.println("error:" + e);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
                if(stmt != null) {
                    stmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        return list;        
    }
 
    private Connection getConnection() throws SQLException {
        Connection conn = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url = "jdbc:mysql://127.0.0.1:3306/webdb";
            conn = DriverManager.getConnection(url, "webdb", "webdb");
        } catch (ClassNotFoundException e) {
            System.out.println("드라이버 로딩 실패:" + e);
        }
        
        return conn;
    }
}
 
cs





728x90

'풀스택 > css' 카테고리의 다른 글

css ex12~  (0) 2019.02.14
html/css/javascript  (0) 2019.02.11
다시시작  (0) 2019.02.09

관련글

댓글

티스토리툴바