jpa hibernate criteria query

1.JPA和Hibernate分别使用criteria api动态查询的例子

2.只选择表中的某几列的例子

3.选择统计列的例子

相比之下，Hibernate的语法要简洁的多。

 

package cn.cytong.test;

import models.OperatorLine;
import models.Order;
import org.apache.commons.lang.time.DateUtils;
import org.hibernate.FetchMode;
import org.hibernate.Session;
import org.hibernate.criterion.*;
import org.junit.Test;
import play.Logger;
import play.db.jpa.JPA;
import play.test.UnitTest;

import javax.persistence.EntityManager;
import javax.persistence.Tuple;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: chenchi
 * Date: 12-8-25
 * Time: 上午10:27
 * To change this template use File | Settings | File Templates.
 */
public class JpaCriteriaTest extends UnitTest {
    //    @Test
    public void testSelectMultiple() {
        EntityManager em = JPA.em();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Tuple> sq = cb.createTupleQuery();

        Root<Order> root = sq.from(Order.class);
        // define alias to override the alias generated by the framework, In order to be same with the count query
        root.alias("o");
        sq.multiselect(root.get("id").alias("id")
                , root.get("guestName").alias("guestName")
                , root.get("mobile").alias("mobile")
                , root.get("line").get("lineName").alias("lineName"));

        Predicate where = cb.conjunction();
        where = cb.and(where, cb.like(root.<String>get("guestName"), "%%"));
        where = cb.and(where, cb.equal(root.get("startAt"), DateUtils.truncate(new Date(), Calendar.DATE)));
        sq.where(where);

        // count items
        CriteriaQuery<Long> cq = cb.createQuery(Long.class);
        Root<Order> cFrom = cq.from(Order.class);
        cFrom.alias("o");
        cq.select(cb.count(cFrom)).where(where);
        Long count = em.createQuery(cq).getSingleResult();
        Logger.info("count is %d", count);

        //  find items
        List<Tuple> tuples = em.createQuery(sq).getResultList();
        for (Tuple tuple : tuples) {
            Logger.info("id(%s)\tname(%s)\tline(%s)", tuple.get("id"), tuple.get("guestName"), tuple.get("lineName"));
        }
    }

    @Test
    public void testHibernateCriteria() {
        Session session = (Session) JPA.em().getDelegate();
        DetachedCriteria criteria = DetachedCriteria.forClass(Order.class);
        criteria.add(Restrictions.like("guestName", "", MatchMode.ANYWHERE))
                .add(Restrictions.eq("startAt", DateUtils.truncate(new Date(), Calendar.DATE)));

        Long count = (Long) criteria.getExecutableCriteria(session)
                .setProjection(Projections.rowCount())
                .uniqueResult();
        Logger.info("count is %s", count);

        criteria.setProjection(null)
                .setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
        List orders = criteria.getExecutableCriteria(session).list();
        for (Object obj : orders) {
            Order order = (Order) obj;
            Logger.info("id(%s), guest(%s), line(%s)", order.id, order.guestName, order.line.lineName);
        }
    }

    //    @Test
    public void testHibernateMultiple() {
        Session session = (Session) JPA.em().getDelegate();
        DetachedCriteria criteria = DetachedCriteria.forClass(Order.class);

        //  set restrictions
        criteria.add(Restrictions.like("guestName", "", MatchMode.ANYWHERE))
                .add(Restrictions.eq("startAt", DateUtils.truncate(new Date(), Calendar.DATE)));

        //  count
        Long count = (Long) criteria.getExecutableCriteria(session)
                .setProjection(Projections.rowCount())
                .uniqueResult();
        Logger.info("count is %s", count);

        //  select multi fields
        List<Map> rows = criteria.setProjection(Projections.projectionList()
                .add(Property.forName("id").as("id"))
                .add(Property.forName("guestName").as("guest")))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
                .getExecutableCriteria(session)
                .list();

        for (Map row : rows) {
            Logger.info("id(%s), guest(%s)", row.get("id"), row.get("guest"));
        }

    }

    //    @Test
    public void testProjection() {
        Session session = (Session) JPA.em().getDelegate();
        DetachedCriteria lineCriteria = DetachedCriteria.forClass(OperatorLine.class)
                .setProjection(Property.forName("line"))
                .add(Property.forName("operator.id").eq(2L));
        DetachedCriteria criteria = DetachedCriteria.forClass(Order.class)
                .add(Restrictions.eq("startAt", DateUtils.truncate(new Date(), Calendar.DATE)))
                .add(Property.forName("line").in(lineCriteria));

//        Long count = (Long) criteria.getExecutableCriteria(session)
//                .setProjection(Projections.rowCount()).uniqueResult();

        criteria.setProjection(Projections.projectionList()
                .add(Projections.groupProperty("state"))
                .add(Projections.groupProperty("line"))
                .add(Projections.count("state")));

        List list = criteria.getExecutableCriteria(session).list();

//        Logger.info("count is %s", count);
        Logger.info("size is %d", list.size());
    }
}