public static IList FindPage( ObjectInfo state, String queryString )
{
// see: System/Web/Mvc/Routes/RouteTool.cs, line 211
if (state.Pager.getCurrent() <= 0) {
int page = CurrentRequest.getCurrentPage();
state.Pager.setCurrent( page );
}
if ( queryString != null && queryString.ToLower().StartsWith( "order " )) {
queryString = " " + queryString;
}
PageCondition pc = new PageCondition();
pc.ConditionStr = queryString;
pc.Property = state.Includer.SelectedProperty;
pc.CurrentPage = state.Pager.getCurrent();
pc.Size = state.Pager.getSize();
pc.OrderStr = state.Order;
pc.Pager = state.Pager;
String sql = new SqlBuilder( state.EntityInfo ).GetPageSql( pc );
return EntityPropertyUtil.FindList( state, sql );
}
private void getPageSql( PageCondition pc, String columnString, String joinTable, Boolean isJoin, Boolean isCustomerOrder, String orderString, String _condition_nowhere, StringBuilder sb ) {
if (_entityInfo.DbType == DatabaseType.MySql) {
int startRow = pc.Size * (pc.CurrentPage - 1);
sb.Append( "select " );
sb.Append( columnString );
sb.Append( " from " );
sb.Append( joinTable );
sb.Append( " " );
sb.Append( pc.ConditionStr );
sb.Append( " " );
sb.Append( orderString );
sb.Append( " limit " );
sb.Append( startRow );
sb.Append( ", " );
sb.Append( pc.Size );
return;
}
if (isCustomerOrder == false) {
sb.Append( "select top " );
sb.Append( pc.Size );
sb.Append( " " );
sb.Append( columnString );
sb.Append( " from " );
sb.Append( joinTable );
if (pc.OrderStr == "asc") {
if (isJoin)
sb.Append( " where t0.Id>(select max(Id) from (select top " );
else
sb.Append( " where Id>(select max(Id) from (select top " );
}
else {
if (isJoin)
sb.Append( " where t0.Id<(select min(Id) from (select top " );
else
sb.Append( " where Id<(select min(Id) from (select top " );
}
sb.Append( pc.Size * (pc.CurrentPage - 1) );
if (isJoin)
sb.Append( " t0.Id from " );
else
sb.Append( " Id from " );
sb.Append( joinTable );
sb.Append( pc.ConditionStr );
sb.Append( orderString );
sb.Append( ") as t) " );
//sb.Append( "and " );
sb.Append( _condition_nowhere );
sb.Append( orderString );
}
else if (_entityInfo.DbType == DatabaseType.Access || _entityInfo.DbType == DatabaseType.SqlServer2000) {
sb.Append( "select top " );
sb.Append( pc.Size );
sb.Append( " " );
sb.Append( columnString );
sb.Append( " from " );
sb.Append( joinTable );
if (strUtil.IsNullOrEmpty( pc.ConditionStr )) {
if (isJoin)
sb.Append( " where t0.Id not in ( select top " );
else
sb.Append( " where Id not in ( select top " );
}
else {
sb.Append( pc.ConditionStr );
if (isJoin)
sb.Append( " and t0.Id not in ( select top " );
else
sb.Append( " and Id not in ( select top " );
}
sb.Append( pc.Size * (pc.CurrentPage - 1) );
if (isJoin)
sb.Append( " t0.Id from " );
else
sb.Append( " Id from " );
sb.Append( joinTable );
sb.Append( pc.ConditionStr );
sb.Append( orderString );
sb.Append( " ) " );
//sb.Append( "and " );
sb.Append( _condition_nowhere );
sb.Append( " " );
sb.Append( orderString );
}
else {
int startRow = pc.Size * (pc.CurrentPage - 1);
int endRow = startRow + pc.Size;
sb.AppendFormat( "select {0} from ", columnString );
sb.Append( "(" );
sb.AppendFormat( "select ROW_NUMBER() over ( {0} ) as rowNumber, {1} from {2} {3}", orderString, columnString, joinTable, pc.ConditionStr );
sb.Append( ") as tmpTable " );
sb.AppendFormat( "where (rowNumber between {0} and {1}) {2}", (startRow + 1), endRow, _condition_nowhere );
}
}
public String GetPageSql( PageCondition pc ) {
String columnString = getColumnsByProperty( pc.Property );
//------------------------ condition ------------------------------
String joinTable = "";
pc.ConditionStr = processCondition( pc.ConditionStr, new Dictionary<String, Object>(), ref joinTable );
Boolean isJoin = false;
if (strUtil.HasText( joinTable )) {
isJoin = true;
joinTable = _entityInfo.TableName + " t0, " + joinTable;
// columnString中的每个columnName都要加上t0前缀
columnString = addJoinTablePrefix( columnString, ',' );
}
else {
joinTable = _entityInfo.TableName;
}
joinTable = joinTable.Trim().TrimEnd( ',' );
if (strUtil.HasText( pc.ConditionStr )) {
if (pc.ConditionStr.ToLower().Trim().StartsWith( "and " )) {
//"and id=7" 剔除and开头
pc.ConditionStr = " where " + pc.ConditionStr.Trim().Substring( 3 );
}
//"order by id" 加上一个空格
else if (pc.ConditionStr.Trim().ToLower().StartsWith( "order" )) {
pc.ConditionStr = " " + pc.ConditionStr;
}
else {
//加上 where
pc.ConditionStr = " where " + pc.ConditionStr;
}
}
//------------------------ order ------------------------------
Boolean isCustomerOrder = false;
if (pc.ConditionStr.ToLower().IndexOf( " order " ) >= 0) isCustomerOrder = true;
String orderString = "";
pc.OrderStr = pc.OrderStr.ToLower();
// 默认排序方式：逆序
if (isCustomerOrder == false) {
if (pc.OrderStr == "asc")
orderString = " order by Id asc";
else
orderString = " order by Id desc";
if (isJoin) orderString = orderString.Replace( "Id", "t0.Id" );
}
else {
// 自定义排序方式
String newOrderString = "";
String tempString = pc.ConditionStr;
//此处conditionString已经不带order
pc.ConditionStr = pc.ConditionStr.Substring( 0, pc.ConditionStr.ToLower().IndexOf( "order " ) );
orderString = tempString.Replace( pc.ConditionStr, "" );
String[] arrOrder = orderString.Split( new char[] { ' ', ',' } );
foreach (String s in arrOrder) {
if (s.ToLower() == "asc" || s.ToLower() == "desc") {
newOrderString += s + ", ";
}
else if (s.ToLower() != "order" && s.ToLower() != "by" && s != null && s != "") {
if (isJoin)
newOrderString += "t0." + s + " ";
else
newOrderString += s + " ";
}
else {
newOrderString += s + " ";
}
}
orderString = newOrderString.Trim().TrimEnd( ',' );
}
String _condition_nowhere = pc.ConditionStr.Trim();
if (strUtil.HasText( _condition_nowhere ) && _condition_nowhere.ToLower().StartsWith( "where" )) {
_condition_nowhere = pc.ConditionStr.Trim().Substring( 5 );
_condition_nowhere = "and " + _condition_nowhere;
}
String sql = "";
String pageCountSql = "select count(*) from " + joinTable + pc.ConditionStr;
//callback执行beginCount方法
pc.CurrentPage = pc.beginCount( pageCountSql, pc.Pager, _entityInfo );
// TODO：最后一页优化
if (pc.CurrentPage == 1) {
sql = "select " + columnString + " from " + joinTable + pc.ConditionStr + orderString;
sql = _entityInfo.Dialect.GetLimit( sql, pc.Size );
}
else {
StringBuilder sb = new StringBuilder();
getPageSql( pc, columnString, joinTable, isJoin, isCustomerOrder, orderString, _condition_nowhere, sb );
sql = sb.ToString();
}
logger.Info( LoggerUtil.SqlPrefix + "[Page Sql] " + sql );
return sql;
}