SqlServer分页工具类

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

public String findArticle(TArticle article,Integer pageNo,Integer pageSize,String sortName,String sortOrder,Integer num) {
		
		StringBuffer sql = new StringBuffer(
				"select top 5  a.id,a.title,a.cate_one,m.parent_id,a.cate_two,m.name as cate_two_name,"
						+ "a.content,a.rank,a.check_status,a.check_time,a.creator,a.create_time,a.updator,a.update_time "
						+ "from t_article a left join t_menu m on a.cate_two = m.id");
		
		if (!CommonUtils.isNullOrLessThanOne(pageNo)
				&& !CommonUtils.isNullOrLessThanOne(pageSize)) {
			int start = (pageNo-1)*pageSize;
			int limit = pageSize;
			String tableName = "t_article a left join t_menu m on a.cate_two = m.id";
			String fields = "a.id,a.update_time";
			StringBuffer filter = new StringBuffer("1 = 1");
			if(article != null){
				if (StringUtils.hasLength(article.getTitle()))
					filter.append(" and a.title like '%").append(article.getTitle())
							.append("%'");

				if (article.getCateOne() != null && article.getCateOne() != 0)
					filter.append(" and a.cate_one=").append(article.getCateOne());
				if (article.getCateTwo() != null && article.getCateTwo() != 0)
					filter.append(" and a.cate_two=").append(article.getCateTwo());
				if (article.getCreator() != null)
					filter.append(" and a.creator=").append(article.getCreator());
				if (article.getCheckStatus() != null){
					if(article.getCheckStatus() == -1)
						filter.append(" and a.check_status in (0,1,2)");
					else{
						
						filter.append(" and a.check_status=").append(
								article.getCheckStatus());
					}
					
				}
			}
			StringBuffer orderBy = new StringBuffer("");
			if (StringUtils.hasLength(sortName)
					&& StringUtils.hasLength(sortOrder)) {
				orderBy.append("a.").append(this.getColumnPropertyMap().get(sortName))
				.append(" ").append(sortOrder);
			}
			
			sql = SqlString.getPaginationSql(start,limit,tableName,fields,filter.toString(),orderBy.toString());
		}else{
			if (article != null) {
				sql.append(" where 1=1  ");
				if (StringUtils.hasLength(article.getTitle()))
					sql.append(" and a.title like '%").append(article.getTitle())
							.append("%'");

				if (article.getCateOne() != null)
					sql.append(" and a.cate_one=").append(article.getCateOne());
				if (article.getCateTwo() != null)
					sql.append(" and a.cate_two=").append(article.getCateTwo());
				if (article.getCreator() != null)
					sql.append(" and a.creator=").append(article.getCreator());
				if (article.getCheckStatus() != null)
					sql.append(" and a.check_status=").append(
							article.getCheckStatus());
			}

			if (StringUtils.hasLength(sortName) && StringUtils.hasLength(sortOrder)) {
				sql.append(" order by rank desc,")
						.append(this.getColumnPropertyMap().get(sortName))
						.append(" ").append(sortOrder);
			}
		}
		
		return sql;
	}

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!