首页 > Java基础 > Criteria的内链接,分页,查询唯一对象,条件查询,分组统计,投影查询,DetachedCriteria
2018
03-23

Criteria的内链接,分页,查询唯一对象,条件查询,分组统计,投影查询,DetachedCriteria

Restrictions.like(属性名, 查询条件的值, 匹配方式):

Restrictions.in(属性名, 查询条件的值, 匹配方式):

Restrictions.eq(属性名, 查询条件的值, 匹配方式):

 

Criteria Query常用的查询限制方法


Restrictions.eq() equal,=
Restrictions.allEq() 参数为Map对象,使用key/value进行多个等于的对比,相当于多个Restrictions.eq()的效果
Restrictions.gt() greater-than, >
Restrictions.lt() less-than, <
Restrictions.le() less-equal, <=
Restrictions.between() 对应sql的between子句
Restrictions.like() 对应SQL的like子句
Restrictions.in() 对应SQL的in子句
Restrictions.and() and关系
Restrictions.or() or关系
Restrictions.isNull() 判断属性是否为空,为空返回true,否则返回false
Restrictions.isNotNull() 与Restrictions.isNull()相反
Order.asc() 根据传入的字段进行升序排序
Order.desc() 根据传入的字段进行降序排序
MatchMode.EXACT 字符串精确匹配,相当于“like 'value'”
MatchMode.ANYWHERE 字符串在中间位置,相当于“like '%value%'”
MatchMode.START 字符串在最前面的位置,相当于“like 'value%'”
MatchMode.END 字符串在最后面的位置,相当于“like '%value'”

Criteria接口提供了设置分页的方法

setFirstResult(intfirstResult)
setMaxResults(intmaxResult)
4.查询唯一对象
Empemp= (Emp)session.createCriteria(Emp.class)
        .add(Restrictions.isNotNull("salary"))
        .addOrder(Order.desc("salary"))
        .setMaxResults(1)
        .uniqueResult();
   方法 说明                             
    Query Criterialist()        返回List集合                 
iterate() 返回Iterator迭代器,只查询出ID值。                
uniqueResult() 返回唯一对象                                 

Criteria接口提供了createCriteria()和createAlias()方法建立内连接
List<Emp>list =session.createCriteria(Emp.class)
        .add(Restrictions.ilike("empName","a",MatchMode.ANYWHERE))
        .createCriteria("dept")
        .add(Restrictions.eq("deptName","财务部").ignoreCase()).list();
List<Emp>list =session.createCriteria(Emp.class,"e")
        .createAlias("dept","d")
        .add(Restrictions.ilike("e.empName","a",
                MatchMode.ANYWHERE))
        .add(Restrictions.eq("d.deptName","财务部").ignoreCase()).list();

Hibernate提供了org.hibernate.criterion.Projection接口和org.hibernate.criterion.Projections类来支持Criteria投影

List<Object[]> list = session
        .createCriteria(Emp.class)
        .setProjection(
                Projections.projectionList()
                        .add(Property.forName("empName"))
                        .add(Property.forName("hiredate")))
       .list();
List<String> list = session.createCriteria(Dept.class)
        .setProjection(Property.forName("deptName")).list();

DetachedCriteria

DetachedCriteria和Criteria功能类似,它实现了CriteriaSpecification接口

Criteria是由Session对象创建的

DetachedCriteria创建时不需要Session对象

使用DetachedCriteria来构造查询条件

可以把DetachedCriteria作为方法参数传递给业务层

DetachedCriteria detachedCriteria = DetachedCriteria
        .forClass(Emp.class, "e")
        .createAlias("e.dept", "d")
        .add(Restrictions.eq("d.deptName", "财务部"))
        .add(Restrictions.ilike("e.empName", "a",
                MatchMode.ANYWHERE));
List<Emp> list = detachedCriteria.getExecutableCriteria(session)
        .list();
DetachedCriteria avgSalary = DetachedCriteria.forClass(Emp.class,
        "e").setProjection(Property.forName("salary").avg());
List<Emp> list = session.createCriteria(Emp.class)
        .add(Property.forName("salary").gt(avgSalary)).list();
================Criteria分页加动态查询方法======================================
public PageInfo<Auction> select(Auction condition, int pageIndex){

DetachedCriteria dc = DetachedCriteria.forClass(Auction.class);
Criteria c = session.createCriteria(Auction.class);//建立一次查询
Criteria c1 = session.createCriteria(Auction.class);//建立一次查询

c.addOrder(Order.desc("auctionstarttime"));//添加查询条件-----------注意非空处理----------排序
c.add(Restrictions.ge("auctionstartprice",condition.getAuctionstartprice()));----------比较大小
c.add(Restrictions.ilike("auctiondesc", condition.getAuctiondesc(),MatchMode.ANYWHERE));----------模糊查询

Set<Integer> settype=new HashSet<Integer>();c.add(Restrictions.in( "movId",settype)); //--------编号在集合数组中

PageInfo<Auction> pageInfo = new PageInfo<Auction>();//实例分页方法
int count = (Integer) c1.setProjection(Projections.rowCount()).uniqueResult();//得到总条数
pageInfo.setCount(count);//设置总条数
pageInfo.setPageIndex(pageIndex);//设置当前页码
c.setMaxResults(PageInfo.PAGESIZE);//设置每次的查询数量
c.setFirstResult((pageIndex - 1) * PageInfo.PAGESIZE);//设置起始条数
List<Auction> list = c.list();
pageInfo.setPageList(list);//将查询的结果付给查询的实体类
}
===============================分页实体类======================================
public class PageInfo<T> { //利用泛型声明一个分页的实体类
public static final int PAGESIZE = 10;
private Integer count;// 总记录数
private List<T> pageList;// 当前页的记录集合
private Integer pageIndex;// 当前页号
private Integer totalPages;// 总页数

public Integer getTotalPages() {//特殊方法,通过总条数和每页的条数得到页数
this.totalPages = this.count / this.PAGESIZE;
if (this.count % this.PAGESIZE != 0)
this.totalPages++;
return this.totalPages;
}
}
===============================分页关于时间的处理======================================
---映射文件-------关于时间的处理---------------
         <property name="auctionstarttime" type="java.sql.Timestamp">
            <column name="AUCTIONSTARTTIME" length="11" not-null="true" />
        </property>

