sqlhelper类 java_SQLHelper类(Java版)

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

}

}

}

分享到：

2011-07-15 15:38

浏览 1302

评论