본문 바로가기
(Before)BitSchool

2014/05/31 JSP - JDBC, connectionPool

k_brown 2014. 5. 31. 15:37
반응형

JDBC - 표준규격

저수준의 데이터베이스 핸들링

여러가지 데이터베이스프로그램은 각각 연결하는 방법, 사용방법이 다르지만 java 개발자의 편의를 위해 드라이버라는 중간 매개체를 통해 어떠한 데이터베이스도 사용이 가능하게 하는 데이터베이스 핸들링이다. 


C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 안에 ojdbc6.jar이 있는지 확인한다.

ojdbc5는 java1.5이하버젼이고, 6은 java 1.5 이상이다.



그리고 같은 위치에 servlet-api.jar파일도 같이 넣어준다.


C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar\oracle\jdbc\driver\OracleDriver.class   를 사용한다.


C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora

외부에서 접근하는 역할이고, 첫번째 클래스가 XE여야한다.




데이터베이스 설정

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
<%@page import="java.sql.*"%>
<%@ 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>
<!-- oracle\jdbc\driver\ -->
    <%
    // 1. DB에 접속을 위한 드라이버 클래스 로딩
        Class.forName("oracle.jdbc.driver.OracleDriver");
    
        // JDBC를 다룰때 중요한 클래스 3개
        //1) connection : 연결
        //2) statement : 명령 -> select
        //3) resultSet : 결과 -> 결과 집합
        
        Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
    
        if(conn != null){
            out.println("접속 성공");
        }else{
            out.println("접속 실패");
        }
        
        %>
</body>
</html>


접속성공





접속성공후 Data Source Explorer에서 새로 만들기를 한다.



Oracle Thin Driver로 설정



그다음  JARList로 가서 프로젝트에 넣었떤 ojdbc6.jar 를 열어준다.




Properties로 넘어가서 userId와 password를 입력하고 Connection URL은 jsp소스에서 쳤던 거랑 맞춰준다.



이렇게~



Host를 localhost나 자신의 아이피를 입력하고 Test Connection하면 끝!


Database가 이렇게 생성되었다.



이미 기존에 있는 테이블을 볼수 있다.





프로젝트에서 SQL파일을 새로 만들어서 테이블을 생성할 수 도 있다.


블록잡고 오른쪽 클릭후 Excute 해주면 실행된다.





sql에서 새로운 테이블을 만든다.


Create table member(

mid char(10) constraint MEM_ID_PK primary key, 

mpw char(10) constraint MEM_PW_NN not null,

mname char(20) constraint MEM_NAME_NN not null,

mtel char(20)

);


Join_member.jsp

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
<%@ 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>
    <h1>회원가입</h1>
 
    <form action="join_proc.jsp" method="post">
        <table border="2">
            <tr>
                <td>아이디:</td>
                <td><input type="text" name="id"></td>
            </tr>
 
            <tr>
                <td>패스워드:</td>
                <td><input type="text" name="pw"></td>
            </tr>
 
            <tr>
                <td>이름:</td>
                <td><input type="text" name="name"></td>
            </tr>
 
            <tr>
                <td>전화:</td>
                <td><input type="text" name="tel"></td>
            </tr>
 
            <tr>
                <td>가입할껴?</td>
                <td><input type="submit" value="가입하기"></td>
            </tr>
        </table>
    </form>
</body>
</html>


join_proc.jsp

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
<%@ 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>Insert title here</title>
</head>
<body>
    <%
    String id = request.getParameter("id");
    String pw = request.getParameter("pw");
    String name = request.getParameter("name");
    String tel = request.getParameter("tel");
    
    //DB 객체
    Connection conn = null;
    Statement stmt = null;
    
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
    //여기로 접속하자는 소스
    conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
    //연결성공!
    
    //conn객체로 부터 statment객체 생성
    stmt = conn.createStatement(); // 명령을 내리기위한 stmt
    
    String sql = "insert into member values('id','pw','name','tel')";
    
    //stmt.executeQuery(arg0);  // select 역할
    stmt.executeUpdate(sql);  // select를 뺀 나머지 역할(insert,delete,update) 
    
    
    }catch(SQLException e){
        out.println(e.getMessage());
    }finally{
        // 문제가 생기든 안생기든 db는 닫아줘야한다.
        if(stmt==null)
            stmt.close();
        if(conn!=null)
            conn.close();
    }
    
    %>
