[JDBC]Connection 객체 분리, 클래스 분리 실습예제

Devel/JDBC|2020. 8. 20. 09:18
반응형

-EmpdTo

-EmpDAO
ArrayList:select 후 psmt, resultset을 jdbcTemplate 넘겨서 close
selectDetailEmp: select 후 jdbcTemplate 이용 close
empDelete : delte 후 jdbcTemplate 이용 close

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
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
public class EmpDAO {
    public ArrayList<EmpDTO> selectAllEmp(Connection con) throws Exception{
        //select 후 psmt, resultset을 jdbcTemplate 넘겨서 close
        
        ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
        String sql = "select empno,ename,job,mgr,to_char(hiredate,'YYYY-MM-DD') hiredate ,"+"sal,comm,deptno from emp order by empno desc";
        pstmt = con.prepareStatement(sql);
        rs = pstmt.executeQuery();
        while(rs.next()) {            
            int empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String job = rs.getString("job");
            int mgr = rs.getInt("mgr");
            String hiredate = rs.getString("hiredate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptno = rs.getInt("deptno");
            
            EmpDTO emp = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
            
//            EmpDTO emp = new EmpDTO();
//            emp.setEmpno(rs.getInt(1));
//            emp.setEname(rs.getString(2));
//            emp.setJob(rs.getString(3));
//            emp.setMgr(rs.getInt(4));            
//            emp.setHiredate(rs.getString(5));
//            emp.setSal(rs.getDouble(6));
//            emp.setComm(rs.getDouble(7));
//            emp.setDeptno(rs.getInt(8));
            list.add(emp);
                
        }}catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcTemplate.close(rs);
            JdbcTemplate.close(pstmt);
        }                
        return list;
    }
    //사원번호로 찾기
    public EmpDTO selectDetailEmp(Connection con, String i) throws DataNotFoundException{
        //select 후 jdbcTemplate 이용 close
        EmpDTO emp = new EmpDTO();
        emp = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "select empno,ename,job,mgr,to_char(hiredate,'YYYY-MM-DD') hiredate ,"+"sal,comm,deptno from emp where empno = ? order by empno desc";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, Integer.parseInt(i));
            rs = pstmt.executeQuery();
            while(rs.next()) {
                
                int empno1 = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                String hiredate = rs.getString("hiredate");
                double sal = rs.getDouble("sal");
                double comm = rs.getDouble("comm");
                int deptno = rs.getInt("deptno");                
                emp = new EmpDTO(empno1, ename, job, mgr, hiredate, sal, comm, deptno);                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcTemplate.close(rs);
            JdbcTemplate.close(pstmt);
        }
        return emp;
    }
    
    public void empUpdate(Connection con, EmpDTO empDTO) throws DataNotFoundException{
        int result = 0;
        PreparedStatement pstmt = null;
        try {
            String sql="update emp set job =?,sal =? where empno=?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(3, empDTO.getEmpno());
            pstmt.setString(1, empDTO.getJob());
            pstmt.setDouble(2, empDTO.getSal());
            result = pstmt.executeUpdate();
            System.out.println("실행된 레코드 갯수: "+ result);
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcTemplate.close(pstmt);
        }
    }
    
    public void empDelete(Connection con, String empno) throws Exception{
        //delte 후 jdbcTemplate 이용 close
        int result = 0;
        PreparedStatement pstmt = null;
        try {
            String sql = "delete emp where empno = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, Integer.parseInt(empno));
            result = pstmt.executeUpdate();
            if(result <=0 ) {
                throw new Exception("삭제할 자료가 없습니다.");
            }
            System.out.println("실행된 레코드 갯수: "+ result);
        
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {            
            JdbcTemplate.close(pstmt);
        }
        
        
    }
}
 


-EmpBiz
JdbcTemplate에서 Connection 얻어오고, Close또한
JdbcTemplate.getConnection()에서 con얻기
dao.selectAllEmp(con); 호출 conn전달
dbcTemplate 이용 close(con)으로 컨넥션 끊기

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
package com.biz;
 
import java.awt.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
 
import com.dao.EmpDAO;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
 
public class EmpBiz {//service 클래스 /con만들지 않고
 
