(1) JDBC 간단한 Query 예제 - Singleton Pattern

Singleton : private static 변수로 자기 자신의 인스턴스를 가짐. 생성자가 private, 객체를 만들 수 없다. getInstance() 메소드로 객체를 가져옴.

MyConnection은 진짜 Connection을 가지고 있음.

MyConnection을 따로 만드는 이유? Try~With 문에서 AutoClosable될 때 껍데기만 close되도록 하기 위해. 

MyConnectionPool이 MyConnection 객체의 Vector을 가지고 있음(그래서 pool). 

MyConnectionPool이 StatementEx에게 MyConnection을 하나 반환한 후에 close 될 때 그 MyConnection이 Vector에 맨 뒤로 다시 추가됨(close()메소드가 MyConnectionPool의 returnConnection()을 실행). MyConnection이 Vector을 통해 계속 순환되는 구조.

import java.sql.Connection;

import java.sql.DriverManager;

import java.util.List;

import java.util.Vector;

public class MyConnectionPool {

List<Connection> conList;

private int idx = 0; 

private static MyConnectionPool instance = new MyConnectionPool();

public static MyConnectionPool getInstance(){

return instance;

}

private MyConnectionPool(){

try{

conList = new Vector<Connection>();

Class.forName("oracle.jdbc.OracleDriver");

String urlPath = "jdbc:oracle:thin:@61.72.16.181:5024:orcl";

String userName = "leepanho";

String pw = "leepanho";

for(int i = 0; i < 10; i++){

conList.add(

new MyConnection(

DriverManager.getConnection(urlPath,userName,pw),

this));

}

}catch(Exception e){

e.printStackTrace();

}

}

public Connection getConnection()throws Exception{

return conList.remove(0);

}

public void returnConnection(Connection con)throws Exception{

conList.add(con);

}

}

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

MyConnection = Connection을 implement 해서 클래스를 만듦. 다음의 내용만 추가.

private Connection realCon;

private MyConnectionPool pool;

public MyConnection(Connection con, MyConnectionPool pool){

this.realCon = con;

this.pool = pool;

}

prepareStatement(String sql)와 close() 메소드에는 각각 다음 내용을 추가해 Override.

prepareStatement(String sql):

return realCon.prepareStatement(sql);

close():

try {

        pool.returnConnection(this);

     } catch (Exception e) {

        e.printStackTrace();

     }

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

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class StatementEx {

public static void main(String[] args) throws Exception {

for (int i = 0; i < 100; i++) {

try (Connection con = MyConnectionPool.getInstance().getConnection();

PreparedStatement stmt = con.prepareStatement("select sysdate from dual");

ResultSet rs = stmt.executeQuery();

){

System.out.println(rs);

rs.next(); //

System.out.println(rs.getString(1));

rs.close();

stmt.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

}