글쓴이 :윤지수 1998년 9월 08일 22:19:21
In Reply to: [질문] java.sql.Connection과 mutithread posted by 강신동 on 1998년 9월 08일 11:15:56:
저도 그문제 때문에 고민을 많이 하였는데.
어떠 외국 사이트에서 Connection POOL을 이용하더군요..
그러니까 초기 연결시에 Connection을 여러개 하여 stack나 배열에 저장하여 놓고 매연결이 이루어 질때마다 connection을 열지 않고 stack에서나 배열에서 가져오더군요...
그리고 맨마지막에 connection을 종료하구요..
다음은 이런 경우의 제가 작업하던 소스입니다.
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.Date;
public class customregister extends HttpServlet
//implements SingleThreadModel
{
DbConnectionPool myPool=null;
//////////////////////////////////////////////////////////////////////////////
public void init(ServletConfig config) throws ServletException
{
super.init(config);
String driver="weblogic.jdbc.mssqlserver4.Driver";
String URL = "jdbc:weblogic:mssqlserver4:mall@SMNETNT";
String username = "sa";
String password = "smnet0531";
try {
myPool = new DbConnectionPool(driver,URL,username,password,2,7,".",2.0);
} catch (Exception e) {
getServletContext().log(e.getMessage());
return;
}
}
//////////////////////////////////////////////////////////////////////////////
public void doPost(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
String memberid=req.getParameter("memberid");
String passwd=req.getParameter("passwd");
String kname=toHangul(req.getParameter("kname"));
String jumin1=req.getParameter("jumin1");
String jumin2=req.getParameter("jumin2");
String email=req.getParameter("email");
String job=req.getParameter("job");
String citycode3=req.getParameter("citycode3");
String home_kuk=req.getParameter("home_kuk");
String home_no=req.getParameter("home_no");
String zipcode1=req.getParameter("zipcode1");
String zipcode2=req.getParameter("zipcode2");
String address=req.getParameter("address");
String old=req.getParameter("old");
String payment=req.getParameter("payment");
String use_bank=req.getParameter("use_bank");
String cardtype=req.getParameter("cardtype");
String cardno=req.getParameter("cardno");
String cardowner=req.getParameter("cardowner");
String duedate=req.getParameter("duedate");
String cookiestring=kname+"#"+old;
Cookie c=new Cookie("shopcustom",cookiestring);
c.setComment("this is shop Hot item");
c.setMaxAge(10000 *24*60*60);
c.setPath("/");
res.addCookie(c);
// 쿠키 저장으로 사용자의 이름과 연령을 저장한다.
if(payment.equals("1"))
{
cardtype="";
cardno="";
cardowner="";
duedate="";
}
else
{
use_bank="";
}
Connection con=null;
PreparedStatement customstmt=null;
try {
con=myPool.getConnection();
customstmt = con.prepareStatement("insert into customtable values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
customstmt.setString(1,memberid);
customstmt.setString(2,passwd);
customstmt.setString(3,kname);
customstmt.setInt(4,Integer.parseInt(jumin1));
customstmt.setInt(5,Integer.parseInt(jumin2));
customstmt.setString(6,"email");
customstmt.setInt(7,Integer.parseInt(job));
customstmt.setInt(8,Integer.parseInt(citycode3));
customstmt.setInt(9,Integer.parseInt(home_kuk));
customstmt.setInt(10,Integer.parseInt(home_no));
customstmt.setInt(11,Integer.parseInt(zipcode1));
customstmt.setInt(12,Integer.parseInt(zipcode2));
customstmt.setString(13,address);
customstmt.setInt(14,Integer.parseInt(old));
customstmt.setInt(15,Integer.parseInt(payment));
customstmt.setInt(16,Integer.parseInt(use_bank));
customstmt.setInt(17,Integer.parseInt(cardtype));
customstmt.setInt(18,Integer.parseInt(cardno));
customstmt.setString(19,"cardowner");
customstmt.setString(20,"duedate");
customstmt.executeUpdate();
} catch (Exception e) {
getServletContext().log(e.getMessage());
new PrintWriter(res.getOutputStream()).println("<p>"+e.toString()+"</p>");
return;
}
finally {
try{if(customstmt != null) {customstmt.close();}} catch(SQLException e1){};
myPool.freeConnection(con);
}
RegisterDisplay(req,res);
destroy();
}
public void RegisterDisplay(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
res.setContentType("text/html;charset=euc-kr");
res.setHeader("pragma","no-cash");
PrintWriter pout=new PrintWriter(res.getOutputStream());
pout.println("<html><body>등록이 완료 되었습니다</body></html>");
pout.close();
}
public void destroy()
{
try{
myPool.destroy();
super.destroy();
}catch(Exception e) {getServletContext().log(e.toString()); }
}
//////////////////////////////////////////////////////////////////////////////
public static String toHangul( String str )
throws UnsupportedEncodingException
{
if ( str == null )
return null;
return new String( str.getBytes("8859_1"), "KSC5601" );
}
//////////////////////////////////////////////////////////////////////////////
} // customregister of end
class DbConnectionPool implements Runnable
{
public DbConnectionPool(String dbDriver, String dbServer, String dbLogin, String dbPassword, int minConns, int maxConns, String logFileString,
double maxConnTime)
throws IOException
{
connPool = new Connection[maxConns];
connStatus = new int[maxConns];
connLockTime = new long[maxConns];
connCreateDate = new long[maxConns];
connID = new String[maxConns];
currConnections = minConns;
this.maxConns = maxConns;
this.dbDriver = dbDriver;
this.dbServer = dbServer;
this.dbLogin = dbLogin;
this.dbPassword = dbPassword;
maxConnMSec = (int)(maxConnTime * 86400000D);
if(maxConnMSec < 30000)
maxConnMSec = 30000;
try
{
log = new PrintStream(new FileOutputStream(logFileString));
}
catch(IOException ex)
{
now = new Date();
log = new PrintStream(new FileOutputStream("DCB_" + String.valueOf(now.getTime()) + ".log"));
}
log.println("Starting DbConnectionPool Version 1.0.6:");
log.println("dbDriver = " + dbDriver);
log.println("dbServer = " + dbServer);
log.println("dbLogin = " + dbLogin);
log.println("log file = " + logFileString);
log.println("minconnections = " + minConns);
log.println("maxconnections = " + maxConns);
log.println("Total refresh interval = " + maxConnTime + " days");
log.println("-----------------------------------------");
boolean connectionsSucceeded = false;
int dbLoop = 20;
try
{
for(int i = 1; i < dbLoop;)
try
{
for(int j = 0; j < currConnections; j++)
createConn(log, j);
connectionsSucceeded = true;
break;
}
catch(SQLException e)
{
log.println("--->Attempt (" + String.valueOf(i) + " of " + String.valueOf(dbLoop) + ") failed to create new connections set at startup: ");
log.println(" " + e);
log.println(" Will try again in 15 seconds...");
try
{
Thread.sleep(15000L);
}
catch(InterruptedException ex) {}
i++;
}
if(!connectionsSucceeded)
{
log.println("\r\nAll attempts at connecting to Database exhausted");
throw new IOException();
}
}
catch(Exception ex)
{
throw new IOException();
}
if(runner == null)
{
runner = new Thread(this);
runner.start();
}
}
public void run()
{
boolean forever = true;
Statement stmt = null;
String currCatalog = null;
while(forever)
{
for(int i = 0; i < currConnections; i++)
try
{
currSQLWarning = connPool[i].getWarnings();
if(currSQLWarning != null)
{
log.println("Warnings on connection " + String.valueOf(i) + " " + currSQLWarning);
connPool[i].clearWarnings();
}
}
catch(SQLException e)
{
log.println("Cannot access Warnings: " + e);
}
for(int i = 0; i < currConnections; i++)
{
Date now = new Date();
long lnow = now.getTime();
long age = lnow - connCreateDate[i];
synchronized(connStatus)
{
if(connStatus[i] > 0)
{
continue;
}
connStatus[i] = 2;
}
try
{
if(age > maxConnMSec)
throw new SQLException();
stmt = connPool[i].createStatement();
connStatus[i] = 0;
}
catch(SQLException ex)
{
try
{
log.println(now.toLocaleString() + " ***** Recycling connection " + String.valueOf(i) + ":");
createConn(log, i);
}
catch(SQLException e1)
{
log.println("Failed: " + e1);
connStatus[i] = 0;
}
}
finally
{
try
{
if(stmt != null)
stmt.close();
}
catch(SQLException ex) {}
}
}
try
{
Thread.sleep(10000L);
}
catch(InterruptedException ex) {}
}
}
public Connection getConnection()
{
Connection conn = null;
boolean gotOne = false;
for(int outerloop = 1; outerloop <= 10; outerloop++)
{
try
{
int loop = 0;
int roundRobin = connLast + 1;
if(roundRobin >= currConnections)
roundRobin = 0;
do
synchronized(connStatus)
{
if(connStatus[roundRobin] < 1)
{
if(!connPool[roundRobin].isClosed())
{
conn = connPool[roundRobin];
connStatus[roundRobin] = 1;
Date now = new Date();
connLockTime[roundRobin] = now.getTime();
connLast = roundRobin;
gotOne = true;
break;
}
}
else
{
loop++;
if(++roundRobin >= currConnections)
roundRobin = 0;
}
}
while(!gotOne && loop < currConnections);
}
catch(SQLException ex) {}
if(gotOne)
break;
synchronized(this)
{
if(currConnections < maxConns)
{
try
{
createConn(log, currConnections);
}
catch(SQLException e)
{
log.println("Unable to create new connection: " + e);
}
currConnections++;
}
}
try
{
Thread.sleep(2000L);
}
catch(InterruptedException ex) {}
log.println("-----> Connections Exhausted! Will wait and try again in loop " + String.valueOf(outerloop));
}
return conn;
}
public int idOfConnection(Connection conn)
{
String tag;
try
{
tag = conn.toString();
}
catch(NullPointerException ex)
{
tag = "none";
}
int match = -1;
for(int i = 0; i < currConnections; i++)
{
if(!connID[i].equals(tag))
continue;
match = i;
break;
}
return match;
}
public String freeConnection(Connection conn)
{
String res = "";
int thisconn = idOfConnection(conn);
if(thisconn >= 0)
{
connStatus[thisconn] = 0;
res = "freed " + conn.toString();
}
else
{
log.println("----> Could not free connection!!!");
}
return res;
}
public long getAge(Connection conn)
{
Date now = new Date();
long lnow = now.getTime();
int thisconn = idOfConnection(conn);
long diff = lnow - connLockTime[thisconn];
return diff;
}
private void createConn(PrintStream out, int i)
throws SQLException
{
Date now = new Date();
try
{
Class.forName(dbDriver);
connPool[i] = DriverManager.getConnection(dbServer, dbLogin, dbPassword);
connStatus[i] = 0;
connID[i] = connPool[i].toString();
connLockTime[i] = 0L;
now = new Date();
connCreateDate[i] = now.getTime();
}
catch(ClassNotFoundException ex) {}
log.println(now.toLocaleString() + " Opening connection " + String.valueOf(i) + " " + connPool[i].toString() + ":");
}
public void destroy()
{
if(runner != null)
runner.stop();
for(int i = 0; i < currConnections; i++)
try
{
connPool[i].close();
}
catch(SQLException ex)
{
log.println("Cannot close connections on Destroy");
}
}
private Thread runner;
Connection connPool[];
int connStatus[];
Date now;
long connLockTime[];
long connCreateDate[];
String connID[];
String dbDriver;
String dbServer;
String dbLogin;
String dbPassword;
String logFileString;
int currConnections;
int connLast;
int minConns;
int maxConns;
int maxConnMSec;
PrintStream log;
SQLWarning currSQLWarning;
}