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;//Stringif(params[i].getClass()==String.class){ps.setString(parameterIndex, params[i].toString());}//shortelse if(params[i].getClass()==short.class){ps.setShort(parameterIndex,Short.parseShort((String) params[i]) );}//Longelse if(params[i].getClass()==long.class){ps.setLong(parameterIndex, Long.parseLong((String) params[i]));}//Integerelse if(params[i].getClass()==Integer.class){ps.setInt(parameterIndex, Integer.parseInt((String) params[i]));}//Dateelse if(params[i].getClass()==Date.class){java.util.Date date=(java.util.Date) params[i];ps.setDate(parameterIndex, new java.sql.Date(date.getTime()));}//Byteelse if(params[i].getClass()==byte.class){ps.setByte(parameterIndex, (Byte)params[i]);}//Floatelse if(params[i].getClass()==float.class){ps.setFloat(parameterIndex, Float.parseFloat((String) params[i]));}//Booleanelse if(params[i].getClass()==boolean.class){ps.setBoolean(parameterIndex, Boolean.parseBoolean((String) params[i]));}//elseelse{throw new Exception("参数准备出错：数据类型不可见"+params[i].getClass().toString());}}}catch (Exception e) {// TODO: handle exceptione.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 ArrayListExecuteReader(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 exceptionthrow 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 exceptionthrow 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 exceptionthrow 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 exceptionthrow 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 exceptionthrow 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 exceptionthrow new Exception("ExecuteScalar方法出错："+e2.getMessage());}}}}