java数据库查询_java数据库查询类

1 packagecom.hongyuan.db;2

3 importjava.math.BigDecimal;4 importjava.net.URL;5 importjava.sql.Array;6 importjava.sql.Blob;7 importjava.sql.Clob;8 importjava.sql.Connection;9 importjava.sql.Date;10 importjava.sql.PreparedStatement;11 importjava.sql.ResultSet;12 importjava.sql.SQLException;13 importjava.sql.SQLXML;14 importjava.sql.Statement;15 importjava.sql.Time;16 importjava.sql.Timestamp;17 importjava.util.ArrayList;18 importjava.util.List;19

20 importjavax.sql.DataSource;21 importjavax.sql.rowset.CachedRowSet;22

23 importcom.sun.rowset.CachedRowSetImpl;24

25 public classDBUtil {26 private DataSource ds = null;27

28 publicDataSource getDs() {29 returnds;30 }31

32 public voidsetDs(DataSource ds) {33 this.ds =ds;34 }35

36 publicDBUtil() {37 }38

39 publicDBUtil(DataSource ds) {40 this.ds =ds;41 }42

43 public Connection getConnection() throwsSQLException {44 returnds.getConnection();45 }46

47 public static voidclose(Connection conn, Statement s, ResultSet rs)48 throwsSQLException {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) throwsSQLException {58 return this.query(sql, null);59 }60

61 public Object query(String sql, List params) throwsSQLException {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 = newArrayList();76 do{77 if(isResultSet) {78 CachedRowSet crs = newCachedRowSetImpl();79 crs.populate(ps.getResultSet());80 result.add(crs);81 } else{82 result.add(newInteger(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 returnresult;93 }94 } catch(SQLException e) {95 throw new SQLException("无法执行的sql语句!");96 } finally{97 DBUtil.close(conn, ps, rs);98 }99 }100 //初始化查询参数

101 private voidinitParam(PreparedStatement ps, List params)102 throwsSQLException {103 for (int i = 0; i < params.size(); i++) {104 Object param =params.get(i);105 if (param instanceofByte) {106 ps.setByte(i + 1, (Byte) param);107 } else if (param instanceofShort) {108 ps.setShort(i + 1, (Short) param);109 } else if (param instanceofInteger) {110 ps.setInt(i + 1, (Integer) param);111 } else if (param instanceofLong) {112 ps.setLong(i + 1, (Long) param);113 } else if (param instanceofFloat) {114 ps.setFloat(i + 1, (Float) param);115 } else if (param instanceofDouble) {116 ps.setDouble(i + 1, (double) param);117 } else if (param instanceofBigDecimal) {118 ps.setBigDecimal(i + 1, (BigDecimal) param);119 } else if (param instanceofBoolean) {120 ps.setBoolean(i + 1, (Boolean) param);121 } else if (param instanceofString) {122 ps.setString(i + 1, (String) param);123 } else if (param instanceofTime) {124 ps.setTime(i + 1, (Time) param);125 } else if (param instanceofDate) {126 ps.setDate(i + 1, (Date) param);127 } else if (param instanceofTimestamp) {128 ps.setTimestamp(i + 1, (Timestamp) param);129 } else if (param instanceofArray) {130 ps.setArray(i + 1, (Array) param);131 } else if (param instanceofBlob) {132 ps.setBlob(i + 1, (Blob) param);133 } else if (param instanceofClob) {134 ps.setClob(i + 1, (Clob) param);135 } else if (param instanceofSQLXML) {136 ps.setSQLXML(i + 1, (SQLXML) param);137 } else if (param instanceofURL) {138 ps.setURL(i, (URL) param);139 } else{140 ps.setObject(i + 1, param);141 }142 }143 }144 }