package com.evangelsoft.crosslink.kboxing.sap.dao;

/**
 * File: ConnectDB.java
 * Author: Liway
 * Function: Connect Database(MySQL) use jdbc
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnectDB {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=Test";
	private static final String USERNAME = "sa";
	private static final String PASSWORD = "123";
	private int result = 0;
	
	public ConnectDB() {
		
	}

	public PreparedStatement getPstmt(String sql) {
		try {
			if(conn == null || conn.isClosed()) {
				Class.forName(DRIVER);
				conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				conn.setAutoCommit(false);
			}
			pstmt = conn.prepareStatement(sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	public void setString(int index, String value) {
		if (pstmt != null) {
			try {
				pstmt.setString(index, value);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void setInt(int index, int value) {
		if (pstmt != null) {
			try {
				pstmt.setInt(index, value);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean executeUpdate() {
		if (conn != null && pstmt != null) {
			try {
				result = pstmt.executeUpdate();
				conn.commit();
			} catch (SQLException e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				e.printStackTrace();
			}
		}
		return result == 1;
	}

	public ResultSet executeQuery() {
		if (pstmt != null) {
			try {
				rs = pstmt.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	public void close() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			pstmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}

	public void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}

	// test
	public static void main(String[] args) {
		String sql = "select * from user";
		ResultSet resultSet = null;
		ConnectDB cdb = new ConnectDB();
		cdb.getPstmt(sql);
		resultSet = cdb.executeQuery();
		try {
			while (resultSet.next()) {
				System.out.println(resultSet.getString("name"));
			}
			resultSet.close();
			cdb.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}