一个公用的dao类和util

dao

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.kmi.util.Util;
import com.sun.rowset.CachedRowSetImpl;

public class JdbcDaoSupport<T extends Serializable> {
	private final Log log = LogFactory.getLog(this.getClass());

	public int save(Connection conn, String sql, List<Object> mList) {
		getValidConnection(conn);
		int autoId = -1;
		if (conn == null) {
			return autoId;
		}
		PreparedStatement psment = null;
		ResultSet rsment = null;
		try {
			log.debug("JdbcDaoSupport.Save sql = " + sql);
			psment = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

			setParameters(psment, mList);

			int count = psment.executeUpdate();
			if (count >= 0) {
				rsment = psment.getGeneratedKeys();
				while (rsment.next()) {
					autoId = rsment.getInt(1);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			autoId = -1;
		} finally {
			DBManager.closePreparedStatement(psment);
		}

		return autoId;
	}
	/**
	 * 执行Save或者Update操作
	 * 
	 * @param conn
	 *            数据库连接
	 * @param sql
	 *            Save或者Update SQL
	 * @param mList
	 *            参数List
	 * @return
	 */
	public boolean saveOrUpdate(Connection conn, String sql, List<Object> mList) {
		getValidConnection(conn);
		boolean executeState = false;
		PreparedStatement psment = null;
		try {
			psment = conn.prepareStatement(sql);
			log.debug("JdbcDaoSupport.saveOrUpdate sql = " + sql);
			setParameters(psment, mList);

			int count = psment.executeUpdate();
			if (count >= 0) {
				executeState = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			executeState = false;
		} finally {
			DBManager.closePreparedStatement(psment);
		}

		return executeState;
	}
	/**
	 * 调用存储过程
	 * 
	 * @param conn
	 * @param procName
	 * @param list
	 * @return
	 */
	public List<Object[]> callProc(Connection conn, String procName, List<Object> list) {

		if (Util.isStringEmpty(procName)) {
			return null;
		}
		getValidConnection(conn);
		CallableStatement callStatement = null;
		ResultSet rsment = null;
		ResultSetMetaData rsmd = null;
		List<Object[]> objList = new ArrayList<Object[]>();
		try {
			log.debug("JdbcDaoSupport.callProc procName = " + procName);
			callStatement = conn.prepareCall(procName);
			if (!Util.isCollectionEmpty(list)) {
				for (int i = 0; i < list.size(); i++) {
					callStatement.setString(i + 1, list.get(i).toString());
				}
			}

			rsment = callStatement.executeQuery();
			rsmd = rsment.getMetaData();
			int cols = rsmd.getColumnCount();
			if (cols > 0) {
				while (rsment.next()) {
					Object[] obj = new Object[cols];
					for (int i = 0; i < obj.length; i++) {
						obj[i] = rsment.getObject(i + 1);
					}
					objList.add(obj);
				}
			}else{
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBManager.closeResultSet(rsment);
			DBManager.closeCallableStatement(callStatement);
		}

		return objList;
	}
	/**
	 * 查询(返回List<Map<name,value>>)
	 * 
	 * @param conn
	 * @param sql
	 * @param list
	 * @return
	 */
	public List<Map<String, Object>> queryForMap(Connection conn, String sql, List<Object> list) {

		if (Util.isStringEmpty(sql)) {
			return null;
		}
		getValidConnection(conn);

		PreparedStatement psment = null;
		ResultSet rsment = null;
		ResultSetMetaData rsmd = null;
		List<Map<String, Object>> objList = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = null;
		try {
			log.debug("JdbcDaoSupport.queryForMap sql = " + sql);
			psment = conn.prepareStatement(sql);
			setParameters(psment, list);

			rsment = psment.executeQuery();
			rsmd = rsment.getMetaData();
			int cols = rsmd.getColumnCount();
			while (rsment.next()) {
				map = new HashMap<String, Object>();
				for (int i = 0; i < cols; i++) {
					map.put(rsmd.getColumnName(i + 1), rsment.getObject(i + 1));
				}
				objList.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBManager.closeResultSet(rsment);
			DBManager.closePreparedStatement(psment);
		}

		return objList;
	}

	private void setParameters(PreparedStatement psment, List<Object> mList) throws SQLException {
		if (Util.isCollectionEmpty(mList)) {
			return;
		}
		for (int i = 0; i < mList.size(); i++) {
			psment.setObject(i + 1, mList.get(i));
		}
	}

	private List<?> getEntity(List<Map<String, Object>> entityList, Class<?> clazz) {
		List<Object> mList = new ArrayList<Object>();
		try {
			String key = null;
			Object obj = null;
			Object tempVale = null;
			Field[] fa = clazz.getDeclaredFields();
			for (Map<String, Object> map : entityList) {
				obj = (Object) Class.forName(clazz.getName()).newInstance();
				for (Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator(); iterator.hasNext();) {
					Map.Entry<String, Object> entry = iterator.next();
					key = entry.getKey();
					tempVale = entry.getValue();
					if (tempVale == null) {
						continue;
					}
					for (Field field : fa) {
						if (field.getName().equalsIgnoreCase(key)) {
							field.setAccessible(true);
							field.set(obj, entry.getValue());
						}
					}
				}
				mList.add(obj);
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return mList;
	}
	/**
	 * 执行批量添加
	 * 
	 * @param conn
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public boolean batchExecRecord(Connection conn, String sql, List<List<Object>> paramList) {
		boolean status = false;
		PreparedStatement psment = null;
		try {
			getValidConnection(conn);
			psment = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < paramList.size(); i++) {
				List<Object> param = paramList.get(i);
				for (int j = 0; j < param.size(); j++) {
					psment.setObject(j + 1, param.get(j));
				}
				psment.addBatch();
			}

			int[] b = psment.executeBatch();

			if (b != null && b.length > 0) {
				status = true;
			}

		} catch (Exception e) {
			status = false;
			e.printStackTrace();
		}
		return status;
	}
	/**
	 * 执行查询SQL
	 * 
	 * @param conn
	 * @param sql
	 * @param mList
	 * @return
	 */
	public CachedRowSet executeQuerySQL(Connection conn, String sql, List<Object> list) {
		// TODO Auto-generated method stub
		if (Util.isStringEmpty(sql)) {
			return null;
		}
		PreparedStatement psment = null;
		ResultSet rsment = null;
		CachedRowSet crs = null;
		try {
			log.debug("JdbcDaoSupport.Method executeQuerySQL = " + sql);
			psment = conn.prepareStatement(sql);
			setParameters(psment, list);
			rsment = psment.executeQuery();
			crs = new CachedRowSetImpl();
			crs.populate(rsment);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBManager.closeResultSet(rsment);
			DBManager.closePreparedStatement(psment);
		}
		return crs;
	}

	public void getValidConnection(Connection conn) {
		try {
			if (conn == null || conn.isClosed()) {
				conn = DBManager.getConnection();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 清空表数据
	 * 
	 * @param conn
	 *            数据库连接
	 * @param tableName
	 *            表的名字
	 * @return
	 */
	public boolean emptyTableData(Connection conn, String tableName) {
		boolean status = false;
		if (Util.isStringEmpty(tableName)) {
			return status;
		}
		PreparedStatement psment = null;
		System.out.println("TRUNCATE table :" + tableName);
		StringBuffer buff = new StringBuffer("TRUNCATE TABLE ");
		buff.append(tableName);

		try {
			psment = conn.prepareStatement(buff.toString());
			psment.executeUpdate();

			status = true;

		} catch (SQLException e) {
			status = false;
			e.printStackTrace();
		} finally {
			DBManager.closePreparedStatement(psment);
		}

		return status;
	}


}

DBManager

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;


import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;


public class DBManager {
	private static final String DB_LOCATION = "dbcpconfig.properties";

	private static BasicDataSource dataSource = null;

	public DBManager() {
	}

	public static void init() {

		if (dataSource != null) {
			try {
				dataSource.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			dataSource = null;
		}

		try {
			Properties properties = new Properties();
			InputStream input = null;
			try {
				input = DBManager.class.getClassLoader().getResourceAsStream(DB_LOCATION);
				properties.load(input);

			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (input != null) {
					try {
						input.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}

			dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(properties);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		System.out.println(getConnection());
	}

	public static synchronized Connection getConnection() {
		Connection conn = null;
		try {
			if (dataSource == null) {
				init();
			}
			if (dataSource != null) {
				conn = dataSource.getConnection();
			}
			conn.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public synchronized static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public synchronized static void closeResultSet(ResultSet rsment) {
		if (rsment != null) {
			try {
				rsment.close();
				rsment = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public synchronized static void closeCachedRowSet(CachedRowSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public synchronized static void closeStatement(Statement stment) {
		if (stment != null) {
			try {
				stment.close();
				stment = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public synchronized static void closeCallableStatement(CallableStatement call) {
		if (call != null) {
			try {
				call.close();
				call = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public synchronized static void closePreparedStatement(PreparedStatement psment) {
		if (psment != null) {
			try {
				psment.close();
				psment = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static DataSource ds;

}

Util

package com.kmi.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Collection;

public class Util {
	/**
	 * 判断字符串是否为空
	 * @param str
	 * @return
	 */
	public static boolean isStringEmpty(String str) {
		return str == null || str.length() == 0;
	}
	/**
	 * 判断集合是否为空
	 * @param collection
	 * @return
	 */
	public static boolean isCollectionEmpty(Collection<?> collection) {
		return collection == null || collection.isEmpty();
	}
	/**
	 * 判断数组是否为空
	 * @param obj
	 * @return
	 */
	public static boolean isArrayEmpty(Object[] obj) {
		return obj == null || obj.length == 0;
	}
	/**
	 * 文件保存
	 * 
	 * @param file
	 *            要保存的文件
	 * @param path
	 *            保存的文件路径
	 * @param fileName
	 *            文件名称
	 * @return boolean
	 */
	public static boolean saveFile(File file, String path, String fileName) {
		boolean b = false;
		try {
//			String root = ServletActionContext.getRequest().getRealPath("/") + path;
//			String root = PropertiesUtils.getProperties("asm_file_path")+path;
			String root = path;
			File f = new File(root);
			if (!f.isDirectory()) {
				f.mkdirs();
			}
			InputStream is = new FileInputStream(file);
			File destFile = new File(root, fileName);
			OutputStream os = new FileOutputStream(destFile);
			byte[] buffer = new byte[1025];

			int length = 0;
			while ((length = is.read(buffer)) > 0) {
				os.write(buffer, 0, length);
			}
			is.close();
			os.close();
			b = true;
		} catch (Exception e) {
			b = false;
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}
}