</body>
</html>






SQL에서 결과를 확인해 볼수 있다.



PreparedStatement를 이용한 JSP구현

join_proc.jsp

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
<%@ 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>Insert title here</title>
</head>
<body>
    <%
    request.setCharacterEncoding("euc-kr");  // 한글깨짐 방지
    
    String id = request.getParameter("id");
    String pw = request.getParameter("pw");
    String name = request.getParameter("name");
    String tel = request.getParameter("tel");
    
    //DB 객체
    Connection conn = null;
    PreparedStatement stmt = null;   // PreparedStatement 사용
    
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
    //여기로 접속하자는 소스
    conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
    //연결성공!
    
    //conn객체로 부터 statment객체 생성
    
    
    
    /*
    preparestatement
    -> 일단 DB에 쿼리문을 전송
    -> 해당하는 값을 그후에 전송
    */
    String sql = "insert into member values(?,?,?,?)";  // 물음표로 들어갈 자리를 만든다.
    
    stmt = conn.prepareStatement(sql);
    
    stmt.setString(1, id);
    stmt.setString(2, pw);
    stmt.setString(3, name);
    stmt.setString(4, tel);
    
    
    //stmt.executeQuery(arg0);  // select 역할
    stmt.executeUpdate();  // select를 뺀 나머지 역할(insert,delete,update) 
    
    
    
    
    }catch(SQLException e){
        out.println(e.getMessage());
    }finally{
        // 문제가 생기든 안생기든 db는 닫아줘야한다.
        if(stmt==null)
            stmt.close();
        if(conn!=null)
            conn.close();
    }
    %>
    
    <h1>가입이 완료되었습니다.</h1>
    <a href="member_list.jsp">목록보기</a>
</body>
</html>


Join_member.jsp

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
<%@ 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>
    <h1>회원가입</h1>
 
    <form action="join_proc.jsp" method="post">
        <table border="2">
            <tr>
                <td>아이디:</td>
                <td><input type="text" name="id"></td>
            </tr>
 
            <tr>
                <td>패스워드:</td>
                <td><input type="text" name="pw"></td>
            </tr>
 
            <tr>
                <td>이름:</td>
                <td><input type="text" name="name"></td>
            </tr>
 
            <tr>
                <td>전화:</td>
                <td><input type="text" name="tel"></td>
            </tr>
 
            <tr>
                <td>가입할껴?</td>
                <td><input type="submit" value="가입하기"></td>
            </tr>
        </table>
    </form>
</body>
</html>


member_list.jsp

DB 불러오기

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 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>Insert title here</title>
</head>
<body >
    <h1>회원 목록</h1>
    
    <table border="1">
        <tr>
            <th>아이디</th>
            <th>패스워드</th>
            <th>이름</th>
            <th>전화번호</th>
        </tr>
        
        
        
        <%
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;  // 결과 값을 한줄 한줄 가져온다.
        
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        
        conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
        
        String sql = "select * from member";
        
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);  // select 기능
        
        while(rs.next()){
        %>
        <tr>
            <td>
        <%
            out.println(rs.getString("mid"));
        %>
            </td>
            <td><%
            out.println(rs.getString("mpw"));
            %>
            </td>
            
            <td><%
            out.println(rs.getString("mname"));
            %>
            </td>
            
            <td><%
            out.println(rs.getString("mtel"));
            %></td>
        
        </tr>
        <%
        }
        
        
        }catch(SQLException e){
            out.println(e.getMessage());
        }finally{
            // 문제가 생기든 안생기든 db는 닫아줘야한다.
            if(stmt==null)
                stmt.close();
            if(conn!=null)
                conn.close();
        }
        %>    
    </table>
