DAO(Data Access Object),DTO(Data Transfer Object).connection pool(DBCP)

2018. 3. 18. 22:45

-DTO : 데이터 접속에 필요한 변수와 데이터를 일반적인 변수에 할당하지 않고 DTO 클래스를 통해서 일괄 관리하는 것

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
package com.javalec.daotoex;
 
public class MemberDTO {
 
    private String name;
    private String id;
    private String pw;
    private String phone1;
    private String phone2;
    private String phone3;
    private String gender;
    
    public MemberDTO(String name, String id, String pw, String phone1, String phone2, String phone3, String gender) {
        this.name = name;
        this.id = id;
        this.pw = pw;
        this.phone1 = phone1;
        this.phone2 = phone2;
        this.phone3 = phone3;
        this.gender = gender;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getPw() {
        return pw;
    }
 
    public void setPw(String pw) {
        this.pw = pw;
    }
 
    public String getPhone1() {
        return phone1;
    }
 
    public void setPhone1(String phone1) {
        this.phone1 = phone1;
    }
 
    public String getPhone2() {
        return phone2;
    }
 
    public void setPhone2(String phone2) {
        this.phone2 = phone2;
    }
 
    public String getPhone3() {
        return phone3;
    }
 
    public void setPhone3(String phone3) {
        this.phone3 = phone3;
    }
 
    public String getGender() {
        return gender;
    }
 
    public void setGender(String gender) {
        this.gender = gender;
    }
    
}
 
Colored by Color Scripter
cs

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
package com.javalec.daotoex;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
 
public class MemberDAO {
 
    private String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private String uid = "scott";
    private String upw = "tiger";
    
    
    public MemberDAO() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public ArrayList<MemberDTO> memberSelect() {
        
        ArrayList<MemberDTO> dtos = new ArrayList<MemberDTO>();
        
        Connection con =null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            con = DriverManager.getConnection(url, uid, upw);
            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from member");
            
            while (rs.next()) {
                String name = rs.getString("name");
                String id = rs.getString("id");
                String pw = rs.getString("pw");
                String phone1 = rs.getString("phone1");
                String phone2 = rs.getString("phone2");
                String phone3 = rs.getString("phone3");
                String gender = rs.getString("gender");
                
                MemberDTO dto = new MemberDTO(name, id, pw, phone1, phone2, phone3, gender);
                dtos.add(dto);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null) rs.close();
                if(stmt != null) stmt.close();
                if(con != null) con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        return dtos;
    }
    
}
 
Colored by Color Scripter
cs

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
<%@page import="com.javalec.daotoex.MemberDTO"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.javalec.daotoex.MemberDAO"%>
<%@ 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>
 
    <%
        MemberDAO memberDAO = new MemberDAO();
        ArrayList<MemberDTO> dtos = memberDAO.memberSelect();
        
        for(int i=0; i<dtos.size(); i++) {
            MemberDTO dto = dtos.get(i);
            String name = dto.getName();
            String id = dto.getId();
            String pw = dto.getPw();
            String phone = dto.getPhone1() + " - "+ dto.getPhone2() + " - " + dto.getPhone3();
            String gender = dto.getGender();
            
            out.println("이름 : " + name + ", 아이디 : " + id + ", 비밀번호 : " + pw + ", 연락처 : " + phone + ",  성별 : " + gender + "<br />" );
        }
        
    %>
 
</body>
</html>
Colored by Color Scripter
cs

-쿼리문을 작성할 때, 쿼리에 변수를 넣는 과정이 귀찮기 때문에 그것을 편리하게 하기 위해 나온 객체

1
2
3
4
5
6
7
String id = "abc"
String pw = "123"
String name = "홍길동"
String phone ="0103323123"
 
String query = "insert into member(id,pw,name,phone) values("'+id+"','"+pw+"','"+name+"','"+phone+"')"
Colored by Color Scripter
cs

1
2
3
4
5
6
7
8
9
10
String id = "abc"
String pw = "123"
String name = "홍길동"
String phone ="0103323123"
 
String query = "insert into member(id,pw,name,phone) values(?,?,?,?)"
preparedStatement.setString(1,id)
preparedStatement.setString(2,pw)
preparedStatement.setString(3,name)
preparedStatement.setString(4,phone)
Colored by Color Scripter
cs

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.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
    <%!
        Connection connection;
        PreparedStatement preparedStatement;
        ResultSet resultSet;
    
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String uid = "scott";
        String upw = "tiger";
    %>
