数据源DBAccess03

/*
* 简单的通用数据库Insert/Update方法
*
* @param sql sql语句,参数用?
*
* @param param 参数值,参数值的顺利必须与sql语句中的参数(?)匹配
*
* @return int
*/
public static int execUpdateSQL(final String sql, List<String> param)
{
logger.info("Get connection begin ...");
Connection conn = getConnection();
logger.info("Get connection Successful.");
PreparedStatement ps = null;
int i_rtn = 0;
try
{
logger.info("Prepare sql statement:[" + sql + "]");
ps = conn.prepareStatement(sql);

logger.info("Prepare sql parameters:" + param.toString());
for (int i = 0; i < param.size(); i++)
{
ps.setString(i + 1, (String) param.get(i));
}
logger.info("Execute sql begin ...");
i_rtn = ps.executeUpdate();
logger.info("Execute sql Successful.");
}
catch (Exception e)
{
logger.error("DataBase update exception! Message:" + e.toString()
+ "\n\t\tSqlText: " + sql + "\n\t\tparam: "
+ param.toString());
throw new BaseException(e);
}
finally
{
if (null != ps)
{
try
{
ps.close();
logger.info("Close PreparedStatement Successful.");
}
catch (SQLException e)
{
logger.error("Close PreparedStatement error! Message:"
+ e.toString());
throw new BaseException(e);
}
}
if (null != conn)
{
try
{
conn.close();
logger.info("Close Connection Successful.");
}
catch (SQLException e)
{
logger.error("Close connection error! message:"
+ e.toString());
throw new BaseException(e);
}
}
}
return i_rtn;
}

public static ArrayList<HashMap<String, String>> execQuerySQL(String sql,
List<String> param, String pageSize, String pageNum)
{
String totalNum = null;
int ps = Integer.parseInt(pageSize);
int pn = Integer.parseInt(pageNum);
if ((pageSize != null) && (!"".equals(pageSize)) && (pageNum != null)
&& (!"".equals(pageNum)))
{
int pos = sql.lastIndexOf("from");
if (pos == -1)
pos = sql.lastIndexOf("FROM");
String totalSql = "select count(1) totalnum " + sql.substring(pos);
List<?> list = execQuerySQL(totalSql, param);
totalNum = (String) ((HashMap<?, ?>) list.get(0)).get("TOTALNUM");
sql = "select * from (" + sql.substring(0, pos - 1)
+ ",rownum rnum " + sql.substring(pos)
+ ") tt where tt.rnum between " + ((pn - 1) * ps + 1)
+ " and " + pn * ps;
}
else
{
logger.warn("Pagination number is invalid, Ignore it. pageSize: "
+ pageSize + ", pageNum: " + pageNum);
}
ArrayList<HashMap<String, String>> rtnList = execQuerySQL(sql, param);
if (totalNum != null)
{
HashMap<String, String> tmpMap = new HashMap<String, String>();
tmpMap.put("totalNum", totalNum);
int mod = Integer.parseInt(totalNum) % Integer.parseInt(pageSize);
int pageCount = Integer.parseInt(totalNum)
/ Integer.parseInt(pageSize);
if (mod > 0)
{
pageCount++;
}
tmpMap.put("totalPage", pageCount + "");
rtnList.add(tmpMap);
}
return rtnList;