DBUtils 使用小结

        Apache DBUtils 是一款轻量级的Java ORM框架,可以有效的提高JDBC的操作效率,目前官方最新版本 1.6。

官网地址:http://commons.apache.org/proper/commons-dbutils/

        这里跟大家分享DBUtils的一些基本用法以及自己总结的一个基于DBUtils 封装的JDBC CRUD工具类,这里使用的DBUtils版本为 1.6,数据源为 c3p0,Log4j管理系统日志。


数据库表结构如下:

CREATE DATABASE test DEFAULT charset utf8;//建库

CREATE TABLE t_user(
	id int(11) NOT NULL AUTO_INCREMENT,
	name varchar(128) NOT NULL,
	nickName varchar(128) DEFAULT NULL,
	password varchar(128) NOT NULL,
	age int(3) unsigned default 0,
	height float(3,2) unsigned default 0,
	PRIMARY KEY('id'),
	UNIQUE KEY('name')
) engine=MyISAM default charset=utf8 COLLATE=utf8_bin;

整个项目目录结构如下图所示:



实体类User

package com.ricky.java.db.test.model;

public class User {
	private long id;
	private String name;
	private String nickName;
	private String password;
	private int age;
	private float height;
	
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getNickName() {
		return nickName;
	}
	public void setNickName(String nickName) {
		this.nickName = nickName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public float getHeight() {
		return height;
	}
	public void setHeight(float height) {
		this.height = height;
	}
	
}

DBUtilsDAO.java

package com.ricky.java.db.test;

import java.sql.SQLException;
import java.util.List;
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;
import com.ricky.java.db.DataSourceManager;
import com.ricky.java.db.test.model.User;

/**
 * use DBUtils CRUD Demo
 * @author Ricky Feng
 * @version 2014-09-03 23:10
 *
 */
public class DBUtilsDAO {

	QueryRunner qr = new QueryRunner(DataSourceManager.getInstance().getDataSource());
	