<!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>
 
    <%
        try{
            
            Class.forName(driver);
            connection = DriverManager.getConnection(url, uid, upw);
            int n;
            String query = "insert into memberforpre (id, pw, name, phone) values (?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(query);
            
            preparedStatement.setString(1, "abc");
            preparedStatement.setString(2, "123");
            preparedStatement.setString(3, "홍길동");
            preparedStatement.setString(4, "010-1234-5678");
            n = preparedStatement.executeUpdate();
            
            preparedStatement.setString(1, "def");
            preparedStatement.setString(2, "456");
            preparedStatement.setString(3, "홍길자");
            preparedStatement.setString(4, "010-9012-3456");
            n = preparedStatement.executeUpdate();
            
            preparedStatement.setString(1, "ghi");
            preparedStatement.setString(2, "789");
            preparedStatement.setString(3, "홍길순");
            preparedStatement.setString(4, "010-7890-1234");
            n = preparedStatement.executeUpdate();
            
            preparedStatement.setString(1, "AAA");
            preparedStatement.setString(2, "111");
            preparedStatement.setString(3, "이길동");
            preparedStatement.setString(4, "010-1234-1111");
            n = preparedStatement.executeUpdate();
            
            if(n == 1) {
                out.println("insert success");
            } else { 
                out.println("insert fail");
            }
            
        } catch(Exception e) {
                e.printStackTrace();
        } finally {
            try{
                if(resultSet != null) resultSet.close();
                if(preparedStatement != null) preparedStatement.close();
                if(connection != null) connection.close();
            } catch(Exception e){}
        }
    %>
    
    <br />
    <a href="memberDateView.jsp">회원정보 보기</a>
 
</body>
</html>
Colored by Color Scripter
cs

-동시에 클라이언트로부터 database connection 객체 생성 요청이 들어오와 그때그때 connection객체를 생성하면 데이터 베이스에 부하가 걸리기 때문에, 일단 커넥션풀에 connection객체를 미리 만들어놓고, 클라이언트로부터 요청이 들어오면 하나하나 배당하는 로직

-DBCP는 자바에서 존재하는 것이 아니고 tomcat WAS에 미리 만들어 놓으라고 요청하는 것. 따라서 tomcat컨테이너가 connection을 미리 만들어 놓도록 context.xml에 설정해둔다.

-context.xml을 수정한 후에는 톰캣에 싱크를 하기 위해서 위 스크린샷의 버튼을 눌러준다.

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
package com.javalec.daotoex;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
 
public class MemberDAO {

//원래 일반적인 ODBC접속이라면  Class.forName("oracle.jdbc.driver.OracleDriver");로 디비 접속을 하지만, 톰캣커넥션 풀에 이미
//커넥션 객체를 쌓아놓은 경우라면 디비 접속하는 부분은 필요 없어진다.
//아래의 빨간색 폰트들은 커넥션풀을 이용하면서 삭제된 코드들이고, 파랑색 폰트들은 새로 추가된 코드들이다. 
 
//    private String url = "jdbc:oracle:thin:@localhost:1521:xe";
//    private String uid = "scott";
//    private String upw = "tiger";
    
    private DataSource dataSource;
    
    public MemberDAO() {
   

    
//        try {
//            Class.forName("oracle.jdbc.driver.OracleDriver");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }

        try {
            Context context = new InitialContext();
            dataSource = (DataSource)context.lookup("java:comp/env/jdbc/Oracle11g");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public ArrayList<MemberDTO> memberSelect() {
        
        ArrayList<MemberDTO> dtos = new ArrayList<MemberDTO>();
        
        Connection con =null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
//          con = DriverManager.getConnection(url, uid, upw);
            con = dataSource.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from member");
            
            while (rs.next()) {
                String name = rs.getString("name");
                String id = rs.getString("id");
                String pw = rs.getString("pw");
                String phone1 = rs.getString("phone1");
                String phone2 = rs.getString("phone2");
                String phone3 = rs.getString("phone3");
                String gender = rs.getString("gender");
                
                MemberDTO dto = new MemberDTO(name, id, pw, phone1, phone2, phone3, gender);
                dtos.add(dto);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null) rs.close();
                if(stmt != null) stmt.close();
                if(con != null) con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        return dtos;
    }
    
}
 
Colored by Color Scripter
cs

저작자표시 비영리 변경금지

'frameworks > JSP_Servlet' 카테고리의 다른 글

expression language(${}, ) (0)	2018.03.28
파일 업로드(<input type="submit">, com.oreilly.servlet.multipart객체, 업로드한 파일명을 불러오는 getFilesystemName, 클라이언트가 업로드한 파일명을 불러오는 getOriginalFileName) (0)	2018.03.27
로그인 로직 만들기(Session객체를 통한 로그인 정보의 전달) (0)	2018.03.18
[Jsp/Servlet]데이터베이스2(오라클 API jar을 이클립스에 연동시키는 법, JDBC데이터 베이스 연결 (0)	2018.03.16
자바빈(자바빈이란 무엇인가? <jsp:useBean ~ >, <jsp:setProperty ~ >, <jsp:getProperty ~ > (0)	2018.03.04

가치관제작소

DAO(Data Access Object),DTO(Data Transfer Object).connection pool(DBCP)

'frameworks > JSP_Servlet' 카테고리의 다른 글

+ Recent posts

티스토리툴바