    EmpDAO dao;
    
    public EmpBiz() {
        //dao생성
        dao = new EmpDAO();
                
    }
    public ArrayList<EmpDTO> selectAllEmp() throws Exception{
        Connection con = JdbcTemplate.getConnection();
        ArrayList<EmpDTO> list =dao.selectAllEmp(con);
        JdbcTemplate.close(con);
        // JdbcTemplate.getConnection()에서 con얻기
        //dao.selectAllEmp(con); 호출 conn전달
        //dbcTemplate 이용 close(con)으로 컨넥션 끊기 / public static void close(Connection conn)
//        Connection con = null;
//        PreparedStatement pstmt= null;
//        ResultSet rset = null;
//        ArrayList<EmpDTO> list = null;
//        try {
//            
//            
//        
//        }finally {
//        
//            try {
//                
//                
//                JdbcTemplate.close(pstmt);
//                JdbcTemplate.close(rset);
//            
//            } catch (Exception e) {
//                
//                e.printStackTrace();
//                
//            }
//        }
    
        return list;
    }
    public EmpDTO selectDetailEmp(String i) throws DataNotFoundException{
    // JdbcTemplate.getConnection()에서 con 얻기
        //dao.selectDetailEmp(con); 호출
        //dbcTemplate 이용 close
        Connection con = null;
        EmpDTO dto = null;
        try {
            con = JdbcTemplate.getConnection();
            dto =dao.selectDetailEmp(con,i);
            JdbcTemplate.close(con);
            return dto;
        } catch (Exception e) {
            JdbcTemplate.rollback(con);
            System.out.println("rollback");
        }finally {
            JdbcTemplate.close(con);
            return dto;
        }
        
        
        
        
    }
    public void empUpdate( EmpDTO empDTO) throws DataNotFoundException{
        Connection con = null;
        try {
            con = JdbcTemplate.getConnection();
            dao.empUpdate(con, empDTO);
            JdbcTemplate.commit(con);
        } catch (Exception e) {
            JdbcTemplate.rollback(con);
            System.out.println("rollback");
        }finally {
            v
        }
        
        
    }
    
    public void empDelete(String empno) throws Exception{
        Connection con = null;
        try {            
            con = JdbcTemplate.getConnection();
            dao.empDelete(con, empno);
            JdbcTemplate.commit(con);
            
        } catch (Exception e) {
            JdbcTemplate.rollback(con);
            System.out.println("rollback");
        }finally {
            JdbcTemplate.close(con);
        }
        
    }
}
 



-Jdbc Template
드라이버 로딩 및 db연결, con 리턴, con.close()이용
Connection을 연결한 후 멤버 attribute 인 conn 에 Connection 객체를 세팅한 후 리턴한다.
db연결 , 연결된 conncection 리턴
connect연결 후 tx를 false로 설정 리턴  con.setAutoCommit(false);로 설정후 실행 후 따로 Commit
DB와 Connect되었는지 여부를 Return 한다.

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
160
161
162
163
 
 
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
public class JdbcTemplate {//드라이버 로딩 및 db연결, con 리턴, con.close()
    //
 
    public static String driver="oracle.jdbc.driver.OracleDriver";
    public static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    public static String userid = "scott";
    public static String passwd = "tiger";
    
    public JdbcTemplate() {
        //드라이버로딩
        try {
            Class.forName(driver);
                    
        } catch (Exception e) {        
            e.printStackTrace();
        }
    }
    /**
     * Connection을 연결한 후 멤버 attribute 인 conn 에 Connection 객체를 세팅한 후 리턴한다.
     * 
     * @return Connection
     */
    public static Connection getConnection() {
        //db연결 , 연결된 conncection 리턴
       // connect연결 후 tx를 false로 설정 리턴  con.setAutoCommit(false);
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, userid, passwd);
            con.setAutoCommit(false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("[JdbcTemplate.getConnection]"+e.getMessage());
            e.printStackTrace();
        }
        
