sql操作类【java】

一直想找一个比较好的java操作数据库的类，但是大多都只是简单的数据库链接，写的并不是很好，今天终于找到一个，适合做操作数据库的通用类，感觉还不错，现将其贴出来。

/**
 * 数据库配置文件config.cfg
 * <?xml version="1.0"?>
	<cfg>
		<dbConfig>
			<Driver>com.mysql.jdbc.Driver</Driver>
			<Url>jdbc:</Url>
			<User>root</User>
			<Pwd>123</Pwd>
		</dbConfig>
	</cfg>
 */


package com.sevend.utils;

import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

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){
				/**
				 * 解析配置文件config.cfg(dom4j解析XML)
				 */
				SAXReader reader=new SAXReader();
				Document document=reader.read(new File("config.cfg"));
				Element cfg=document.getRootElement();
				Element dbConfig=cfg.element("dbConfig");
				driver=dbConfig.element("Driver").getTextTrim();
				url=dbConfig.element("Url").getTextTrim();
				user=dbConfig.element("User").getTextTrim();
				pwd=dbConfig.element("Pwd").getTextTrim();
				
				Class.forName(driver);
				//获取数据库链接
				connect=DriverManager.getConnection(url,user,pwd);
			}
			return connect;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 准备SQL参数，并进行相应类型转换
	 * @param ps
	 * @param params
	 */
	public static void PrepareCommand(PreparedStatement ps,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){
					ps.setString(parameterIndex, params[i].toString());
				}
				//short
				else if(params[i].getClass()==short.class){
					ps.setShort(parameterIndex,Short.parseShort((String) params[i]) );
				}
				//Long
				else if(params[i].getClass()==long.class){
					ps.setLong(parameterIndex, Long.parseLong((String) params[i]));
				}
				//Integer
				else if(params[i].getClass()==Integer.class){
					ps.setInt(parameterIndex, Integer.parseInt((String) params[i]));
				}
				//Date
				else if(params[i].getClass()==Date.class){
					java.util.Date date=(java.util.Date) params[i];
					ps.setDate(parameterIndex, new java.sql.Date(date.getTime()));
				}
				//Byte
				else if(params[i].getClass()==byte.class){
					ps.setByte(parameterIndex, (Byte)params[i]);
				}
				//Float
				else if(params[i].getClass()==float.class){
					ps.setFloat(parameterIndex, Float.parseFloat((String) params[i]));
				}
				//Boolean
				else if(params[i].getClass()==boolean.class){
					ps.setBoolean(parameterIndex, Boolean.parseBoolean((String) params[i]));
				}
				//else
				else{
					throw new Exception("参数准备出错：数据类型不可见"+params[i].getClass().toString());
				}
			}
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	/**
	 * 执行语句（eg:insert update delete)
	 * @param sql
	 * @param params OracleParameter[]
	 * @return
	 * @throws Exception int(sql 影响的行数)
	 */
	public static int ExecuteNonQuery(String sql,Object[] params)throws Exception{
		PreparedStatement ps=null;
		Connection conn=null;
		try{
			conn=getConnect();
			ps=conn.prepareStatement(sql);
			PrepareCommand(ps,params);
			return ps.executeUpdate();
		}catch(Exception e){
			throw new Exception("executeNonQuery方法出错："+e.getMessage());
		}finally{
			try{
				if(ps!=null&&(!ps.isClosed()))
					ps.close();
				if(conn!=null&&(!conn.isClosed()))
					conn.close();
			}catch(Exception e){
				throw new Exception("executeNonQuery方法出错："+e.getMessage());
			}
		}
	}
	/**
	 * 获取结果集语句
	 * @param sql
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static ArrayList	ExecuteReader(String sql,Object[] params)throws Exception{
		PreparedStatement ps=null;
		Connection conn=null;
		try {
			conn=getConnect();
			ps=conn.prepareStatement(sql);
			PrepareCommand(ps,params);
			ResultSet rs=ps.executeQuery();
			ArrayList al=new ArrayList();
			//
			ResultSetMetaData rsmd=rs.getMetaData();
			int column=rsmd.getColumnCount();
			
			while(rs.next()){
				Object[] obj=new Object[column];
				for(int i=1;i<=column;i++){
					obj[i-1]=rs.getObject(i);
				}
				al.add(obj);
			}
			//
			rs.close();
			ps.close();
			conn.close();
			return al;
		} catch (Exception e) {
			// TODO: handle exception
			throw new Exception("ExcuteReader方法出错："+e.getMessage());
		} finally{
			try {
				if(ps!=null&&(!ps.isClosed()))
					ps.close();
				if(conn!=null&&(!conn.isClosed()))
					conn.close();
			} catch (Exception e2) {
				// TODO: handle exception
				throw new Exception("ExcuteReader方法出错："+e2.getMessage());
			}
		}
	}
	/**
	 * 获取单个字段的值的语句（用名字指定字段）
	 * @param sql
	 * @param name
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public static Object ExecuteScalar(String sql,String name,Object[] params)throws Exception{
		PreparedStatement ps=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn=getConnect();
			ps=conn.prepareStatement(sql);
			PrepareCommand(ps,params);
			
			rs=ps.executeQuery();
			if(rs.next()){
				return rs.getObject(name);
			}else{
				return null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			throw new Exception("ExecuteScalar方法出错："+e.getMessage());
		} finally{
			try {
				if(rs!=null&&(!rs.isClosed()))
					rs.close();
				if(ps!=null&&(!ps.isClosed()))
					ps.close();
				if(conn!=null&&(!conn.isClosed()))
					conn.close();
			} catch (Exception e2) {
				// TODO: handle exception
				throw new Exception("ExecuteScalar方法出错："+e2.getMessage());
			}
		}
	}
	
	public static Object executeScalar(String sql,int index,Object[] params)
		throws Exception{
		PreparedStatement ps=null;
		Connection conn=null;
		ResultSet rs=null;
		
		try {
			conn=getConnect();
			ps=conn.prepareStatement(sql);
			PrepareCommand(ps,params);
			rs=ps.executeQuery();
			if(rs.next()){
				return rs.getObject(index);
			}else{
				return null;
			}
		} catch (Exception e) {
			// TODO: handle exception
			throw new Exception("ExecuteScalar方法出错："+e.getMessage());
		} finally{
			try {
				if(rs!=null&&(!rs.isClosed()))
					rs.close();
				if(ps!=null&&(!ps.isClosed()))
					ps.close();
				if(conn!=null&&(!conn.isClosed()))
					conn.close();
			} catch (Exception e2) {
				// TODO: handle exception
				throw new Exception("ExecuteScalar方法出错："+e2.getMessage());
			}
		}
	}
}