jdbconnection.java~2~

JSP+SQL实现了一个拥有添加、删除、查询基本功能的CRM系统

package com.tool;


import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

//这是一个工具类实现与数据库的连接
public class JDBConnection {
  private final String dbDriver = "net.sourceforge.jtds.jdbc.Driver";
  private final String url = "jdbc:jtds:sqlserver://localhost:1433/db_Client";
  private final userName = "sa";
  private final password = "";
  private Connection conn;
//加载数据库驱动
  public JDBConnection() {
    Class.forName(dbDriver).newInstance();
  }

  //创建数据库的连接
  public boolean creatConnection( ) {
    conn=DriverManager.
    return true;
  }

  //建立连接
  public JDBConnection(DataSource dataSource) {

    try {
      setConn(dataSource.getConnection());
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  //执行查询，返回一个结果集
  public ResultSet executeQuery(String s) throws SQLException {

    Statement statement = conn.createStatement();
    return statement.executeQuery(s);
  }

  //执行更新，插入，删除 无返回值
  public void executeUpdate(String s) throws SQLException {

    Statement statement = conn.createStatement();
    statement.executeUpdate(s);
  }

  //关闭连接
  public void closeConn() throws SQLException {

    conn.close();
  }

  //设置提交
  public void setAutoCommit(boolean flag) {
    try {
      conn.setAutoCommit(flag);
    }
    catch (Exception exception) {
      System.out.println("Exception in set autoCommit :" + exception.getMessage());
    }
  }

  //执行提交
  public void commit() {
    try {
      conn.commit();
    }
    catch (Exception exception) {
      System.out.println("Exception in commit :" + exception.getMessage());
    }
  }

  //执行回滚
  public void rollback() {
    try {
      conn.rollback();
    }
    catch (Exception exception) {
      System.out.println("Exception in rollback :" + exception.getMessage());
    }
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为字符串型的
  public String editSqlCode(String sql, String str) {

    try {
      // 截取从0到第一个问号的字符串加参数str，然后在加上第一问号以后的字符串
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为长整型的
  public String editSqlCode(String sql, Integer str) {

    try {
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为整型的
  public String editSqlCode(String sql, int str) { //提取SQL语句，并把(int str)传出来的值来代替？

    try {
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }

}