得到分页列表的分页数据:
public static TablePager getTablePager(Connection conn, int pageSize, int pageIndex, String selectClause, String fromClause, String whereClause, String groupClause, String havingClause,
String orderClause)
{
TablePager table;
ResultSet resultSet;
CallableStatement callStatement;
boolean isExtConn;
table = new TablePager(null);
if(pageSize <= 0)
pageSize = 10;
if(pageIndex <= 0)
pageIndex = 0;
table.pageSize = pageSize;
table.pageIndex = pageIndex;
table.pageCount = 0;
table.queryRowCount = 0;
table.maxKeyValue = 0;
table.querySql = "";
table.keyField = "";
table.Name = fromClause;
resultSet = null;
callStatement = null;
isExtConn = true;
if(conn == null)
{
conn = Conn.Create();
isExtConn = false;
}
if(!SqlConf.DefClassForName.toLowerCase().equals("oracle.jdbc.driver.oracledriver"))
break MISSING_BLOCK_LABEL_835;
try
{
String sql = "{call SELECTDATA(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
callStatement = conn.prepareCall(sql);
callStatement.setInt(1, table.pageSize);
callStatement.setInt(2, table.pageIndex);
callStatement.setInt(3, table.pageCount);
callStatement.setInt(4, table.queryRowCount);
callStatement.setInt(5, table.maxKeyValue);
callStatement.setString(6, table.querySql);
callStatement.setString(7, table.keyField);
callStatement.registerOutParameter(1, 4);
callStatement.registerOutParameter(2, 4);
callStatement.registerOutParameter(3, 4);
callStatement.registerOutParameter(4, 4);
callStatement.registerOutParameter(5, 4);
callStatement.registerOutParameter(6, 12);
callStatement.registerOutParameter(7, 12);
if(StringUtils.isEmptyOrBlank(table.keyField))
callStatement.setNull(7, 0);
else
callStatement.setString(7, table.keyField);
if(StringUtils.isEmptyOrBlank(selectClause))
callStatement.setNull(8, 0);
else
callStatement.setString(8, selectClause);
if(StringUtils.isEmptyOrBlank(fromClause))
callStatement.setNull(9, 0);
else
callStatement.setString(9, fromClause);
if(StringUtils.isEmptyOrBlank(whereClause))
callStatement.setNull(10, 0);
else
callStatement.setString(10, whereClause);
if(StringUtils.isEmptyOrBlank(groupClause))
callStatement.setNull(11, 0);
else
callStatement.setString(11, groupClause);
if(StringUtils.isEmptyOrBlank(havingClause))
callStatement.setNull(12, 0);
else
callStatement.setString(12, havingClause);
if(StringUtils.isEmptyOrBlank(orderClause))
callStatement.setNull(13, 0);
else
callStatement.setString(13, orderClause);
callStatement.setString(14, "");
callStatement.registerOutParameter(14, 12);
callStatement.registerOutParameter(15, -10);
callStatement.execute();
table.pageSize = Integer.parseInt(callStatement.getObject(1).toString());
table.pageIndex = Integer.parseInt(callStatement.getObject(2).toString());
table.pageCount = Integer.parseInt(callStatement.getObject(3).toString());
table.queryRowCount = Integer.parseInt(callStatement.getObject(4).toString());
table.maxKeyValue = Integer.parseInt(callStatement.getObject(5).toString());
table.querySql = callStatement.getObject(6).toString();
table.keyField = (String)callStatement.getObject(7);
table.note = "";
resultSet = (ResultSet)callStatement.getObject(15);
setResultSet(table, resultSet);
break MISSING_BLOCK_LABEL_822;
}
catch(Exception e)
{
System.out.println((new StringBuilder("select:")).append(selectClause).toString());
System.out.println((new StringBuilder("from:")).append(fromClause).toString());
System.out.println((new StringBuilder("where:")).append(whereClause).toString());
table = null;
Error.handler(e);
}
closeAll(resultSet, callStatement, conn, isExtConn);
break MISSING_BLOCK_LABEL_2103;
Exception exception;
exception;
closeAll(resultSet, callStatement, conn, isExtConn);
throw exception;
closeAll(resultSet, callStatement, conn, isExtConn);
break MISSING_BLOCK_LABEL_2103;
if(!SqlConf.DefClassForName.toLowerCase().equals("com.microsoft.sqlserver.jdbc.sqlserverdriver"))
break MISSING_BLOCK_LABEL_1565;
try
{
String sql = "{call SELECTDATA(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
callStatement = conn.prepareCall(sql);
callStatement.setInt(1, table.pageSize);
callStatement.setInt(2, table.pageIndex);
callStatement.setInt(3, table.pageCount);
callStatement.setInt(4, table.queryRowCount);
callStatement.setInt(5, table.maxKeyValue);
callStatement.setString(6, table.querySql);
if(StringUtils.isEmptyOrBlank(table.keyField))
callStatement.setNull(7, 0);
else
callStatement.setString(7, table.keyField);
callStatement.registerOutParameter(1, 4);
callStatement.registerOutParameter(2, 4);
callStatement.registerOutParameter(3, 4);
callStatement.registerOutParameter(4, 4);
callStatement.registerOutParameter(5, 4);
callStatement.registerOutParameter(6, 12);
callStatement.registerOutParameter(7, 12);
if(StringUtils.isEmptyOrBlank(selectClause))
callStatement.setNull(8, 12);
else
callStatement.setString(8, selectClause);
if(StringUtils.isEmptyOrBlank(fromClause))
callStatement.setNull(9, 12);
else
callStatement.setString(9, fromClause);
if(StringUtils.isEmptyOrBlank(whereClause))
callStatement.setNull(10, 12);
else
callStatement.setString(10, whereClause);
if(StringUtils.isEmptyOrBlank(groupClause))
callStatement.setNull(11, 12);
else
callStatement.setString(11, groupClause);
if(StringUtils.isEmptyOrBlank(havingClause))
callStatement.setNull(12, 12);
else
callStatement.setString(12, havingClause);
if(StringUtils.isEmptyOrBlank(orderClause))
callStatement.setNull(13, 0);
else
callStatement.setString(13, orderClause);
callStatement.setString(14, "");
resultSet = callStatement.executeQuery();
setResultSet(table, resultSet);
if(callStatement.getMoreResults())
{
resultSet.close();
resultSet = callStatement.getResultSet();
setResultSet(table, resultSet);
}
table.pageSize = Integer.parseInt(callStatement.getObject(1).toString());
table.pageIndex = Integer.parseInt(callStatement.getObject(2).toString());
table.pageCount = Integer.parseInt(callStatement.getObject(3).toString());
table.queryRowCount = Integer.parseInt(callStatement.getObject(4).toString());
table.maxKeyValue = Integer.parseInt(callStatement.getObject(5).toString());
table.querySql = (String)callStatement.getObject(6);
table.keyField = (String)callStatement.getObject(7);
table.note = "";
break MISSING_BLOCK_LABEL_1552;
}
catch(Exception e)
{
System.out.println((new StringBuilder("select:")).append(selectClause).toString());
System.out.println((new StringBuilder("from:")).append(fromClause).toString());
System.out.println((new StringBuilder("where:")).append(whereClause).toString());
Error.handler(e);
table = null;
}
closeAll(resultSet, callStatement, conn, isExtConn);
break MISSING_BLOCK_LABEL_2103;
exception;
closeAll(resultSet, callStatement, conn, isExtConn);
throw exception;
closeAll(resultSet, callStatement, conn, isExtConn);
break MISSING_BLOCK_LABEL_2103;
if(SqlConf.DefClassForName.toLowerCase().equals("com.mysql.jdbc.driver"))
{
String sqlStr = "";
if(StringUtils.isEmptyOrBlank(selectClause))
selectClause = " * ";
else
selectClause = (new StringBuilder(" ")).append(selectClause).toString();
fromClause = (new StringBuilder(" from ")).append(fromClause).toString();
if(StringUtils.isEmptyOrBlank(whereClause))
whereClause = " ";
else
whereClause = (new StringBuilder(" where ")).append(whereClause).toString();
if(StringUtils.isEmptyOrBlank(groupClause))
groupClause = " ";
else
groupClause = (new StringBuilder(" group by ")).append(groupClause).toString();
if(StringUtils.isEmptyOrBlank(havingClause))
havingClause = " ";
else
havingClause = (new StringBuilder(" having ")).append(havingClause).toString();
if(StringUtils.isEmptyOrBlank(orderClause))
orderClause = " ";
else
orderClause = (new StringBuilder(" order by ")).append(orderClause).toString();
sqlStr = String.format("select count(*) from (select %s %s %s %s %s %s) temp", new Object[] {
selectClause, fromClause, whereClause, groupClause, havingClause, orderClause
});
table.queryRowCount = Scalar.scalarInt(conn, sqlStr, 0);
if(table.pageIndex < 0)
table.pageIndex = 0;
if(table.pageSize < 0 || table.pageSize > table.queryRowCount)
table.pageSize = table.queryRowCount;
if(table.queryRowCount == 0)
{
table.pageIndex = 0;
table.pageSize = 0;
table.pageCount = 0;
} else
{
table.pageCount = (int)Math.ceil((1.0D * (double)table.queryRowCount) / (double)table.pageSize);
}
if(table.pageIndex >= table.pageCount)
table.pageIndex = table.pageCount - 1;
int rowNumStart = table.pageSize * table.pageIndex;
sqlStr = String.format("select %s %s %s %s %s %s limit %d ,%d", new Object[] {
selectClause, fromClause, whereClause, groupClause, havingClause, orderClause, Integer.valueOf(rowNumStart), Integer.valueOf(table.pageSize)
});
TablePager tableTemp = selectTablePager(conn, sqlStr, null, null);
tableTemp.pageCount = table.pageCount;
tableTemp.pageIndex = table.pageIndex;
tableTemp.pageSize = table.pageSize;
tableTemp.queryRowCount = table.queryRowCount;
tableTemp.querySql = sqlStr;
closeAll(resultSet, callStatement, conn, isExtConn);
return tableTemp;
}
return table;
}