*/
protected DBResult OracleQrySQL(String SQLStatement, LinkedHashMap param,
int Page, int rowsPerPage, int TotalPage, long rowCount,String Action)
throws Exception {
logWriter.Info(SQLStatement);
DBResult Result = new DBResult();
ResultSet Rs = null;
LinkedHashMap Record = null;
StringBuffer PageSearch = new StringBuffer();
StringBuffer Search = new StringBuffer("?");
long Rows = 0;
// 启始记录位置
int Offset = 0;
long m=0;
try {
// 初始化分页对象
if (Page == 0) {
Rs = stmt.executeQuery("select count(*) from (" + SQLStatement
+ ")");
if (Rs.next())
Rows = Rs.getLong(1);
Rs.close();
Result.setRowCount(String.valueOf(Rows));
if (Rows % rowsPerPage != 0)
Result.setTotalPage(String.valueOf(Rows / rowsPerPage + 1));
else
Result.setTotalPage(String.valueOf(Rows / rowsPerPage));
} else
{
Result.setTotalPage(String.valueOf(TotalPage));
Result.setRowCount(String.valueOf(rowCount));
}
if (rowCount!=0)
Result.setRowCount(String.valueOf(rowCount));
if (Result.getTotalPage().equalsIgnoreCase("0"))
Result.setTotalPage("1");
// 计算启始记录位置
if (Page == 0) {
Page = 1;
Result.setCurrentPage("1");
} else
Result.setCurrentPage(String.valueOf(Page));
Offset = (Page - 1) * rowsPerPage;
Rs = stmt.executeQuery("select * from (select a.*,rownum rn from ("
+ SQLStatement + ") a where rownum<="
+ new Integer(Offset + rowsPerPage) + ") where rn>"
+ new Integer(Offset));
// 初始化参数变量
Result.setParamList(param);
Result.setColumnCount(Rs.getMetaData().getColumnCount() - 1);
Result.setRowsPerPage(String.valueOf(rowsPerPage));
Iterator iterator = param.keySet().iterator();
while (iterator.hasNext()) {
String key = (String) iterator.next();
PageSearch.append("<input type=\"hidden\" name=\"" + key
+ "\" value=\"" + (String) param.get(key) + "\">");
if (!com.trulytech.mantis.system.Properties.isInternational)
Search.append(key + "="
+ URLEncoder.encode((String) param.get(key), "UTF-8")
+ "&");
else
Search.append(key
+ "="
+ URLEncoder.encode((String) param.get(key),
"UTF-8") + "&");
}
PageSearch.append("<input type=\"hidden\" name=\"total\" value=\""
+ Result.getTotalPage() + "\">");
PageSearch.append("<input type=\"hidden\" name=\"len\" value=\""
+ Result.getRowsPerPage() + "\">");
PageSearch.append("<input type=\"hidden\" name=\"rows\" value=\""
+ Result.getRowCount() + "\">");
if (Action != null) {
PageSearch.append("<input type=\"hidden\" name=\""
+ Properties.Action_Tag + "\" value=\"" + Action
+ "\">");
Search.append(Properties.Action_Tag + "=" + Action + "&");
}
Search.append("total=" + Result.getTotalPage() + "&");
Search.append("rows=" + Result.getRowCount() + "&");
Search.append("len=" + String.valueOf(rowsPerPage) + "&page=");
Result.setUrlSearch(Search.toString());
Result.setFirstPage(Search.toString() + "1");
Result.setLastPage(Search.toString() + Result.getTotalPage());
if (Page == 1)
Result.setPreviousPage(Search.toString() + "1");
else
Result.setPreviousPage(Search.toString()
+ String.valueOf(Page - 1));
if (Page == (new Integer(Result.getTotalPage()).intValue()))
Result.setNextPage(Search.toString() + Result.getTotalPage());
else
Result
.setNextPage(Search.toString()
+ String.valueOf(Page + 1));
Result.setPageSearch(PageSearch.toString());
Result.setRowsPerPage(String.valueOf(rowsPerPage));
// 取值
while (Rs.next()) {
m++;
Record = new LinkedHashMap();
for (int j = 1; j <= Result.getColumnCount(); j++) {
Record.put(Rs.getMetaData().getColumnLabel(j).toLowerCase(),
Rs.getString(j) == null ? "" : Rs.getString(j));
}
//add index
if (Page==0)
Record.put("_index", String.valueOf(m));
else
Record.put("_index", String.valueOf(m+(Page-1)*rowsPerPage));
Result.getResultBuffer().add(Record);
}
} finally {
if (Rs != null) {
Rs.close();
}