sqlhelper.java

java版源代码,里面包含很多源代码,大家可以看看.

package com.trulytech.mantis.thread;

import java.sql.*;
import java.util.*;
import com.trulytech.mantis.system.Properties;
import com.trulytech.mantis.system.logWriter;
import com.trulytech.mantis.result.DBResult;
import com.trulytech.mantis.result.DBColumn;
import com.trulytech.mantis.system.ConnManager;

/**
 *
 * <p>Title: Mantis</p>
 *
 * <p>Description: SQL帮助类</p>
 *
 * <p>Copyright: Copyright (c) 2002</p>
 *
 * <p>Company: </p>
 *
 * @author Wang Xian
 * @version 1.0
 */
public class SQLHelper {
  //数据库连接
  private Connection conn = null;

  /**
   * 构造方法
   * @throws Exception
   */
  public SQLHelper() throws Exception{
    this.conn = ConnManager.getConnection();
  }


  /**
   * 获得数据库连接
   * @return Connection 数据库连接
   */
  public Connection getConn() {
    return conn;
  }

  /**
   * 执行update,delete操作
   * @param SQL String SQL语句
   * @throws SQLException
   */
  public void SQLExecute(String SQL) throws SQLException {
    Statement stmt = null;

    try {
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
      logWriter.Debug(SQL);

      stmt.executeUpdate(SQL);
    }

    finally {

      try {
        if (stmt != null)
          stmt.close();
      }
      catch (Exception e) {}
    }
  }

  /**
   * 查询SQL操作
   * @param SQL String SQL语句
   * @return String[]
   * @throws SQLException
   */
  public String[] QuerySingle(String SQL) throws SQLException {
    String[] Ret = null;
    Statement stmt = null;
    ResultSet rs = null;
    logWriter.Debug(SQL);
    try {
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
      stmt.setFetchSize(Properties.Fetch_Size);
      stmt.setMaxRows(Properties.MaxRows);
      stmt.setQueryTimeout(Properties.QueryTimeout);

      rs = stmt.executeQuery(SQL);
      if (rs.next()) {
        int Count = rs.getMetaData().getColumnCount();
        Ret = new String[Count];
        for (int i = 1; i <= Count; i++)
          Ret[i - 1] = rs.getString(i)==null?"":rs.getString(i);
      }
    }

    finally {
      try {
        if (rs != null)
          rs.close();
      }
      catch (SQLException e) {}
      try {
        if (stmt != null)
          stmt.close();
      }
      catch (Exception e) {}
    }
    return Ret;
  }

  /**
   * 返回多记录的SQL查询
   * @param SQL String SQL语句
   * @return DBResult
   * @throws SQLException
   */
  public DBResult QueryExecute(String SQL) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    DBResult ret = new DBResult();
    logWriter.Debug(SQL);
    try {
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                  ResultSet.CONCUR_READ_ONLY);
      stmt.setFetchSize(Properties.Fetch_Size);
      stmt.setMaxRows(Properties.MaxRows);

      stmt.setQueryTimeout(Properties.QueryTimeout);

      rs = stmt.executeQuery(SQL);
      while (rs.next()) {
        int Count = rs.getMetaData().getColumnCount();
        ArrayList rec = new ArrayList();
        for (int i = 1; i <= Count; i++) {
          DBColumn Column = new DBColumn();
          Column.Value = rs.getString(i);
          if (Column.Value == null)
            Column.Value = "";
          if (Properties.MetaData) {
            Column.Length = rs.getMetaData().getColumnDisplaySize(i);
            Column.isNullable = rs.getMetaData().isNullable(i);
          }

          rec.add(Column);
        }
        ret.ResultBuffer.add(rec);
      }
    }

    finally {
      try {
        if (rs != null)
          rs.close();
      }
      catch (SQLException e) {}
      try {
        if (stmt != null)
          stmt.close();
      }
      catch (Exception e) {}

    }
    return ret;
  }

  public void Close() throws SQLException
  {

    if (this.conn!=null)
    ConnManager.closeConnection(this.conn);

  }
}