public static IList FindPage( ObjectInfo state, String queryString )
{
// see: wojilu/wojilu/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;
}