commons-dbutils轻量级JDBC数据库规范
业内流行这么一句话:"三流公司卖产品;二流公司卖服务;一流公司卖标准."
标准就是规范，所以开发中，最难的不是具体功能的实现，而是制定一个开发规范，大家共同去遵守.废话不说了,直接进入正题^-^
一 介绍
commons是apache旗下一个著名的开源项目.他提供了很多方便使用的工具类.今天之所以把标题命名为，是因为相对于方便的工具来说,commons-dbutils的更大意义在于JDBC的开发规范.这样增强了代码的可读性和维护性.本文的出发点也是从规范说起,捎带着一些常用的方法.
commons-dbutils是一个轻量级的JDBC开发框架.里面只是一些非常简单的封装.下面笔者先介绍几个常用的类和接口.
1)org.apache.commons.dbutils.DbUtils.java
这个类提供了数据库初始化和关闭相关的初始操作.包括资源的开关,驱动的加载.事务回滚等常用操作。
public static void closeQuietly(Connection conn, Statement stmt,
ResultSet rs) {
try {
closeQuietly(rs);
} finally {
try {
closeQuietly(stmt);
} finally {
closeQuietly(conn);
}
}
}
这个方法，提供了对Connection,Statement/PrepareStatement,ResultSet的关闭操作.只需要一行代码，就可以安全的关闭.其中具体的细节,大家可以更深入的阅读源代码.
public static void commitAndClose(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.commit();
} finally {
conn.close();
}
}
}
这个接口是先提交，后关闭.
2)org.apache.commons.dbutils.QueryRunner.java
Object[][] params = new Object[vars.size()][5];Var var = new Var();int count = 0;String sql = "";count = vars.size();// 组装参数for (int i = 0; i < count; i++) {var = vars.get(i);params[i][0] = var.getDate_Time();params[i][1] = var.getId();params[i][2] = var.getValue_t();params[i][3] = var.getDate_Time();params[i][4] = var.getId();}// 执行批处理sql = "insert into vardata(time,name,data) select ?,?,? from dual where not exists (select * from vardata where time=? and name=?)";run.batch(sql, params);上面是一个批处理的例子,这个类提供了相当强大的查询功能,可以类似ORM一样，使用查询. CREATE DATABASE `test`; CREATE TABLE `icerecord` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value_t` float(10,5) DEFAULT '0.00000', `Date_Time` varchar(50) DEFAULT '', PRIMARY KEY (`id`) ) ; INSERT INTO `icerecord` VALUES (1,100,'2010-07-31'); INSERT INTO `icerecord` VALUES (2,200,'2010-07-30'); INSERT INTO `icerecord` VALUES (3,300,'2010-07-29'); INSERT INTO `icerecord` VALUES (4,400,'2010-07-28'); INSERT INTO `icerecord` VALUES (5,500,'2010-07-27'); INSERT INTO `icerecord` VALUES (6,600,'2010-07-26');
package test.common.db;import java.sql.SQLException;import java.util.List;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;public class DBBase {private static DBBase dbBase;private static QueryRunner run;private DataSource dataSource;public DataSource getDataSource() {return dataSource;}public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}private DBBase() {}private void init() {dbBase = this;run = new QueryRunner(dataSource);}public static DBBase getInstance() {return dbBase;}/** * eg: select count(1) from user * * @param sql * @param params * @return */public int count(String sql, Object[] params) {Object o = getAnAttr(sql, params);if (o instanceof Integer) {return (Integer) o;}if (o instanceof Long) {Long l = (Long) o;return l.intValue();}String s = (String) o;try {return Integer.parseInt(s);} catch (NumberFormatException e) {return 0;}}/** * 获得第一个查询第一行第一列 * * @param sql * @param params * @return */public Object getAnAttr(String sql, Object[] params) {showSql(sql);Object s = null;try {s = run.query(sql, new ScalarHandler(1), params);} catch (SQLException e) {e.printStackTrace();}return s;}/** * 查询返回单个对象 * * @param sql * @param clazz * @return */public T queryForObject(String sql, Object param[], Class clazz) {T obj = null;try {showSql(sql);obj = (T) run.query(sql, new BeanHandler(clazz), param);} catch (SQLException e) {e.printStackTrace();}return obj;}/** * 查询返回list对象 * * @param sql * @param clazz * @return */public List queryForOList(String sql, Object[] param, Class clazz) {List obj = null;try {showSql(sql);obj = (List) run.query(sql, new BeanListHandler(clazz), param);} catch (SQLException e) {e.printStackTrace();}return obj;}/** * 保存返回主键 * * @param sql * @param param * @return */public int storeInfoAndGetGeneratedKey(String sql, Object[] param) {int pk = 0;try {showSql(sql);run.update(sql, param);pk = ((Long) run.query("SELECT LAST_INSERT_ID()", new ScalarHandler(1))).intValue();} catch (SQLException e) {e.printStackTrace();}return pk;}/** * 更新 * * @param sql * @return */public int update(String sql, Object[] param) {int i = 0;try {showSql(sql);i = run.update(sql, param);} catch (SQLException e) {e.printStackTrace();}return i;}private void showSql(String sql) {System.out.println(sql);}public static void main(String[] args) {DataSource ds = setupDataSource();DBBase db = new DBBase();db.setDataSource(ds);db.init();// 聚合函数查询结果String sql = "select count(*) from IceRecord";int result1 = db.count(sql, null);System.out.println("聚合函数查询结果:" + result1);// 通过List封装sql = "select * from IceRecord";List vars = DBBase.getInstance().queryForOList(sql, null, Var.class);for (Var var : vars) {System.out.println("通过List封装:" + var.getId() + " " + var.getValue_t() + " " + var.getDate_Time());}// 插入数据sql = "insert into IceRecord(value_t) values(?)";int pk = DBBase.getInstance().storeInfoAndGetGeneratedKey(sql, new Object[] { 1 });System.out.println("/插入数据:" + pk);// 按条件查询数据sql = "select Date_Time from IceRecord where id =?";String result2 = (String) DBBase.getInstance().getAnAttr(sql, new Object[] { 1 });System.out.println("按条件查询数据:" + result2);}// 初始化数据源private static DataSource setupDataSource() {BasicDataSource ds = new BasicDataSource();ds.setDriverClassName("com.mysql.jdbc.Driver");ds.setUsername("root");ds.setPassword("147258369");ds.setUrl("jdbc:mysql://127.0.0.1:3306/test");return ds;}/*运行结果:select count(*) from IceRecord聚合函数查询结果:7select * from IceRecord通过List封装:1 100.0 2010-07-31通过List封装:2 200.0 2010-07-30通过List封装:3 300.0 2010-07-29通过List封装:4 400.0 2010-07-28通过List封装:5 500.0 2010-07-27通过List封装:6 600.0 2010-07-26通过List封装:7 1.0 insert into IceRecord(value_t) values(?)/插入数据:8select Date_Time from IceRecord where id =?按条件查询数据:2010-07-31*/}package test.common.db;public class Var {private int id;private float value_t;private String Date_Time;public int getId() {return id;}public void setId(int id) {this.id = id;}public float getValue_t() {return value_t;}public void setValue_t(float valueT) {value_t = valueT;}public String getDate_Time() {return Date_Time;}public void setDate_Time(String dateTime) {Date_Time = dateTime;}}