5.JSP - DBCP 커넥션 풀

SERVER에 CONTEXT.XML 

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
 
    <!-- dbcp 설정 (dataBase connection pool) -->
 
 
    <Resource name="myDB" auth="Container" driverClassName="com.mysql.jdbc.Driver"
        maxTotal="50" maxIdle="50" maxWaitMillis="-1"
        url="jdbc:mysql://localhost:3306/java" username="java" password="java1234"
        type="javax.sql.DataSource" />
 
</Context>

BOOK DAO

package book;
 
// jdbc import
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
 
public class BookDAO {
 
    
    
    // db접속
        public Connection dbConn() {
            
            
            DataSource ds=null;
            Connection conn=null;
            
            try {
            
                //context.xml을 분석하는 객체
                Context ctx=new InitialContext();
                
                //context.xml의 resource 태그 검색
                
                ds =(DataSource)ctx.lookup("java:comp/env/myDB");
                conn=ds.getConnection();        //커넥션을 할당받음
                
            } catch (Exception e) {
                    e.printStackTrace();
            }
            return conn;
            
        }
    
    
    
    
    // db접속
    /*public Connection dbConn() {

        Connection conn = null; // db접속 객체

        try {
            // mysql jdbc driver 로딩
            Class.forName("com.mysql.jdbc.Driver");

            // db연결 문자열 but 이방법은 보안에 취약하다. ..
            String url = "jdbc:mysql://localhost:3306/java";
            String id = "java"; // mysql 접속아이디
            String pwd = "java1234"; // mysql 접속 비번

            // db 접속

            conn = DriverManager.getConnection(url, id, pwd);
            System.out.println("db접속 성공");
        } catch (Exception e) {
            // db관련작업은 반드시 익셉션 처리
            System.out.println("db접속 실패");
            e.printStackTrace();
        }
        return conn;

    }*/
 
    // 북리스트
    public ArrayList<bookDTO> bookList() {
 
        ArrayList<bookDTO> list = new ArrayList<bookDTO>();
        Connection conn = null; // DB접속 객체
        PreparedStatement pstmt = null; // SQL실행객체
        ResultSet rs = null; // 결과셋 처리 객체
 
        try {
            conn = dbConn(); // db연결 키
            String sql = "select * from book_table";
            pstmt = conn.prepareStatement(sql); // sql을 실행시키는 객체 만들어짐
            rs = pstmt.executeQuery(); // 실행 후 결과 값이 rs에 넘어옴
 
            while (rs.next()) { // 결과셋.next(); 다음 레코드가 있으면 true
 
                bookDTO dto = new bookDTO();
                dto.setId(rs.getInt("id"));
                dto.setTitle(rs.getString("title"));
                dto.setAuthor(rs.getString("author"));
                dto.setPrice(rs.getInt("price"));
                dto.setQty(rs.getInt("qty"));
 
                // ArrayList에 추가
                list.add(dto);
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally { // 오픈한 역순으로 닫기작업 실행
 
            // resultset= > statement=> connection
 
            try {
                if (rs != null) {
                    rs.close();
                }
 
            } catch (Exception e2) {
                e2.printStackTrace();
            }
 
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
 
            } catch (Exception e2) {
                e2.printStackTrace();
            }
 
            try {
                if (conn != null) {
                    conn.close();
                }
 
            } catch (Exception e2) {
                e2.printStackTrace();
            }
 
        }
        return list;
    }
    //삽입
    public void bookInsert(bookDTO dto) {
        
        Connection conn = null; // DB접속 객체
        PreparedStatement pstmt = null; // SQL실행객체
        
        try{
            
            conn=dbConn();
            
            String sql="insert into book_table(title, author, price, qty)"+" values(?,?,?,?)";
            
            
            //sql 실행객체 생성
            pstmt=conn.prepareStatement(sql);
            
            //? 에 입력될 값 매핑
            pstmt.setString(1, dto.getTitle());
            pstmt.setString(2, dto.getAuthor());
            pstmt.setInt(3, dto.getPrice());
            pstmt.setInt(4, dto.getQty());
            
            //executeQuery() select 명령어
            //executeUpdate select 이외 명령어
            pstmt.executeUpdate();
            
            
        }catch(Exception e){
            
            e.printStackTrace();
            
        }finally{
            //리소스 정리작업
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
 
            } catch (Exception e2) {
                e2.printStackTrace();
            }
 
            try {
                if (conn != null) {
                    conn.close();
                }
 
            } catch (Exception e2) {
                e2.printStackTrace();
            }
 
            
        }
        
    }
 
}