oracle数据库帮助类

package com.yc.dd.dao;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class DbHelper {private final String forName = "oracle.jdbc.OracleDriver";private final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";private final String user = "scott";private final String password = "a";/** *  * @param sql * @param arg * @return */public int executeUpdate(String sql,List<String> params) {int row = 0;Connection con = toGetConnection();PreparedStatement pstmt = null;if (con != null) {try {pstmt = con.prepareStatement(sql);setParams(params, pstmt);row = pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {closeAll(con, pstmt, null);}}return row;}/** * @return */public Connection toGetConnection() {Connection conn=null;//try {//Context initCtx = new InitialContext();//Context envCtx = (Context) initCtx.lookup("java:comp/env");//DataSource ds = (DataSource)//envCtx.lookup("jdbc/EmployeeDB");//conn= ds.getConnection();//} catch (NamingException e) {//// TODO Auto-generated catch block//e.printStackTrace();//} catch (SQLException e) {//// TODO Auto-generated catch block//e.printStackTrace();//}try {Class.forName(forName);conn=DriverManager.getConnection(url, user, password);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return conn;}/** *  * @param sql * @param params * @return */public int toSelectCount(String sql, List<String> params) {int result = 0;Connection con = toGetConnection();PreparedStatement pstmt = null;ResultSet rs = null;try {if (con != null) {pstmt = con.prepareStatement(sql);setParams(params, pstmt);rs = pstmt.executeQuery();if (rs.next()) {result = rs.getInt(1);}}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {closeAll(con, pstmt, rs);}return result;}public <E> List<E> find(String sql, List<String> params, Class<E> c) {Connection con = toGetConnection();PreparedStatement pstmt = null;ResultSet rs = null;List<E> list = new ArrayList<E>();Method[] mth = c.getMethods();try {if (con != null) {pstmt = con.prepareStatement(sql);setParams(params, pstmt);rs = pstmt.executeQuery();ResultSetMetaData rsmd = rs.getMetaData();String[] fieldNames = new String[rsmd.getColumnCount()];for (int i = 0; i < fieldNames.length; i++) { fieldNames[i] = rsmd.getColumnName(i + 1);}while (rs.next()) {E e= c.newInstance();for (Method m : mth) {String methodName=m.getName();for (int i = 0; i < fieldNames.length; i++) {String fileName = fieldNames[i];if(methodName.equalsIgnoreCase("set"+fileName)){String type=rs.getObject(fileName).getClass().getName();if(type.equals("java.math.BigDecimal")){  int temp=rs.getInt(fileName);m.invoke(e,temp);}else{String s=rs.getString(fileName);m.invoke(e, s);}}}}list.add(e);}}} catch (SQLException e) {e.printStackTrace();} catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalArgumentException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (InvocationTargetException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{closeAll(con, pstmt, rs);}return list;}/** *  * @param params * @param pstmt * @throws SQLException */private void setParams(List<String> params, PreparedStatement pstmt)throws SQLException {if (pstmt != null) {if (params != null && params.size() > 0) {for (int i = 0; i < params.size(); i++) {pstmt.setString(i + 1, params.get(i));}}}}/** *  * @param con * @param pstmt * @param rs */private void closeAll(Connection con, PreparedStatement pstmt, ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (pstmt != null) {try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}}try {if (con != null && !con.isClosed()) {con.close();}} catch (SQLException e) {e.printStackTrace();}}}