package com.sitech.database;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.ResourceBundle;

import com.sitech.util.DES3;
import com.sitech.util.Formater;

public class ConnectionPool {
	private static int iDrivertype = 0;

	private static String sUserName;

	private static String sPassword;

	private static String sUrl;

	private static String sClassforName;

	private static boolean waitingcloseconnection = false;

	private static ConnectionPool instance = new ConnectionPool();

	static private java.sql.Connection conn = null;

	static private java.util.Date connDate = null;

	static private int connCount = 0;

	static private int currconnCount = 0;

	private boolean bCommitstate;

	private ConnectionPool() {
	}

	public static ConnectionPool getInstance() {
		return instance;
	}

	static {
		try {
			ResourceBundle LogfileInfo = ResourceBundle.getBundle("LoginInfo");

			sClassforName = LogfileInfo.getString("ClassforName");
			sUrl = LogfileInfo.getString("url");
			sUserName = LogfileInfo.getString("sqluser");
			sPassword = LogfileInfo.getString("sqlpassword");
			sPassword = DES3.decrypt(sPassword);
		} catch (Exception e) {
			
		}
	}

	public java.sql.Connection Connection() throws SQLException {
		return conn;
	}

	public synchronized void increase(int i) {
		connCount++;
		currconnCount = currconnCount + i;
	}

	synchronized public java.sql.Connection createConnection()
			throws SQLException {
		if (waitingcloseconnection) {
			try {
				while (waitingcloseconnection) {
					
					Thread.sleep(1);
				}
			} catch (Exception e) {

			}
		}
		increase(1);
		if (conn != null && !conn.isClosed())
			return conn;
		try {
			Class.forName(sClassforName).newInstance();
			conn = DriverManager.getConnection(sUrl, sUserName, sPassword);
			connDate = new Date();
			connCount = 0;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	synchronized public void closeConn() throws SQLException {
		Date preRefresh = Formater.computeDate(new Date(), 'f', -10);// l��10������Ч
		if (preRefresh.after(connDate) || connCount > 500) {
			if (conn != null) {
				if (currconnCount > 1) {
					waitingcloseconnection = true;
					try {
						int i = 0;
						while (currconnCount > 1 && i < 10) {
							Thread.sleep(80);
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				conn.close();
				conn = null;
				increase(-1);
				waitingcloseconnection = false;
			}
		}
	}

	protected void finalize() throws Throwable {
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}

	public java.sql.Statement createStatement() throws SQLException {

		if (conn != null)
			return conn.createStatement();
		else
			return null;
	}

	public java.sql.Statement createScrollStatement() throws SQLException {

		if (conn != null)
			if (iDrivertype == 0)
				return conn.createStatement();
			else
				return conn.createStatement(
						java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
						java.sql.ResultSet.CONCUR_READ_ONLY);
		else
			return null;
	}

	public java.sql.PreparedStatement createPreparedStatement(String sql)
			throws SQLException {

		if (conn != null)
			return conn.prepareStatement(sql);
		else
			return null;
	}

	/* execute Prepared Statement */

	public void executePreparedStmt(java.sql.PreparedStatement pstmt)
			throws SQLException {
		if (conn != null) {
			pstmt.executeUpdate();
		} else
			return;

	}

	/* execute Prepared Statement */

	public java.sql.CallableStatement createPreparedCall(String sql)
			throws SQLException {
		if (conn != null) {
			return conn.prepareCall(sql);
		} else
			return null;

	}

	public void beginTransaction() throws SQLException {

		if (conn != null) {
			bCommitstate = conn.getAutoCommit();
			conn.setAutoCommit(false);
		}
	}

	public void endTransaction() throws SQLException {

		if (conn != null) {
			if (bCommitstate != conn.getAutoCommit())
				conn.setAutoCommit(bCommitstate);
		}
	}

	public void commit() throws SQLException {

		if (conn != null)
			conn.commit();
	}

	public void rollback() throws SQLException {

		if (conn != null)
			conn.rollback();
	}

	public static void main(String[] argv) throws Exception {
		String SQL = "select * from tblWasteNo";
		// String SQL = "insert into company
		// (company_id,name,phone,fax,email,address,postcode,describe) values
		// ('0001','sitechcompany','62501566','62501566','bossd_www@si-tech.com.cn','zhongguancun','100086','hello')";
		// String SQL = "insert into EMPLOYEE (
		// Employee_Id,Name,UserName,Password,Warn_Before_Days,PW_Invalid_Date )
		// values ('tt02209','test','tttt','1678',5,'1999-02-12')";
		// String SQL = "insert into tt values (1)";
		// DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

		// java.sql.Connection con =
		// DriverManager.getConnection("jdbc:oracle:thin:@172.16.9.3:1521:ora252","dalu","dalu");

		ConnectionPool conPool = ConnectionPool.getInstance();
		conPool.createConnection();
		System.out.println(conPool);
		// java.sql.Statement stmt = con.createStatement();
		java.sql.Statement stmt = conPool.createStatement();
		System.out.println(stmt);
		boolean results = true;
		stmt.execute(SQL);
		System.out.println(results);
		java.sql.ResultSet rs;
		// int iQuantity;
		if (results) {
			rs = stmt.getResultSet();
			System.out.println(rs);
			while (rs.next()) {
				/*
				 * System.out.println(rs.getInt(1));
				 * System.out.println(rs.getInt(2));
				 * System.out.println(sitech.util.Format.fromSybaseString(rs.getString(3)).trim());
				 * System.out.println(rs.getDate(4));
				 * System.out.println(rs.getInt(5));
				 * 
				 * System.out.println(sitech.util.Format.fromSybaseString(rs.getString(6)).trim());
				 * System.out.println(sitech.util.Format.fromSybaseString(rs.getString(7)).trim());
				 * System.out.println(rs.getInt(8));
				 * System.out.println(rs.getInt(9));
				 * System.out.println(rs.getInt(10));
				 * 
				 * System.out.println(rs.getInt(11));
				 * System.out.println(sitech.util.Format.fromSybaseString(rs.getString(12)).trim());
				 * 
				 */
				System.out.println(rs.getInt(1));
				// iQuantity = rs.getRow();
			}
			rs.close();
		}
		stmt.close();
		// con.close();
		conPool.closeConn();
	}

}