java数据库查询类建议收藏

 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 //处理结果集 http://www.cnblogs.com/roucheng/  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 }

 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  @BeforeClass 20 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  @Test 29 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<rs.getMetaData().getColumnCount();i++){ 38 System.out.print(rs.getObject(i+1)+"\t"); 39  } 40  System.out.println(); 41  } 42 }else{ 43  System.out.println(obj); 44  } 45  } 46 47 }