commons dbutils 常用方法

要运行要使用了dbcp连接池

package com.crm.db.base;

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;

import com.crm.domain.User;
import com.crm.util.Constants;
// wnick123@gmail.com
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> T queryForObject(String sql,Object param[],Class<T> 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 <T> List<T> queryForOList(String sql,Object[] param,Class<T> clazz){
		List<T> obj = null;
		try {
			showSql(sql);
			obj = (List<T>)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){
		if(Constants.SHOW_SQL){
			System.out.println(sql);
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		String uri = "jdbc:mysql://localhost:3306/miccrm";   
		DataSource ds = setupDataSource(uri);
		DBBase db = new DBBase();
		db.setDataSource(ds);
		db.init();

		String sql = "select count(1) from user";	
		int i = db.count(sql,null);

		sql="select name from user";
		List<User> us = DBBase.getInstance().queryForOList(sql, null, User.class);		
		for(User u:us){
			System.out.println(u.getName());
		}

		sql = "insert into user(name) values(?)";
		int pk = DBBase.getInstance().storeInfoAndGetGeneratedKey(sql, new Object[]{"a"});
		System.out.println(pk);

		sql ="select name from user where id =?";
		String a =(String) DBBase.getInstance().getAnAttr(sql, new Object[]{1});

		System.out.println(a);

	}


    private static DataSource setupDataSource(String connectURI) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUsername("root");
        ds.setPassword("123456");
        ds.setUrl(connectURI);
        return ds;
    }

}