Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to use JPA for CriteriaQuery query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Notes for using JPA CriteriaQuery query 1.pojo class @ Entity@Table (name = "report_workload") @ JsonIgnoreProperties ({"hibernateLazyInitializer", "handler"}) @ JsonIdentityInfo (generator = JSOGGenerator.class) public class ReportWorkload {private int id; private Integer flowWorkItemApprId; private Integer busId; private Integer deptId; private Integer staffId; private Integer busiValueIndustryId; private Integer busiValueScaleId; private String taskName; private Integer count; private BigDecimal amount; private Date approvalTime; private String reportTime Private String deptName; private String staffName; @ Id @ Column (name = "id") @ GeneratedValue (strategy = GenerationType.AUTO) public int getId () {return id;} public void setId (int id) {this.id = id;} @ Basic @ Column (name = "flow_work_item_appr_id") public Integer getFlowWorkItemApprId () {return flowWorkItemApprId } public void setFlowWorkItemApprId (Integer flowWorkItemApprId) {this.flowWorkItemApprId = flowWorkItemApprId;} @ Basic @ Column (name = "bus_id") public Integer getBusId () {return busId;} public void setBusId (Integer busId) {this.busId = busId;} @ Basic @ Column (name = "dept_id") public Integer getDeptId () {return deptId } public void setDeptId (Integer deptId) {this.deptId = deptId;} @ Basic @ Column (name = "staff_id") public Integer getStaffId () {return staffId;} public void setStaffId (Integer staffId) {this.staffId = staffId;} @ Basic @ Column (name = "busi_value_industry_id") public Integer getBusiValueIndustryId () {return busiValueIndustryId } public void setBusiValueIndustryId (Integer busiValueIndustryId) {this.busiValueIndustryId = busiValueIndustryId;} @ Basic @ Column (name = "busi_value_scale_id") public Integer getBusiValueScaleId () {return busiValueScaleId;} public void setBusiValueScaleId (Integer busiValueScaleId) {this.busiValueScaleId = busiValueScaleId;} @ Basic @ Column (name = "task_name") public String getTaskName () {return taskName } public void setTaskName (String taskName) {this.taskName = taskName;} @ Basic @ Column (name = "count") public Integer getCount () {return count;} public void setCount (Integer count) {this.count = count;} @ Basic @ Column (name = "amount") public BigDecimal getAmount () {return amount } public void setAmount (BigDecimal amount) {this.amount = amount;} @ Basic @ Column (name = "approval_time") public Date getApprovalTime () {return approvalTime;} public void setApprovalTime (Date approvalTime) {this.approvalTime = approvalTime;} @ Basic @ Column (name = "report_time") public String getReportTime () {return reportTime } public void setReportTime (String reportTime) {this.reportTime = reportTime;} @ Transient public String getDeptName () {return deptName;} public void setDeptName (String deptName) {this.deptName = deptName;} @ Transient public String getStaffName () {return staffName;} public void setStaffName (String staffName) {this.staffName = staffName } @ Override public boolean equals (Object o) {if (this = = o) return true; if (! (o instanceof ReportWorkload)) return false; ReportWorkload that = (ReportWorkload) o; return id = = that.id;} @ Override public int hashCode () {return id } public ReportWorkload (int id, Integer flowWorkItemApprId, Integer busId, Integer deptId, Integer staffId, Integer busiValueIndustryId, Integer busiValueScaleId, String taskName, Long count, BigDecimal amount, Date approvalTime, String reportTime) {this.id = id; this.flowWorkItemApprId = flowWorkItemApprId; this.busId = busId This.deptId = deptId; this.staffId = staffId; this.busiValueIndustryId = busiValueIndustryId; this.busiValueScaleId = busiValueScaleId; this.taskName = taskName; this.count = Integer.parseInt (count+ ""); / / this.count = count; this.amount = amount; this.approvalTime = approvalTime; this.reportTime = reportTime;} public ReportWorkload () {}}

When summing the aggregate function sum, it turns out that int will be automatically promoted to long, and the following error will be reported without special processing:

