使用commons-dbutils进行数据库操作的封装

  这里直接将上面的《关于JDBC简单封装》的代码进行改动，使用commons-dbutils进行数据库的操作封装。

根据JDBC特性写的自动部分sql生成方法类

package com.liyu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class GetSql {
	
	/**
	 * @Title: getChaSql
	 * @return String  根据表生成查询语句
	 * @author  SandRomace
	 *  @param tableName
	 */
	public String getChaSql(String tableName) {
		String sql="select * from"+tableName;
		return sql;
	}
	
	/**
	 * @Title: getDeleteSql
	 * @return String  根据表生成删除语句
	 * @author  SandRomace
	 *  @param tableName
	 */
	public String getDeleteSql(String tableName){
		String sql="DELETE FROM "+tableName;
		return sql;
	}
	
	/**
	 * @Title: getInsertSql
	 * @return String   根据表生成添加语句
	 * @author  SandRomace
	 *  @param tableName
	 */
	public String getInsertSql(String tableName){
		String insertSql=" insert into  "+tableName;
		LiYuQueary liYuQueary = new LiYuQueary();
		Connection con = null;
		Statement cstat = null;
		ResultSet rslt = null;
		try {
			 con = liYuQueary.getCon("config/Orcal.properties");
			 cstat = con.createStatement();
			 rslt = cstat.executeQuery("SELECT * FROM "+tableName);
			ResultSetMetaData metaData = rslt.getMetaData();
			int columnCount = metaData.getColumnCount();
			String kl="";
			String kl2="";
				for(int i=1;i<=columnCount;i++){
					String columnName = metaData.getColumnName(i)+',';
					String column2=metaData.getColumnName(i)+"},{";
					if(columnCount==i){
						columnName = metaData.getColumnName(i);
					    column2=metaData.getColumnName(i);
					}
					kl=kl+columnName;
					kl2=kl2+column2;
				}
				insertSql=insertSql+" ("+kl+")"+" values"+" {"+kl2+"}";
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rslt.close();
				cstat.close();
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return insertSql;
	}
	
	
	/**
	 * @Title: getUpdateSql
	 * @return String 根据表生成更新语句
	 * @author  SandRomace
	 *  @param tableName
	 */
	public String getUpdateSql(String tableName){
		LiYuQueary liYuQueary = new LiYuQueary();
		String updateSql=" update  "+tableName+" set ";
		Connection con=null;
		ResultSet rslt = null;
		Statement cstat = null;
		try {
			 con = liYuQueary.getCon("config/Orcal.properties");
			 cstat = con.createStatement();
			 rslt = cstat.executeQuery("SELECT * FROM he_user");
			ResultSetMetaData metaData = rslt.getMetaData();
			int columnCount = metaData.getColumnCount();
			String kl="";
				for(int i=1;i<=columnCount;i++){
					String columnName = metaData.getColumnName(i);
					columnName=columnName+"={"+columnName+"} , ";
					if(columnCount==i){
						 columnName = columnName.replaceAll(" ,", "");
						}
					kl=kl+columnName;
				}
				updateSql=updateSql+kl;
		} catch (Exception e) {
			System.out.println(e);
		}finally {
			try {
				rslt.close();
				cstat.close();
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return updateSql;
	}

}

这里生成的sql都是单表的没有where条件查询的语句，需要条件查询的需要字符串拼接sql语句 生成sql语句的格式为

insert into wmp_person  (person_id,person_channel,person_creator,person_created）values ({id} ,{channel} ,{creator} ,{created} ,{name} ,{sort}）；参数则都封装在Map集合中。

将上篇的query工具类重写

package com.liyu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

public class LiYuQueryRunner {
	private static LiYuQueryRunner instance = null;
	private String dbType = null;
	
	public static LiYuQueryRunner newIntance(){
	    createInstance();
	    return instance;
	}
	
	private LiYuQueryRunner(){
		dbType="mysql";
	}
	private static synchronized void createInstance(){
	    if(instance==null) instance = new LiYuQueryRunner();
	}
	
    public String getDbType(){
    	return this.dbType;
    }

	public Connection getConnection() throws Exception{
    	Connection conn=null;
		if(conn == null){
			LiYuJDBC liYuJDBC = new LiYuJDBC();
			 conn = liYuJDBC.getCon("config/Orcal.properties");
		}
    	return conn;    	
    }

    public void closeConnection(Connection conn){    	
    	DbUtils.closeQuietly(conn);
    }
    
    
    private Map getSqlAndParas(String sql,Map sqlParas){
    	List lst=new ArrayList();
    	//针对sql中所有的{}创建正则规则
    	Pattern pattern=Pattern.compile("\\{\\w+\\}");
    	Matcher matcher=pattern.matcher(sql);
    	//将map中的数据取出按照sql中的{}内的字符顺序放入list集合中
    	while(matcher.find()){
    		String key=matcher.group();
    		key=key.substring(1,key.length()-1);
    		if(sqlParas!=null)lst.add(sqlParas.get(key));
    	}
    	//替换sql中特殊字符
    	sql=matcher.replaceAll("?");
    	
    	Map m=new HashMap();
    	m.put("sql", sql);
    	m.put("paras", lst.toArray());
    	return m;
    }
    
    private List queryByList(String sql,Map sqlParas) throws Exception{
    	List rslt = null;
        Map m=this.getSqlAndParas(sql,sqlParas);
        String ssql=String.valueOf(m.get("sql"));
        Object[] paras=(Object[])m.get("paras");

        Connection conn=this.getConnection();
        try{
	        QueryRunner qr = new QueryRunner(true);	        
	        rslt=(List)qr.query(conn,ssql,new MapListHandler(),paras);
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
        	this.closeConnection(conn);
        }
    	return rslt;
    }
    
  
    private List queryMSSql(String sql,Map sqlParas) throws Exception{
    	List rslt=null;
    	Map m=this.getSqlAndParas(sql,sqlParas);
        String ssql=String.valueOf(m.get("sql"));
        Object[] paras=(Object[])m.get("paras");
        
        PreparedStatement stmt = null;
        ResultSet rs = null;

        Connection conn=this.getConnection();
        try{
            stmt = conn.prepareStatement(ssql,rs.TYPE_SCROLL_INSENSITIVE,rs.CONCUR_READ_ONLY);
            QueryRunner qr = new QueryRunner(true);
            qr.fillStatement(stmt, paras);
            rs = stmt.executeQuery();
            ResultSetHandler rsh=new MapListHandler();
            rslt=(List) rsh.handle(rs);
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
			try {
				DbUtils.close(rs);
				DbUtils.close(stmt);
			} catch (SQLException e) {
				e.printStackTrace();
			}
        	this.closeConnection(conn);
        }
    	return rslt;
    }
    
    //传入sql和map参数进行查询获得list封装的集合
    public List query(String sql,Map sqlParas) throws Exception{
    	List rslt = null;
        if(sqlParas==null)sqlParas=new HashMap();
        if("oracle".equals(dbType) || "mysql".equals(dbType) || "db2".equals(dbType)) {           
            rslt=this.queryByList(sql,sqlParas);
        }else{
        	rslt=queryMSSql(sql,sqlParas);
        }
        return rslt;
    }
    
    
    private int update(String sql,Object[] paras) throws Exception{
        QueryRunner qu = new QueryRunner(true);   
        int i = 0;   
        Connection conn=this.getConnection();
        try {   
            i = qu.update(conn,sql, paras);     
        }catch(SQLException e){
            e.printStackTrace();
            i=-1;
        }finally{
        	this.closeConnection(conn);
        }     
        return i; 
    }
    
    /**
     * @Title: update
     * @return int 曾删改方法
     *  @param sql
     *  @param sqlParas
     *  @throws Exception
     */
    public int update(String sql,Map sqlParas) throws Exception{ 
        if(sqlParas==null)sqlParas=new HashMap();
        //处理sql和map中的数据
    	Map m=getSqlAndParas(sql,sqlParas);
    	return update(String.valueOf(m.get("sql")),(Object[])m.get("paras"));
    }

}

基本上改改就可以快速使用。适合第二数据源操作使用方便依赖commons-dbutils-1.7.jar。