通用查询数据库辅助类，可实现任意查询语句的查询，还可以进行多结果集查询。
类的代码：
1 package com.hongyuan.db; 2 3 import java.math.BigDecimal; 4 import java.net.URL; 5 import java.sql.Array; 6 import java.sql.Blob; 7 import java.sql.Clob; 8 import java.sql.Connection; 9 import java.sql.Date; 10 import java.sql.PreparedStatement; 11 import java.sql.ResultSet; 12 import java.sql.SQLException; 13 import java.sql.SQLXML; 14 import java.sql.Statement; 15 import java.sql.Time; 16 import java.sql.Timestamp; 17 import java.util.ArrayList; 18 import java.util.List; 19 20 import javax.sql.DataSource; 21 import javax.sql.rowset.CachedRowSet; 22 23 import com.sun.rowset.CachedRowSetImpl; 24 25 public class DBUtil { 26 private DataSource ds = null; 27 28 public DataSource getDs() { 29 return ds; 30 } 31 32 public void setDs(DataSource ds) { 33 this.ds = ds; 34 } 35 36 public DBUtil() { 37 } 38 39 public DBUtil(DataSource ds) { 40 this.ds = ds; 41 } 42 43 public Connection getConnection() throws SQLException { 44 return ds.getConnection(); 45 } 46 47 public static void close(Connection conn, Statement s, ResultSet rs) 48 throws SQLException { 49 if (rs != null) 50 rs.close(); 51 if (s != null) 52 s.close(); 53 if (conn != null) 54 conn.close(); 55 } 56 57 public Object query(String sql) throws SQLException { 58 return this.query(sql, null); 59 } 60 61 public Object query(String sql, List params) throws SQLException { 62 Connection conn = null; 63 PreparedStatement ps = null; 64 ResultSet rs = null; 65 try { 66 conn = this.getConnection(); 67 ps = conn.prepareStatement(sql); 68 69 if (null != params) { 70 //初始化查询参数 71 this.initParam(ps, params); 72 } 73 //处理结果集 74 boolean isResultSet = ps.execute(); 75 List result = new ArrayList(); 76 do { 77 if (isResultSet) { 78 CachedRowSet crs = new CachedRowSetImpl(); 79 crs.populate(ps.getResultSet()); 80 result.add(crs); 81 } else { 82 result.add(new Integer(ps.getUpdateCount())); 83 } 84 } while ((isResultSet = ps.getMoreResults()) == true 85 || ps.getUpdateCount() != -1); 86 87 if (result.size() == 0) { 88 return null; 89 } else if (result.size() == 1) { 90 return result.get(0); 91 } else { 92 return result; 93 } 94 } catch (SQLException e) { 95 throw new SQLException("无法执行的sql语句!"); 96 } finally { 97 DBUtil.close(conn, ps, rs); 98 } 99 }100 //初始化查询参数101 private void initParam(PreparedStatement ps, List params)102 throws SQLException {103 for (int i = 0; i < params.size(); i++) {104 Object param = params.get(i);105 if (param instanceof Byte) {106 ps.setByte(i + 1, (Byte) param);107 } else if (param instanceof Short) {108 ps.setShort(i + 1, (Short) param);109 } else if (param instanceof Integer) {110 ps.setInt(i + 1, (Integer) param);111 } else if (param instanceof Long) {112 ps.setLong(i + 1, (Long) param);113 } else if (param instanceof Float) {114 ps.setFloat(i + 1, (Float) param);115 } else if (param instanceof Double) {116 ps.setDouble(i + 1, (double) param);117 } else if (param instanceof BigDecimal) {118 ps.setBigDecimal(i + 1, (BigDecimal) param);119 } else if (param instanceof Boolean) {120 ps.setBoolean(i + 1, (Boolean) param);121 } else if (param instanceof String) {122 ps.setString(i + 1, (String) param);123 } else if (param instanceof Time) {124 ps.setTime(i + 1, (Time) param);125 } else if (param instanceof Date) {126 ps.setDate(i + 1, (Date) param);127 } else if (param instanceof Timestamp) {128 ps.setTimestamp(i + 1, (Timestamp) param);129 } else if (param instanceof Array) {130 ps.setArray(i + 1, (Array) param);131 } else if (param instanceof Blob) {132 ps.setBlob(i + 1, (Blob) param);133 } else if (param instanceof Clob) {134 ps.setClob(i + 1, (Clob) param);135 } else if (param instanceof SQLXML) {136 ps.setSQLXML(i + 1, (SQLXML) param);137 } else if (param instanceof URL) {138 ps.setURL(i, (URL) param);139 } else {140 ps.setObject(i + 1, param);141 }142 }143 }144 } 上面类的使用 DBUtilTest.java：
1 package com.hongyuan.db; 2 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.util.ArrayList; 6 import java.util.List; 7 8 import javax.sql.DataSource; 9 10 import org.junit.BeforeClass;11 import org.junit.Test;12 13 import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;14 15 public class DBUtilTest {16 17 private static DataSource ds=null;18 19 @BeforeClass20 public static void setUpBeforeClass() throws Exception {21 MysqlDataSource mds=new MysqlDataSource();22 mds.setURL("jdbc:mysql://127.0.0.1:3306/test");23 mds.setUser("root");24 mds.setPassword("123456");25 ds=mds;26 }27 28 @Test29 public void testQuery() throws SQLException {30 DBUtil util=new DBUtil(ds);31 List params=new ArrayList();32 params.add(2);33 Object obj=util.query("select * from emp where id=?",params);34 if(obj instanceof ResultSet){35 ResultSet rs=(ResultSet)obj;36 while(rs.next()){37 for(int i=0;i