package org.tips.dao;import java.io.IOException;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.math.BigDecimal;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;import javax.xml.parsers.ParserConfigurationException;import org.apache.commons.dbcp.BasicDataSource;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import org.tips.dao.config.DomParseService;import org.xml.sax.SAXException;/*** * 数据库增删查改通用类 已注入dataSource *  * @author tips *  */public class DBActionByMap {	protected static Log logger = LogFactory.getLog(DBActionByMap.class);	private BasicDataSource dataSource;	private Connection conn;	public DBActionByMap() {	}	public BasicDataSource getDataSource() {		return dataSource;	}	public void setDataSource(BasicDataSource dataSource) {		this.dataSource = dataSource;	}	/**	 * 初始化，设置数据库是否自动提交	 * 	 * @param autoCommit	 *            true-自动提交	 * @return	 * @throws SQLException	 */	public void initialization(boolean autoCommit) throws SQLException {		conn = this.dataSource.getConnection();		conn.setAutoCommit(autoCommit);	}	/***	 * 通过SQL查询数据，返回List	 * 	 * @param sql	 * @param params	 * @param c	 * @return	 * @throws SQLException	 * @throws IllegalAccessException	 * @throws InstantiationException	 * @throws InvocationTargetException	 * @throws IllegalArgumentException	 * @throws Exception	 */	public 
    
      List
     
       findBySql(String sqlMap, HashMap
      
        params, Class
       
         c)			throws SQLException, InstantiationException,			IllegalAccessException, IllegalArgumentException,			InvocationTargetException {		if (conn == null || conn.isClosed()) {			this.initialization(true);		}		String sql = this.getSQL(sqlMap);				List
        
          list = new ArrayList
         
          (); /** * 取出c的这个对象中有多少个方法 */ Method[] ms = c.getMethods(); PreparedStatement ps = conn.prepareStatement(sql.replaceAll("#+[A-Za-z0-9_]+#", "?")); this.doParams(ps, params, this.parseSQL(sql)); ResultSet rs = ps.executeQuery(); /** * 得到db中的表的列 */ ResultSetMetaData rsmd = rs.getMetaData(); /** * 存储全部的列名 */ String[] columnname = new String[rsmd.getColumnCount()]; for (int i = 0; i < columnname.length; i++) { columnname[i] = rsmd.getColumnName(i + 1); } while (rs.next()) { T t = c.newInstance(); // t.setId(rs.getInt("id")); for (int i = 0; i < columnname.length; i++) { String cn = columnname[i]; cn = "set" + cn.substring(0, 1).toUpperCase() + cn.substring(1).toLowerCase(); for (Method m : ms) { if (m.getName().equals(cn)) { if (rs.getObject(columnname[i]) != null) { if ("java.sql.Timestamp".equals(rs .getObject(columnname[i]).getClass() .getName())) { m.invoke(t, rs.getString(columnname[i])); } else if ("java.math.BigDecimal".equals(rs .getObject(columnname[i]).getClass() .getName())) { m.invoke(t, rs.getBigDecimal(columnname[i])); } else if ("java.sql.Date".equals(rs .getObject(columnname[i]).getClass() .getName())) { m.invoke( t, new java.util.Date(rs.getDate( columnname[i]).getTime())); } else { m.invoke(t, rs.getObject(columnname[i])); } } else { m.invoke(t, rs.getObject(columnname[i])); } break; } } } list.add(t); } return list; } /*** * 通过SQL更新数据 * * @param sql * @param params * @return * @throws SQLException */ public 
          
            int doUpdate(String sqlMap, HashMap
           
             params) throws SQLException { if (conn == null || conn.isClosed()) { this.initialization(true); } String sql = this.getSQL(sqlMap); int i = 0; PreparedStatement ps = conn.prepareStatement(sql.replaceAll("#+[A-Za-z0-9_]+#", "?")); doParams(ps, params,this.parseSQL(sql)); i = ps.executeUpdate(); return i; } /** * 通过SQL查询数据，返回数据集 * * @param sql * @param params * @return 数据集 * @throws SQLException */ public ResultSet RsBySql(String sqlMap, HashMap
            
              params) throws SQLException { if (conn == null || conn.isClosed()) { this.initialization(true); } String sql = this.getSQL(sqlMap); PreparedStatement ps = conn.prepareStatement(sql.replaceAll("#+[A-Za-z0-9_]+#", "?")); this.doParams(ps, params,this.parseSQL(sql)); ResultSet rs = ps.executeQuery(); return rs; } /** * 关闭数据库连接对象 * * @param i * 大于0则提交，否则回滚 */ public void closeAll(int i) { try { if (this.conn != null && !this.conn.isClosed()) { if (i > 0) { this.conn.commit(); } else { this.conn.rollback(); } this.conn.close(); } } catch (SQLException e) { // TODO Auto-generated catch block logger.error(e); } } /** * 获取sql * * @param sqlMap * @return */ private String getSQL(String sqlMap) { String sql = null; try { sql = DomParseService.getSQL(sqlMap); } catch (ParserConfigurationException e) { logger.error(e); } catch (SAXException e) { logger.error(e); } catch (IOException e) { logger.error(e); } return sql; } /*** * 解析sql * @param sql * @return */ private HashMap
             
               parseSQL(String sql) { HashMap
              
                hash = new HashMap
               
                (); Pattern p = Pattern.compile("#+[A-Za-z0-9]+#"); Matcher matcher = p.matcher(sql); String value = null; int index=0; while(matcher.find()){ value = matcher.group(); value = value.replace("#", ""); hash.put(String.valueOf(index), value); index++; } /*** int i = 0; int index = 0; while (index != -1) { index = sql.indexOf("#"); sql = sql.substring(index + 1); int index2 = sql.indexOf("#"); if (index2 != -1) { hash.put(i + "", sql.substring(0, index2)); sql = sql.substring(index2 + 1); } i++; } ***/ return hash; } /** * 设置预编译对象参数 * * @param pstmt * @param params * @throws SQLException */ private 
                
                  void doParams(PreparedStatement pstmt, HashMap
                 
                   params,HashMap
                  
                    hash) throws SQLException { if(pstmt!=null && params!=null && hash!=null){ for(int i=0;i