	public long insert(User user){
		
		try {
			String sql = "INSERT INTO t_user (name,nickName,password,age,height) VALUES (?,?,?,?,?)";
			Object[] params = new Object[]{user.getName(),user.getNickName(),user.getPassword()
					,user.getAge(),user.getHeight()};
			//Execute the SQL update statement and return the number of inserts that were made
//			int inserts = qr.update(sql, params);
//			
//			return inserts;
			
			//return AUTO_INCREMENT id
			long id = qr.insert(sql, new ScalarHandler<Long>(), params);
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public int[] batch(List<User> userList){
		try {
			String sql = "INSERT INTO t_user (name,nickName,password,age,height) VALUES (?,?,?,?,?)";
			Object[][] params = new Object[userList.size()][];	//二维数组
			for(int i=0;i<userList.size();i++){
				User user = userList.get(i);
				params[i] = new Object[]{user.getName(),user.getNickName(),user.getPassword()
						,user.getAge(),user.getHeight()};
			}
			
			return qr.batch(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public int update(User user){
		
		try {
			String sql = "UPDATE t_user SET name=?,nickName=?,password=?,age=?,height=? WHERE name=?";
			
			Object[] params = new Object[]{user.getName(),user.getNickName(),user.getPassword()
					,user.getAge(),user.getHeight(),user.getName()};
			
//			Execute the SQL update statement and return the number of inserts that were made
			return qr.update(sql, params);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public int delete(String name){
		try {
			String sql = "DELETE FROM t_user WHERE name=?";
			
			return qr.update(sql, new Object[]{name});
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public User query(String name){
		
		try {
			String sql = "SELECT * FROM t_user WHERE name=?";
			return qr.query(sql, new BeanHandler<User>(User.class), new Object[]{name});
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public List<User> queryAll(){
		
		try {
			String sql = "SELECT * FROM t_user";
			return qr.query(sql, new BeanListHandler<User>(User.class));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public long count(){
		try {
			
			String sql = "SELECT COUNT(*) as count FROM t_user";
			
			return qr.query(sql, new ScalarHandler<Long>());
			
		} catch (SQLException e) {
			e.printStackTrace();
		}  
		return 0;
	}
}


使用DBUtils操作数据库非常方便,省去了很多重复代码。

虽然DBUtils已经帮我们做了很多工作,但是个人感觉还不够,例如:每次操作都要获取数据源,每次拼SQL语句等等,这些都是通用的,可以封装起来,于是乎有了 BaseDAO,示例代码如下:

package com.ricky.java.db.dao;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import com.ricky.java.db.DataSourceManager;

/**
 * Generic CRUD Operation Interface
 * 
 * @author Ricky Feng
 * @version 2014-09-03 23:30
 * 
 * @param <T>
 */
public abstract class BaseDAO<T> {

	protected final Logger mLogger = Logger.getLogger("devLog");

	protected QueryRunner qr = new QueryRunner(DataSourceManager.getInstance()
			.getDataSource());

	//return AUTO_INCREMENT id
	public long insert(String[] columns, ScalarHandler<Long> rsh,
			Object... params) throws SQLException {
		String sql = "INSERT INTO " + getTableName() + " ("
				+ getColumnsName(columns) + ") VALUES ("
				+ getColumnsValue(columns) + ")";

		mLogger.debug("BaseDAO insert sql=" + sql + "**params="
				+ Arrays.toString(params));

		// return qr.update(sql, params);
		return qr.insert(sql, rsh, params);
	}
	
	public int insert(String[] columns,
			Object... params) throws SQLException {
		String sql = "INSERT INTO " + getTableName() + " ("
				+ getColumnsName(columns) + ") VALUES ("
				+ getColumnsValue(columns) + ")";

		mLogger.debug("BaseDAO insert sql=" + sql + "**params="
				+ Arrays.toString(params));

		 return qr.update(sql, params);
	}

	public int delete(String whereClause, Object[] whereArgs)
			throws SQLException {
		String sql = "delete from " + getTableName() + " where " + whereClause;

		mLogger.debug("BaseDAO delete sql=" + sql + "**params="
				+ Arrays.toString(whereArgs));

		return qr.update(sql, whereArgs);
	}

	public int update(String[] updateColumns, String whereClause,
			Object[] params) throws SQLException {
		String sql = "UPDATE " + getTableName() + " SET "
				+ getUpdateColumns(updateColumns) + " WHERE " + whereClause;

		mLogger.debug("BaseDAO update sql=" + sql + "**params="
				+ Arrays.toString(params));

		return qr.update(sql, params);
	}

	public T query(String[] columns, String selection, ResultSetHandler<T> rsh,
			Object[] selectionArgs) throws SQLException {
		String sql = "SELECT " + getColumnsName(columns) + " FROM "
				+ getTableName() + " WHERE " + selection;

		mLogger.debug("BaseDAO query sql=" + sql + "**params="
				+ Arrays.toString(selectionArgs));

		return qr.query(sql, rsh, selectionArgs);
	}

	public List<T> queryAll(String[] columns, String selection,
			BeanListHandler<T> rsh, Object[] selectionArgs, String orderBy)
			throws SQLException {

		String whereClause = " ";
		if (selection != null && selection.length() > 0) {
			whereClause = " WHERE " + selection;
		}

		String sql = "SELECT " + getColumnsName(columns) + " FROM "
				+ getTableName() + whereClause + " ORDER BY " + orderBy;

		mLogger.debug("BaseDAO queryAll sql=" + sql + "**params="
				+ Arrays.toString(selectionArgs));

		return qr.query(sql, rsh, selectionArgs);
	}

	public List<T> queryAll(String[] columns, String selection,
			BeanListHandler<T> rsh, Object[] selectionArgs, String orderBy,
			int offset, int size) throws SQLException {
		String whereClause = " ";
		if (selection != null && selection.length() > 0) {
			whereClause = " WHERE " + selection;
		}

		String sql = "SELECT " + getColumnsName(columns) + " FROM "
				+ getTableName() + whereClause + " ORDER BY " + orderBy
				+ " limit " + offset + "," + size;

		mLogger.debug("BaseDAO queryAll limit sql=" + sql + "**params="
				+ Arrays.toString(selectionArgs));

		return qr.query(sql, rsh, selectionArgs);
	}

	public boolean find(String selection, ResultSetHandler<T> rsh,
			Object[] selectionArgs) throws SQLException {
		String sql = "SELECT * FROM " + getTableName() + " WHERE " + selection;

		mLogger.debug("BaseDAO find sql=" + sql + "**params="
				+ Arrays.toString(selectionArgs));

		return qr.query(sql, rsh, selectionArgs) != null;
	}

	public long count(String selection, Object[] selectionArgs)
			throws SQLException {

		String whereClause = " ";
		if (selection != null && selection.length() > 0) {
			whereClause = " WHERE " + selection;
		}
		String sql = "SELECT COUNT(*) as count FROM " + getTableName()
				+ whereClause;

		mLogger.debug("BaseDAO count sql=" + sql + "**params="
				+ Arrays.toString(selectionArgs));

		return qr.query(sql, new ScalarHandler<Long>());

	}

	public abstract String getTableName();

	public String getUpdateColumns(String[] columns) {

		if (columns == null || columns.length < 1) {
			throw new NullPointerException("getColumnsValue columns is empty");
		}

		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < columns.length; i++) {
			sb.append(columns[i]).append("=?");
			if (i != columns.length - 1) {
				sb.append(",");
			}
		}
		return sb.toString();
	}

	public String getColumnsName(String[] columns) {

		if (columns == null || columns.length < 1) {
			throw new NullPointerException("getColumnsValue columns is empty");
		}

		if ("*".equals(columns[0])) {
			return "*";
		}

		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < columns.length; i++) {
			sb.append(columns[i]);
			if (i != columns.length - 1) {
				sb.append(",");
			}
		}
		return sb.toString();
	}

	public String getColumnsValue(String[] columns) {

		if (columns == null || columns.length < 1) {
			throw new NullPointerException("getColumnsValue columns is empty");
		}

		if ("*".equals(columns[0])) {
			return "*";
		}

		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < columns.length; i++) {
			sb.append("?");
			if (i != columns.length - 1) {
				sb.append(",");
			}
		}
		return sb.toString();
	}
}


有了BaseDAO之后,再写DAO就非常简单了,这里以t_user表为例,实现对User对象的CRUD操作。

UserDAO.java

package com.ricky.java.db.test.dao;

import com.ricky.java.db.dao.BaseDAO;
import com.ricky.java.db.test.model.User;

/**
 * DAO Demo
 * @author Ricky Feng
 * @version 2014-09-03 23:45
 *
 */
public class UserDAO extends BaseDAO<User>{

	@Override
	public String getTableName() {
		
		return "t_user";
	}

}

DAO业务逻辑接口 UserDAOBiz.java

package com.ricky.java.db.test.dao.biz;

import java.util.List;
import com.ricky.java.db.test.model.User;

/**
 * Define User CRUD OP
 * @author Ricky Feng
 * @version 2014-09-03 23:47
 *
 */
public interface UserDAOBiz {

	public long insert(User user);
	
	public int update(User user);
	
	public int delete(String name);
	
	public List<User> getUserList();
}

DAO业务逻辑实现类 UserDAOBizImpl.java

package com.ricky.java.db.test.dao.biz;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import com.ricky.java.db.test.dao.UserDAO;
import com.ricky.java.db.test.model.User;

/**
 * DAO Biz Demo
 * @author Ricky Feng
 * @version 2014-09-03 23:48
 *
 */
public class UserDAOBizImpl implements UserDAOBiz {

	protected final Logger mLogger = Logger.getLogger("devLog");
	
	private UserDAO userDAO = new UserDAO();
	
	private String[] columns = new String[]{"name","nickName","password","age","height"};
	
	@Override
	public long insert(User user) {
		
		try {
			Object[] params = new Object[]{user.getName(),user.getNickName(),user.getPassword()
					,user.getAge(),user.getHeight()};
			
			return userDAO.insert(columns, new ScalarHandler<Long>(), params);
			
		} catch (SQLException e) {
			e.printStackTrace();
			mLogger.error("UserDAOBizImpl insert SQLException",e);
		}
		return 0;
	}

	@Override
	public int update(User user) {
		
		try {
			Object[] params = new Object[]{user.getName(),user.getNickName(),user.getPassword()
					,user.getAge(),user.getHeight(),user.getName()};
			
			return userDAO.update(columns, "name=?", params);
			
		} catch (SQLException e) {
			e.printStackTrace();
			mLogger.error("UserDAOBizImpl update SQLException",e);
		}
		return 0;
	}

	@Override
	public int delete(String name) {
		try {
			return userDAO.delete("name=?", new Object[]{name});
		} catch (SQLException e) {
			e.printStackTrace();
			mLogger.error("UserDAOBizImpl delete SQLException",e);
		}
		return 0;
	}

	@Override
	public List<User> getUserList() {
		
		try {
			return userDAO.queryAll(columns, null, new BeanListHandler<User>(User.class), null, "id ASC");
		} catch (SQLException e) {
			e.printStackTrace();
			mLogger.error("UserDAOBizImpl getUserList SQLException",e);
		}
		return null;
	}

}


OK,到这里整个DAO相关逻辑就写完了,最后是测试代码

package com.ricky.java.db.test;

import com.ricky.java.db.test.dao.DBUtilsDAO;
import com.ricky.java.db.test.dao.biz.UserDAOBiz;
import com.ricky.java.db.test.dao.biz.UserDAOBizImpl;
import com.ricky.java.db.test.model.User;

public class DBTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		//1.普通DAO
		DBUtilsDAO dbUtilsDAO = new DBUtilsDAO();
		
		User user = new User();
		user.setName("Ricky");
		user.setPassword("ricky");
		user.setAge(25);
		user.setHeight(1.80f);
		
		dbUtilsDAO.insert(user);
		
		
		//2.使用模板DAO
		UserDAOBiz userDAOBiz = new UserDAOBizImpl();
		userDAOBiz.insert(user);
	}

}


一个简单的JDBC 工具类就完成了,欢迎大家拍砖!

不早了,该洗洗睡了,晚安,北京!



源码下载地址:http://download.csdn.net/detail/fx_sky/7861253





评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值