자바 Language 22일째 2번 Dbconnection MGR 및 그 외 문제까지

package pac;

import java.sql.*;
import java.util.Properties;
import java.util.Vector;

public class DBConnectionMgr {
private Vector<Object> connections = new Vector<Object>(10);
private String _driver = "sun.jdbc.odbc.JdbcOdbcDriver";
private String _url = "jdbc:odbc:oradbs";
private String _user = "scott";
private String _password = "tiger";
private boolean _traceOn = false;
private boolean initialized = false;
private int _openConnections = 10;
private static DBConnectionMgr instance = null;

public DBConnectionMgr() {
}

public static DBConnectionMgr getInstance() { // getInstance 로 객체를 반납함.
if (instance == null) {
synchronized (DBConnectionMgr.class) {
if (instance == null) {
instance = new DBConnectionMgr();
}
}
}

return instance;
}

public void setOpenConnectionCount(int count) {
_openConnections = count;
}

public void setEnableTrace(boolean enable) {
_traceOn = enable;
}

/** Returns a Vector of java.sql.Connection objects */
public Vector getConnectionList() { // connection 객체를 반납한다.
return connections;
}

/** Opens specified "count" of connections and adds them to the existing pool */
public synchronized void setInitOpenConnections(int count)
throws SQLException {
Connection c = null;
ConnectionObject co = null;

for (int i = 0; i < count; i++) {
c = createConnection();
co = new ConnectionObject(c, false);

connections.addElement(co);
trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
}
}

/** Returns a count of open connections */
public int getConnectionCount() {
return connections.size();
}

/** Returns an unused existing or new connection. */
public synchronized Connection getConnection() // 자원을 공유 한다.
throws Exception {
if (!initialized) {
Class c = Class.forName(_driver);
DriverManager.registerDriver((Driver) c.newInstance());

initialized = true;
}

Connection c = null;
ConnectionObject co = null;
boolean badConnection = false;

for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);

// If connection is not in use, test to ensure it's still valid!
if (!co.inUse) {
try {
badConnection = co.connection.isClosed();
if (!badConnection)
badConnection = (co.connection.getWarnings() != null);
} catch (Exception e) {
badConnection = true;
e.printStackTrace();
}

// Connection is bad, remove from pool
if (badConnection) {
connections.removeElementAt(i);
trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
continue;
}

c = co.connection;
co.inUse = true;

trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
break;
}
}

if (c == null) {
c = createConnection();
co = new ConnectionObject(c, true);
connections.addElement(co);

trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
}
return c;
}

/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void freeConnection(Connection c) {
if (c == null)
return;

ConnectionObject co = null;

for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
co.inUse = false;
break;
}
}

for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if ((i + 1) > _openConnections && !co.inUse)
removeConnection(co.connection);
}
}

public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
try {
if (r != null) r.close();
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection c, Statement s, ResultSet r) {
try {
if (r != null) r.close();
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection c, PreparedStatement p) {
try {
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void freeConnection(Connection c, Statement s) {
try {
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}

/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void removeConnection(Connection c) {
if (c == null)
return;

ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
try {
c.close();
connections.removeElementAt(i);
trace("Removed " + c.toString());
} catch (Exception e) {
e.printStackTrace();
}

break;
}
}
}

private Connection createConnection()
throws SQLException {
Connection con = null;

try {
if (_user == null)
_user = "";
if (_password == null)
_password = "";

Properties props = new Properties();
props.put("user", _user);
props.put("password", _password);

con = DriverManager.getConnection(_url,_user,_password);
} catch (Throwable t) {
throw new SQLException(t.getMessage());
}

return con;
}

/** Closes all connections and clears out the connection pool */
public void releaseFreeConnections() {
trace("ConnectionPoolManager.releaseFreeConnections()");

ConnectionObject co = null;

for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (!co.inUse)
removeConnection(co.connection);
}
}

/** Closes all connections and clears out the connection pool */
public void finalize() {
trace("ConnectionPoolManager.finalize()");

ConnectionObject co = null;

for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
try {
co.connection.close();
} catch (Exception e) {
e.printStackTrace();
}

co = null;
}

connections.removeAllElements();
}

private void trace(String s) {
if (_traceOn)
System.err.println(s);
}
}

class ConnectionObject {
public java.sql.Connection connection = null;
public boolean inUse = false;

public ConnectionObject(Connection c, boolean useFlag) {
connection = c;
inUse = useFlag;
}
}

//////////////

package pac;

import java.sql.*;
import pac.*;

