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>