[13] DAO(Data Access Object) 빈즈 이용 데이터베이스 접속

 

[DB Connection Bean 클래스]

 

파일명 : DBConnect.java

------------------------------------------------

package www.utility;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnect {
    public DBConnect() { }
    public Connection getConnection(){

        //MySQL 연결
        String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8";
        String id = "root";
        String pass = "1234";
        String driver="org.gjt.mm.mysql.Driver";

 

        //오라클 연결

        /*
        String url="jdbc:oracle:thin://@127.0.0.1:1521:xe";
        String id="user6";
        String pass="1234";
        String driver="oracle.jdbc.OracleDriver";
        */
        
        //데이터베이스 접속 연결 정보를 가지고 있는 객체
        Connection con = null;
      
        try{
            //JDBC드라이버를 JVM으로 로딩
            Class.forName(driver);
           
            //데이터베이스에 연결
            con=DriverManager.getConnection(url, id, pass);
         
        }catch(Exception  e){
            System.out.println(e);
        }
       
        return con; // MySQL 연결 리턴
    }
}

------------------------------------------------

 

 

[DB Connection Beans Close 클래스]

 

파일명 : DBClose.java

------------------------------------------------

package www.utility;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * JDBC관련 객체 닫음.
 */
public class DBClose {
   public static void close(Connection con, PreparedStatement pstmt, ResultSet rs){
        try{
            try{
                if(rs != null){rs.close(); rs = null; }
            }catch(Exception e){}
           
            try{
                if(pstmt != null){ pstmt.close(); pstmt = null; }
            }catch(Exception e){}

            try{
                if(con != null){ con.close(); con = null; }
            }catch(Exception e){}
           
        }catch(Exception e){}
    }

    public static void close(Connection con, PreparedStatement pstmt){
        try{
            try{
                if(pstmt != null){ pstmt.close(); pstmt = null; }
            }catch(Exception e){}

            try{
                if(con != null){ con.close(); con = null; }
            }catch(Exception e){}
           
           
        }catch(Exception e){}
    }      

    public static void close(Connection con, Statement stmt){
        try{
            try{
                if(stmt != null){ stmt.close(); stmt = null; }
            }catch(Exception e){}

            try{
                if(con != null){ con.close(); con = null; }
            }catch(Exception e){}
           
           
        }catch(Exception e){}
    }
   
    public static void close(Connection con){
        try{
            try{
                if(con != null){ con.close(); con = null; }
            }catch(Exception e){}
           
           
        }catch(Exception e){}
    }
   
    public static void close(PreparedStatement pstmt){
        try{
            try{
                if(pstmt != null){ pstmt.close(); pstmt = null; }
            }catch(Exception e){}
        }catch(Exception e){}
    }
   
    public static void close(Statement stmt){
        try{
            try{
                if(stmt != null){ stmt.close(); stmt = null; }
            }catch(Exception e){}
        }catch(Exception e){}
    }   

    public static void close(ResultSet rs){
        try{
            try{
                if(rs != null){rs.close(); rs = null; }
            }catch(Exception e){}
        }catch(Exception e){}
    }

    public static void close(PreparedStatement pstmt, ResultSet rs) {
        try{
            try{
                if(rs != null){rs.close(); rs = null; }
            }catch(Exception e){}
           
            try{
                if(pstmt != null){ pstmt.close(); pstmt = null; }
            }catch(Exception e){}

        }catch(Exception e){}
       
    }

}

------------------------------------------------

 

 

[DB Connection 테스트]

 

파일명 : jdbcTest.jsp

-------------------------------------------------

<%@ page contentType="text/html; charset=utf-8"%>

<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="www.utility.*" %>

<jsp:useBean id="dbconnect" class="www.utility.DBConnect"/>
<%//DBConnect dbconnect=new DBConnect(); %>

<%
Connection con=dbconnect.getConnection();
String sql = "SELECT COUNT(*) as cnt FROM tb_sungjuk";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();

int cnt = 0;

if (rs.next() == true){
    cnt = rs.getInt("cnt");
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-ｅquiv="Content-Type" content="text/html; charset=EUC-KR">
<title>JDBC 테스트</title>
</head>
<body>
<%
out.println("MySQL 접속  성공!!" + "<br>");
out.println("tb_sungjuk 테이블의 레코드 갯수: " + cnt);
%>
</body>
</html>

<%
DBClose.close(con, pstmt, rs);
%>

 

 

-------------------------------------------------