一个简单的JDBC包装类

前一段时间项目上需要一个JDBC的包装类，上网找了找也没有找到合适的，所以就自己写了一个。

当然还有一些功能有待增加，比如不支持数据库连接池等等，以后再逐步完善了。

 

/**
 * DBHelper works as JDBC wrapper class to simplify JDBC operations.
 * @author orlando
 * @version 0.1
 */
package com.orlando.Common;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
import java.util.Map.Entry;
import java.util.logging.FileHandler;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import oracle.jdbc.OraclePreparedStatement;
 
public class DBHelper {
 private static Connection conn = null;
 private static OraclePreparedStatement pstmt = null;
 private static Statement stmt = null;
 private static String logFilename;
 
 private static Logger log = Logger.getLogger(DBHelper.class.getName());
 
 /**
  * Initiate the logger variable including specifying the log filename.
  */
 private static void logEnv() throws IOException{
  FileHandler logfile = new FileHandler(logFilename, true);
  SimpleFormatter formatTxt = new SimpleFormatter();
  logfile.setFormatter(formatTxt);
  log.addHandler(logfile);
 }

 /**
  * Query and return Database connection information configured in property file.
  * @param aURL Oracle database URL of the form
  * @param user the database user used to connect database
  * @param password the user's password
  */
 private static void readProperties (StringBuffer aURI, StringBuffer aUser, StringBuffer aPwd) throws IOException{
  InputStream inputStream = DBHelperTest.class.getResourceAsStream("/DBConnection.properties");
  Properties p = new Properties();
  p.load(inputStream);
  aURI.append(p.getProperty("DBUri").toString());
  aUser.append(p.getProperty("UserName").toString());
  aPwd.append(p.getProperty("Password").toString());
  logFilename = p.getProperty("Logfile").toString();
 }
 /**
  * Create a new Oracle connection or return the current connection
  */
 public static boolean createConnection()
 {
  StringBuffer uri = new StringBuffer();
  StringBuffer user = new StringBuffer();
  StringBuffer password = new StringBuffer();
  if (conn == null) {
   try {
    logEnv();
    readProperties(uri, user, password);
    log.info("Connecting to database");
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    conn = DriverManager.getConnection(uri.toString(),
      user.toString(),
      password.toString());
   
   } catch (IOException e) {
    e.printStackTrace();
    return false;
   } catch (SQLException e) {
    log.severe("database connection failed: " + e.getMessage());
    return false;
   }
  }
  return true;
 }
 
 /**
  * Release the connection with Oracle database.
  */
 public static void DBClose() {
  try {
   log.info("Close connection with database");
   if (pstmt != null) {
    pstmt.close();
   }
   if (stmt != null) {
    stmt.close();
   }
  } catch (SQLException e) {
   log.severe("Close statements failed: " + e.getMessage());
  } finally {
   if (conn != null) {
    try {
     conn.close();
    } catch (SQLException e) {
     log.severe("Close database connection failed: " + e.getMessage());
    }
   }
  }
 }
 /**
  * This method is optimized for handling parametric SQL statements that benefit from precompilation.
  * @param aSQL SQL statement
  * @param aParams Parameters and values used for SQL statement.
  */
 public static ArrayList> preparedStatement(String aSQL, HashMap aParams) {
  String fieldName;
  String fieldValue;
  HashMap rowEntity;
  ArrayList> resultSetList = new ArrayList>();
  Entry mapEntry;
  try {
   pstmt = (OraclePreparedStatement)conn.prepareStatement(aSQL);
   Iterator> mapIter = aParams.entrySet().iterator();
   while (mapIter.hasNext()) {
    mapEntry = (Entry) mapIter.next();
    fieldName = (String) mapEntry.getKey();
    fieldValue = (String) mapEntry.getValue();
    pstmt.setStringAtName(fieldName, fieldValue);
   }
   //retrieve the query result.
   ResultSet rslt = pstmt.executeQuery();
   while (rslt.next()) {
    rowEntity = new HashMap();
    for (int i=1; i <= rslt.getMetaData().getColumnCount();i++){
     rowEntity.put(rslt.getMetaData().getColumnLabel(i), rslt.getString(i));
    }
    resultSetList.add(rowEntity);
   }
   rslt.close();
   pstmt.close();
   return resultSetList;
  } catch (SQLException e) {
   log.severe("Execute SQL statement failed: " + e.getMessage());
   return null;
  }
 }
 
 /**
  * Execute Select SQL statement on database.
  * @param aSQL SQL statement
  */
 public static ArrayList> StatementQuery(String aSQL) {
  HashMap rowEntity;
  ArrayList> resultSetList = new ArrayList>();
  try {
   stmt = conn.createStatement();
   ResultSet rslt = stmt.executeQuery(aSQL);
   while (rslt.next()) {
    rowEntity = new HashMap();
    for (int i=1; i <= rslt.getMetaData().getColumnCount();i++){
     rowEntity.put(rslt.getMetaData().getColumnLabel(i), rslt.getString(i));
    }
    resultSetList.add(rowEntity);
   }
   rslt.close();
   stmt.close();
   return resultSetList;
  } catch (SQLException e) {
   log.severe("Execute SQL statement failed: " + e.getMessage());
   return null;
  }
 }
 /**
  * Execute Update, Delete and Insert SQL statement on database.
  * @param aSQL SQL statement
  * @return the row count for SQL Data Manipulation Language (DML) statements
  */
 public static int StatementUpdate(String aSQL) {
  try {
   stmt = conn.createStatement();
   int rowCount = stmt.executeUpdate(aSQL);
   stmt.close();
   return rowCount;
  } catch (SQLException e) {
   log.severe("Execute SQL statement failed: " + e.getMessage());
   return 0;
  }
 }
 /**
  * Make all changed updated to database in current session.
  */
 public static void commit() {
  try {
   conn.commit();
  } catch (SQLException e) {
   log.severe("Database commit failed: " + e.getMessage());
  }
 }
 
 /**
  * Undoes all changed to database in current session.
  */
 public static void rollback() {
  try {
   conn.rollback();
  } catch (SQLException e) {
   log.severe("Database rollback failed: " + e.getMessage());
  }
 }
 
}

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/51146/viewspace-629765/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/51146/viewspace-629765/