dbconnection.java

用户登录

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;
    }
  }
}