public class ConPool {
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private DBConnectionMgr pool;
public ConPool() {
try {
pool = DBConnectionMgr.getInstance();
} catch (Exception e) {
System.out.println("커넥션 오류: " + e);
}
try {
conn = pool.getConnection(); // 이렇게 하면 연결 까지 완료 됨.
} catch (Exception e) {
System.out.println("객체 생성 오류: " + e);
}
// // 이제부터 불러다 쓰겠습니다.
processDb();
}

public void processDb() {
try {
String sql="select * from sawon";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
String no=rs.getString("SAWON_NO");
String name=rs.getString("SAWON_NAME");
String pay=rs.getString("SAWON_PAY");
String jik=rs.getString("SAWON_JIK");
System.out.println(no+" "+name+" "+pay+" "+jik);
}
}catch (Exception e) {
}finally{
pool.freeConnection(conn, pstmt, rs); // pool 을 사용한다.
}
}

public static void main(String[] args) {
new ConPool();
}

}

///////////

package pac;

import java.sql.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import db.accDb;

import pac.*;

public class ConPoolMon1 extends JFrame implements ActionListener {
private JTextField txtGogek, txtJu1, txtJu2;
private JTextArea txtAsawon = new JTextArea("",4,25);;
private JButton btnOk = new JButton("확인"), btnDetail = new JButton("자세히"), btnWan = new JButton("완료");
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
private DBConnectionMgr pool;
private JLabel jl, jl2;
JPanel pn2=new JPanel();
int xx=400,yy=250;
public ConPoolMon1() {
init();
try {
pool = DBConnectionMgr.getInstance();
} catch (Exception e) {
System.out.println("커넥션 오류: " + e);
}
try {
conn = pool.getConnection(); // 이렇게 하면 연결 까지 완료 됨.
} catch (Exception e) {
System.out.println("객체 생성 오류: " + e);
}
}

public void processDb() {
try {
String sql="select buser_name, buser_tel, sawon_jik, sawon_sex, buser_loc, gogek_jumin, sawon_name from sawon, gogek, buser " +
"where sawon_no(+) = gogek_damsano and buser_no(+) = buser_num and gogek_name='"+txtGogek.getText()+"'";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
txtAsawon.setText("부서명 부서전화 직급 성별 근무지역\n");
while(rs.next()) {
String jumin=rs.getString("gogek_jumin");
String saname=rs.getString("sawon_name");
StringTokenizer sa = new StringTokenizer(jumin,"-");
txtAsawon.append(rs.getString("buser_name") + " "+ rs.getString("buser_tel")+ " "+
rs.getString("sawon_jik")+" "+rs.getString("sawon_sex")+" "+rs.getString("buser_loc"));
txtJu1.setText(sa.nextToken());
txtJu2.setText(sa.nextToken());
jl2.setText(saname);
}
}catch (Exception e) {
}finally{
pool.freeConnection(conn, pstmt, rs); // pool 을 사용한다.
}
}

public void init(){
txtAsawon.setEnabled(false);
txtGogek=new JTextField("",10);
txtJu1=new JTextField("",9);
txtJu2=new JTextField("",9);
JPanel pn1=new JPanel();
pn1.setLayout(new GridLayout(3,1));
JPanel pn11=new JPanel();
jl = new JLabel("고 객 명:");
pn11.add(jl);
pn11.add(txtGogek);
pn11.add(btnOk);
JPanel pn12=new JPanel();
jl = new JLabel("주민번호:");
pn12.add(jl);
pn12.add(txtJu1);
jl = new JLabel("-");
pn12.add(jl);
pn12.add(txtJu2);
JPanel pn13=new JPanel();
jl = new JLabel("담당직원: ");
pn13.add(jl);
jl2 = new JLabel(" ",10);
pn13.add(jl2);
pn13.add(btnDetail);
pn1.add(pn11);
pn1.add(pn12);
pn1.add(pn13);

JPanel pn3=new JPanel();

pn2.add(txtAsawon,"Center");
pn2.add(btnWan,"East");
this.setLayout(new FlowLayout());
this.add("Center",pn1);
this.add("South",pn2);

btnOk.addActionListener(this);
btnDetail.addActionListener(this);
btnWan.addActionListener(this);

this.setBounds(200,200,400,150);
this.setVisible(true);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setResizable(false); // 리사이즈 안됨
}

public static void main(String[] args) {
new ConPoolMon1();
}

public void actionPerformed(ActionEvent ae) {
if (ae.getActionCommand().equals("확인")) {
if (txtGogek.getText().equals(""))
JOptionPane.showMessageDialog(this, "값을 넣어 주세요.");
else processDb();
}
else if (ae.getActionCommand().equals("자세히")) {
this.setSize(400, 250);
this.setVisible(true);

}
else if (ae.getActionCommand().equals("완료")) {
this.setSize(0,0);
this.setSize(400, 150);
this.setVisible(true);
}
}
}