package com.tsinghuait.st0717.hospitalsystem.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collection;

public class SQLServerDBManager {

	/**
	 * 該方法用于連接SQL Server數據庫
	 * 
	 * @param url:
	 *            連接數據庫的url
	 * @param username:
	 *            數據庫訪問用戶名
	 * @param password:
	 *            數據庫訪問密碼
	 */
	public static Connection getConnection(String url, String username,
			String password) {
		Connection conn = null;
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 該方法用戶查詢數據庫并返回ResultSet類型的結果集
	 * 
	 * @param sql:數據庫查詢語句
	 * @param id:
	 *            要查找數據的ID
	 */
	public static ResultSet execQuery(String sql) {
		ResultSet rs = null;
		try {
			Connection conn = getConnection(
					"jdbc:microsoft:sqlserver://localhost:1433;databaseName=Hospital",
					"sa", "sa");
			Statement stmt = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	public Collection execQuery(int id) {
		Collection coll = null;
		return coll;
	}

	/**
	 * 該方法用于修改對應的數據信息
	 * 
	 * @param sql:用于修改數據的sql語句
	 * @return int :影響的行數
	 */
	public int updateData(String sql) {
		int refrenceRows = 0;
		try {
			Connection conn = getConnection(
					"jdbc:microsoft:sqlserver://localhost:1433;databaseName=Hospital",
					"sa", "sa");
			Statement stmt = conn.createStatement();
			refrenceRows = stmt.executeUpdate(sql);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return refrenceRows;
	}

	/**
	 * 該方法用于刪除對應的數據信息
	 * 
	 * @param sql:用于刪除數據的sql語句
	 */
	public int deleteData(String sql) {
		int refrenceRows = 0;
		try {
			Connection conn = getConnection(
					"jdbc:microsoft:sqlserver://localhost:1433;databaseName=Hospital",
					"sa", "sa");
			Statement stmt = conn.createStatement();
			refrenceRows = stmt.executeUpdate(sql);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return refrenceRows;
	}

	/**
	 * 該方法用于存儲數據信息
	 * 
	 * @param sql:用于存儲數據的sql語句
	 */
	public static void saveData(String sql) {
		try {
			Connection conn = getConnection(
					"jdbc:microsoft:sqlserver://localhost:1433;databaseName=Hospital",
					"sa", "sa");
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}