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

1.首先是MyBeanProcessor:

重写BeanProcessor的实现,使用策略模式
[java] view plaincopy
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:

[java] view plaincopy在CODE上查看代码片派生到我的代码片
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：
[java] view plaincopy在CODE上查看代码片派生到我的代码片
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：

[java] view plaincopy在CODE上查看代码片派生到我的代码片
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：

[java] view plaincopy在CODE上查看代码片派生到我的代码片
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.创建连接池的数据源对象

[java] view plaincopy在CODE上查看代码片派生到我的代码片
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
[html] view plaincopy在CODE上查看代码片派生到我的代码片
<!–?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>