SQLHelper类(Java版)

SQLHelper类(Java版)， 模仿微软提供的SQLHelper类的功能，真正实现了除SQLHelper类外，外界全部可以无须引用连接数据库的类，并且无须担心数据库的连接与关闭。


SQLHelper类支持数据库字段类型
1) 文本类型
2) 带符号整数类型
3) 双精度浮点数类型
4) 日期类型

注意：如果没有想要的类时，请手动添加到以下方法内

private static void prepareCommand(PreparedStatement pstmt, String[] parms)
	throws Exception {
		//代码段
}

package com.dal;

import javax.sql.DataSource;
import javax.naming.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.sql.*;

public abstract class SQLHelper {
	
	/**
	 * 连接数据库
	 * 
	 * @return
	 */
	private static Connection getConnect() {
		try {
			
			InitialContext context = new InitialContext();
			DataSource ds = (DataSource) context.lookup("java:/MSAccessDS");
			
			return ds.getConnection();
		} catch (Exception e) {
			return null;
		}
	}
	
	/**
	 * 用于执行语句（eg：insert语句，update语句，delete语句）
	 * 
	 * @param String
	 *            cmdtext,SQL语句
	 * @param OracleParameter[]
	 *            parms,参数集合
	 * @return int,SQL语句影响的行数
	 */
	public static int ExecuteNonQuery(String cmdtext, String[] parms)
	throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		
		try {
			conn = getConnect();
			pstmt = conn.prepareStatement(cmdtext);
			prepareCommand(pstmt, parms);
			
			return pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new Exception("executeNonQuery方法出错:" + e.getMessage());
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				throw new Exception("执行executeNonQuery方法出错:" + e.getMessage());
			}
		}
	}
	
	/**
	 * 用于获取结果集语句（eg：selete * from table）
	 * 
	 * @param cmdtext
	 * @param parms
	 * @return ResultSet
	 * @throws Exception
	 */
	public static ArrayList ExecuteReader(String cmdtext, String[] parms)
	throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		
		try {
			conn = getConnect();
			
			pstmt = conn.prepareStatement(cmdtext);
			
			prepareCommand(pstmt, parms);
			ResultSet rs = pstmt.executeQuery();
			
			ArrayList al = new ArrayList();
			ResultSetMetaData rsmd = rs.getMetaData();
			int column = rsmd.getColumnCount();
			
			while (rs.next()) {
				Object[] ob = new Object[column];
				for (int i = 1; i <= column; i++) {
					ob[i - 1] = rs.getObject(i);
				}
				al.add(ob);
			}
			
			rs.close();
			return al;
			
		} catch (Exception e) {
			throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
			}
		}
	}
	
	/**
	 * 用于获取单字段值语句（用名字指定字段）
	 * 
	 * @param cmdtext
	 *            SQL语句
	 * @param name
	 *            列名
	 * @param parms
	 *            OracleParameter[]
	 * @return Object
	 * @throws Exception
	 */
	public static Object ExecuteScalar(String cmdtext, String name,
			String[] parms) throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = getConnect();
			
			pstmt = conn.prepareStatement(cmdtext);
			prepareCommand(pstmt, parms);
			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getObject(name);
			} else {
				return null;
			}
		} catch (Exception e) {
			throw new Exception("executeSqlObject方法出错:" + e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				throw new Exception("executeSqlObject方法出错:" + e.getMessage());
			}
		}
	}
	
	/**
	 * 用于获取单字段值语句（用序号指定字段）
	 * 
	 * @param cmdtext
	 *            SQL语句
	 * @param index
	 *            列名索引
	 * @param parms
	 *            OracleParameter[]
	 * @return Object
	 * @throws Exception
	 */
	public static Object ExecuteScalar(String cmdtext, int index, String[] parms)
	throws Exception {
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = getConnect();
			
			pstmt = conn.prepareStatement(cmdtext);
			prepareCommand(pstmt, parms);
			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getObject(index);
			} else {
				return null;
			}
		} catch (Exception e) {
			throw new Exception("executeSqlObject方法出错:" + e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				throw new Exception("executeSqlObject方法出错:" + e.getMessage());
			}
		}
	}
	
	/**
	 * @param pstmt
	 * @param cmdtext
	 * @param parms
	 *            Object[]
	 * @throws Exception
	 */
	private static void prepareCommand(PreparedStatement pstmt, String[] parms)
	throws Exception {
		try {
			if (parms != null) {
				for (int i = 0; i < parms.length; i++) {
					try {
						pstmt.setDate(i + 1, java.sql.Date.valueOf(parms[i]));
					} catch (Exception e) {
						try {
							pstmt
							.setDouble(i + 1, Double
									.parseDouble(parms[i]));
						} catch (Exception e1) {
							try {
								pstmt.setInt(i + 1, Integer.parseInt(parms[i]));
							} catch (Exception e2) {
								try {
									pstmt.setString(i + 1, parms[i]);
								} catch (Exception e3) {
									System.out
									.print("SQLHelper-PrepareCommand Err1:"
											+ e3);
								}
							}
						}
					}
				}
			}
		} catch (Exception e1) {
			System.out.print("SQLHelper-PrepareCommand Err2:" + e1);
		}
	}
}