Java 数据库的Util

从浏览器接收到参数以后，传统的方法要判断传上来的参数有哪些，然后根据这些参数写service和dao方法，这会导致dao方法又多，方法重用性又不高。用这个工具可以减少dao方法，简化dao操作。

package com.hexiang.utils; import java.sql.*; import java.util.*; /** * * Title: 数据库工具类 * * * Description: 将大部分的数据库操作放入这个类中, 包括数据库连接的建立, 自动释放等. * * * @author beansoft 日期: 2004年04月 * @version 2.0 */ public class DatabaseUtil { /** 数据库连接 */ private java.sql.Connection connection; /** * All database resources created by this class, should be free after all * operations, holds: ResultSet, Statement, PreparedStatement, etc. */ private ArrayList resourcesList = new ArrayList(5); public DatabaseUtil() { } /** 关闭数据库连接并释放所有数据库资源 */ public void close() { closeAllResources(); close(getConnection()); } /** * Close given connection. * * @param connection * Connection */ public static void close(Connection connection) { try { connection.close(); } catch (Exception ex) { System.err.println("Exception when close a connection: " + ex.getMessage()); } } /** * Close all resources created by this class. */ public void closeAllResources() { for (int i = 0; i < this.getResourcesList().size(); i++) { closeJDBCResource(getResourcesList().get(i)); } } /** * Close a jdbc resource, such as ResultSet, Statement, Connection.... All * these objects must have a method signature is void close(). * * @param resource - * jdbc resouce to close */ public void closeJDBCResource(Object resource) { try { Class clazz = resource.getClass(); java.lang.reflect.Method method = clazz.getMethod("close", null); method.invoke(resource, null); } catch (Exception e) { // e.printStackTrace(); } } /** * 执行 SELECT 等 SQL 语句并返回结果集. * * @param sql * 需要发送到数据库 SQL 语句 * @return a ResultSet object that contains the data produced * by the given query; never null */ public ResultSet executeQuery(String sql) { try { Statement statement = getStatement(); ResultSet rs = statement.executeQuery(sql); this.getResourcesList().add(rs); this.getResourcesList().add(statement);// BUG fix at 2006-04-29 by BeanSoft, added this to res list // MySql 数据库要求必需关闭 statement 对象, 否则释放不掉资源 // - 此观点错误, 因为关闭此对象后有时数据无法读出 //statement.close(); return rs; } catch (Exception ex) { System.out.println("Error in executeQuery(\"" + sql + "\"):" + ex); // ex.printStackTrace(); return null; } } /** * Executes the given SQL statement, which may be an INSERT, * UPDATE, or DELETE statement or an SQL * statement that returns nothing, such as an SQL DDL statement. 执行给定的 SQL * 语句, 这些语句可能是 INSERT, UPDATE 或者 DELETE 语句, 或者是一个不返回任何东西的 SQL 语句, 例如一个 SQL * DDL 语句. * * @param sql * an SQL INSERT,UPDATE or * DELETE statement or an SQL statement that * returns nothing * @return either the row count for INSERT, * UPDATE or DELETE statements, or * 0 for SQL statements that return nothing */ public int executeUpdate(String sql) { try { Statement statement = getStatement(); return statement.executeUpdate(sql); // MySql 数据库要求必需关闭 statement 对象, 否则释放不掉资源 // - 此观点错误, 因为关闭此对象后有时数据无法读出 //statement.close(); } catch (Exception ex) { System.out.println("Error in executeUpdate(): " + sql + " " + ex); //System.out.println("executeUpdate:" + sql); ex.printStackTrace(); } return -1; } /** * 返回记录总数, 使用方法: getAllCount("SELECT count(ID) from tableName") 2004-06-09 * 可滚动的 Statement 不能执行 SELECT MAX(ID) 之类的查询语句(SQLServer 2000) * * @param sql * 需要执行的 SQL * @return 记录总数 */ public int getAllCount(String sql) { try { Statement statement = getConnection().createStatement(); this.getResourcesList().add(statement); ResultSet rs = statement.executeQuery(sql); rs.next(); int cnt = rs.getInt(1); rs.close(); try { statement.close(); this.getResourcesList().remove(statement); } catch (Exception ex) { ex.printStackTrace(); } return cnt; } catch (Exception ex) { System.out.println("Exception in DatabaseUtil.getAllCount(" + sql + "):" + ex); ex.printStackTrace(); return 0; } } /** * 返回当前数据库连接. */ public java.sql.Connection getConnection() { return connection; } /** * 连接新的数据库对象到这个工具类, 首先尝试关闭老连接. */ public void setConnection(java.sql.Connection connection) { if (this.connection != null) { try { getConnection().close(); } catch (Exception ex) { } } this.connection = connection; } /** * Create a common statement from the database connection and return it. * * @return Statement */ public Statement getStatement() { // 首先尝试获取可滚动的 Statement, 然后才是普通 Statement Statement updatableStmt = getUpdatableStatement(); if (updatableStmt != null) return updatableStmt; try { Statement statement = getConnection().createStatement(); this.getResourcesList().add(statement); return statement; } catch (Exception ex) { System.out.println("Error in getStatement(): " + ex); } return null; } /** * Create a updatable and scrollable statement from the database connection * and return it. * * @return Statement */ public Statement getUpdatableStatement() { try { Statement statement = getConnection() .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); this.getResourcesList().add(statement); return statement; } catch (Exception ex) { System.out.println("Error in getUpdatableStatement(): " + ex); } return null; } /** * Create a prepared statement and return it. * * @param sql * String SQL to prepare * @throws SQLException * any database exception * @return PreparedStatement the prepared statement */ public PreparedStatement getPreparedStatement(String sql) throws SQLException { try { PreparedStatement preparedStatement = getConnection() .prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); this.getResourcesList().add(preparedStatement); return preparedStatement; } catch (Exception ex) { ex.printStackTrace(); } return null; } /** * Return the resources list of this class. * * @return ArrayList the resources list */ public ArrayList getResourcesList() { return resourcesList; } /** * Fetch a string from the result set, and avoid return a null string. * * @param rs * the ResultSet * @param columnName * the column name * @return the fetched string */ public static String getString(ResultSet rs, String columnName) { try { String result = rs.getString(columnName); if (result == null) { result = ""; } return result; } catch (Exception ex) { } return ""; } /** * Get all the column labels * * @param resultSet * ResultSet * @return String[] */ public static String[] getColumns(ResultSet resultSet) { if (resultSet == null) { return null; } try { ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); if (numberOfColumns <= 0) { return null; } String[] columns = new String[numberOfColumns]; //System.err.println("numberOfColumns=" + numberOfColumns); // Get the column names for (int column = 0; column < numberOfColumns; column++) { // System.out.print(metaData.getColumnLabel(column + 1) + "\t"); columns[column] = metaData.getColumnName(column + 1); } return columns; } catch (Exception ex) { ex.printStackTrace(); } return null; } /** * Get the row count of the result set. * * @param resultset * ResultSet * @throws SQLException * if a database access error occurs or the result set type is * TYPE_FORWARD_ONLY * @return int the row count * @since 1.2 */ public static int getRowCount(ResultSet resultset) throws SQLException { int row = 0; try { int currentRow = resultset.getRow(); // Remember old row position resultset.last(); row = resultset.getRow(); if (currentRow > 0) { resultset.absolute(row); } } catch (Exception ex) { ex.printStackTrace(); } return row; } /** * Get the column count of the result set. * * @param resultSet * ResultSet * @return int the column count */ public static int getColumnCount(ResultSet resultSet) { if (resultSet == null) { return 0; } try { ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); return numberOfColumns; } catch (Exception ex) { ex.printStackTrace(); } return 0; } /** * Read one row's data from result set automatically and put the result it a * hashtable. Stored as "columnName" = "value", where value is converted to * String. * * @param resultSet * ResultSet * @return Hashtable */ public static final Hashtable readResultToHashtable(ResultSet resultSet) { if (resultSet == null) { return null; } Hashtable resultHash = new Hashtable(); try { String[] columns = getColumns(resultSet); if (columns != null) { // Read data column by column for (int i = 0; i < columns.length; i++) { resultHash.put(columns[i], getString(resultSet, columns[i])); } } } catch (Exception ex) { ex.printStackTrace(); } return resultHash; } /** * Read data from result set automatically and put the result it a * hashtable. Stored as "columnName" = "value", where value is converted to * String. * * Note: assume the default database string encoding is ISO8859-1. * * @param resultSet * ResultSet * @return Hashtable */ @SuppressWarnings("unchecked") public static final Hashtable readResultToHashtableISO(ResultSet resultSet) { if (resultSet == null) { return null; } Hashtable resultHash = new Hashtable(); try { String[] columns = getColumns(resultSet); if (columns != null) { // Read data column by column for (int i = 0; i < columns.length; i++) { String isoString = getString(resultSet, columns[i]); try { resultHash.put(columns[i], new String(isoString .getBytes("ISO8859-1"), "GBK")); } catch (Exception ex) { resultHash.put(columns[i], isoString); } } } } catch (Exception ex) { ex.printStackTrace(); } return resultHash; } /** Test this class. */ public static void main(String[] args) throws Exception { DatabaseUtil util = new DatabaseUtil(); // TODO: 从连接池工厂获取连接 // util.setConnection(ConnectionFactory.getConnection()); ResultSet rs = util.executeQuery("SELECT * FROM e_hyx_trans_info"); while (rs.next()) { Hashtable hash = readResultToHashtableISO(rs); Enumeration keys = hash.keys(); while (keys.hasMoreElements()) { Object key = keys.nextElement(); System.out.println(key + "=" + hash.get(key)); } } rs.close(); util.close(); } }