DBUtils与C3P0结合--支持自定义字段映射和线程池

1.首先是MyBeanProcessor:

重写BeanProcessor的实现,使用策略模式

package c3p0.util2;

import java.beans.PropertyDescriptor;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;

import org.apache.commons.dbutils.BeanProcessor;
/**
 * 策略模式的BeanProcessor
 */
public class MyBeanProcessor extends BeanProcessor {
	private Matcher matcher;

	public MyBeanProcessor() {
	}

	public MyBeanProcessor(Matcher matcher) {
		this.matcher = matcher;
	}

	public Matcher getMatcher() {
		return matcher;
	}

	public void setMatcher(Matcher matcher) {
		this.matcher = matcher;
	}

	/**
	 * 重写BeanProcessor的实现,使用策略模式
	 */
	protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
			PropertyDescriptor[] props) throws SQLException {
		if (matcher == null)
			throw new IllegalStateException("Matcher must be setted!");
		int cols = rsmd.getColumnCount();
		int columnToProperty[] = new int[cols + 1];
		Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
		for (int col = 1; col <= cols; col++) {
			String columnName = rsmd.getColumnLabel(col);
			if (null == columnName || 0 == columnName.length()) {
				columnName = rsmd.getColumnName(col);
			}
			for (int i = 0; i < props.length; i++) {
				if (matcher.match(columnName, props[i].getName())) {// 与BeanProcessor不同的地方
					columnToProperty[col] = i;
					break;
				}
			}
		}

		return columnToProperty;
	}
}

MyBeanProcessor重写了BeanProcessor的mapColumnsToProperties方法,把原先写死的字段名与属性名的匹配逻辑交由Matcher来实现

2.Matcher是一个接口,它是”字段名与属性名是否匹配”的抽象.

下面是接口Matcher:

package com.recommend.utils.db;

public interface Matcher {
	/**
	 * 判断字段名与属性名是否匹配
	 * 
	 * @param columnName
	 *            字段名
	 * @param propertyName
	 *            属性名
	 * @return 匹配结果
	 */
	boolean match(String columnName, String propertyName);
}

3. match.三个个常用实现,分别是MappingMatcher(二维数组匹配)与HumpMatcher(驼峰命名匹配)以及XmlMatcher：

XmlMatcher：

package com.recommend.utils.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import com.recommend.utils.StringUtil;
import com.recommend.utils.parser.Field;
import com.recommend.utils.parser.FieldFactory;

public class XmlMatcher implements Matcher {
	private static Map<String,String> xmlMap = new HashMap<String,String>(); 
	public XmlMatcher(){
		loadXml();
	}
	@Override
	public boolean match(String columnName, String propertyName) {
		if(columnName!=null&&xmlMap.containsKey(columnName)){
			if(!StringUtil.isEmpty(propertyName)&&xmlMap.get(columnName).equals(propertyName)){
				return true;
			}
		}
		return false;
	}
	public static void loadXml(){
		Field dbFieldList = null;
		try {
//			String path = XmlMatcher.class.getProtectionDomain().getCodeSource().getLocation().getPath().split("classes")[0]+"classes/ArticleMonitorMemory.hbm.xml";
			String path="E:\\BaiduYunDownload\\workspace\\label\\src\\bean\\PositionData.hbm.xml";
			dbFieldList = FieldFactory.getFieldByXML(new FileInputStream(new File(path)));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		for (Field dbFirld : dbFieldList.getFieldList()) {
			List<Field> classFieldList = dbFirld.getFieldList();

			for (Field classField : classFieldList) {
				String name="";
				String column="";
				name = classField.getAttributebuteByName("name").getFieldContent();
				List<Field>  idFieldList = classField.getFieldList();
				for (Field idField : idFieldList) {
					if("column".equals(idField.getFieldName())){
						column=idField.getAttributebuteByName("name").getFieldContent();
					}
				}
				
				xmlMap.put(column,name);
			}
				
		}

	}
	public static void main(String[] args) {
		loadXml();
	}
}

HumpMatcher：

package com.recommend.utils.db;

/**
 * 驼峰转换的匹配器
 * 
 */
public class HumpMatcher implements Matcher {
	@Override
	public boolean match(String columnName, String propertyName) {
		if (columnName == null)
			return false;
		columnName = columnName.toLowerCase();
		String[] _ary = columnName.split("_");
		StringBuilder strBuilder = new StringBuilder();
		for (int i = 0; i < _ary.length; i++) {
			String str = _ary[i];
			if (!"".equals(str) && i > 0) {
				StringBuilder _builder = new StringBuilder();
				str = _builder.append(str.substring(0, 1).toUpperCase())
						.append(str.substring(1)).toString();
			}
			strBuilder.append(str);
		}
		return strBuilder.toString().equals(propertyName);
	}
}

MappingMatcher：

package com.recommend.utils.db;

import java.util.HashMap;
import java.util.Map;

/**
 * 二维数组映射的匹配器
 * 
 */
public class MappingMatcher implements Matcher {
	private Map<String, String> _map = null;

