package com.yitong.struts.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class SQLConnection {
	
	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private PreparedStatement pstmt;
	
	private static SQLConnection sqlConnection;
	
	private SQLConnection(){
		initConn();
	}
	
	public static void main(String[] args) {
		SQLConnection dbaccess=SQLConnection.getInstance();
		Connection conn=null;
		Statement stat=null;
		ResultSet rs=null;
		try {
			conn = dbaccess.getConnection();
			conn.setAutoCommit(false);
			stat=sqlConnection.getConnection().createStatement();
			rs=stat.executeQuery("select top 1 id from T_PM_MaterialPurchasePlanDetails");
			if(rs.next()){
				System.out.println("--------------link is ok-:>"+rs.getString(1));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {if(null!=rs){rs.close();}} catch (SQLException e) {e.printStackTrace();}
			try {if(null!=stat){stat.close();}} catch (SQLException e) {e.printStackTrace();}
			try {if(null!=conn){conn.close();}} catch (SQLException e) {e.printStackTrace();}
		}
	}
	
	
	public void initConn() {
		try {
			Properties property = new Properties();
			property.load(SQLConnection.class.getResourceAsStream("sql.properties"));
			String driverclass = property.getProperty("db.driver");
			String url = property.getProperty("db.url");
			String user = property.getProperty("db.user");
			String pass = property.getProperty("db.pass");
			//System.out.println("link datebase info:"+url+"\t"+user+"\t"+pass);
			Class.forName(driverclass);
			conn=DriverManager.getConnection(url, user, pass);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
		}catch (IOException e1) {
			e1.printStackTrace();
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static SQLConnection getInstance() {
		if (null==sqlConnection) {
			sqlConnection = new SQLConnection();
			sqlConnection.initConn();
		}else{
			try{
				if(null==sqlConnection.getConnection() || sqlConnection.getConnection().isClosed()){
					sqlConnection = new SQLConnection();
					sqlConnection.initConn();
				}
//				else{
//					Statement stat=sqlConnection.getConnection().createStatement();
//					ResultSet rs=stat.executeQuery("select top 1 id from T_PM_MaterialPurchasePlanDetails");
//					if(rs.next()){
//						System.out.println("--------------link is ok");
//					}
//					rs.close();
//					stat.close();
//				}
			}catch(Exception e){
				sqlConnection = new SQLConnection();
				sqlConnection.initConn();
			}
		}
		return sqlConnection;
	}
	
	public Connection getConnection() throws SQLException {
		return conn;
	}
	
	public Statement createStatement(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		return stmt;
	}
	
	public PreparedStatement getPrepStmt(String sql) throws SQLException {
		pstmt = conn.prepareStatement(sql);
		return pstmt;
	}
	
	public void executeUpdate(String sql) throws SQLException {
		stmt.executeUpdate(sql);
	}
	
	public ResultSet executeQuery(String sql) throws SQLException {
		rs = stmt.executeQuery(sql);
		return rs;
	}
	
	public void closeCon() {
		try {
			if (null!=conn) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void closeStmt() {
		try {
			if (null!=stmt) {
				stmt.close();
				stmt = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}