DBActionByMap

为什么80%的码农都做不了架构师？>>>   

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 <T> List<T> findBySql(String sqlMap, HashMap<String,Object> params, Class<T> c)
			throws SQLException, InstantiationException,
			IllegalAccessException, IllegalArgumentException,
			InvocationTargetException {
		if (conn == null || conn.isClosed()) {
			this.initialization(true);
		}

		String sql = this.getSQL(sqlMap);
		
		List<T> list = new ArrayList<T>();
		/**
		 * 取出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 <T> int doUpdate(String sqlMap, HashMap<String,Object> 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<String,Object> 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<String, String> parseSQL(String sql) {				

		HashMap<String, String> hash = new HashMap<String, String>();		
		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 <T> void doParams(PreparedStatement pstmt, HashMap<String,Object> params,HashMap<String, String> hash)
			throws SQLException {
		if(pstmt!=null && params!=null && hash!=null){
			for(int i=0;i<hash.size();i++){
				String key = hash.get(i+"");
				if(params.containsKey(key)){
					Object temp = params.get(key);
					if(temp.getClass().getName().equals("java.lang.String")){
						pstmt.setString(i+1, (String)temp);
					}else if(temp.getClass().getName().equals("java.math.BigDecimal")){
						pstmt.setBigDecimal(i+1, (BigDecimal)temp);
					}else{
						pstmt.setObject(i+1, temp);
					}
				}
			}
		}
		
	}
	
}

转载于:https://my.oschina.net/scotts/blog/115926