SqlServer分页工具类

无详细内容 无 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.n

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