SQLHelper种(Java版)

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);}}}