My DAL Util Class

import java.lang.reflect.Method;
import java.sql.Connection;
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.List;
import java.util.Map;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

public class DALUtils {
	/*
	 * Data Access Layer Util Class.
	 */
	public static RowSet query(Connection conn, String sql) {		
		try {
			ResultSet rset = null;
			Statement stmt = conn.createStatement();
			rset = stmt.executeQuery(sql);
			CachedRowSet crs = new CachedRowSetImpl();
			crs.populate(rset);
			stmt.close();
			return crs;
		} catch (SQLException ex) {ex.printStackTrace();}
		return null;
	}
	
	public static int exec(Connection conn, String sql) {		
		try {
			int rt = 0;
			Statement stmt = conn.createStatement();
			rt = stmt.executeUpdate(sql);
			stmt.close();
			return rt;
		} catch (SQLException ex) {ex.printStackTrace();}
		return 0;
	}

	public static List<Object> resultSetToList(ResultSet rs, String className) {
		List<Object> list = new ArrayList<Object>();
		try {
			ResultSetMetaData meta = rs.getMetaData();  
			Object obj = null;  
			while (rs.next()) {  
				obj = Class.forName(className).newInstance();  
			    for(int i = 1; i <= meta.getColumnCount(); i++)  {  
			        String colName = meta.getColumnName(i);
			        String methodName = "set" + new String(colName.charAt(0)+"").toUpperCase() + colName.substring(1);
			        Object value = rs.getObject(i);  
			        Method method = obj.getClass().getMethod(methodName, value.getClass());  
			        method.invoke(obj, value);  
			    }  
			    list.add(obj);  
			}  
		} catch(Exception ex) {ex.printStackTrace();}
		return list;
	}
	
	public static List<Map<String, Object>> resultSetToList(ResultSet rset) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		try {
			ResultSetMetaData meta = rset.getMetaData();  
			while (rset.next()) {  
				Map<String, Object> m = new HashMap<String, Object>();  
			    for(int i = 1; i <= meta.getColumnCount(); i++)			    	
			        m.put(meta.getColumnName(i), rset.getObject(i));
			    list.add(m);  
			}  
		} catch(SQLException ex) {ex.printStackTrace();}
		return list;
	}
	
	public static List<String> getColumnLabelList(ResultSet rset) {
		List<String> list = new ArrayList<String>();
		try {
			ResultSetMetaData meta = rset.getMetaData();  
			for (int i = 1; i <= meta.getColumnCount(); i++) 
				list.add(meta.getColumnLabel(i));
		} catch(SQLException ex) {ex.printStackTrace();}
		return list;
	}
	
	public static int getTableMaxCount(Connection conn, String tableName) {
		
		int cnt = 0;
		String sql = "select count(*) from " + tableName;
		try {
			Statement stmt = null;
			stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(sql);
            while(rset.next()) {
                cnt = rset.getInt(1);
            }
            stmt.close();
		} catch (SQLException ex) {ex.printStackTrace(); } 
		return cnt;
	}
	
	public static Map<String, Integer> getPagerData(Connection conn, String tableName, int pageSize, int pageCnt) {
		int maxCnt = getTableMaxCount(conn, tableName);
		int totalPageCnt = (maxCnt + pageSize -1) / pageSize;
//		int pageCnt = 1;
//		if (page != null && page.length() > 0) pageCnt = Integer.valueOf(page);
		if (pageCnt > totalPageCnt) pageCnt = totalPageCnt;
		if (pageCnt <= 0) pageCnt = 1;
		int offset = (pageCnt -1) * pageSize;
		
		Map<String, Integer> m = new HashMap<String, Integer>();
		m.put("maxCnt", maxCnt);
		m.put("pageCnt", pageCnt);
		m.put("totalPageCnt", totalPageCnt);
		m.put("offset", offset);
		m.put("pageSize", pageSize);
		return m;
	}
	
	public static String getTableData(Connection conn, String sql) {
		/*
		 *  return table head and body
		 */
		StringBuffer sb = new StringBuffer();
		if (sql.toUpperCase().contains("SELECT"))
			sql = "select * from (" + sql + ") a limit 0, 1000";
		
		RowSet rs = query(conn, sql);
		sb.append("<thead>");
		sb.append("<tr>");
		List<String> LabelList = getColumnLabelList(rs);
		for (String s: LabelList) {
			sb.append("<th>");
			sb.append(s);
			sb.append("</th>");
		}
		sb.append("</tr>");
		sb.append("</thead>");
		
		List<Map<String, Object>> dataList = resultSetToList(rs);
		sb.append("<tbody>");		
		for(Map<String, Object> m: dataList) {
			sb.append("<tr>");
			for (String s: LabelList) {
				sb.append("<td>");
				sb.append(m.get(s).toString());
				sb.append("&nbsp;</td>");
			}
			sb.append("</tr>");
		}
		sb.append("<tbody>");
		
		return sb.toString();
	}
	
	public static String getTableData(Connection conn, String tableName, Map<String, Integer> pagerData) {
		int offset = pagerData.get("offset");
		int pageSize = pagerData.get("pageSize");	
		String sql = "select * from " + tableName + " limit " + offset + ", " + pageSize;
		
		return getTableData(conn, sql);
	}
	
	public static void main(String args[]) {
		Connection conn = null;
		RowSet rs = query(conn, "select * from plantMapping");
		resultSetToList(rs, "");
	}
	
}

 

各位给评一下？

评论

2 楼 23号 2009-03-30  

1. 我觉得传className比较好，如果传进来的是Object，要修改Object的值感觉不好。
2. 是的，直接用Connection，所有的方法都是static的。

1 楼 ukinglli 2009-03-30  

写的很好. 两点建议:
1.resultSetToList 的想法很好,但是方法写的不太优雅,手动加set前缀看起来有点难过,不过倒是很直白,反射里面有方法可以直接取到一个属性的setter. 而且与其费劲的Class.forName(className) 还不如直接传递一个Class的参数进来...

2.这util没有连接池......