package cn.dao;
import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.math.BigDecimal; 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; /** * 数据库操作通用类 * @author tip */ public class DBHelp { private Connection con; private PreparedStatement ps; private ResultSet rs; static Readdoc td = new Readdoc();//读取XML文件中的数据库连接参数 static { try { Class.forName(td.getDriver()); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public Connection getCon() throws SQLException { con = DriverManager.getConnection("jdbc:oracle:thin:@" + td.getAdd() + ":1521:" + td.getDb(), td.getUname(), td.getPwd()); return con; } public <T> List<T> findBySql(String sql, List<String> params, Class<T> c) throws SQLException { List<T> list = new ArrayList<T>(); // 取出c的这个对象中有多少个方法 Method[] ms = c.getMethods(); con = this.getCon(); try { ps = con.prepareStatement(sql); this.doParams(ps, params); rs = ps.executeQuery(); // 得到db中的表的列 ResultSetMetaData rsmd = rs.getMetaData(); // 存储全部的列名 String[] columnname = new String[rsmd.getColumnCount()]; for (int i = 0; i < columnname.length; i++) { columnname[i] = rsmd.getColumnName(i + 1); } // id=> "set"+"I"+d=> "setId()" =>对应数据库表的字段 while (rs.next()) { // new对象 T t = c.newInstance(); // Topic t=new Topic(); // t.setId(rs.getInt("id")); for (int i = 0; i < columnname.length; i++) { String cn = columnname[i]; // id title contents cn = "set" + cn.substring(0, 1).toUpperCase() + cn.substring(1).toLowerCase(); // "setId" for (Method m : ms) { if (m.getName().equals(cn)) { if (rs.getObject(columnname[i]) != null) { if ("java.sql.Timestamp".equals(rs.getObject(columnname[i]).getClass().getName())) { m.invoke(t, rs.getString(columnname[i])); } else if ("java.lang.Integer".equals(rs.getObject(columnname[i]).getClass().getName())) { m.invoke(t, rs.getInt(columnname[i])); } else { m.invoke(t, rs.getObject(columnname[i])); } } else { m.invoke(t, rs.getObject(columnname[i])); } break; } } } list.add(t); } } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { closeAll(rs, ps, con); } return list; } // insert into xx values(?,?); params={"tip","whx"}; public <T> int doUpdate(String sql, List<T> params) throws SQLException { con = this.getCon(); int i = 0; try { ps = con.prepareStatement(sql); doParams(ps, params); i = ps.executeUpdate(); } catch (SQLException e) { try { con.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } finally { closeAll(null, ps, con); } return i; } // 设置预编译对象参数 public <T> void doParams(PreparedStatement pstmt, List<T> params) throws SQLException { if (pstmt != null && params != null && params.size() > 0) { for (int i = 0; i < params.size(); i++) { if (params.get(i) != null) { if (params.get(i).getClass().getName().equals("java.lang.String")) { pstmt.setString(i + 1, (String) params.get(i)); } else if (params.get(i).getClass().getName() .equals("java.math.BigDecimal")) { pstmt.setBigDecimal(i + 1, (BigDecimal) params.get(i)); } else if (params.get(i).getClass().getName().equals("java.sql.Timestamp")) { pstmt.setString(i + 1, (String) params.get(i)); } else { pstmt.setObject(i + 1, params.get(i)); } } else { pstmt.setString(i + 1, ""); } } } } public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
转载于:https://www.cnblogs.com/SZ2015/p/4719491.html
相关资源：Java数据库操作封装类