</body>
</html>







JSP는 원초적인 방법으로 DB에 접근한다.


CallableStatement를 이용한 JSP구현(중요하진 않다.)


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
<%@ 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>Insert title here</title>
</head>
<body>
    <%
    request.setCharacterEncoding("euc-kr");  // 한글깨짐 방지
    
    String id = request.getParameter("id");
    String pw = request.getParameter("pw");
    String name = request.getParameter("name");
    String tel = request.getParameter("tel");
    
    //DB 객체
    Connection conn = null;
    // 저장 프록시 전용
    CallableStatement stmt = null;   // PreparedStatement 사용
    
    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
    //여기로 접속하자는 소스
    conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:XE","scott","tiger");
    //연결성공!
    
    //conn객체로 부터 statment객체 생성
    
    
    
    /*
    preparestatement
    -> 일단 DB에 쿼리문을 전송
    -> 해당하는 값을 그후에 전송
    */
    String sql = "{call up_insertemp(?,?,?)}";  // 물음표로 들어갈 자리를 만든다.
    
    
    
    stmt = conn.prepareCall(sql);
    
    stmt.setInt(1, 7979);
    stmt.setString(2, "Tom");
    stmt.setInt(3, 3333);
    
    
    
    //stmt.executeQuery(arg0);  // select 역할
    stmt.executeUpdate();  // select를 뺀 나머지 역할(insert,delete,update) 
    
    
    
    
    }catch(SQLException e){
        out.println(e.getMessage());
    }finally{
        // 문제가 생기든 안생기든 db는 닫아줘야한다.
        if(stmt==null)
            stmt.close();
        if(conn!=null)
            conn.close();
    }
    %>
    
    <h1>가입이 완료되었습니다.</h1>
    <a href="member_list.jsp">목록보기</a>
</body>
</html>




커넥션풀(connection pool)

매번 Connection 객체를 생성할때마다 Connection 객체를 생성하는 비용을 계속 지불하게 되므로 응용 어플리케이션의 성능은 그만큼 떨어질수밖에 없다.

[출처] JDBC - Connection Pool |작성자 자바킹

Connection Pool 은 한번 생성된 Connection 을 사용한 후에 바로 닫지 않고, Application 의다른 요구에서도 재사용할수있도록 Connection의 물리적인 연결을 끊지 않고 모아서 관리한다는 개념이다.


[출처] JDBC - Connection Pool |작성자 자바킹

Join_memeber.jsp

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
<%@ 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>
    <h1>회원가입</h1>
 
    <form action="join_proc.jsp" method="post">
        <table border="2">
            <tr>
                <td>아이디:</td>
                <td><input type="text" name="id"></td>
            </tr>
 
            <tr>
                <td>패스워드:</td>
                <td><input type="text" name="pw"></td>
            </tr>
 
            <tr>
                <td>이름:</td>
                <td><input type="text" name="name"></td>
            </tr>
 
            <tr>
                <td>전화:</td>
                <td><input type="text" name="tel"></td>
            </tr>
 
            <tr>
                <td>가입할껴?</td>
                <td><input type="submit" value="가입하기"></td>
            </tr>
        </table>
    </form>
</body>
</html>


join_proc.jsp

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
<%@page import="bitDB.DBConnectionMgr"%>
<%@ 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>Insert title here</title>
</head>
<body>
    <%
    request.setCharacterEncoding("euc-kr");  // 한글깨짐 방지
    
    String id = request.getParameter("id");
    String pw = request.getParameter("pw");
    String name = request.getParameter("name");
    String tel = request.getParameter("tel");
    
    //DB 객체
    Connection conn = null;
    PreparedStatement stmt = null;   // PreparedStatement 사용
    
    
    try{
        DBConnectionMgr DBmgr = DBConnectionMgr.getInstance();
        
        conn = DBmgr.getConnection();
        
        
        
    String sql = "insert into member values(?,?,?,?)";  // 물음표로 들어갈 자리를 만든다.
    
    stmt = conn.prepareStatement(sql);
    
    stmt.setString(1, id);
    stmt.setString(2, pw);
    stmt.setString(3, name);
    stmt.setString(4, tel);
    
    
    //stmt.executeQuery(arg0);  // select 역할
    stmt.executeUpdate();  // select를 뺀 나머지 역할(insert,delete,update) 
    
    
    
    
    }catch(SQLException e){
        out.println(e.getMessage());
    }finally{
        // 문제가 생기든 안생기든 db는 닫아줘야한다.
        if(stmt==null)
            stmt.close();
        if(conn!=null)
            conn.close();
    }
    %>
    
    <h1>가입이 완료되었습니다.</h1>
    <a href="member_list.jsp">목록보기</a>
