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();}@Overridepublic 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 {@Overridepublic 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