package myapp;

import java.sql.*;

/**
 * <p>數據庫訪問類</p>
 */
public class DBConnection {
  Connection con = null;
  Statement stmt = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  String sql = null;
  public DBConnection() {
  }

  //使用JDBC連接數據庫
  public void getConnection() {
    try {
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); //載入驅動程序
      String url =
          "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=data"; //data為數據庫名
      String username = "sa"; //用戶名
      String password = "bb"; //用戶密碼
      //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//載入驅動程序
      //String url="jdbc:odbc:StudentData";//StudentData為ODBC數據源
      //String username="sa";//用戶名
      //String password="bb";//用戶密碼
      con = DriverManager.getConnection(url, username, password); //連接數據庫
    }
    catch (Exception e) {
      System.out.println("連接數據庫服務器失敗。");
      e.printStackTrace();
    }
  }

  //查詢數據1
  public ResultSet selectRecord(String sql) {
    try {
      stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                 ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery(sql);

    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return rs;
  }

  //查詢數據2
  public ResultSet selectRecord1(String sql) {
    try {
      ps = con.prepareStatement(sql);
      rs = ps.executeQuery();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return rs;
  }

  //增加數據
  public int insertRecord(String sql) {
    int num = 0;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(sql);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return num;
  }

  //修改數據
  public int updateRecord(String sql) {
    int num = 0;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(sql);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return num;
  }

  //刪除數據
  public int deleteRecord(String sql) {
    int num = 0;
    try {
      stmt = con.createStatement();
      stmt.executeUpdate(sql);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return num;
  }

  //統計記錄數
  public int countRecord(String sql) {
    int num = 0;
    try {
      stmt = con.createStatement();
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        num = rs.getInt(1);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return num;
  }

  //關閉數據庫連接并釋放資源
  public void closeConn() {
    try {
      if (rs != null) {
        rs.close();
        rs = null;
      }
      if (stmt != null) {
        stmt.close();
        stmt = null;
      }
      if (ps != null) {
        ps.close();
        ps = null;
      }
      if (con != null) {
        con.close();
        con = null;
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      con = null;
    }
  }
}