dbcontroller.java

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

package com.trulytech.mantis.system;

import java.sql.ResultSet;
import java.sql.Statement;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.ArrayList;
import java.util.Iterator;
import com.trulytech.mantis.result.DBColumn;
import com.trulytech.mantis.result.DBResult;

/**
 * <p>Title: DBController</p>
 * <p>Description: 数据库访问，用于业务逻辑</p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: trulytech</p>
 * @author WangXian
 * @version 1.2
 */
public class DBController {

  private Statement stmt = null;

  public DBController(Statement stmt) {
    this.stmt = stmt;
  }

  /**
   * 获得stmt
   * @return Statement stmt
   */
  public Statement getStatement() {
    return stmt;
  }

  /**
   * 执行SQL [insert,update]
   * @param SQLStatement SQL语句
   * @throws Exception
   */
  protected void SQL(String SQLStatement) throws Exception {
    logWriter.Info(SQLStatement);
    stmt.executeUpdate(SQLStatement);
  }

  /**
   * 执行QrySQL [select]
   * @param SQLStatement SQL语句
   * @param param 参数列表
   * @return DBResult
   * @throws Exception
   */
  protected DBResult QrySQL(String SQLStatement, HashMap param) throws
      Exception {
    logWriter.Info(SQLStatement);
    DBResult Result = new DBResult();
    Result.ParamList = param;
    ResultSet Rs = null;
    ArrayList Record = null;
    try {
      Rs = stmt.executeQuery(SQLStatement);
      Result.ColumnCount = Rs.getMetaData().getColumnCount();

      while (Rs.next()) {
        Record = new ArrayList();
        for (int i = 1; i <= Result.ColumnCount; 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);
          }
          Record.add(Column);

        }
        Result.ResultBuffer.add(Record);
      }
    }
    finally {
      if (Rs != null) {
        Rs.close();
      }

    }
    return Result;
  }

  /**
   * 执行单一查询 [select]
   * @param SQLStatement SQL语句
   * @return DBResult
   * @throws Exception
   */
  protected String[] SingleQry(String SQLStatement) throws Exception {
    logWriter.Info(SQLStatement);
    ResultSet Rs = null;
    try {
      Rs = stmt.executeQuery(SQLStatement);
      int Count = Rs.getMetaData().getColumnCount();
      String[] Ret = new String[Count];
      if (Rs.next()) {
        for (int i = 1; i <= Count; i++) {
          Ret[i - 1] = Rs.getString(i);
          if (Ret[i - 1] == null)
            Ret[i - 1] = "";

        }

      }
      return Ret;
    }
    finally {
      if (Rs != null) {
        Rs.close();
      }

    }

  }

  /**
   * 执行分页QrySQL [select]
   * @param SQLStatement SQL语句
   * @param param 参数
   * @param Page 页码
   * @param TotalPage 总页码
   * @param rowsPerPage 每页记录数
   * @param Action Action命令
   * @return DBResult
   * @throws Exception
   */

  protected DBResult QrySQL(String SQLStatement, HashMap param, int Page,
                            int rowsPerPage, int TotalPage, String Action) throws
      Exception {
    logWriter.Info(SQLStatement);
    DBResult Result = new DBResult();
    ResultSet Rs = null;
    ArrayList Record = null;
    StringBuffer PageSearch = new StringBuffer();
    StringBuffer Search = new StringBuffer("?");
//启始记录位置
    int Offset = 0;
    try {
      Rs = stmt.executeQuery(SQLStatement);
//初始化分页对象
      if (Page == 0 && Rs.first() && Rs.last())
        if (Rs.getRow() % rowsPerPage != 0)
          Result.TotalPage = String.valueOf(Rs.getRow() / rowsPerPage + 1);
        else
          Result.TotalPage = String.valueOf(Rs.getRow() / rowsPerPage);
      else
        Result.TotalPage = String.valueOf(TotalPage);
      if (Result.TotalPage.equalsIgnoreCase("0"))
        Result.TotalPage = "1";
//计算启始记录位置
      if (Page == 0) {
        Page = 1;
        Result.currentPage = "1";
      }
      else
        Result.currentPage = String.valueOf(Page);
      Offset = (Page - 1) * rowsPerPage;
      if (Offset == 0) {
        Rs.beforeFirst();
      }
      else {
        Rs.absolute(Offset);
      }

//初始化参数变量
      Result.ParamList = param;
      Result.ColumnCount = Rs.getMetaData().getColumnCount();
      Result.rowsPerPage = String.valueOf(rowsPerPage);
      Iterator iterator = param.keySet().iterator();
      while (iterator.hasNext()) {

        String key = (String) iterator.next();
        PageSearch.append("<input type=\"hidden\" name=\"" + key +
                          "\" value=\"" + (String) param.get(key) + "\">");
        if (!com.trulytech.mantis.system.Properties.isInternational)
          Search.append(key + "=" +
                        URLEncoder.encode( (String) param.get(key), "GBK") +
                        "&");
        else
          Search.append(key + "=" +
                        URLEncoder.encode( (String) param.get(key), "UTF-8") +
                        "&");

      }
      PageSearch.append("<input type=\"hidden\" name=\"total\" value=\"" +
                        Result.TotalPage + "\">");
      PageSearch.append("<input type=\"hidden\" name=\"len\" value=\"" +
                        Result.rowsPerPage + "\">");
      if (Action != null) {
        PageSearch.append("<input type=\"hidden\" name=\"" +
                          Properties.Action_Tag + "\" value=\"" +
                          Action + "\">");
        Search.append(Properties.Action_Tag + "=" + Action + "&");
      }
      Search.append("total=" + Result.TotalPage + "&");
      Search.append("len=" + String.valueOf(rowsPerPage) + "&page=");
      Result.URLSearch = Search.toString();
      Result.firstPage = Search.toString() + "1";
      Result.lastPage = Search.toString() + Result.TotalPage;
      if (Page == 1)
        Result.previousPage = Search.toString() + "1";
      else
        Result.previousPage = Search.toString() + String.valueOf(Page - 1);

      if (Page == (new Integer(Result.TotalPage).intValue()))
        Result.nextPage = Search.toString() + Result.TotalPage;
      else
        Result.nextPage = Search.toString() + String.valueOf(Page + 1);

      Result.pageSearch = PageSearch.toString();

      Result.rowsPerPage = String.valueOf(rowsPerPage);
//取值
      for (int i = 0; Rs.next() && i < rowsPerPage; i++) {
        Record = new ArrayList();
        for (int j = 1; j <= Result.ColumnCount; j++) {
          DBColumn Column = new DBColumn();
          Column.Value = Rs.getString(j);
          if (Column.Value == null)
            Column.Value = "";
          if (com.trulytech.mantis.system.Properties.MetaData) {
            Column.Length = Rs.getMetaData().getColumnDisplaySize(j);
            Column.isNullable = Rs.getMetaData().isNullable(j);
          }

          Record.add(Column);

        }
        Result.ResultBuffer.add(Record);
      }
    }
    finally {
      if (Rs != null) {
        Rs.close();
      }
    }
    return Result;
  }

  /**
   * Oracle优化-执行分页QrySQL [select]
   * @param SQLStatement SQL语句
   * @param param 参数
   * @param Page 页码
   * @param TotalPage 总页码
   * @param rowsPerPage 每页记录数
   * @param Action Action命令
   * @return DBResult
   * @throws Exception
   */

  /*
     protected DBResult QrySQL(String SQLStatement, HashMap param, int Page,
   int rowsPerPage, int TotalPage,String Action) throws Exception {
   logWriter.Info(SQLStatement);
   DBResult Result = new DBResult();
   ResultSet Rs = null;
   ArrayList Record = null;
   StringBuffer PageSearch = new StringBuffer();
   StringBuffer Search = new StringBuffer("?");
   int Rows=0;
//启始记录位置
   int Offset = 0;
   try {

//初始化分页对象
     if (Page == 0)
     {
       Rs=stmt.executeQuery("select count(*) from (" + SQLStatement + ")");
       if (Rs.next()) Rows=Rs.getInt(1);
       Rs.close();
       if (Rows % rowsPerPage != 0)
         Result.TotalPage = String.valueOf(Rows / rowsPerPage + 1);
       else
         Result.TotalPage = String.valueOf(Rows / rowsPerPage);
     }
     else
       Result.TotalPage = String.valueOf(TotalPage);
     if (Result.TotalPage.equalsIgnoreCase("0"))
       Result.TotalPage = "1";
//计算启始记录位置
     if (Page == 0) {
       Page = 1;
       Result.currentPage = "1";
     }
     else
       Result.currentPage = String.valueOf(Page);
     Offset = (Page - 1) * rowsPerPage;

     Rs=stmt.executeQuery("select * from (select a.*,rownum rn from (" + SQLStatement + ") a where rownum<=" + new Integer(Offset+rowsPerPage) +
         ") where rn>" + new Integer(Offset));


//初始化参数变量
     Result.ParamList = param;
     Result.ColumnCount = Rs.getMetaData().getColumnCount()-1;
     Result.rowsPerPage = String.valueOf(rowsPerPage);
     Iterator iterator = param.keySet().iterator();
     while (iterator.hasNext()) {

       String key = (String) iterator.next();
       PageSearch.append("<input type=\"hidden\" name=\"" + key +
                         "\" value=\"" + (String) param.get(key) + "\">");
       if (!com.trulytech.mantis.system.Properties.isInternational)
       Search.append(key + "=" +
                     URLEncoder.encode( (String) param.get(key), "GBK") +
                     "&");
     else
      Search.append(key + "=" +
                    URLEncoder.encode( (String) param.get(key), "UTF-8") +
                    "&");

     }
     PageSearch.append("<input type=\"hidden\" name=\"total\" value=\"" +
                       Result.TotalPage + "\">");
     PageSearch.append("<input type=\"hidden\" name=\"len\" value=\"" +
                       Result.rowsPerPage + "\">");
     if (Action!=null)
     {
       PageSearch.append("<input type=\"hidden\" name=\"" +
                         Properties.Action_Tag + "\" value=\"" +
                         Action + "\">");
       Search.append(Properties.Action_Tag + "=" + Action + "&");
     }
     Search.append("total=" + Result.TotalPage + "&");
     Search.append("len=" + String.valueOf(rowsPerPage) + "&page=");
     Result.URLSearch=Search.toString();
     Result.firstPage = Search.toString() + "1";
     Result.lastPage = Search.toString() + Result.TotalPage;
     if (Page == 1)
       Result.previousPage = Search.toString() + "1";
     else
       Result.previousPage = Search.toString() + String.valueOf(Page - 1);

     if (Page == (new Integer(Result.TotalPage).intValue()))
       Result.nextPage = Search.toString() + Result.TotalPage;
     else
       Result.nextPage = Search.toString() + String.valueOf(Page + 1);

     Result.pageSearch = PageSearch.toString();

     Result.rowsPerPage = String.valueOf(rowsPerPage);
//取值
     while (Rs.next()){
       Record = new ArrayList();
       for (int j = 1; j <= Result.ColumnCount; j++) {
         DBColumn Column = new DBColumn();
         Column.Value = Rs.getString(j);
         if (Column.Value == null)
           Column.Value = "";
         if (com.trulytech.mantis.system.Properties.MetaData)
         {
           Column.Length = Rs.getMetaData().getColumnDisplaySize(j);
           Column.isNullable = Rs.getMetaData().isNullable(j);
         }


         Record.add(Column);

       }
       Result.ResultBuffer.add(Record);
     }
   }
   finally {
     if (Rs != null) {
       Rs.close();
     }
   }
   return Result;
    }
   */

}