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(" </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, "");
}
}
各位给评一下?