package com.java;
/**
* SqlHelper类(java版)
*
* @author Jianyu Du
* @version 1.0
* @see http://www.leadnt.org
*
*/
import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.sql.*;
import org.jdom.*;
import org.jdom.input.SAXBuilder;
public final class SqlHelper
{
private static Connection _connect = null;
private static String _driver = null;
private static String _url = null;
private static String _user = null;
private static String _pwd = null;
/**
* 获得数据库连接
*
* @return
*/
private static Connection getConnect()
{
try
{
if(_connect == null)
{
SAXBuilder builder = new SAXBuilder();
Document doc = builder.build(new File("config.cfg"));
Element cfg = doc.getRootElement();
Element dbConfig = cfg.getChild("dbConfig");
_driver = dbConfig.getChildText("Driver");
_url = dbConfig.getChildText("Url");
_user = dbConfig.getChildText("User");
_pwd = dbConfig.getChildText("Pwd");
Class.forName(_driver);
_connect = DriverManager.getConnection(_url,_user,_pwd);
}
return _connect;
}
catch (Exception e)
{
return null;
}
}
/**
* 用于执行语句（eg：insert语句，update语句，delete语句）
*
* @param String
* cmdtext,SQL语句
* @param OracleParameter[]
* params,参数集合
* @return int,SQL语句影响的行数
*/
public static int ExecuteNonQuery(String cmdtext, Object[] params) throws Exception
{
PreparedStatement pstmt = null;
Connection conn = null;
try
{
conn = getConnect();
pstmt = conn.prepareStatement(cmdtext);
PrepareCommand(pstmt, params);
return pstmt.executeUpdate();
}
catch (Exception e)
{
throw new Exception("executeNonQuery方法出错:" + e.getMessage());
}
finally
{
try
{
if (pstmt != null && (!pstmt.isClosed()))
pstmt.close();
if (conn != null && (!conn.isClosed()))
conn.close();
}
catch (Exception e)
{
throw new Exception("执行executeNonQuery方法出错:" + e.getMessage());
}
}
}
/**
* 用于获取结果集语句（eg：selete * from table）
*
* @param cmdtext
* @param params
* @return ResultSet
* @throws Exception
*/
public static ArrayList<Object[]> ExecuteReader(String cmdtext, Object[] params) throws Exception
{
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = getConnect();
pstmt = conn.prepareStatement(cmdtext);
PrepareCommand(pstmt, params);
ResultSet rs = pstmt.executeQuery();
ArrayList<Object[]> al = new ArrayList<Object[]>();
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();
pstmt.close();
conn.close();
return al;
}
catch (Exception e)
{
throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
}
finally
{
try
{
if (pstmt != null && (pstmt.isClosed()))
pstmt.close();
if (conn != null && (conn.isClosed()))
conn.close();
}
catch (Exception e)
{
throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
}
}
}
/**
* 用于获取单字段值语句（用名字指定字段）
*
* @param cmdtext
* SQL语句
* @param name
* 列名
* @param params
* OracleParameter[]
* @return Object
* @throws Exception
*/
public static Object ExecuteScalar(String cmdtext, String name,Object[] params) throws Exception
{
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
try
{
conn = getConnect();
pstmt = conn.prepareStatement(cmdtext);
PrepareCommand(pstmt, params);
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 params
* OracleParameter[]
* @return Object
* @throws Exception
*/
public static Object ExecuteScalar(String cmdtext, int index, Object[] params)
throws Exception {
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
try {
conn = getConnect();
pstmt = conn.prepareStatement(cmdtext);
PrepareCommand(pstmt, params);
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());
}
}
}
/**
* 准备SQL参数
* @param pstm
* @param params
*/
public static void PrepareCommand(PreparedStatement pstm,Object[] params)
{
if(params == null || params.length == 0)
{
return;
}
try
{
for(int i = 0;i < params.length;i++)
{
int parameterIndex = i + 1;
//String
if(params[i].getClass() == String.class)
{
pstm.setString(parameterIndex, params[i].toString());
}
//Short
else if(params[i].getClass() == short.class)
{
pstm.setShort(parameterIndex, Short.parseShort(params[i].toString()));
}
//Long
else if(params[i].getClass() == long.class)
{
pstm.setLong(parameterIndex, Long.parseLong(params[i].toString()));
}
//Integer
else if(params[i].getClass() == Integer.class)
{
pstm.setInt(parameterIndex, Integer.parseInt(params[i].toString()));
}
//Date
else if(params[i].getClass() == Date.class)
{
java.util.Date dt = (java.util.Date)params[i];
pstm.setDate(parameterIndex, new java.sql.Date(dt.getTime()));
}
//Byte
else if(params[i].getClass() == byte.class)
{
pstm.setByte(parameterIndex, (Byte)params[i]);
}
//Float
else if(params[i].getClass() == float.class)
{
pstm.setFloat(parameterIndex, Float.parseFloat(params[i].toString()));
}
//Boolean
else if(params[i].getClass() == boolean.class)
{
pstm.setBoolean(parameterIndex, Boolean.parseBoolean(params[i].toString()));
}
else
{
throw new Exception("参数准备出错:数据类型不可见" + params[i].getClass().toString());
}
}
}
catch(Exception e)
{
}
}
}
附:数据库连接配置文件Config.cfg内容:
<?xml version="1.0"?>
<cfg>
<dbConfig>
<Driver>com.mysql.jdbc.Driver</Driver>
<Url>jdbc:mysql://localhost/test</Url>
<User>root</User>
<Pwd>123</Pwd>
</dbConfig>
</cfg>