Connection Pooling
µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ ConnectionÀ» °ü¸®ÇÏ´Â °ÍÀº ¸Å¿ì ¹Ù¶÷Á÷ÇÑ ÀÏÀÔ´Ï´Ù.
ÇÁ·Î±×·¥³»¿¡¼ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÀ» ½ÃµµÇÏ´Â °Í(Connection °´Ã¼¸¦ ¾ò´Â°Í)Àº ½Ã°£ÀÌ ¸¹ÀÌ °É¸®´Â ÀÛ¾÷À̱⠶§¹®ÀÔ´Ï´Ù.
±×·³¿¡µµ Áö±Ý±îÁö ¿ì¸®´Â Connection À» ¾òÀº ÈÄ »ç¿ë ÈÄ ¹Ù·Î ÀÚ¿øÀ» ¹Ý³³Çß½À´Ï´Ù.
¸¸¾à, ÀÏÁ¤·®ÀÇ Connection À» ¹Ì¸® »ý¼º½ÃÄÑ free Connection list À̶õ ÀúÀå¼Ò¸¦ ¸¸µé¾î¼ ÀúÀåÇß´Ù°¡
ÇÁ·Î±×·¥¿¡¼ È£ÃâÀÌ ÀÖÀ¸¸é free Connection list Áß¿¡¼ Connection Á¦°øÇϰí, ÀÏÀÌ ³¡³ª¸é free Connection list
·Î ´Ù½Ã ÀúÀåÇÑ´Ù¸é Connection °´Ã¼¸¦ »ý¼ºÇϱâ À§ÇÑ ½Ã°£À» Àý¾àÇÒ ¼ö ÀÖÀ» °Ì´Ï´Ù.
¹Ù·Î ÀÌ ±â¹ýÀÌ Connection Pooling ÀÔ´Ï´Ù.
1. Àüü Ŭ·¡½º ¿ä¾à
Log.java
·Î±× ÆÄÀÏ¿¡ ·Î±× ¸Þ½ÃÁö¸¦ ÀÔ·ÂÇϱâ À§ÇÑ Å¬·¡½º
DBConnectionPool.java
ƯÁ¤ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ Ä¿³Ø¼Ç °´Ã¼¸¦ Ç®·Î °ü¸®ÇÏ´Â Ä¿³Ø¼ÇÇ® Ŭ·¡½º
DBConnectionPoolManager.java
Ä¿³Ø¼ÇÇ® °´Ã¼µéÀ» °ü¸®Çϴ Ŭ·¡½º
ConnectionManager.java
DBConnectionPoolManager Ŭ·¡½º¿¡°Ô Ä¿³Ø¼ÇÀ» ¿äûÇϴ Ŭ·¡½º
¿©·¯ µ¥ÀÌÅͺ£À̽º¸¦ °í·ÁÇÑ Ãß»óŬ·¡½º
µ¥ÀÌÅͺ£À̽º¿¡ µû¶ó ÀÌ Å¬·¡½º¸¦ »ó¼ÓÇϴ Ŭ·¡½º¸¦ ÀÛ¼º
OracleConnectionManager.java
¿À¶óŬ¿ë Ä¿³Ø¼ÇÀ» ¾ò±âÀ§ÇØ ConnectionManager Ŭ·¡½º¸¦ »ó¼ÓÇØ¼ ±¸ÇöÇÑ Å¬·¡½º
oracle.properties
¿À¶óŬ¿ë Ä¿³Ø¼ÇÇ® ¼³Á¤ ÆÄÀÏ
oracle.properties ¿¡¼ "oracle" À̶õ ¹®ÀÚ¿Àº ¿À¶óŬ¿ë Ä¿³Ø¼ÇÇ® °´Ã¼¸¦ ±¸º°ÇÏ´Â À̸§À¸·Î »ç¿ëµÈ´Ù.
¼³Á¤ ³»¿ëÀ» Äڵ尡 ¾Æ´Ñ ÆÄÀÏ¿¡ ÀúÀåÇÏ¸é °ü¸®ÇϱⰡ ½±´Ù.
2. Connection Pooling °ü·Ã ¼Ò½º
Log.java
package net.java_school.util;
import java.io.*;
import java.util.Date;
public class Log {
public String dbgFile = "D:\\jdbc\\error.txt"; // À©µµ¿ì °è¿
//public String dbgFile = "/jdbc/error.dbg"; // À¯´Ð½º °è¿
FileWriter fw = null;
public Log() {
super();
try {
fw = new FileWriter( dbgFile, true );
} catch ( IOException e ){}
}
public void close() {
try {
fw.close();
} catch ( IOException e ){}
}
public void close( FileWriter fw ) {
try {
fw.close();
} catch ( IOException e ){}
}
public void debug( String msg ) {
try {
fw.write( new java.util.Date()+ " : " );
fw.write( msg + " \r\n" );
fw.flush();
} catch ( IOException e ) {
System.err.println( "IOException.......!!" );
}
}
public static void out( String msg ) {
System.out.println( new Date() + ": " + msg );
}
public static void err( String msg ) {
System.out.println( new Date() + ": " + msg );
}
public static void err( Throwable e, String msg ) {
System.err.println( new Date() + ": " + msg );
e.printStackTrace( System.out );
}
}
DBConnectionPool.java
package net.java_school.db.dbpool;
import java.util.*;
import java.sql.*;
import java.util.Date;
import net.java_school.util.Log;
// Connection PoolÀ» °ü¸®Çϴ Ŭ·¡½º
class DBConnectionPool {
// ÇöÀç »ç¿ë ÁßÀÎ Connection °³¼ö
private int checkedOut;
// Free Connection List JDK 1.5 ÀÌÈĹöÀüÀÏ °æ¿ì
private Vector<Connection> freeConnections = new Vector<Connection>();
// Free Connection List JDK 1.5 ÀÌÀü¹öÀüÀÏ °æ¿ì
// private Vector freeConnections = new Vector();
// Connection ÃÖ´ë °³¼ö
private int maxConn;
// Connection Ãʱ⠰³¼ö
private int initConn;
// Waiting time (pool¿¡ connectionÀÌ ¾øÀ»¶§ ±â´Ù¸®´Â ÃÖ´ë½Ã°£)
private int maxWait;
// Connection Pool Name
private String name;
// DB Password
private String password;
// DB URL
private String URL;
// DB UserID
private String user;
// Constructor
public DBConnectionPool( String name, String URL, String user, String password,
int maxConn, int initConn, int waitTime ) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
this.maxWait = waitTime;
for ( int i = 0; i < initConn; i++ ) {
freeConnections.addElement( newConnection() );
}
}
// Connection ¹Ý³³
// @param con : ¹Ý³³ÇÒ Connection
public synchronized void freeConnection( Connection con ) {
freeConnections.addElement( con );
checkedOut--;
// ConnectionÀ» ¾ò±â À§ÇØ ´ë±âÇϰí ÀÖ´Â thread¿¡ ¾Ë¸²
notifyAll();
}
// Connection À» ¾òÀ½
public synchronized Connection getConnection() {
Connection con = null;
// ConnectionÀÌ Free List¿¡ ÀÖÀ¸¸é ListÀÇ Ã³À½ °ÍÀ» ¾òÀ½
if ( freeConnections.size() > 0 ) {
con = (Connection) freeConnections.firstElement();
freeConnections.removeElementAt(0);
try {
// DBMS¿¡ ÀÇÇØ ConnectionÀÌ close µÇ¾úÀ¸¸é ´Ù½Ã ¿ä±¸
if ( con.isClosed() ) {
Log.err( "Removed bad connection from " + name );
con = getConnection();
}
} // ¿ä»óÇÑ Connection ¹ß»ýÇÏ¸é ´Ù½Ã ¿ä±¸
catch ( SQLException e ) {
Log.err( e, "Removed bad connection from " + name );
con = getConnection();
}
} // ConnectionÀÌ Free List¿¡ ¾øÀ¸¸é »õ·Î »ý¼º
else if ( maxConn == 0 || checkedOut < maxConn ) {
con = newConnection();
}
if ( con != null ) {
checkedOut++;
}
return con;
}
// ConnectionÀ» ¾òÀ½
// @param timeout : ConnectionÀ» ¾ò±â À§ÇÑ ÃÖ´ë ±â´Ù¸² ½Ã°£
public synchronized Connection getConnection( long timeout ) {
long startTime = new Date().getTime();
Connection con;
while ( (con = getConnection()) == null ) {
try {
wait( timeout * maxWait );
} catch ( InterruptedException e ) {}
if ( (new Date().getTime() - startTime) >= timeout ) {
// ±â´Ù¸² ½Ã°£ Ãʰú
return null;
}
}
return con;
}
// Connection »ý¼º
private Connection newConnection() {
Connection con = null;
try {
if ( user == null ) {
con = DriverManager.getConnection( URL );
} else {
con = DriverManager.getConnection( URL, user, password );
}
Log.out( "Created a new connection in pool " + name );
} catch ( SQLException e ) {
Log.err(e, "Can't create a new connection for " + URL + " user : " +
user + " passwd : " + password);
return null;
}
return con;
}
}
DBConnectionPoolManager.java
package net.java_school.db.dbpool;
import java.sql.*;
import java.util.*;
import net.java_school.util.Log;
public class DBConnectionPoolManager {
// ÀνºÅϽº¸¦ Çϳª¸¸ À¯ÁöÇϱâ À§ÇØ static À¸·Î ¼±¾ð
static private DBConnectionPoolManager instance = null;
//JDBC µå¶óÀ̹ö °ü¸® JDK 1.5 ÀÌ»óÀÎ °æ¿ì
private Vector<String> drivers = new Vector<String>();
//JDBC µå¶óÀ̹ö °ü¸® JDK 1.5 ÀÌÇÏÀÎ °æ¿ì
//private Vector drivers = new Vector();
// DB Connection Pool List JDK 1.5 ÀÌ»óÀÇ °æ¿ì
private Hashtable<String,DBConnectionPool> pools =
new Hashtable<String,DBConnectionPool>();
// DB Connection Pool List JDK 1.5 ÀÌÇÏÀÇ °æ¿ì
// private Hashtable pools = new Hashtable();
// DBConnectionPoolManagerÀÇ instance¸¦ ¾òÀ½
// @return DBConnectionManger
static synchronized public DBConnectionPoolManager getInstance() {
if ( instance == null ) {
instance = new DBConnectionPoolManager();
}
return instance;
}
// Default Constructor
private DBConnectionPoolManager() {}
// ÇöÀç ConnectionÀ» Free Connection List·Î º¸³¿
// @param name : Pool Name
// @param con : Connection
public void freeConnection( String name, Connection con ) {
DBConnectionPool pool = (DBConnectionPool) pools.get( name );
if ( pool != null ) {
pool.freeConnection(con);
}
Log.out( "One Connection of " + name + " was freed" );
}
// Open ConnectionÀ» ¾òÀ½. ÇöÀç ¿¸° Ä¿³Ø¼ÇÀÌ ¾ø°í ÃÖ´ë Ä¿³Ø¼Ç °³¼ö°¡
// »ç¿ë ÁßÀÌ ¾Æ´Ò ¶§´Â »õ·Î¿î Ä¿³Ø¼ÇÀ» »ý¼º. ÇöÀç ¿¸° Ä¿³Ø¼ÇÀÌ ¾ø°í
// ÃÖ´ë Ä¿³Ø¼Ç °³¼ö°¡ »ç¿ë ÁßÀÏ ¶§ ±âº» ´ë±â ½Ã°£À» ±â´Ù¸²
// @param name : Pool Name
// @return Connection : The connection or null
public Connection getConnection( String name ) {
DBConnectionPool pool = (DBConnectionPool) pools.get( name );
if ( pool != null ) {
return pool.getConnection(10);
}
return null;
}
// Connection PoolÀ» »ý¼º
// @param poolName : »ý¼ºÇÒ Pool Name
// @param url : DB URL
// @param user : DB UserID
// @param password : DB Password
private void createPools( String poolName, String url, String user,
String password, int maxConn, int initConn, int maxWait ) {
DBConnectionPool pool = new DBConnectionPool( poolName, url, user,
password, maxConn, initConn, maxWait );
pools.put( poolName, pool );
Log.out( "Initialized pool " + poolName );
}
// ÃʱâÈ ÀÛ¾÷
public void init( String poolName, String driver, String url,
String user, String passwd, int maxConn, int initConn, int maxWait ) {
loadDrivers( driver );
createPools( poolName, url, user, passwd, maxConn, initConn, maxWait );
}
// JDBC Driver Loading
// @param driverClassName : »ç¿ëÇϰíÀÚ ÇÏ´Â DBÀÇ JDBC µå¶óÀ̹ö
private void loadDrivers( String driverClassName ) {
try {
Class.forName( driverClassName );
drivers.addElement( driverClassName );
Log.out( "Registered JDBC driver " + driverClassName );
} catch ( Exception e ) {
Log.err( e, "Can't register JDBC driver: " + driverClassName );
}
}
public Hashtable<String,DBConnectionPool> getPools() {
return pools;
}
public int getDriverNumber() {
return drivers.size();
}
}
ConnectionManager.java
package net.java_school.db.dbpool;
import java.sql.*;
import java.io.*;
import java.util.*;
import net.java_school.util.Log;
public abstract class ConnectionManager {
protected DBConnectionPoolManager connMgr = null;
protected String poolName, dbServer, dbName, port, userID, passwd;
int maxConn,initConn, maxWait;
private Properties dbProperties;
private String configFile;
public ConnectionManager( String poolName ) {
this.poolName = poolName;
// PropertyÆÄÀÏ µð·ºÅ丮 ÁöÁ¤
configFile = "D:\\jdbc\\"+poolName+".properties";
try {
dbProperties = readProperties();
dbServer = getProperty( "dbServer" );
port = getProperty( "port" );
dbName = getProperty( "dbName" );
userID = getProperty( "userID" );
passwd = getProperty( "passwd" );
maxConn = Integer.parseInt( getProperty( "maxConn" ) );
initConn = Integer.parseInt( getProperty( "initConn" ) );
maxWait = Integer.parseInt( getProperty( "maxWait" ) );
} catch( IOException ioe ) {
Log.err( "Error reading properties of " + configFile );
}
}
public Connection getConnection() {
return ( connMgr.getConnection( poolName ) );
}
public void freeConnection( Connection conn ) {
connMgr.freeConnection( poolName, conn );
}
private String getProperty( String prop ) throws IOException {
return ( dbProperties.getProperty( prop ) );
}
protected synchronized Properties readProperties() throws IOException {
Properties tempProperties = new Properties();
FileInputStream in = new FileInputStream( configFile );
tempProperties.load( in );
return tempProperties;
}
public int getDriverNumber() {
return connMgr.getDriverNumber();
}
}
OracleConnectionManager.java
package net.java_school.db.dbpool;
public class OracleConnectionManager extends ConnectionManager {
public OracleConnectionManager() {
super( "oracle" );
String JDBCDriver = "oracle.jdbc.driver.OracleDriver";
// ¿À¶óŬ¿ë JDBC thin driver
String JDBCDriverType = "jdbc:oracle:thin";
String url = JDBCDriverType + ":@" + dbServer + ":" + port + ":" + dbName;
connMgr = DBConnectionPoolManager.getInstance();
connMgr.init( poolName, JDBCDriver, url, userID, passwd, maxConn, initConn, maxWait );
}
}
oracle.properties
############################################ # Database Connection Properties for Oracle ############################################ # Database Server Name OR IP address dbServer = 127.0.0.1 # The port number your DB server listents to. port = 1521 # Database Name dbName = orcl # Database User userID = scott # Database Password passwd = tiger # Maximum Connection Number maxConn = 20 # Inital Connection Number initConn = 5 # Maximum Wait Time maxWait = 5
3. »ç¿ë¹ý
¸ÕÀú À§ÀÇ ¼Ò½º¸¦ ÀüºÎ Àû´çÇÑ µð·ºÅ丮¿¡ ³Ö°í ( ¿¹ D:\jdbc ) javac -d . *.java ·Î ÄÄÆÄÀÏÇÕ´Ï´Ù.
¾Æ·¡ºÎÅÍ´Â D:\jdbc ¸¦ ±âÁØÀ¸·Î ¼³¸íÇÕ´Ï´Ù.
Log.java ¼Ò½º¿¡¼
public String dbgFile = "D:\\jdbc\\error.txt"; ¿¡ ¸Â°Ô D:\jdbc ¿¡ error.txt ¶ó´Â ³»¿ëÀÌ ºó ÆÄÀÏÀ» ¸¸µé¾î ³õ½À´Ï´Ù.
ConnectionManager.java ¼Ò½º¿¡¼
configFile = "D:\\jdbc\\"+poolName+".properties"; ¿¡ ¸Â°Ô orcale.properties ÆÄÀÏÀ» D:\jdbc ¿¡ À§Ä¡½Ãŵ´Ï´Ù.
ÀÌÁ¦ D:\jdbc µð·ºÅ丮¸¦ CLASSPATH¿¡ Ãß°¡ÇÕ´Ï´Ù.
»õ ¸í·É ÇÁ·ÒÇÁÆ®¸¦ ¶ç¿ö¼ set classpath ·Î È®ÀÎÇÕ´Ï´Ù.
C:\Documents and Settings\Administrator>set classpath CLASSPATH=.;F:\oracle\product\10.2.0\db_1\jdbc\lib\classes12.jar;D:\jdbc C:\Documents and Settings\Administrator>
Connection Pooling ÀÌ¿ëÀº Áö±Ý±îÁö ÇØ¿Â ¼ø¼ö ÀÚ¹Ù ¾ÖÇø®ÄÉÀ̼ÇÀ¸·Î ¿¹Á¦¸¦ ±¸ÇöÇϱâ±â Èûµì´Ï´Ù.
Servlets À̳ª JSP ¿¡¼ ½ÇÁ¦ ¿¹Á¦¿¡¼ ´Ù·ç°Ú½À´Ï´Ù.
À̰ÍÀ¸·Î JDBC Tutorial ¸¦ ¸¶Ä§´Ï´Ù.
- ÀÌÀü °Á : Æ®·£Àè¼Ç
