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 }