[Java]MySQL/Oracle資料庫存取函式

序言

延續上一篇，這篇針對MySQL資料庫的存取寫出的函式提供給大家參考，開發環境與資料庫連接器的設定可參考上一篇。
我在這邊有兩種建立連線物件的方式，預設是來自JNDI的連線設定，另一種是直接產生的方式，可在建立物件時做選擇。
使用範例的部分我改用JUnit來進行我自己的原始碼驗證，大家可直接參考呼叫的方式。
PS.2011/6/21 更新，加入Oracle的連線方法(但分頁查詢的函式只能用在MySQL)

原始碼函式類別

package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBBaseIO {
 private static boolean _isDebug=false;
 public enum DBType{
  MySQL
  ,Oracle
 }
 
 public static void setDebug(boolean isDebug) {
  _isDebug = isDebug;
 }

 public static boolean isIsdebug() {
  return _isDebug;
 }

 private Connection con = null; // Database objects
 public Connection getConnection() {
  return con;
 }

 private Statement stat = null;
 private ResultSet rs = null;
 private PreparedStatement pst = null;
 private boolean isClosed=false;
 public boolean isClosedConn(){
  return isClosed;
 }
 
 protected void finalize ()  {
        if(!isClosed)CloseConn();
    }
 
/**
 * 建立資料庫存取物件(使用JNDI XML設定，名稱為MySqlDS)
 * @throws SQLException
 * @throws NamingException
 */
 public DBBaseIO() throws SQLException, NamingException {
  setupConnMySQLByXML();
 }
 /**
  * 建立資料庫存取物件(使用JNDI XML設定)
  * @param dsName 設定名稱
  * @throws SQLException
  * @throws NamingException
  */
 public DBBaseIO(String dsName) throws SQLException, NamingException {
  setupConnMySQLByXML(dsName);
 }
 /**
  * 
  * @param sourceType
  * @throws ClassNotFoundException
  * @throws SQLException
  * @throws NamingException
  */
 public DBBaseIO(int sourceType) throws ClassNotFoundException, SQLException, NamingException{
  switch(sourceType){
   case 0:
    setupConnMySQL("localhost:3306", "root", "", "mysql");
    break;
   case 1:
    setupConnMySQLByXML();
    break;
  }  
 }
 /**
   * 建立資料庫存取物件
   * 
   * @param str_SQLIP
   *            資料庫IP
   * @param str_SQLID
   *            登入帳號
   * @param str_SQLPSWD
   *            登入密碼
   * @param str_SQLDefaultDBName
   *            預設資料庫
   * @throws SQLException 
   * @throws ClassNotFoundException 
   */
 public DBBaseIO(String str_SQLIP, String str_SQLID, String str_SQLPSWD,
   String str_SQLDefaultDBName) throws ClassNotFoundException, SQLException  {
  setupConnMySQL(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
 }
 /**
   * 建立資料庫存取物件
   * 
   * @param dbtype
   *            資料庫類型
   * @param str_SQLIP
   *            資料庫IP
   * @param str_SQLID
   *            登入帳號
   * @param str_SQLPSWD
   *            登入密碼
   * @param str_SQLDefaultDBName
   *            預設資料庫
   * @throws SQLException 
   * @throws ClassNotFoundException 
   */
 public DBBaseIO(DBType dbtype,String str_SQLIP, String str_SQLID, String str_SQLPSWD,
   String str_SQLDefaultDBName) throws ClassNotFoundException, SQLException  {
  if(dbtype==DBType.MySQL)
   setupConnMySQL(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
  else if(dbtype==DBType.Oracle)
   setupConnOracle(str_SQLIP, str_SQLID, str_SQLPSWD, str_SQLDefaultDBName);
  else
   throw new ClassNotFoundException("DBType un-define");
 }
 private void setupConnMySQLByXML() throws  NamingException, SQLException{setupConnMySQLByXML("MySqlDS"); }
 private void setupConnMySQLByXML(String name) throws  NamingException, SQLException{
  if(isIsdebug()){
   System.out.println("DataSource:"+"java:/" + name);
  }
  
  try{  
  InitialContext ctx = new InitialContext();
  DataSource ds = (DataSource)ctx.lookup("java:/" + name);
  con = ds.getConnection();
  } catch (NamingException e) {
   System.err.println("Connection XML Load Error :" + e.toString());
   throw e;
  } catch (SQLException e) {
   System.err.println("Get Connection Error :" + e.toString());
   throw e;
  }
 }

 private void setupConnMySQL(String str_SQLIP, String str_SQLID,
   String str_SQLPSWD, String str_SQLDefaultDBName)  throws ClassNotFoundException,SQLException {
  if(isIsdebug()){
   System.out.println("DataSource:"+"jdbc:mysql://" + str_SQLIP + "/"
     + str_SQLDefaultDBName
     + "?useUnicode=true&characterEncoding=UTF8");
   System.out.println("ID:"+str_SQLID+"\tPassword:"+str_SQLPSWD);
   System.out.println("JDBC Driver:" + "com.mysql.jdbc.Driver");
  }
  try {
   Class.forName("com.mysql.jdbc.Driver");
  
   con = DriverManager.getConnection("jdbc:mysql://" + str_SQLIP + "/"
     + str_SQLDefaultDBName
     + "?useUnicode=true&characterEncoding=UTF8", str_SQLID,
     str_SQLPSWD);
   
  } catch (ClassNotFoundException e) {
   System.err.println("DriverClassNotFound :" + e.toString());
   throw e;
  }
  catch (SQLException x) {
   System.err.println("Exception :" + x.toString());
   throw x;
  }
 }
 private void setupConnOracle(String str_SQLIP, String str_SQLID,
   String str_SQLPSWD, String str_SQLDefaultDBName)  throws ClassNotFoundException,SQLException {
  if(isIsdebug()){
   System.out.println("DataSource:"+"jdbc:oracle:thin:"+str_SQLID+"/"+str_SQLPSWD+"@//" + str_SQLIP + "/"
     + str_SQLDefaultDBName);
  }
  try {
   Class.forName("oracle.jdbc.OracleDriver");
  
   con = DriverManager.getConnection("jdbc:oracle:thin:"+str_SQLID+"/"+str_SQLPSWD+"@//" + str_SQLIP + "/"
     + str_SQLDefaultDBName);
   
  } catch (ClassNotFoundException e) {
   System.err.println("DriverClassNotFound :" + e.toString());
   throw e;
  }
  catch (SQLException x) {
   System.err.println("Exception :" + x.toString());
   throw x;
  }
 }

  /**
   * 執行insert update delete等更新指令用
   * 
   * @param str_SQL
   *            查詢語法
   * @return 資料庫變動筆數
   */
 public int executeUpdate(String str_SQL) throws SQLException,NullPointerException {
  return executeUpdate(str_SQL, null);
 }

  /**
   * 執行insert update delete等更新指令用
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @return 資料庫變動筆數
   */
 public int executeUpdate(String str_SQL, Object[] param) throws SQLException ,NullPointerException{
  return executeUpdate(str_SQL, param, true);
 }

  /**
   * 執行insert update delete等更新指令用
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @param isCloseConn
   *            是否關閉連線
   * @return 資料庫變動筆數
   */
 public int executeUpdate(String str_SQL, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeUpdate:"+str_SQL);
   if(param!=null)
   for(int i=0;i<param.length;i++)
    System.out.println("Parameter "+ i +":"+param[i]);
   System.out.println("isCloseConn :"+isCloseConn);
  }
  
  int result = 0;
  if (con != null) {
   try {
    int i = 0;
    if (param == null) {
     stat = con.createStatement();
     result = stat.executeUpdate(str_SQL);
    } else {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.length; i++) {
      pst.setObject(i + 1, param[i]);
     }
     result = pst.executeUpdate();
     try {
      pst.clearParameters();
     } catch (Exception e) {
      CloseConn();
     }
    }
   } catch (SQLException e) {
    System.err.println("executeUpdate Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }
 
 
  /**
   * 批次執行SQL指令使用
   * 
   * @param str_SQL
   *            查詢語法的陣列
   * @param isCloseConn
   *            是否關閉連線
   * @return 資料庫變動筆數的陣列
   */
 public int[] executeBatch(String[] str_SQL, boolean isCloseConn) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeBatch:"+str_SQL);
  }
  
  int[] result=null;
  if (con != null) {
   try {
    int i = 0;
    stat = con.createStatement();
    for (i = 0; i < str_SQL.length; i++) {
     stat.addBatch(str_SQL[i]);
    }
    result=stat.executeBatch();
    if(isIsdebug()){
     System.out.println("executeBatch result:");
      for (i = 0; i < result.length; i++) {
       System.out.println(i+":"+result[i]);
      }
    }
   } catch (SQLException e) {
    System.err.println("executeBatch Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }
 
 /**
  * 批次執行SQL指令使用
  * @param str_SQL 查詢語法
  * @param param 以相同數量的參數形成的ArrayList
  * @param isCloseConn  是否關閉連線
  * @return   資料庫變動筆數的陣列
  * @throws SQLException
  * @throws NullPointerException
  */
 public int[] addBatch(String str_SQL, ArrayList<Object[]> param, boolean isCloseConn) throws SQLException,NullPointerException {

  int i = 0;
  int j = 0;
  if(isIsdebug()){
   System.out.println("executeBatch:"+str_SQL);
   if(param!=null)
    for (i = 0; i < param.size(); i++) {
     for (j = 0;j < param.get(i).length; j++) {
      System.out.println("Parameter "+ i+","+j +":"+param.get(i)[j]);
     }
    }
  }
  
  int[] result=null;
  if (con != null) {
   try {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.size(); i++) {
      for (j = 0;j < param.get(i).length; j++) {
       pst.setObject(j + 1, param.get(i)[j]);
      }
      pst.addBatch();
     }
     result=pst.executeBatch(); 
     if(isIsdebug()){
      System.out.println("executeBatch result:");
       for (i = 0; i < result.length; i++) {
        System.out.println(i+":"+result[i]);
       }
     }
   } catch (SQLException e) {
    System.err.println("executeBatch Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  
  return result;
 }
 
  /**
   * 查詢資料
   * 
   * @param str_SQL
   *            查詢語法
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public ArrayList<Object[]> executeQuery(String str_SQL) throws SQLException,NullPointerException {
  return executeQuery(str_SQL, null);
 }
  /**
   * 查詢資料
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public ArrayList<Object[]> executeQuery(String str_SQL, Object[] param) throws SQLException,NullPointerException {
  return executeQuery(str_SQL, param, true);
 }

  /**
   * 查詢資料
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @param isCloseConn
   *            是否關閉連線
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public ArrayList<Object[]> executeQuery(String str_SQL, Object[] param,
   boolean isCloseConn) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeQuery:"+str_SQL);
   if(param!=null)
   for(int i=0;i<param.length;i++)
    System.out.println("Parameter "+ i +":"+param[i]);
   System.out.println("isCloseConn :"+isCloseConn);
  }
  ArrayList<Object[]> result = new ArrayList<Object[]>();
  if (con != null) {
   try {
    int i = 0;
    if (param == null) {
     stat = con.createStatement();
     rs = stat.executeQuery(str_SQL);
    } else {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.length; i++) {
      pst.setObject(i + 1, param[i]);
     }
     rs = pst.executeQuery();
     pst.clearParameters();
    }

    ArrayList<Object> arr;
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    while (rs.next()) {
     arr = new ArrayList<Object>();
     for (i = 1; i < numberOfColumns + 1; i++) {
      arr.add(rs.getObject(i));
     }
     result.add(arr.toArray());
    }
   } catch (SQLException e) {
    System.err.println("executeQuery Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }

  /**
   * 查詢資料(請記得要關閉Result Set)
   * 
   * @param str_SQL
   *            查詢語法
   * @return 回傳查詢得到的result set
   */
 public ResultSet executeQueryResultset(String str_SQL) throws SQLException,NullPointerException {
  return executeQueryResultset(str_SQL,null);
 }
  /**
   * 查詢資料(請記得要關閉Connection)
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @return 回傳查詢得到的result set
   */
 public ResultSet executeQueryResultset(String str_SQL, Object[] param) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeQuery:"+str_SQL);
   if(param!=null)
   for(int i=0;i<param.length;i++)
    System.out.println("Parameter "+ i +":"+param[i]);
  }
  
  ResultSet result = null;
  if (con != null) {
   try {
    int i = 0;
    if (param == null) {
     stat = con.createStatement();
     rs = stat.executeQuery(str_SQL);
    } else {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.length; i++) {
      pst.setObject(i + 1, param[i]);
     }
     rs = pst.executeQuery();
     pst.clearParameters();
    }

    result=rs;
   } catch (SQLException e) {
    System.err.println("executeQueryResultset Exception :" + e.toString());
    throw e;
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }
 
 /**
   * 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
   * 
   * @param str_SQL
   *            查詢語法
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public Object executescalar(String str_SQL) throws SQLException,NullPointerException {
  return executescalar(str_SQL, null);
 }
 /**
   * 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public Object executescalar(String str_SQL, Object[] param) throws SQLException,NullPointerException {
  return executescalar(str_SQL, param, true);
 }

 /**
   * 查詢資料，並傳回查詢所傳回的結果集第一個資料列的第一個資料行
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @param isCloseConn
   *            是否關閉連線
   * @return 每列一筆資料，每筆資料的欄位存於 Object Array中
   */
 public Object executescalar(String str_SQL, Object[] param,
   boolean isCloseConn) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeQuery:"+str_SQL);
   if(param!=null)
   for(int i=0;i<param.length;i++)
    System.out.println("Parameter "+ i +":"+param[i]);
   System.out.println("isCloseConn :"+isCloseConn);
  }
  Object result = null;
  if (con != null) {
   try {
    int i = 0;
    if (param == null) {
     stat = con.createStatement();
     rs = stat.executeQuery(str_SQL);
    } else {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.length; i++) {
      pst.setObject(i + 1, param[i]);
     }
     rs = pst.executeQuery();
     pst.clearParameters();
    }

    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    if (rs.next() && numberOfColumns>=1) {
     result=rs.getObject(1);
    }
   } catch (SQLException e) {
    System.err.println("executescalar Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }


  /**
   * 執行SQL
   * 
   * @param str_SQL
   *            查詢語法
   * @return 是否成功
   */
 public boolean execute(String str_SQL) throws SQLException,NullPointerException {
  return execute(str_SQL, null);
 }

  /**
   * 執行SQL
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @return 是否成功
   */
 public boolean execute(String str_SQL, Object[] param) throws SQLException,NullPointerException {
  return execute(str_SQL, param, true);
 }

  /**
   * 執行SQL
   * 
   * @param str_SQL
   *            查詢語法
   * @param param
   *            參數設定,SQL語法中以?指定，順序即為?的順序，如 where name=? (此參數可為null)
   * @param isCloseConn
   *            是否關閉連線
   * @return 是否成功
   */
 public boolean execute(String str_SQL, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException {
  if(isIsdebug()){
   System.out.println("executeQuery:"+str_SQL);
   if(param!=null)
   for(int i=0;i<param.length;i++)
    System.out.println("Parameter "+ i +":"+param[i]);
   System.out.println("isCloseConn :"+isCloseConn);
  }
  boolean result = false;
  if (con != null) {
   try {
    int i = 0;
    if (param == null) {
     stat = con.createStatement();
     result = stat.execute(str_SQL);
    } else {
     pst = con.prepareStatement(str_SQL);
     for (i = 0; i < param.length; i++) {
      pst.setObject(i + 1, param[i]);
     }
     result = pst.execute();
     pst.clearParameters();
    }
   } catch (SQLException e) {
    System.err.println("execute Exception :" + e.toString());
    throw e;
   } finally {
    CloseConn(isCloseConn);
   }
  } else {
   System.err.println("Connection is null");
   throw new NullPointerException("Connection is null");
  }
  return result;
 }
/**
 * 關閉連線
 */
 public void CloseConn() {
  CloseConn(true);
 }

 /**
  * 關閉連線
  * @param isCloseConn  是否關閉Connection
  */
 public void CloseConn(boolean isCloseConn) {
  if(isIsdebug()){
   System.out.println("CloseConn:"+isCloseConn);
  }
  try {
   if (rs != null) {
    rs.close();
    rs = null;
   }
   if (stat != null) {
    stat.close();
    stat = null;
   }
   if (pst != null) {
    pst.close();
    pst = null;
   }
  } catch (SQLException e) {
   //System.err.println("Close Exception :" + e.toString());
  } finally {
   try {
    if (con != null && isCloseConn) {
     con.close();
     isClosed=true;
    }
   } catch (SQLException e) {
    //System.err.println("Close Exception :" + e.toString());
   }
  }
 }
 
 
 int allpage=0;
 
 public int getAllpage() {
  return allpage;
 }
 public void setAllpage(int allpage) {
  this.allpage = allpage;
 }
 /**
  * 查詢分頁資料
  * @param colunms
  * @param from
  * @param where
  * @param group
  * @param order
  * @param fpage
  * @param pages
  * @return
  * @throws SQLException
  * @throws NullPointerException
  */
 public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages) throws SQLException,NullPointerException{
  return selectPage(colunms,from,where,group,having,order,fpage,pages,null);
  }
 /**
  * 查詢分頁資料
  * @param colunms
  * @param from
  * @param where
  * @param group
  * @param order
  * @param fpage
  * @param pages
  * @param param
  * @return
  * @throws SQLException
  * @throws NullPointerException
  */
 public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages, Object[] param) throws SQLException,NullPointerException{
  return selectPage(colunms,from,where,group,having,order,fpage,pages,param,true);
  }
/**
 * 查詢分頁資料
 * @param colunms
 * @param from
 * @param where
 * @param group
 * @param order
 * @param fpage
 * @param pages
 * @param param
 * @param isCloseConn
 * @return
 * @throws SQLException
 * @throws NullPointerException
 */
 public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException{
  return selectPage(colunms, from, where, group, having, order, fpage, pages, true, 0, param, isCloseConn);
 }
 /**
  * 查詢分頁資料
  * @param colunms
  * @param from
  * @param where
  * @param group
  * @param having
  * @param order
  * @param fpage
  * @param pages
  * @param isCountPage
  * @param allpageCount
  * @param param
  * @param isCloseConn
  * @return
  * @throws SQLException
  * @throws NullPointerException
  */
 public ArrayList<Object[]> selectPage(String colunms,String from,String where,String group,String having,String order,int fpage,int pages,boolean isCountPage,int allpageCount, Object[] param, boolean isCloseConn) throws SQLException,NullPointerException{
  ArrayList<Object[]> result=null;
  try{
   String sql;
  if(!isCountPage){
   allpage=allpageCount;
  }else{
   int intRowCount;
   sql="select count(*) from (select ";
   if(colunms!=null){
    sql+="" + colunms + " \n ";
   }else{
    sql+="* \n "; 
   }
   sql+="from " + from.toString() + " \n ";
   if(where!=null){
    sql+="where " + where + " \n ";
   }
   if(group!=null){
    sql+="group by " + group + " \n ";
   }
   if(having!=null){
    sql+="having " + group + " \n ";
   }
   sql+=") selectcount \n ";
   
   Object temp=executescalar(sql,param,false);
   if(temp==null){
    return result;
   }
   intRowCount=Integer.parseInt(temp.toString());
   allpage=(intRowCount+fpage-1) / fpage;
  }
  int spage;
  
  if(pages > allpage ){// pages == 0){
   pages = 1;
  } 
  spage=(pages-1)*fpage;  
  //sql="select " + colunms + " \n from " + from + " \n where " + where + " \n order by " + order + " \n limit "+ spage +","+fpage  ;
  sql="select ";
  if(colunms!=null){
   sql+= colunms + " \n ";
  }else{
   sql+="* \n "; 
  }
  sql+="from " + from + " \n ";
  if(where!=null){
   sql+="where " + where + " \n ";
  }
  if(group!=null){
   sql+="group by " + group + " \n ";
  }
  if(having!=null){
   sql+="having " + group + " \n ";
  }
  if(order!=null){
   sql+="order by " + order + " \n ";
  }
  sql+="limit "+ spage +","+fpage;
  result=executeQuery(sql, param, false);
  } catch (NullPointerException e) {
   System.err.println("NullPointerException Exception :" + e.toString());
   throw e;
  } catch (SQLException e) {
   System.err.println("SQLException Exception :" + e.toString());
   throw e;
  } finally {
   CloseConn(isCloseConn);
  }
  return result;
 }
 
}

使用範例程式

package common.test;

import static org.junit.Assert.*;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import common.DBBaseIO;

public class DBBaseIOTest {

 DBBaseIO dbio;
 int m_id;
 
 String m_name;
 @Before
 public void setUp() throws Exception {
  dbio=new DBBaseIO(0);
  m_id=20100111;
  m_name="tester" + m_id;
 }

 @After
 public void tearDown() throws Exception {
  dbio.CloseConn();
  dbio=null;
 }

 @Test
 public void testExecuteString() {
  String msg="\n# testExecuteString\n Drop and Create :\t TestTable" + m_id;
  System.out.println(msg);//DROP TABLE IF EXISTS TestTable" + m_id + "; 
  String str_Sql = "CREATE TABLE  TestTable" + m_id + " ( " + 
     "`test_id` int(10) unsigned NOT NULL auto_increment, " +
     "`member_id` int(10) unsigned NOT NULL, "+
     "`member_name` varchar(45) NOT NULL, " +
     "PRIMARY KEY  (`test_id`) "+
   ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ";
  try{
   dbio.execute(str_Sql);
   
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }
 }
 
 @Test
 public void testExecuteUpdateString() {
  String msg="\n# testExecuteUpdateString\n Insert :\tID=" + m_id + "\tName=" + m_name;
  System.out.println(msg);
  String str_Sql="Insert into TestTable" + m_id + " (member_id,member_name) values('" + m_id + "','" + m_name + "')";
  try{
   int result=dbio.executeUpdate(str_Sql);
   if(result<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }  
 }

 @Test
 public void testExecuteQueryString() {
  String msg="\n# testExecuteQueryString\n Select ALL :";
  System.out.println(msg);
  String str_Sql="Select * from TestTable" + m_id + "";
  try{
   ArrayList<Object[]> result=dbio.executeQuery(str_Sql);
   if(result.size()<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }else{
    for(int i=0;i<result.size();i++){
     for(int j=0;j<result.get(i).length;j++){
      System.out.print(result.get(i)[j] + "\t");
     }
     System.out.println();
    }
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }
 }

 @Test
 public void testexecuteQueryResultset() {
  String msg="\n# testexecuteQueryResultset\n Select ALL :";
  System.out.println(msg);
  String str_Sql="Select * from TestTable" + m_id + "";
  try{
   
   ResultSet rs=dbio.executeQueryResultset(str_Sql);
   ResultSetMetaData rsMetaData = rs.getMetaData();
   int numberOfColumns = rsMetaData.getColumnCount();
   while (rs.next()) {
    for (int i = 1; i < numberOfColumns + 1; i++) {
     System.out.print(rs.getObject(i) + "\t");
    }
    System.out.println();
   }
   
   dbio.CloseConn();
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }
 }
 
 @Test
 public void testselectPage() {
  String msg="\n# testselectPage\n Insert 98 User";
  System.out.println(msg);
  String str_Sql="";
  try{
   
   for(int i=1;i<=98;i++){
    str_Sql="Insert into TestTable" + m_id + " (member_id,member_name) values('" + m_id  + i + "','" + m_name + "_" + i + "')";
    int result=dbio.executeUpdate(str_Sql,null,false);
    if(result<=0){
     fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
    }
   }
   str_Sql="";
   ArrayList<Object[]> result=dbio.selectPage("member_id,member_name", "TestTable" + m_id, null, "member_id", 10, 10, null, true);
   if(result.size()<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }else{
    for(int i=0;i<result.size();i++){
     for(int j=0;j<result.get(i).length;j++){
      System.out.print(result.get(i)[j] + "\t");
     }
     System.out.println();
    }
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }
  
  
 }
 

 @Test
 public void testExecuteUpdateStringObjectArray() {
  m_name+="_Updateed";
  String msg="\n# testExecuteUpdateStringObjectArray\n Update :\tID=" + m_id + "\tName=" + m_name;
  System.out.println(msg);
  String str_Sql="Update TestTable" + m_id + " Set member_name=? WHERE member_id=?";
  Object[] param={m_name,m_id};
  try{
   int result=dbio.executeUpdate(str_Sql,param);
   if(result<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }  
 }

 @Test
 public void testExecuteQueryStringObjectArray() {
  String msg="\n# testExecuteQueryStringObjectArray\n Select :\tID=" + m_id;
  System.out.println(msg);
  String str_Sql="Select * from TestTable" + m_id + " where member_id=?";
  Object[] param={m_id}; 
  try{
   ArrayList<Object[]> result=dbio.executeQuery(str_Sql,param);
   if(result.size()<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }else{
    for(int i=0;i<result.size();i++){
     for(int j=0;j<result.get(i).length;j++){
      System.out.print(result.get(i)[j] + "\t");
     }
     System.out.println();
    }
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }
 }

 @Test
 public void testExecuteUpdateStringObjectArrayBoolean() {
  String msg="\n# testExecuteUpdateStringObjectArrayBoolean\n Delete :\tID=" + m_id + "\tName=" + m_name;
  System.out.println(msg);
  String str_Sql="Delete from TestTable" + m_id + " WHERE member_id=?";
  Object[] param={m_id};
  try{
   int result=dbio.executeUpdate(str_Sql,param,false);
   if(result<=0){
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }finally{
   dbio.CloseConn();
  }
 }

 @Test
 public void testExecuteQueryStringObjectArrayBoolean() {
  String msg="\n# testExecuteQueryStringObjectArrayBoolean\n Select :\tID=" + m_id;
  System.out.println(msg);
  String str_Sql="Select * from TestTable" + m_id + " where member_id=?";
  Object[] param={m_id};
  try{
   ArrayList<Object[]> result=dbio.executeQuery(str_Sql,param,false);
   if(result.size()>0){
    for(int i=0;i<result.size();i++){
     for(int j=0;j<result.get(i).length;j++){
      System.out.print(result.get(i)[j] + "\t");
     }
     System.out.println();
    }
    fail(msg + "\n\n" + str_Sql + "\n\nResult:" + result);
   }
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }finally{
   dbio.CloseConn();
  }
 }



 

 @Test
 public void testExecuteStringObjectArrayBoolean() {
  String msg="\n# testExecuteStringObjectArrayBoolean\n Drop :\t TestTable_" + m_id;
  System.out.println(msg);
  String str_Sql="DROP TABLE IF EXISTS TestTable" + m_id + ";" ;
  
  try{
   dbio.execute(str_Sql,null,false);
   
  }catch(Exception e){
   fail(msg + "\n\n" + str_Sql + "\n\nResult:" + e.getMessage());
  }finally{
   dbio.CloseConn();
  }
 }

 

}

總結

我在開發時採用ArrayList把資料給回傳回來，因此相反的就沒有辦法回傳欄位名稱(不過我想你在寫SQL時應該已經可以指定自己要用的欄位名稱了吧)。

相關文章

• [程式]VB.Net2.0資料庫通用存取函式