//Model 层(Pager.java)
public class Pager<T> {
private List<T> entityList;//分页对象集合
private int totalCounts;//总条数
private int currentPage;//当前页数
private int nextPage; //下一页
private int prePage; //上一页
private int pageCount; //总页数
//get set省略
}
//DAO层,DAO实现(moduleSearchDaoImpl.java)
@Repository(value = "moduleSearchDao")
public class ModuleSearchDaoImpl extends GenericDaoImpl<Module, Long> implements ModuleSearchDao {
@SuppressWarnings("unchecked")
@Override
public List<Object[]> searchCount(String nameValue) {
List<Object[]> list = new ArrayList<Object[]>();
String strsql = "select treeId.paId,count(*) from Module where treeId.name like '%" + nameValue + "%' group by treeId.paId";
list = getSession().createQuery(strsql).list();
return list;
}
/*
* 注:firstResult要为maxResult的整数倍
*/
@SuppressWarnings("unchecked")
@Override
public Pager<Module> searchByPropertyByPager(String propertyName, String nameValue, String orderName, boolean flag, int firstResult, int maxResult) {
Pager<Module> p = new Pager<Module>();
List<Module> moduleList = new ArrayList<Module>();
if (null !=orderName && !"".equals(orderName) && "name".equals(orderName))// 按照中文排序
{
int begin=firstResult;
int end=firstResult+maxResult;
String sql = null;
if (null !=propertyName && !"".equals(propertyName))/// 按类别查询
{
if(flag)//升序排序
sql="select * from (select row_.*, rownum rownum_ from (select m.ID as M_ID,m.DESCS as M_DESCS, m.CREATEDATE as M_CREATEDATE, m.LOCATION as M_LOCATION, m.TREEID as M_TREEID, m.STATUSID as M_STATUSID, m.DATASTATUSID as M_DATASTAT, t.ID as T_ID, t.NAME as T_NAME, t.PAID as T_PAID, t.ICON as T_ICON, d.ID as D_ID, d.NAME as D_NAME, d.TYPE as D_TYPE, d.ORDERS as D_ORDERS from DMM.module m inner join DMM.TREE t on m.TREEID = t.ID left outer join DMM.DATA_DICTIONARY d on m.STATUSID = d.ID where t.PAID=" + new Long(propertyName) + " and t.NAME like '%" + nameValue + "%' order by nlssort(t.NAME,'NLS_SORT=SCHINESE_PINYIN_M') asc) row_ where rownum <= "+end+") where rownum_ > "+begin;
else//降序排序
sql="select * from (select row_.*, rownum rownum_ from (select m.ID as M_ID,m.DESCS as M_DESCS, m.CREATEDATE as M_CREATEDATE, m.LOCATION as M_LOCATION, m.TREEID as M_TREEID, m.STATUSID as M_STATUSID, m.DATASTATUSID as M_DATASTAT, t.ID as T_ID, t.NAME as T_NAME, t.PAID as T_PAID, t.ICON as T_ICON, d.ID as D_ID, d.NAME as D_NAME, d.TYPE as D_TYPE, d.ORDERS as D_ORDERS from DMM.module m inner join DMM.TREE t on m.TREEID = t.ID left outer join DMM.DATA_DICTIONARY d on m.STATUSID = d.ID where t.PAID=" + new Long(propertyName) + " and t.NAME like '%" + nameValue + "%' order by nlssort(t.NAME,'NLS_SORT=SCHINESE_PINYIN_M') desc) row_ where rownum <= "+end+") where rownum_ > "+begin;
} else/// 查询全部
{
if(flag)//升序排序
sql="select * from (select row_.*, rownum rownum_ from (select m.ID as M_ID,m.DESCS as M_DESCS, m.CREATEDATE as M_CREATEDATE, m.LOCATION as M_LOCATION, m.TREEID as M_TREEID, m.STATUSID as M_STATUSID, m.DATASTATUSID as M_DATASTAT, t.ID as T_ID, t.NAME as T_NAME, t.PAID as T_PAID, t.ICON as T_ICON, d.ID as D_ID, d.NAME as D_NAME, d.TYPE as D_TYPE, d.ORDERS as D_ORDERS from DMM.module m inner join DMM.TREE t on m.TREEID = t.ID left outer join DMM.DATA_DICTIONARY d on m.STATUSID = d.ID where t.NAME like '%" + nameValue + "%' order by nlssort(t.NAME,'NLS_SORT=SCHINESE_PINYIN_M') asc) row_ where rownum <= "+end+") where rownum_ > "+begin;
else //降序排序
sql="select * from (select row_.*, rownum rownum_ from (select m.ID as M_ID,m.DESCS as M_DESCS, m.CREATEDATE as M_CREATEDATE, m.LOCATION as M_LOCATION, m.TREEID as M_TREEID, m.STATUSID as M_STATUSID, m.DATASTATUSID as M_DATASTAT, t.ID as T_ID, t.NAME as T_NAME, t.PAID as T_PAID, t.ICON as T_ICON, d.ID as D_ID, d.NAME as D_NAME, d.TYPE as D_TYPE, d.ORDERS as D_ORDERS from DMM.module m inner join DMM.TREE t on m.TREEID = t.ID left outer join DMM.DATA_DICTIONARY d on m.STATUSID = d.ID where t.NAME like '%" + nameValue + "%' order by nlssort(t.NAME,'NLS_SORT=SCHINESE_PINYIN_M') desc) row_ where rownum <= "+end+") where rownum_ > "+begin;
}
List<Object[]> list=getSession().createSQLQuery(sql).list();
//将数据添加到结果对象集中
for(Object[] i:list)
{
Tree t=new Tree();
t.setId(new BigDecimal(i[7].toString()).longValue());//i[7]:t.ID
t.setName(i[8].toString());//i[8]:t.NAME
t.setPaId(new BigDecimal(i[9].toString()).longValue());//i[9]:t.PAID
t.setIcon(i[10].toString());//i[10]:t.ICON
DataDictionary d=new DataDictionary();
d.setId(new BigDecimal(i[11].toString()).longValue());//i[11]:d.ID
d.setName(i[12].toString());//i[12]:d.NAME
d.setType(new BigDecimal(i[13].toString()).intValue());//i[13]:d.TYPE
d.setOrders(new BigDecimal(i[14].toString()).intValue());//i[14]:d.ORDERS
Module m=new Module();
m.setId(new BigDecimal(i[0].toString()).longValue());//i[0]:m.ID
if(i[1]==null)
m.setDescs("");//i[1]:m.DESCS
else
m.setDescs(i[1].toString());//i[1]:m.DESCS
if(i[2]==null)
m.setCreateDate(Timestamp.valueOf("1970-01-01 00:00:00"));//i[2]:m.CREATEDATE
else
m.setCreateDate((Timestamp)i[2]);//i[2]:m.CREATEDATE
if(i[3]==null)
m.setLocation("");//i[3]:m.LOCATION
else
m.setLocation(i[3].toString());//i[3]:m.LOCATION
m.setTreeId(t);
m.setStatusId(d);
moduleList.add(m);
}
}
else// 按照其他条件排序
{
Criteria criteria = getSession().createCriteria(Module.class);
criteria.createAlias("treeId", "tree");
if (StringUtils.isNotBlank(propertyName)) {
criteria.add(Restrictions.eq("tree.paId", new Long(propertyName)));
}
if (StringUtils.isNotBlank(nameValue)) {
criteria.add(Restrictions.like("tree.name", "%" + nameValue + "%"));
}
if (null != orderName && !"".equals(orderName)) {
if (flag)
criteria.addOrder(Order.asc(orderName));
else
criteria.addOrder(Order.desc(orderName));
}
if (maxResult > 0) {
criteria.setFirstResult(firstResult);
criteria.setMaxResults(maxResult);
}
criteria.setCacheable(true);
moduleList = criteria.list();//得到查询结果
}
//设置结果集
p.setEntityList(moduleList);
//设置总条数
Integer count=findAllCount(propertyName, nameValue);
p.setTotalCounts(count);
//设置总页数
if(maxResult>0)
{
Integer pageCount=new Integer(0);
if(count%maxResult==0)//总条数/每页显示数 刚好整除
{
pageCount=(Integer)count/maxResult;
}
else
{
pageCount=(Integer)count/maxResult+1;
}
p.setPageCount(pageCount);
}
//设置当前页数
if(maxResult>0)
{
Integer currentPage=new Integer(0);
if(firstResult%maxResult==0)//开始条数为每页显示条数的倍数
{
currentPage=(Integer)firstResult/maxResult+1;
}
p.setCurrentPage(currentPage);
}
return p;
}
//查询记录总条数
private Integer findAllCount(String propertyName, String nameValue) {
Criteria criteria = getSession().createCriteria(Module.class);
criteria.createAlias("treeId", "tree");
if (StringUtils.isNotBlank(propertyName)) {
criteria.add(Restrictions.eq("tree.paId", new Long(propertyName)));
}
if (StringUtils.isNotBlank(nameValue)) {
criteria.add(Restrictions.like("tree.name", "%" + nameValue + "%"));
}
Object countResult = criteria.setProjection(Projections.count("id"))
.uniqueResult();
if (null != countResult) {
return Integer.parseInt(countResult.toString());
}
return 0;
}
}
Hibernate分页排序
最新推荐文章于 2021-03-04 15:21:58 发布