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; }