        return con;//connection리턴
    }
 
    /**
     * DB와 Connect되었는지 여부를 Return 한다.
     * 
     * @return DB와 Connect 되었는지 여부.
     */
    public static void close(Connection con) {
        
        try {
            if(con != null)con.close();                            
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        
        
        //isConnected()이용 검사후 close
     }
//    public static void close(Connection conn) {
//    if(isConnected(con)) {
//        try {
//            con.close();                            
//                } catch (SQLException e) {
//                    // TODO Auto-generated catch block
//                    e.printStackTrace();
//                }
//        
//        
//        //isConnected()이용 검사후 close
//     }}
    public static boolean isConnected(Connection con) {
 
        boolean validConnection = true;
        try {
            if(con == null || con.isClosed()) {
                validConnection = false;
            }
        } catch (SQLException e) {
            validConnection = false;
            e.printStackTrace();
        }
//conn 이 null 이거나   conn.isClose()가 참이면  validConnection을 false 로 설정 
        return validConnection;
    }
 
    /**
     * Connection 객체를 시스템에 반환한다.
     */
 
 
    /**
     * Statement를 Close 한다.
     * 
     * @param stmt
     *            Statement 객체.
     */
    public static void close(PreparedStatement pstmt) {
        
        try {
            if( pstmt != null)pstmt.close(); 
        } catch (Exception e) {            
                    e.printStackTrace();
                }
        }
       //dao에서  pstmt을 넘겨줘서 preparedStatement 를 close;//stmt close() // try-catch처리
    
 
    /**
     * ResultSet을 Close 한다.
     * 
     * @param result
     *            ResultSet 객체.
     */
    public static void close(ResultSet rset) {
        
        try {
            if(rset != null)rset.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
      //rset close
    }
 
    /**
     * 지금까지의 트랜잭션을 Commit 처리한다.
     */
    public static void commit(Connection conn) {
 
        try {
            if (isConnected(conn)) {
                conn.commit();
                System.out.println("[JdbcTemplate.commit] : DB Successfully Committed!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 지금까지의 트랜잭션을 Rollback 처한다.
     */
    public static void rollback(Connection conn) {
 
        try {
            if (isConnected(conn)) {
                conn.rollback();
                System.out.println("[JdbcTemplate.rollback] : DB Successfully Rollbacked!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
}
 



-EmpTest
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
import java.util.ArrayList;
 
import com.biz.EmpBiz;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;
 
 
public class EmpTest {
 
    
    public static void main(String[] args){
        
        EmpBiz empBiz = new EmpBiz();
//        ArrayList<EmpDTO> list = empBiz.selectAllEmp();
    
//        try {
//            EmpDTO list = empBiz.selectDetailEmp("107");
//            System.out.println(list);
//        } catch (DataNotFoundException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
        
        try {
            empBiz.empDelete("103");
        } catch (Exception e) {            
            e.printStackTrace();
        }
    
//        System.out.println(list);
//        empBiz.selectDetailEmp("107");
//        empBiz.empDelete("107");
        
//        empBiz.empUpdate(new EmpDTO(105, "josh","owner",7000, null, 1000, 500, 30));
                    //for each
        
    }//end main
}//end class
 
 




-MySqlSessionFactory
Template Class와 동일
필요한 정보들 configuration에 들어가 있음.

파일을 읽어와야하기 때문에
InputStream 을 함.
18~24라인
            try {
                  inputStream = Resources.getResourceAsStream(resource);
            } catch (IOException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
            }
       sqlSessionFactory =
            new SqlSessionFactoryBuilder().build(inputStream);
      }//end static
con 얻어갈 수 있는 클래스를 생성 sqlSessionFactory
getSession을 통해 접속
connection대신 사용

Biz클래스를 Service클래스라 생각.
메인에서 service함수 호출

-service클래스

저작자표시 변경금지

'Devel > JDBC' 카테고리의 다른 글

[JDBC]Mybatis설정방법 및 적용예제2  (0) 2020.08.20
[JDBC]Mybatis설정방법 및 적용예제  (0) 2020.08.20
[JDBC] 2 table Join, group by 사용하는 방법 및 적용예제  (0) 2020.08.19
[JDBC] 클래스 생성부터 분리까지 모든 예제  (0) 2020.08.19
[JDBC]SQL연동 클래스 분리 예제2(DAO, DTO, exception, service)  (0) 2020.08.19
댓글()
loading

티스토리툴바