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();
}
}
3140
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
