无意间看了下代码,看到数据库查询这块有用到dbutils,自己之前没有接触过,其实已经很少写什么sql,很多东西项目底层已经封装的很完善,我们就调用一个方法就全部搞定了,寻思着不能这样子了,那么也来看下dbutils的使用方法吧,手动写了个小例子。
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class DBUtilsDao {
public static List<RecordBean> getResultList() {
ConnectionManager conManager = ConnectionManager.getInstance();
Connection con = null;
QueryRunner qr = new QueryRunner();
try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
DbUtils.loadDriver("com.ibm.db2.jcc.DB2Drive");
String url = "jdbc:db2://10.200.50.79:50000/GMCC";
String userName = "db2inst1";
String passwd = "db2inst1";
con = DriverManager.getConnection(url, userName, passwd);
if (con == null) {
return null;
}
List<RecordBean> list = new ArrayList<RecordBean>();
ResultSetHandler<List<RecordBean>> handler = new BeanListHandler<RecordBean>(RecordBean.class);
String querySql = "select SERINUM, MOBILENO, RECMOBILENO, RECTIME, RECBIZNAME, RECBOSSDESC FROM WEBUSER.TF_WEB_CXLM_BIZRECORD WHERE RECBIZNAME=? WITH UR";
Object[] params = { "10元短信套餐" };
list = qr.query(con, querySql, handler, params);
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
}
return null;
}
public static void main(String args[]) {
List<RecordBean> list = getResultList();
if (list != null) {
System.out.println(list.size());
} else {
System.out.println("0");
}
}
}
bean的代码为:
package dao;
import java.sql.Timestamp;
public class RecordBean {
private String serinum;
private String mobileno;
private String recmobileno;
private Timestamp rectime;
private String recbizname;
private String recbossdesc;
// public String getSerinum() {
// return serinum;
// }
//
// public void setSerinum(String serinum) {
// this.serinum = serinum;
// }
public String getMobileno() {
return mobileno;
}
public void setMobileno(String mobileno) {
this.mobileno = mobileno;
}
public String getRecmobileno() {
return recmobileno;
}
public void setRecmobileno(String recmobileno) {
this.recmobileno = recmobileno;
}
public Timestamp getRectime() {
return rectime;
}
public void setRectime(Timestamp rectime) {
this.rectime = rectime;
}
public String getRecbizname() {
return recbizname;
}
public void setRecbizname(String recbizname) {
this.recbizname = recbizname;
}
public String getRecbossdesc() {
return recbossdesc;
}
public void setRecbossdesc(String recbossdesc) {
this.recbossdesc = recbossdesc;
}
}
我看了很多所谓的dbutils的总结都说对于dbutils“DBUtils对结果集自动封装为JavaBean是有着苛刻要求的:必须满足JavaBean的规范,其次Bean的getter与setter方法的名字与结果集的列名一一对应,而不要求JavaBean的私有成员与表结果集列名一一对应。
可是事实上,我自己写的例子中用我尝试过将getSerinum()和setSerinum()方法注释掉,运行结果仍然正常,只不过是serinum的字段值为null而已。我用的dbutils的jar包版本为1.3,对于上面那个总结中说到的我仍然无法验证是否和版本有关系又或者和我理解的有出入。