通用查询数据库辅助类，可实现任意查询语句的查询，还可以进行多结果集查询。
类的代码：
package com.hongyuan.db; import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List; import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet; import com.sun.rowset.CachedRowSetImpl; public class DBUtil {
private DataSource ds = null; public DataSource getDs() {
return ds;
} public void setDs(DataSource ds) {
this.ds = ds;
} public DBUtil() {
} public DBUtil(DataSource ds) {
this.ds = ds;
} public Connection getConnection() throws SQLException {
return ds.getConnection();
} public static void close(Connection conn, Statement s, ResultSet rs)
throws SQLException {
if (rs != null)
rs.close();
if (s != null)
s.close();
if (conn != null)
conn.close();
} public Object query(String sql) throws SQLException {
return this.query(sql, null);
} public Object query(String sql, List params) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = this.getConnection();
ps = conn.prepareStatement(sql); if (null != params) {
//初始化查询参数
this.initParam(ps, params);
}
//处理结果集 http://www.cnblogs.com/roucheng/
boolean isResultSet = ps.execute();
List result = new ArrayList();
do {
if (isResultSet) {
CachedRowSet crs = new CachedRowSetImpl();
crs.populate(ps.getResultSet());
result.add(crs);
} else {
result.add(new Integer(ps.getUpdateCount()));
}
} while ((isResultSet = ps.getMoreResults()) == true
|| ps.getUpdateCount() != -1); if (result.size() == 0) {
return null;
} else if (result.size() == 1) {
return result.get(0);
} else {
return result;
}
} catch (SQLException e) {
throw new SQLException("无法执行的sql语句!");
} finally {
DBUtil.close(conn, ps, rs);
}
}
//初始化查询参数
private void initParam(PreparedStatement ps, List params)
throws SQLException {
for (int i = 0; i < params.size(); i++) {
Object param = params.get(i);
if (param instanceof Byte) {
ps.setByte(i + 1, (Byte) param);
} else if (param instanceof Short) {
ps.setShort(i + 1, (Short) param);
} else if (param instanceof Integer) {
ps.setInt(i + 1, (Integer) param);
} else if (param instanceof Long) {
ps.setLong(i + 1, (Long) param);
} else if (param instanceof Float) {
ps.setFloat(i + 1, (Float) param);
} else if (param instanceof Double) {
ps.setDouble(i + 1, (double) param);
} else if (param instanceof BigDecimal) {
ps.setBigDecimal(i + 1, (BigDecimal) param);
} else if (param instanceof Boolean) {
ps.setBoolean(i + 1, (Boolean) param);
} else if (param instanceof String) {
ps.setString(i + 1, (String) param);
} else if (param instanceof Time) {
ps.setTime(i + 1, (Time) param);
} else if (param instanceof Date) {
ps.setDate(i + 1, (Date) param);
} else if (param instanceof Timestamp) {
ps.setTimestamp(i + 1, (Timestamp) param);
} else if (param instanceof Array) {
ps.setArray(i + 1, (Array) param);
} else if (param instanceof Blob) {
ps.setBlob(i + 1, (Blob) param);
} else if (param instanceof Clob) {
ps.setClob(i + 1, (Clob) param);
} else if (param instanceof SQLXML) {
ps.setSQLXML(i + 1, (SQLXML) param);
} else if (param instanceof URL) {
ps.setURL(i, (URL) param);
} else {
ps.setObject(i + 1, param);
}
}
}
}
上面类的使用 DBUtilTest.java：
package com.hongyuan.db; import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; import javax.sql.DataSource; import org.junit.BeforeClass;
import org.junit.Test; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class DBUtilTest { private static DataSource ds=null; @BeforeClass
public static void setUpBeforeClass() throws Exception {
MysqlDataSource mds=new MysqlDataSource();
mds.setURL("jdbc:mysql://127.0.0.1:3306/test");
mds.setUser("root");
mds.setPassword("123456");
ds=mds;
} @Test
public void testQuery() throws SQLException {
DBUtil util=new DBUtil(ds);
List params=new ArrayList();
params.add(2);
Object obj=util.query("select * from emp where id=?",params);
if(obj instanceof ResultSet){
ResultSet rs=(ResultSet)obj;
while(rs.next()){
for(int i=0;i<rs.getMetaData().getColumnCount();i++){
System.out.print(rs.getObject(i+1)+"\t");
}
System.out.println();
}
}else{
System.out.println(obj);
}
} }