Org.hibernate.hql.internal.ast.DetailedSemanticException: Unable to locate appropriate constructor on class [com.changfa.frame.data.entity.report.Report Workload]. Expected arguments are: int, java.lang.String, long, java.math.BigDecimal, java.util.Date, java.lang.String at org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor (ConstructorNode.java:182) at org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare (ConstructorNode.java:144) at org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor (HqlSqlWalker.java:1092) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr (HqlSqlBaseWalker.java:2359)

It will prompt you that the type returned by the query database does not correspond to your constructor type.

Service layer

Load the EntityManager through annotations:

@ PersistenceContext private EntityManager em; query method public List reportworkloadsearch (String reportTime, String deptId, String staffId, String typeId, String industryId) {List reportWorkloadList = new ArrayList (); CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder (); CriteriaQuery cq = criteriaBuilder.createQuery (ReportWorkload.class); Root rt = cq.from (ReportWorkload.class) Cq.multiselect (rt.get ("id"), rt.get ("flowWorkItemApprId"), rt.get ("busId"), rt.get ("deptId"), rt.get ("staffId"), rt.get ("busiValueIndustryId"), rt.get ("busiValueScaleId"), rt.get ("taskName"), criteriaBuilder.sum (rt.get ("count")) CriteriaBuilder.sum (rt.get ("amount")), rt.get ("approvalTime"), rt.get ("reportTime")) If (reportTimestone = "") {cq.where (criteriaBuilder.equal (rt.get ("reportTime"), reportTime));} if (criteriaBuilder.equal (rt.get ("deptId"), Integer.parseInt (deptId) } if {cq.where (criteriaBuilder.equal (rt.get ("staffId"), Integer.parseInt (staffId) {cq.where (rt.get ("typeId"), Integer.parseInt (typeId) } if (industry busId = "") {cq.where (criteriaBuilder.equal ("industryId"), Integer.parseInt (industryId));} cq.groupBy (rt.get ("busId"), rt.get ("deptId"), rt.get ("taskName"); reportWorkloadList = em.createQuery (cq). GetResultList (); return reportWorkloadList;}

When customizing the return field in cq.multiselect, you must give a corresponding return field constructor in the corresponding pojo

Encapsulate JPA dynamic query (CriteriaQuery)

A piece of code encapsulated by JPA dynamic query (CriteriaQuery):

Package com.platform.framework.dao.jpa; import java.io.Serializable;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map; import javax.persistence.EntityManager;import javax.persistence.criteria.CriteriaBuilder;import javax.persistence.criteria.CriteriaBuilder.In;import javax.persistence.criteria.CriteriaQuery;import javax.persistence.criteria.Order;import javax.persistence.criteria.Predicate;import javax.persistence.criteria.Root Import org.apache.log4j.Logger; / * Query base class

* * @ describe: encapsulates JPA CriteriaBuilder query criteria * @ author:lry * @ since:2014-05-23 * / @ SuppressWarnings ({"unused", "unchecked", "rawtypes", "null", "hiding"}) public class Query implements Serializable {private static final long serialVersionUID = 5064932771068929342L; private static Logger log = Logger.getLogger (Query.class); private EntityManager entityManager / * * Model objects to be queried * / private Class clazz; / * * query condition list * / private Root from; private List predicates; private CriteriaQuery criteriaQuery; private CriteriaBuilder criteriaBuilder; / * * sort method list * / private List orders; / * * Association Mode * / private Map subQuery Private Map linkQuery; private String projection; / * * or condition * / private List orQuery; private String groupBy; private Query () {} private Query (Class clazz, EntityManager entityManager) {this.clazz = clazz; this.entityManager = entityManager; this.criteriaBuilder = this.entityManager.getCriteriaBuilder () This.criteriaQuery = criteriaBuilder.createQuery (this.clazz); this.from = criteriaQuery.from (this.clazz); this.predicates = new ArrayList (); this.orders = new ArrayList () } / * * create query conditions through classes * / public static Query forClass (Class clazz, EntityManager entityManager) {return new Query (clazz, entityManager) } / * * add subquery * / private void addSubQuery (String propertyName, Query query) {if (this.subQuery = = null) this.subQuery = new HashMap (); if (query.projection = = null) throw new RuntimeException ("subquery field not set") This.subQuery.put (propertyName, query);} private void addSubQuery (Query query) {addSubQuery (query.projection, query);} / * * add Association query * / public void addLinkQuery (String propertyName, Query query) {if (this.linkQuery = = null) this.linkQuery = new HashMap () This.linkQuery.put (propertyName, query);} / * * equivalent * / public void eq (String propertyName, Object value) {if (isNullOrEmpty (value)) return; this.predicates.add (criteriaBuilder.equal (from.get (propertyName), value)) } private boolean isNullOrEmpty (Object value) {if (value instanceof String) {return value = = null | | ".equals (value);} return value = = null;} public void or (List propertyName, Object value) {if (isNullOrEmpty (value)) return If ((propertyName = = null) | | (propertyName.size () = = 0)) return; Predicate predicate = criteriaBuilder.or (criteriaBuilder.equal (from.get (propertyName.get (0)), value)); for (int I = 1; I

< propertyName.size(); ++i) predicate = criteriaBuilder.or(predicate, criteriaBuilder.equal(from.get(propertyName.get(i)), value)); this.predicates.add(predicate); } public void orLike(List propertyName, String value) { if (isNullOrEmpty(value) || (propertyName.size() == 0)) return; if (value.indexOf("%") < 0) value = "%" + value + "%"; Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(from.get(propertyName.get(0)), value.toString())); for (int i = 1; i < propertyName.size(); ++i) predicate = criteriaBuilder.or(predicate, criteriaBuilder.like(from.get(propertyName.get(i)), value)); this.predicates.add(predicate); } /** 空 */ public void isNull(String propertyName) { this.predicates.add(criteriaBuilder.isNull(from.get(propertyName))); } /** 非空 */ public void isNotNull(String propertyName) { this.predicates.add(criteriaBuilder.isNotNull(from.get(propertyName))); } /** 不相等 */ public void notEq(String propertyName, Object value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.notEqual(from.get(propertyName), value)); } /** * not in * * @param propertyName * 属性名称 * @param value * 值集合 */ public void notIn(String propertyName, Collection value) { if ((value == null) || (value.size() == 0)) { return; } Iterator iterator = value.iterator(); In in = criteriaBuilder.in(from.get(propertyName)); while (iterator.hasNext()) { in.value(iterator.next()); } this.predicates.add(criteriaBuilder.not(in)); } /** * 模糊匹配 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void like(String propertyName, String value) { if (isNullOrEmpty(value)) return; if (value.indexOf("%") < 0) value = "%" + value + "%"; this.predicates.add(criteriaBuilder.like(from.get(propertyName), value)); } /** * 时间区间查询 * * @param propertyName * 属性名称 * @param lo * 属性起始值 * @param go * 属性结束值 */ public void between(String propertyName, Date lo, Date go) { if (!isNullOrEmpty(lo) && !isNullOrEmpty(go)) { this.predicates.add(criteriaBuilder.between(from.get(propertyName), lo, go)); } // if (!isNullOrEmpty(lo) && !isNullOrEmpty(go)) { // this.predicates.add(criteriaBuilder.lessThan(from.get(propertyName), // new DateTime(lo).toString())); // } // if (!isNullOrEmpty(go)) { // this.predicates.add(criteriaBuilder.greaterThan(from.get(propertyName), // new DateTime(go).toString())); // } } public void between(String propertyName, Number lo, Number go) { if (!(isNullOrEmpty(lo))) ge(propertyName, lo); if (!(isNullOrEmpty(go))) le(propertyName, go); } /** * 小于等于 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void le(String propertyName, Number value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.le(from.get(propertyName), value)); } /** * 小于 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void lt(String propertyName, Number value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.lt(from.get(propertyName), value)); } /** * 大于等于 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void ge(String propertyName, Number value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.ge(from.get(propertyName), value)); } /** * 大于 * * @param propertyName * 属性名称 * @param value * 属性值 */ public void gt(String propertyName, Number value) { if (isNullOrEmpty(value)) { return; } this.predicates.add(criteriaBuilder.gt(from.get(propertyName), value)); } /** * in * * @param propertyName * 属性名称 * @param value * 值集合 */ public void in(String propertyName, Collection value) { if ((value == null) || (value.size() == 0)) { return; } Iterator iterator = value.iterator(); In in = criteriaBuilder.in(from.get(propertyName)); while (iterator.hasNext()) { in.value(iterator.next()); } this.predicates.add(in); } /** 直接添加JPA内部的查询条件,用于应付一些复杂查询的情况,例如或 */ public void addCriterions(Predicate predicate) { this.predicates.add(predicate); } /** * 创建查询条件 * * @return JPA离线查询 */ public CriteriaQuery newCriteriaQuery() { criteriaQuery.where(predicates.toArray(new Predicate[0])); if (!isNullOrEmpty(groupBy)) { criteriaQuery.groupBy(from.get(groupBy)); } if (this.orders != null) { criteriaQuery.orderBy(orders); } addLinkCondition(this); return criteriaQuery; } private void addLinkCondition(Query query) { Map subQuery = query.linkQuery; if (subQuery == null) return; for (Iterator queryIterator = subQuery.keySet().iterator(); queryIterator.hasNext();) { String key = (String) queryIterator.next(); Query sub = (Query) subQuery.get(key); from.join(key); criteriaQuery.where(sub.predicates.toArray(new Predicate[0])); addLinkCondition(sub); } } public void addOrder(String propertyName, String order) { if (order == null || propertyName == null) return; if (this.orders == null) this.orders = new ArrayList(); if (order.equalsIgnoreCase("asc")) this.orders.add(criteriaBuilder.asc(from.get(propertyName))); else if (order.equalsIgnoreCase("desc")) this.orders.add(criteriaBuilder.desc(from.get(propertyName))); } public void setOrder(String propertyName, String order) { this.orders = null; addOrder(propertyName, order); } public Class getModleClass() { return this.clazz; } public String getProjection() { return this.projection; } public void setProjection(String projection) { this.projection = projection; } public Class getClazz() { return this.clazz; } public List getOrders() { return orders; } public void setOrders(List orders) { this.orders = orders; } public EntityManager getEntityManager() { return this.entityManager; } public void setEntityManager(EntityManager em) { this.entityManager = em; } public Root getFrom() { return from; } public List getPredicates() { return predicates; } public void setPredicates(List predicates) { this.predicates = predicates; } public CriteriaQuery getCriteriaQuery() { return criteriaQuery; } public CriteriaBuilder getCriteriaBuilder() { return criteriaBuilder; } public void setFetchModes(List fetchField, List fetchMode) { } public String getGroupBy() { return groupBy; } public void setGroupBy(String groupBy) { this.groupBy = groupBy; } } insert update delete package com.platform.framework.dao.jpa; import javax.persistence.EntityManager;import javax.persistence.EntityManagerFactory;import javax.persistence.EntityTransaction; import org.apache.log4j.Logger;import org.aspectj.lang.ProceedingJoinPoint;import org.aspectj.lang.Signature; /** * @describe JPA事务管理 * @author lry * @since：2014-05-23 * */public class TransactionHandler { private static final Logger log = Logger .getLogger(TransactionHandler.class); private String[] txmethod;// 配置事务的传播特性方法 private EntityManagerFactory entityManagerFactory;// JPA工厂 public Object exec(ProceedingJoinPoint point) throws Throwable { Signature signature = point.getSignature(); log.debug(point.getTarget().getClass().getName() + "." + signature.getName() + "()"); Boolean isTransaction = false; for (String method : txmethod) { if (signature.getName().startsWith(method)) {// 以method开头的方法打开事务 isTransaction = true; break; } } // JPA->

Hibernate if (point.getTarget () instanceof EntityManagerFactoryProxy) {/ / get the delegated object EntityManagerFactoryProxy emfp = (EntityManagerFactoryProxy) point.getTarget (); EntityManager em = emfp.getEntityManager () If (em! = null) {/ / ignore return point.proceed () if the object already has return point.proceed ();} else {em = entityManagerFactory.createEntityManager () } log.debug ("JPA- > Hibernate open connection..."); if (isTransaction) {EntityTransaction t = null Try {/ / Open the connection and open the transaction log.debug ("JPA- > Hibernate begin transaction..."); t = em.getTransaction () If (! t.isActive ()) t.begin (); emfp.setEntityManager (em); Object obj = point.proceed () / / commit transaction log.debug ("JPA- > Hibernate commit..."); t.commit (); return obj } catch (Exception e) {if (t! = null) {log.debug ("JPA- > Hibernate error...") Rollback... "+ e.getMessage ()) T.rollback ();} e.printStackTrace (); throw e } finally {if (em! = null & & em.isOpen ()) {/ / close the connection em.close (); log.debug ("JPA- > Hibernate close connection...") } emfp.setEntityManager (null);}} else {try {emfp.setEntityManager (em) Return point.proceed ();} catch (Exception e) {log.debug ("JPA- > Hibernate error..." + e.getMessage ()); e.printStackTrace () Throw e;} finally {if (em! = null & & em.isOpen ()) {/ / close the connection em.close () Log.debug ("JPA- > Hibernate close connection...");} emfp.setEntityManager (null) } else {return point.proceed ();}} public String [] getTxmethod () {return txmethod;} public void setTxmethod (String [] txmethod) {this.txmethod = txmethod } public void setEntityManagerFactory (EntityManagerFactory entityManagerFactory) {this.entityManagerFactory = entityManagerFactory;}} EntityManager Manager to manage package com.platform.framework.dao.jpa; import java.util.Collection; import javax.persistence.EntityManager;import javax.persistence.EntityManagerFactory through spring / * EntityManager Manager * * @ author:yangjian1004 * @ since:2011-11-30 16:14:24 AM * / public class EntityManagerFactoryProxy {private static ThreadLocal emThreadLocal = new ThreadLocal (); private static EntityManagerFactory emf; public void setEmf (EntityManagerFactory emf) {EntityManagerFactoryProxy.emf = emf;} public static EntityManagerFactory getEmf () {return emf } public EntityManager getEntityManager () {return emThreadLocal.get ();} public void setEntityManager (EntityManager em) {emThreadLocal.set (em) } / * create query condition * * @ param name * Field name * @ param values * Field value * / public String createInCondition (String name Collection values) {if (values = = null | | values.size () = = 0) {return "11" } StringBuffer sb = new StringBuffer (); sb.append (name + "in ("); for (String id: values) {sb.append ("" + id + ",");} String hsqlCondition = sb.substring (0, sb.length ()-1) + ")" Return hsqlCondition;}} Page pagination and result wrapper class package com.platform.framework.dao.jpa; import java.io.Serializable;import java.util.ArrayList;import java.util.List; / * Page base class

* * @ describe: pagination * / public class Page implements Serializable {private static final long serialVersionUID = 665620345605746930L; / * Total number of entries * / private int count; / * Page number * / private int pageNo; / * * how many pages are displayed per page * / private int rowsPerPage; / * * Total number of pages * / private int totalPageCount / * * starting number * / private int firstRow; / * * ending number * / private int lastRow; / * * result in the form of query result set * / private List result; / * * result in the form of query result object * / public Object obj; public Integer code; / / return code private boolean success = true Private String message; public Page () {} public Page (List list) {this (list.size (), 1, list.size (), list);} public Page (int count, int pageNo, int rowsPerPage, List result) {if (rowsPerPage)

< 1) { rowsPerPage = 1; } this.count = count; this.pageNo = pageNo; this.result = result; this.rowsPerPage = rowsPerPage; if (this.result == null) this.result = new ArrayList(); totalPageCount = count / rowsPerPage; if (count - (count / rowsPerPage) * rowsPerPage >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.