---实体类-------关于时间的处理---------------
        private Timestamp auctionstarttime;、

---数据库-------关于时间的处理---------------
        AUCTIONSTARTTIME             TIMESTAMP(6)
---方法内使用-------关于时间的处理---------------
======表单赋值
<label for="time">开始时间</label>
<input name="auctionStartTime" type="text" id="time" class="nwinput"/>
======提取:输入的时间按字符串转化为date,后提取毫秒数,转化为Java.sql.Timestamp
           if(request.getParameter("auctionStartTime")!=null&&!"".equals(request.getParameter("auctionStartTime"))){
condition.setAuctionstarttime(new java.sql.Timestamp(Tool.strToDate(request.getParameter("auctionStartTime"), "yyyy-MM-dd HH:mm:dd").getTime()));
}
======底层
     if (condition.getAuctionstarttime() != null) {
c.add(Restrictions.ge("auctionstarttime",
condition.getAuctionstarttime()));
c1.add(Restrictions.ge("auctionstarttime",
condition.getAuctionstarttime()));
}


===============================servlet的调用方法以及获得返回值======================================


int pageIndex=1;//登陆界面进入
if(request.getParameter("pageIndex")!=null){
pageIndex=new Integer(request.getParameter("pageIndex")).intValue();//
}Auction condition=new Auction();

PageInfo<Auction> auctionPageinfo = biz.find(condition,pageIndex);
request.setAttribute("auctionPageInfo", auctionPageinfo);
request.getRequestDispatcher("auctionList.jsp").forward(request, response);




===============================jsp页面======================================


---------页码显示------------------------------------------------------------------------------
       <a href="javascript:goToPage(1)">首页</a>
        <c:if test="${auctionPageinfo.pageIndex!=1}">
        <a href="javascript:goToPage(${auctionPageinfo.pageIndex-1 })">上一页</a>
        </c:if>&nbsp;&nbsp;&nbsp;&nbsp;
         当前页:${auctionPageinfo.pageIndex}&nbsp;&nbsp;&nbsp;&nbsp;
         
        <c:forEach step="1" begin="1" end="${auctionPageInfo.totalPages}" var="pageIndex">
        <a href="javascript:goToPage(${pageIndex})">${pageIndex}</a>
        </c:forEach> 
      
        <c:if test="${auctionPageinfo.pageIndex!=auctionPageinfo.totalPages}">
        <a href="javascript:goToPage(${auctionPageinfo.pageIndex+1 })">下一页</a>
        </c:if>
        <a href="javascript:goToPage(${auctionPageinfo.totalPages})" >尾页</a>


------------------
<script type="text/javascript">
function goToPage(pageIndex){
document.forms[0].action=document.forms[0].action+"?pageIndex="+pageIndex;
document.forms[0].submit();
}
</script>
---------动态查询表单------------------------------------------------------------------------------
<form action="goodservlet?opr=list&ad=u" method="post">
    <label for="name">名称</label>
   <input name="auctionname" type="text" class="nwinput" id="name" value="${param.auctionname}"/>
       <label for="names">描述</label>
       <input name="auctiondesc" type="text" id="names" class="nwinput" value="${param.auctiondesc}"/>
       <label for="time">开始时间</label>
       <input name="auctionstarttime" type="text" id="time" class="nwinput"  value="${param.auctionstarttime}"/>
       <label for="end-time">结束时间</label>
       <input name="auctionendtime" type="text" id="end-time" class="nwinput"  value="${param.auctionendtime}"/>
       <label for="price">起拍价</label>
   <input name="auctionstartprice" type="text" id="price" class="nwinput" value="${param.auctionstartprice}"/>
   <input name="" type="submit"  value="查询" class="spbg buttombg f14  sale-buttom"/>
    </form>


---------详细信息显示------------------------------------------------------------------------------
 <c:forEach items="${requestScope.auctionPageInfo.pageList }" var="auction">
      <ul class="rows">
        <li>${auction.auctionname }</li>
        <li class="list-wd">${auction.auctiondesc }</li>
        <li>${auction.auctionstarttime }</li>
        <li>${auction.auctionendtime }</li>
        <li>${auction.auctionstartprice }</li>
        <li class="borderno red">
        <c:if test="${sessionScope.user.userisadmin==true }">
            修改|
          删除
          </c:if>
        <c:if test="${sessionScope.user.userisadmin==false }">
          <a href="auctionDetail?auctionId=${auction.auctionid }">竞拍</a>
          </c:if>
        </li>
      </ul>
      </c:forEach>


本文》有 0 条评论

留下一个回复