欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SqlServer分页工具类

程序员文章站 2024-02-12 20:33:22
...

无详细内容 无 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;
	}