hqlページング実装

次は、自分のブログプロジェクトから抜粋したdao実装の具体的なプロセスで、メモを記録します.

package x.dao.impl.article;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import x.ifac.article.ArticlesDao;
import x.impl.BaseDaoImpl;
import x.ArcticleImgs;
import x.repackbean.YearMonthSumCount;
import x.xutil.PageModel;

/**
 * @author   E-mail: [email protected]
 * @category  dao 
 * @version  ：2015-5-24  4:42:55
 */

public class ArticlesDaoImpl extends BaseDaoImpl implements ArticlesDao {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel nListArtcs(final int pageNo, final int pageSize,
			final String obj, final String order) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session
						.createQuery("from " + obj + " order by id " + order)
						.setFirstResult((pageNo - 1) * pageSize)
						.setMaxResults(pageSize).list();
			}

		});
		return new PageModel(list, this.getListAmount(obj), pageSize, pageNo);
	}

	@SuppressWarnings("rawtypes")
	public int getListAmount(String obj) {
		List list = findByHQL("select count(*) from " + obj);
		return (!list.isEmpty()) ? ((Long) list.get(0)).intValue() : 0;
	}

	@SuppressWarnings("rawtypes")
	public List getAllArticleCateList() {

		return findByHQL("from ArticleCate");
	}

	@SuppressWarnings("rawtypes")
	public Object findArticleObjectByCondition(String object, String condition) {
		List list = findByHQL("from " + object + " where " + condition);
		return (list.isEmpty()) ? null : list.get(0);
	}

	@SuppressWarnings("unchecked")
	public List<ArcticleImgs> findArticleImgsByCondition(String articleTitle) {
		return findByHQL("from ArcticleImgs where articTitle='" + articleTitle
				+ "'");
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel findArticleByArticCate(final int cateID, final int pageNo,
			final int pageSize, final String obj, final String order) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session
						.createQuery(
								"from " + obj + " a where  a.articCateID="
										+ cateID + " order by  a.articCateID "
										+ order)
						.setFirstResult((pageNo - 1) * pageSize)
						.setMaxResults(pageSize).list();
			}

		});
		return new PageModel(list,
				this.getListAmount("Article a where  a.articCateID=" + cateID
						+ ""), pageSize, pageNo);
	}

	@SuppressWarnings("rawtypes")
	public List findYear_month_articCount() {
		return getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery query = session
						.createSQLQuery("select  datename(year,artic_createddate) as year,datename(month,artic_createddate) as month,count(*) as sumCount "
								+ "from article  "
								+ "group by datename(year,artic_createddate),datename(month,artic_createddate) "
								+ "order by year,month");
				query.addScalar("year", Hibernate.STRING)
						.addScalar("month", Hibernate.STRING)
						.addScalar("sumCount", Hibernate.INTEGER);
				query.setResultTransformer(Transformers
						.aliasToBean(YearMonthSumCount.class));
				return query.list();
			}
		});

	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel findArticleByYearAndMonth(final String year,
			final String month, final int pageNo, final int pageSize,
			final String obj, final String order) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session
						.createQuery(
								"from "
										+ obj
										+ " a "
										+ "where  datename(year,a.articCreatedDate)='"
										+ year
										+ "' and datename(month,a.articCreatedDate)='"
										+ month
										+ "' "
										+ "order by  datename(year,a.articCreatedDate),datename(month,a.articCreatedDate)  "
										+ order)
						.setFirstResult((pageNo - 1) * pageSize)
						.setMaxResults(pageSize).list();
			}
		});
		return new PageModel(list, getListAmount(obj), pageSize, pageNo);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageModel findArticleByCondition(final String condition,
			final int pageNo, final int pageSize, final String obj,
			final String order) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session
						.createQuery(
								"from " + obj + " as a where "
										+ " a.articCateID.articCate like '%"
										+ condition + "%'"
										//  '.0'
										+ " or (convert(varchar,a.articCreatedDate,120)+'.0') like '%"
										+ condition + "%'"
										+ " or a.articDesc like '%" + condition
										+ "%'" + " or a.articTag like '%"
										+ condition + "%'"
										+ " or a.articTitle like '%"
										+ condition + "%'"
										+ " or a.smallImg like '%" + condition
										+ "%'" + " or a.articImgDesc like '%"
										+ condition + "%' order by a.id "
										+ order)
						.setFirstResult((pageNo - 1) * pageSize)
						.setMaxResults(pageSize).list();
			}
		});
		List list2 = getHibernateTemplate().executeFind(new HibernateCallback() {
			
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session
						.createQuery(
								"from " + obj + " as a where "
										+ " a.articCateID.articCate like '%"
										+ condition + "%'"
										//  '.0'
										+ " or (convert(varchar,a.articCreatedDate,120)+'.0') like '%"
										+ condition + "%'"
										+ " or a.articDesc like '%" + condition
										+ "%'" + " or a.articTag like '%"
										+ condition + "%'"
										+ " or a.articTitle like '%"
										+ condition + "%'"
										+ " or a.smallImg like '%" + condition
										+ "%'" + " or a.articImgDesc like '%"
										+ condition + "%' order by a.id "
										+ order)
										.list();
			}
		});
		return new PageModel(list, list2.size(), pageSize, pageNo);
	}

}