</body>
</html>


member_list.jsp

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
<%@page import="DAO.MemDAO"%>
<%@page import="java.util.*"%>
<%@page import="bitDB.DBConnectionMgr"%>
 
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
    <%@ page import = "java.sql.*" %>
    <%@ page import = "MemBeen.MemberBean" %>
<!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 >
    <h1>회원 목록</h1>
    
    <table border="2">
        <tr>
            <th>아이디</th>
            <th>패스워드</th>
            <th>이름</th>
            <th>전화번호</th>
        </tr>
        
        <%
            MemDAO dao = new MemDAO();
        ArrayList memlist = dao.GetMemberList();
        
        for(int i=0; i<memlist.size();i++){
        %>
            <tr>
                <td><% out.println(((MemberBean)memlist.get(i)).getId()); %></td>
                <td><% out.println(((MemberBean)memlist.get(i)).getPw()); %></td>
                <td><% out.println(((MemberBean)memlist.get(i)).getName()); %></td>
                <td><% out.println(((MemberBean)memlist.get(i)).getTel()); %></td>
            </tr>
        <%
        }
        %>
        
    
    </table>
</body>
</html>




MemDAO.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
package DAO;
 
import java.sql.*;
import java.util.ArrayList;
 
 
import MemBeen.MemberBean;
import bitDB.DBConnectionMgr;
 
//DAO --> 프레임웍이나 스프링이 나오기전에 사용하던 방식, 데이터에 직접엑세스오브젝트
 
public class MemDAO {
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    
    public MemDAO(){
        //DBCP : database connection pool
        
        DBConnectionMgr DBmgr = DBConnectionMgr.getInstance();
        try {
            conn = DBmgr.getConnection();
            
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    // DAO는 기본적으로 4가지의 C,R,U,D 가 있어야한다.
    public ArrayList GetMemberList(){
        
        ArrayList memList = new ArrayList();
        
        String sql = "select * from member";
        
        try{
            stmt = conn.createStatement();
            
            rs = stmt.executeQuery(sql);  // select 기능
            
            while(rs.next()){
                MemberBean newBean = new MemberBean(); 
                newBean.setId(rs.getString("mid"));
                newBean.setPw(rs.getString("mpw"));
                newBean.setName(rs.getString("mname"));
                newBean.setTel(rs.getString("mtel"));
                memList.add(newBean);
            }
            
        }catch(SQLException e){
            e.printStackTrace();
        }
        
        
        return memList;
    }
}
 


MemberBean.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
package MemBeen;
 
public class MemberBean {
    private String id;
    private String pw;
    private String name;
    private String tel;
 
    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 getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getTel() {
        return tel;
    }
 
    public void setTel(String tel) {
        this.tel = tel;
    }
 
}
 


DBConnectionMgr.java


반응형

'(Before)BitSchool' 카테고리의 다른 글

2014/06/10 XML - xml파서, DOM 수정, 추가, 삭제, SAX 파서  (0) 2014.06.10
2014/06/03 XML  (0) 2014.06.03
2014/05/30 JSP - session,cookie, javaBean  (0) 2014.05.30
2014/05/29 Servlet, JSP  (0) 2014.05.29
2014/05/28 JSP - servlet, get방식, post방식  (0) 2014.05.28

'(Before)BitSchool' Related Articles

티스토리툴바