package com.etown.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.log4j.Logger;
/**
* 通用DAO
* @author knight
*
*/
public class CommonDAO {
private static Logger log=Logger.getLogger(CommonDAO.class);
private CommonDAO(){}
/**
* 获取JSON数组
* @param sql
* @param params
* @return
*/
public static JSONArray getList(String sql, Object... params) {
Connection con=DBPool.getConnection();
final JSONArray jsonArr = new JSONArray();
try {
QueryRunner runner = new QueryRunner();
runner.query(con,sql.toString(), new ResultSetHandler<Object>() {
public Object handle(ResultSet rs) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
int cols = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
for (int i = 1; i <= cols; i++) {
String colName = metaData.getColumnLabel(i);
String value = rs.getString(colName);
if (value != null && value.length() != 0) {
jsonObj.put(colName.toLowerCase(), value);// 列名全部转换成小写，便于前台代码统一处理
}
}
jsonArr.add(jsonObj);
}
return jsonArr;
}
}, params);
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new RuntimeException(e.getMessage(), e);
}finally{
DBPool.releaseConnection(con);
}
return jsonArr;
}
/**
* 获取单条JSON记录
* @param sql
* @param params
* @return
*/
public static JSONObject getSingle(String sql, Object... params) {
JSONArray result = getList(sql, params);
if (result == null || result.size() == 0) {
return null;
}
return result.getJSONObject(0);
}
/**
* 更新表
* @param sql
* @param params
* @return
*/
public static int update(String sql, Object... params) {
Connection con=DBPool.getConnection();
try {
QueryRunner runner = new QueryRunner();
return runner.update(con,sql, params);
} catch (Exception e) {
log.error(e.getMessage(), e);
}finally{
DBPool.releaseConnection(con);
}
return 0;
}
}