package net.chat;
import java.sql.*;
import java.lang.ClassNotFoundException;

public class BaseConn {
  private Connection conn = null;
  private Statement stmt = null;
  private PreparedStatement ps = null;
  private ResultSet rs = null;  
  /**
   * BaseConn的構造函數，在這里我們完成數據庫的初始華操作，即連接數據庫操作
   * */
  public BaseConn() throws SQLException,ClassNotFoundException{
    try
    {
      String url="jdbc:sqlserver://localhost:1433;DatabaseName=chatroom";
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      conn = DriverManager.getConnection(url,"sa","");
      stmt = conn.createStatement();
    }
    catch(SQLException e)
    {
      System.out.println("Error occured when Connect DataBase:"+e);
      throw e;
    }
    catch(ClassNotFoundException e)
    {
      System.out.println("Error occured when Connect DataBase:"+e);
      throw e;
    }
  }
  
  /**
   * 初始化預編譯的 SQL 語句的對象
   * */
  public PreparedStatement preparedStatement(String sql) throws SQLException
  {
    try
    {
      ps = conn.prepareStatement(sql);
      return ps;
    }catch(SQLException e)
    {
      System.out.println("Error occured when create preparedStatement:"+e);
      throw e;
    }
  }  
  /**
   * 執行靜態 SQL 查詢語句并返回它所生成結果的對象
   * */
  public ResultSet executeQuery(String sql) throws SQLException
  {
    rs = null;
    try {
      rs = stmt.executeQuery(sql);
    }
    catch (SQLException ex) {
      System.out.println("Error occured when query database：" + ex);
      throw ex;
    }
    return rs;
  }
  
  /**
   * 執行靜態SQL更新語句并返回影響數據條數
   * */
  public int executeUpdate(String sql) throws SQLException
  {
    try {
      conn.setAutoCommit(false);
      int re = stmt.executeUpdate(sql);
      conn.commit();
      return re;
    }
    catch (SQLException e) {
      conn.rollback();
      System.out.println("Error occured when update database：" + e);
      throw e;
    }
  }  
  /**
   * 執行預編譯的SQL查詢語句
   * 
   * */
  public ResultSet executeQuery() throws SQLException
  {
    try {
      return ps.executeQuery();
    }
    catch (SQLException e) {
      System.out.println("Error occured when query database：" + e);
      throw e;
    }
  }
  /**
   * 執行預編譯的SQL更新語句
   * 
   * */
  public int executeUpdate() throws SQLException
  {
    try {
      conn.setAutoCommit(false);
      int r = ps.executeUpdate();
      conn.commit();
      return r;
    }
    catch (SQLException e) {
      conn.rollback();
      System.out.println("Error occured when update database：" + e);
      throw e;
    }
  }
 /**
  * 數據庫關閉操作
  * */
  public boolean closeDB() throws SQLException
  {
    try {
      if (this.rs != null)
        rs.close();
      if (this.stmt != null)
        this.stmt.close();
      if (this.ps != null)
        this.ps.close();
      if (this.conn != null)
        conn.close();
      return true;
    }
    catch (SQLException e) {
      System.out.println("Error occured when close database：" + e);
      throw e;
    }
  }
}