	public MappingMatcher(String[][] mapping) {
		if (mapping == null)
			throw new IllegalArgumentException();
		_map = new HashMap<String, String>();
		for (int i = 0; i < mapping.length; i++) {
			String columnName = mapping[i][0];
			if (columnName != null)
				_map.put(columnName.toUpperCase(), mapping[i][1]);
		}
	}

	public boolean match(String columnName, String propertyName) {
		if (columnName == null)
			return false;
		String pname = _map.get(columnName.toUpperCase());
		if (pname == null)
			return false;
		else {
			return pname.equals(propertyName);
		}
	}
}

4.创建连接池的数据源对象

package com.recommend.utils.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
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.mchange.v2.c3p0.ComboPooledDataSource;
 
public class JdbcUtilC3P0 {
    // 创建连接池的数据源对象
    // 指定的是从c3p0-config.xml配置文件中选择那个链配置进行连接
	//读取c3p0-config.xml name为mysql
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("mysql");
    
    private static QueryRunner run;
    
    public JdbcUtilC3P0(){
    	cpds = new ComboPooledDataSource();
    	run = new QueryRunner(cpds);
    }
    
    public JdbcUtilC3P0(String sqlName){
    	cpds = new ComboPooledDataSource(sqlName);
    	run = new QueryRunner(cpds);
    }
 
    // 书写返回连接对象的方法
    public static Connection getConn() {
        try {
            return cpds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
 
	/**
	 * 查询返回单个对象
	 * 
	 * @param sql
	 * @param clazz
	 * @return
	 */
	public <T> T queryForObject(String sql, Object params[], Class<T> clazz) {
		T obj = null;
		try {
			showSql(sql);
			obj = (T) run.query(sql, new BeanHandler(clazz,new BasicRowProcessor(new MyBeanProcessor(new HumpMatcher()))), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	/**
	 * 查询返回list对象
	 * 
	 * @param sql
	 * @param clazz
	 * @return
	 */
	public <T> List<T> queryForList(String sql, Object[] params, Class<T> clazz) {
		List<T> obj = null;
		try {
			showSql(sql);
			obj = (List<T>) run.query(sql, new BeanListHandler(clazz), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	/**
	 * 保存返回主键
	 * 
	 * @param sql
	 * @param param
	 * @return
	 */
	public int storeInfoAndGetGeneratedKey(String sql, Object[] params) {
		int pk = 0;
		try {
			showSql(sql);
			run.update(sql, params);
			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[] params) {
		int i = 0;
		try {
			showSql(sql);
			i = run.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;

	}
	
	/**
	 * 插入
	 * 
	 * @param sql
	 * @param clazz
	 * @param param
	 * @return
	 */
	public <T> int insert(String sql,Class<T> clazz, Object[] params){
		int i = 0;
		try {
			showSql(sql);
			i = run.insert(sql, new BeanListHandler(clazz), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	private void showSql(String sql) {
		System.out.println(sql);
	}
    // 释放资源的操作
    public static void release(ResultSet rs, PreparedStatement pstmt,
            Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
 
    }
}

重点调用：

QueryRunner run = new QueryRunner(dataSource);

ResultSetHandler<List<Person>> h = new BeanListHandler(Person.class, new BasicRowProcessor(new MyBeanProcessor(new HumpMatcher())));

List<Person> persons = run.query("SELECT * FROM Person", h);

5.XML文件：

c3p0-config.xml

<!--?xml version="1.0" encoding="UTF-8"?-->
<c3p0-config>
  <named-config name="pgsql">
    <property name="jdbcUrl">jdbc:postgresql://10.15.187.70/plproxy</property>        
    <property name="driverClass">org.postgresql.Driver</property>       
    <property name="user">postgres</property>       
    <property name="password"></property>
    <property name="initialPoolSize">10</property>      
    <property name="maxPoolSize">30</property>
    <property name="minPoolSize">10</property>
    <property name="acquireIncrement">5</property>
  </named-config>
   
  <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://10.15.172.108/userdata</property>
        <property name="user">userdata</property>
        <property name="password">43f59a7e5d</property>
         
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">30</property>
    </named-config>
    
    <named-config name="article_npro">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://dbserver_article_npro/article_npro</property>
        <property name="user">pro_admin</property>
        <property name="password">3c2d4c41</property>
         
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">30</property>
    </named-config>
</c3p0-config>

复制去Google翻译 翻译结果

XmlMatcher