使用JPA進(jìn)行CriteriaQuery進(jìn)行查詢的注意事項(xiàng)
更新時(shí)間：2021年12月07日 10:25:54 作者：Pionner17
這篇文章主要介紹了使用JPA進(jìn)行CriteriaQuery進(jìn)行查詢的注意事項(xiàng)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教
使用JPA CriteriaQuery查詢的注意事項(xiàng)
1.pojo類
@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() {
}
}
在進(jìn)行聚合函數(shù)sum求和時(shí)，原來是int會(huì)自動(dòng)提升為long,不做特殊處理就會(huì)報(bào)以下錯(cuò)誤了：
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, int, int, int, int, int, 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)
會(huì)提示你查詢數(shù)據(jù)庫返回的類型和你的構(gòu)造函數(shù)類型對(duì)應(yīng)不上。
service層
通過注解將EntityManager加載進(jìn)來：
@PersistenceContext private EntityManager em;
查詢方法
public List<ReportWorkload> reportworkloadsearch(String reportTime, String deptId, String staffId, String typeId, String industryId) {
List<ReportWorkload> reportWorkloadList = new ArrayList<>();
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<ReportWorkload> cq = criteriaBuilder.createQuery(ReportWorkload.class);
Root<ReportWorkload> 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(reportTime!=null&&reportTime!=""){
cq.where(criteriaBuilder.equal(rt.get("reportTime"), reportTime));
}
if(deptId!=null&&deptId!=""){
cq.where(criteriaBuilder.equal(rt.get("deptId"), Integer.parseInt(deptId)));
}
if(staffId!=null&&staffId!=""){
cq.where(criteriaBuilder.equal(rt.get("staffId"), Integer.parseInt(staffId)));
}
if(typeId!=null&&typeId!=""){
cq.where(criteriaBuilder.equal(rt.get("typeId"), Integer.parseInt(typeId)));
}
if(industryId!=null&&industryId!=""){
cq.where(criteriaBuilder.equal(rt.get("industryId"), Integer.parseInt(industryId)));
}
cq.groupBy(rt.get("busId"),rt.get("deptId"),rt.get("taskName"));
reportWorkloadList = em.createQuery(cq).getResultList();
return reportWorkloadList;
}
在進(jìn)行cq.multiselect自定義返回字段時(shí)，必須在對(duì)應(yīng)的pojo中給一個(gè)對(duì)應(yīng)的返回字段構(gòu)造函數(shù)
封裝JPA動(dòng)態(tài)查詢（CriteriaQuery）
JPA動(dòng)態(tài)查詢（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基類<br>
*
* @describe：封裝JPA CriteriaBuilder查詢條件
* @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;
/** 要查詢的模型對(duì)象 */
private Class clazz;
/** 查詢條件列表 */
private Root from;
private List<Predicate> predicates;
private CriteriaQuery criteriaQuery;
private CriteriaBuilder criteriaBuilder;
/** 排序方式列表 */
private List<Order> orders;
/** 關(guān)聯(lián)模式 */
private Map<String, Query> subQuery;
private Map<String, Query> linkQuery;
private String projection;
/** 或條件 */
private List<Query> 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();
}
/** 通過類創(chuàng)建查詢條件 */
public static Query forClass(Class clazz, EntityManager entityManager) {
return new Query(clazz, entityManager);
}
/** 增加子查詢 */
private void addSubQuery(String propertyName, Query query) {
if (this.subQuery == null)
this.subQuery = new HashMap();
if (query.projection == null)
throw new RuntimeException("子查詢字段未設(shè)置");
this.subQuery.put(propertyName, query);
}
private void addSubQuery(Query query) {
addSubQuery(query.projection, query);
}
/** 增關(guān)聯(lián)查詢 */
public void addLinkQuery(String propertyName, Query query) {
if (this.linkQuery == null)
this.linkQuery = new HashMap();
this.linkQuery.put(propertyName, query);
}
/** 相等 */
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<String> 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<String> 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));
}
/**
* 時(shí)間區(qū)間查詢
*
* @param propertyName
* 屬性名稱
* @param lo
* 屬性起始值
* @param go
* 屬性結(jié)束值
*/
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內(nèi)部的查詢條件,用于應(yīng)付一些復(fù)雜查詢的情況,例如或 */
public void addCriterions(Predicate predicate) {
this.predicates.add(predicate);
}
/**
* 創(chuàng)建查詢條件
*
* @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<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> 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<Predicate> getPredicates() {
return predicates;
}
public void setPredicates(List<Predicate> predicates) {
this.predicates = predicates;
}
public CriteriaQuery getCriteriaQuery() {
return criteriaQuery;
}
public CriteriaBuilder getCriteriaBuilder() {
return criteriaBuilder;
}
public void setFetchModes(List<String> fetchField, List<String> fetchMode) {
}
public String getGroupBy() {
return groupBy;
}
public void setGroupBy(String groupBy) {
this.groupBy = groupBy;
}
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!-- JPA Entity Manager Factory -->
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
p:packagesToScan="com.**.model" p:dataSource-ref="dataSource"
p:jpaVendorAdapter-ref="hibernateVendor" p:jpaPropertyMap-ref="jpaPropertyMap"/>
<util:map id="jpaPropertyMap">
<entry key="hibernate.hbm2ddl.auto" value="update" /><!-- create,update,none -->
<entry key="hibernate.format_sql" value="false" />
<entry key="hibernate.show_sql" value="false" />
<entry key="hibernate.current_session_context_class" value="org.hibernate.context.internal.ThreadLocalSessionContext"/>
<entry key="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
<!-- To enable Hibernate's second level cache and query cache settings -->
<entry key="hibernate.max_fetch_depth" value="4" />
<entry key="hibernate.cache.use_second_level_cache" value="true" />
<entry key="hibernate.cache.use_query_cache" value="true" />
<!-- <entry key="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.EhCacheRegionFactory" /> -->
<entry key="hibernate.cache.region.factory_class" value="org.hibernate.cache.SingletonEhCacheRegionFactory" />
</util:map>
<bean id="hibernateVendor"
class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
p:database="MYSQL" p:showSql="true" p:generateDdl="true"
p:databasePlatform="org.hibernate.dialect.MySQLDialect" />
<bean id="transactionHandler" class="com.platform.framework.dao.jpa.TransactionHandler" >
<property name="txmethod">
<list>
<value>insert</value>
<value>update</value>
<value>delete</value>
</list>
</property>
<property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>
<aop:config>
<aop:aspect id="tran" ref="transactionHandler">
<aop:pointcut id="tranMethod"
expression="
execution(* com.*.dao.*.*(..))||
execution(* com.*.service.impl.*.*(..))||
execution(* com.*.*.dao.*.*(..))||
execution(* com.*.*.service.impl.*.*(..))||
execution(* com.*.*.*.dao.*.*(..))||
execution(* com.*.*.*.service.impl.*.*(..))||
execution(* com.*.*.*.*.dao.*.*(..))||
execution(* com.*.*.*.*.service.impl.*.*(..))||
execution(* com.*.*.*.*.*.dao.*.*(..))||
execution(* com.*.*.*.*.*.service.impl.*.*(..))||
execution(* com.*.*.*.*.*.*.dao.*.*(..))||
execution(* com.*.*.*.*.*.*.service.impl.*.*(..))||
execution(* com.platform.framework.dao.jpa.BaseDaoImpl.*(..))"/>
<aop:around method="exec" pointcut-ref="tranMethod" />
</aop:aspect>
</aop:config>
<bean id="baseDao" class="com.platform.framework.dao.jpa.BaseDaoImpl">
<property name="emf" ref="entityManagerFactory"/>
</bean>
</beans>
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事務(wù)管理
* @author lry
* @since：2014-05-23
*
*/
public class TransactionHandler {
private static final Logger log = Logger
.getLogger(TransactionHandler.class);
private String[] txmethod;// 配置事務(wù)的傳播特性方法
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開頭的方法打開事務(wù)
isTransaction = true;
break;
}
}
// JPA->Hibernate
if (point.getTarget() instanceof EntityManagerFactoryProxy) {
// 獲得被代理對(duì)象
EntityManagerFactoryProxy emfp = (EntityManagerFactoryProxy) point
.getTarget();
EntityManager em = emfp.getEntityManager();
if (em != null) {// 如果對(duì)象已經(jīng)有em了就不管
return point.proceed();
} else {
em = entityManagerFactory.createEntityManager();
}
log.debug("JPA->Hibernate open connection...");
if (isTransaction) {
EntityTransaction t = null;
try {
// 打開連接并開啟事務(wù)
log.debug("JPA->Hibernate begin transaction...");
t = em.getTransaction();
if (!t.isActive())
t.begin();
emfp.setEntityManager(em);
Object obj = point.proceed();
// 提交事務(wù)
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()) {// 關(guān)閉連接
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()) {// 關(guān)閉連接
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管理器，通過spring管理
package com.platform.framework.dao.jpa;
import java.util.Collection;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
/**
* EntityManager管理器
*
* @author：yangjian1004
* @since：2011-11-30 16:14:24 AM
*/
public class EntityManagerFactoryProxy {
private static ThreadLocal<EntityManager> emThreadLocal = new ThreadLocal<EntityManager>();
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);
}
/**
* 創(chuàng)建查詢條件
*
* @param name
* 字段名稱
* @param values
* 字段值
*/
public String createInCondition(String name, Collection<String> values) {
if (values == null || values.size() == 0) {
return "1<>1";
}
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分頁和結(jié)果封裝類
package com.platform.framework.dao.jpa;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
/**
* Page基類<br>
*
* @describe：分頁
*/
public class Page<T> implements Serializable {
private static final long serialVersionUID = 665620345605746930L;
/** 總條數(shù) */
private int count;
/** 頁碼 */
private int pageNo;
/** 每頁顯示多少條 */
private int rowsPerPage;
/** 總頁數(shù) */
private int totalPageCount;
/** 起始條數(shù) */
private int firstRow;
/** 結(jié)束條數(shù) */
private int lastRow;
/** 查詢結(jié)果集合形式的結(jié)果 */
private List<T> result;
/** 查詢結(jié)果對(duì)象形式的結(jié)果 */
public Object obj;
public Integer code; // 返回碼
private boolean success = true;
private String message;
public Page() {
}
public Page(List<T> list) {
this(list.size(), 1, list.size(), list);
}
public Page(int count, int pageNo, int rowsPerPage, List<T> 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<T>();
totalPageCount = count / rowsPerPage;
if (count - (count / rowsPerPage) * rowsPerPage > 0)
totalPageCount++;
if (count == 0) {
totalPageCount = 0;
pageNo = 0;
}
firstRow = (pageNo - 1) * rowsPerPage + 1;
if (count == 0) {
firstRow = 0;
}
lastRow = (pageNo) * rowsPerPage;
if (lastRow > count) {
lastRow = count;
}
}
/** 返回每頁的條數(shù) */
public int getCount() {
return count;
}
public List<T> getResult() {
return result;
}
public int getPageNo() {
return pageNo;
}
/** 返回每頁的條數(shù) */
public int getRowsPerPage() {
return rowsPerPage;
}
/** 返回總的頁數(shù) */
public int getTotalPageCount() {
return totalPageCount;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public void setRowsPerPage(int rowsPerPage) {
this.rowsPerPage = rowsPerPage;
}
public int getFirstRow() {
return firstRow;
}
public int getLastRow() {
return lastRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
public void setCount(int count) {
this.count = count;
}
public void setTotalPageCount(int totalPageCount) {
this.totalPageCount = totalPageCount;
}
public void setResult(List<T> result) {
this.result = result;
}
public Object getObj() {
return obj;
}
public void setObj(Object obj) {
this.obj = obj;
}
public boolean isSuccess() {
return success;
}
public void setSuccess(boolean success) {
this.success = success;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
/**
* 計(jì)算起始條數(shù)
*/
public static int calc(int pageNo, int rowsPerPage, int count) {
if (pageNo <= 0)
pageNo = 1;
if (rowsPerPage <= 0)
rowsPerPage = 10;
// 當(dāng)把最后一頁數(shù)據(jù)刪除以后,頁碼會(huì)停留在最后一個(gè)上必須減一
int totalPageCount = count / rowsPerPage;
if (pageNo > totalPageCount && (count % rowsPerPage == 0)) {
pageNo = totalPageCount;
}
if (pageNo - totalPageCount > 2) {
pageNo = totalPageCount + 1;
}
int firstRow = (pageNo - 1) * rowsPerPage;
if (firstRow < 0) {
firstRow = 0;
}
return firstRow;
}
}
IBaseDao接口實(shí)現(xiàn)了BaseDaoImpl
package com.platform.framework.dao.jpa;
import java.io.Serializable;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Selection;
import javax.persistence.metamodel.EntityType;
import org.apache.log4j.Logger;
import com.google.common.base.Strings;
/**
* IBaseDao接口實(shí)現(xiàn)了BaseDaoImpl類<br>
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public class BaseDaoImpl<T> extends EntityManagerFactoryProxy implements IBaseDao {
private static Logger log = Logger.getLogger(BaseDaoImpl.class);
/** 每次批量操作數(shù) */
private int batchSize = 50;
/** 設(shè)置每次操作數(shù) */
public void setBatchSize(int batchSize) {
this.batchSize = batchSize;
}
public <E> E get(Class clazz, Serializable id) {
return (E) getEntityManager().find(clazz, id);
}
/**
* 插入記錄
*
* @param entity
* 要插入的記錄
*/
public void insert(Object entity) {
if (entity instanceof List) {
insertList((List) entity);
return;
} else if (entity instanceof Object[]) {
return;
}
try {
getEntityManager().persist(entity);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 批量增加
*
* @param list
* 要新增的數(shù)據(jù)
*/
public void insertList(List list) {
EntityManager entityManager = getEntityManager();
if (list == null || list.size() == 0) {
return;
}
int i = 0;
for (Object o : list) {
insert(o);
if (i % batchSize == 0) {
entityManager.flush();
}
i++;
}
log.debug(list.get(0).getClass() + "批量增加數(shù)據(jù)" + i + "條");
}
/**
* 更新記錄
*
* @param entity
* 要更新的記錄
*/
public void update(Object entity) {
if (entity instanceof List) {
this.updateList((List) entity);
return;
}
getEntityManager().merge(entity);
}
/** 更新list */
public void updateList(List list) {
for (Object entity : list) {
this.update(entity);
}
}
/**
* 刪除記錄
*
* @param entity
* 要?jiǎng)h除的記錄
*/
public void delete(Object entity) {
if (entity instanceof List) {
List list = (List) entity;
for (Object o : list) {
getEntityManager().remove(o);
}
} else {
getEntityManager().remove(entity);
}
}
public <E extends Serializable> List<E> query(String jpql) {
return getEntityManager().createQuery(jpql).getResultList();
}
public Integer updateJpql(String jpql) {
return getEntityManager().createQuery(jpql).executeUpdate();
}
public Integer updateSql(String sql) {
return getEntityManager().createNativeQuery(sql).executeUpdate();
}
public <E extends Serializable> List<E> queryBySql(String sql) {
return getEntityManager().createNativeQuery(sql).getResultList();
}
/**
* 查詢記錄
*
* @param clazz
* 要查詢的實(shí)體類
* @param hqlCondition
* 查詢條件
*/
public <E extends Serializable> List<E> query(Class clazz, String hqlCondition) {
return getEntityManager().createQuery("select t from " + clazz.getName() + " as t where " + hqlCondition)
.getResultList();
}
public void delete(Class entity, String jpqlCondition) {
if (Strings.isNullOrEmpty(jpqlCondition)) {
jpqlCondition = "1=1";
}
int no = updateJpql("delete " + entity.getName() + " where " + jpqlCondition);
log.debug(entity.getName() + "刪除" + no + "條數(shù)據(jù)");
}
/**
* 根據(jù)ids刪除數(shù)據(jù)
*
* @param entity
* 刪除實(shí)體類
* @param ids
* 刪除條件
*/
public void delete(Class entity, List ids) {
String idName = getIdName(entity, getEntityManager());
StringBuffer sb = new StringBuffer();
sb.append(idName + " in(");
for (int i = 0; i < ids.size(); i++) {
sb.append("'" + ids.get(i) + "',");
}
String jpqlCondition = sb.substring(0, sb.length() - 1) + ")";
delete(entity, jpqlCondition);
}
public <E extends Serializable> List<E> query(String jpql, int firstResult, int maxResults) {
List result = getEntityManager().createQuery(jpql).setFirstResult(firstResult).setMaxResults(maxResults)
.getResultList();
return result;
}
public <E extends Serializable> List<E> queryBySql(String sql, int firstResult, int maxResults) {
return getEntityManager().createNativeQuery(sql).setFirstResult(firstResult).setMaxResults(maxResults)
.getResultList();
}
public <E extends Serializable> List<E> queryAll(Class clazz) {
CriteriaQuery criteriaQuery = getEntityManager().getCriteriaBuilder().createQuery(clazz);
criteriaQuery.from(clazz);
return getEntityManager().createQuery(criteriaQuery).getResultList();
}
public Page queryPageByJpql(String jpql, int pageNo, int rowsPerPage) {
if (pageNo <= 0)
pageNo = 1;
if (rowsPerPage <= 0)
rowsPerPage = 7;
log.debug("-----開始查詢,頁碼:" + pageNo + ",每頁顯示:" + rowsPerPage + "----");
String countJpql = "select count(*) from (" + jpql + ")";
int count = getCount(countJpql).intValue();
// 當(dāng)把最后一頁數(shù)據(jù)刪除以后,頁碼會(huì)停留在最后一個(gè)上必須減一
int totalPageCount = count / rowsPerPage;
if (pageNo > totalPageCount && (count % rowsPerPage == 0)) {
pageNo = totalPageCount;
}
if (pageNo - totalPageCount > 2) {
pageNo = totalPageCount + 1;
}
int firstResult = (pageNo - 1) * rowsPerPage;
if (firstResult < 0) {
firstResult = 0;
}
List result = getEntityManager().createQuery(jpql).setFirstResult(firstResult).setMaxResults(rowsPerPage)
.getResultList();
return new Page(count, pageNo, rowsPerPage, result);
}
public Long getCount(String jpql) {
return (Long) getEntityManager().createQuery(jpql).getResultList().get(0);
}
/***
*
* @Method updateJpql
* @Description 根據(jù)傳入的帶有占位符的sql語句, 做增刪改操作 例如
* updateJpql("update user t set t.name=? where t.id=?"
* ,{[zhongxiang],[23]})
* @Author 鐘翔/zhongxiang
* @Date 2012-8-9 下午3:38:35
* @param jpql
* 占位符式的sql
* @param paramList
* list里面裝有[zhongxiang , 23]
*/
public void updateJpql(String jpql, List paramList) {
javax.persistence.Query query = getEntityManager().createQuery(jpql);
for (int i = 0; i < paramList.size(); i++) {
query.setParameter(i + 1, paramList.get(i));
}
query.executeUpdate();
}
/**
* 統(tǒng)計(jì)記錄
*
* @param query
* 統(tǒng)計(jì)條件
*/
public Long getCount(Query query) {
Selection selection = query.getCriteriaQuery().getSelection();
query.getCriteriaQuery().select(query.getCriteriaBuilder().count(query.getFrom()));
Long count = (Long) getEntityManager().createQuery(query.newCriteriaQuery()).getResultList().get(0);
query.getCriteriaQuery().select(selection);
return count;
}
/**
* 分頁查詢
*
* @param query
* 查詢條件
* @param pageNo
* 頁號(hào)
* @param rowsPerPage
* 每頁顯示條數(shù)
*/
public Page queryPage(Query query, int pageNo, int rowsPerPage) {
if (pageNo <= 0)
pageNo = 1;
if (rowsPerPage <= 0)
rowsPerPage = 7;
log.debug(query.getClazz() + "-----開始查詢,頁碼:" + pageNo + ",每頁顯示:" + rowsPerPage + "----");
log.debug("查詢條件:");
for (Predicate cri : query.getPredicates())
log.debug(cri);
int count = getCount(query).intValue();
// 當(dāng)把最后一頁數(shù)據(jù)刪除以后,頁碼會(huì)停留在最后一個(gè)上必須減一
int totalPageCount = count / rowsPerPage;
if (pageNo > totalPageCount && (count % rowsPerPage == 0)) {
pageNo = totalPageCount;
}
if (pageNo - totalPageCount > 2) {
pageNo = totalPageCount + 1;
}
int firstResult = (pageNo - 1) * rowsPerPage;
if (firstResult < 0) {
firstResult = 0;
}
List result = getEntityManager().createQuery(query.newCriteriaQuery()).setFirstResult(firstResult)
.setMaxResults(rowsPerPage).getResultList();
return new Page(count, pageNo, rowsPerPage, result);
}
/**
* 根據(jù)query查找記錄
*
* @param query
* 查詢條件
* @param firstResult
* 起始行
* @param maxResults
* 結(jié)束行
*/
public <E extends Serializable> List<E> query(Query query, int firstResult, int maxResults) {
List result = getEntityManager().createQuery(query.newCriteriaQuery()).setFirstResult(firstResult)
.setMaxResults(maxResults).getResultList();
return result;
}
/**
* 根據(jù)query查找記錄
*
* @param query
* 查詢條件
*/
public <E extends Serializable> List<E> query(Query query) {
return getEntityManager().createQuery(query.newCriteriaQuery()).getResultList();
}
/**
* 獲得主鍵名稱
*
* @param clazz
* 操作是實(shí)體對(duì)象
* @param EntityManager
* jpa的entityManager工廠
* @return 初建名稱
* */
public static String getIdName(Class clazz, EntityManager entityManager) {
EntityType entityType = entityManager.getMetamodel().entity(clazz);
return entityType.getId(entityType.getIdType().getJavaType()).getName();
}
}
IBaseDao接口
package com.platform.framework.dao.jpa;
import java.io.Serializable;
import java.util.List;
import javax.persistence.EntityManager;
/**
* IBaseDao基類<br>
*
* @describe：系統(tǒng)基礎(chǔ)JPA Dao接口
*/
@SuppressWarnings({ "rawtypes" })
public interface IBaseDao {
public EntityManager getEntityManager();
public <E> E get(Class clazz, Serializable id);
/**
* 插入記錄
*
* @param entity
* 要插入的記錄
*/
public void insert(Object entity);
/**
* 更新記錄
*
* @param entity
* 要更新的記錄
*/
public void update(Object entity);
/** 更新list */
public void updateList(List list);
/**
* 刪除記錄
*
* @param entity
* 要?jiǎng)h除的記錄
*/
public void delete(Object entity);
/**
* 刪除記錄
*
* @param entity
* 要?jiǎng)h除的記錄
*/
public void delete(Class entity, List ids);
/**
* 刪除記錄
*
* @param entity
* 要?jiǎng)h除的記錄
*/
public void delete(Class entity, String jpqlCondition);
/**
* 統(tǒng)計(jì)記錄
*
* @param query
* 統(tǒng)計(jì)條件
*/
public Long getCount(Query query);
public Long getCount(String jpql);
/**
* 分頁查詢
*
* @param query
* 查詢條件
* @param pageNo
* 頁號(hào)
* @param rowsPerPage
* 每頁顯示條數(shù)
*/
public Page queryPage(Query query, int pageNo, int rowsPerPage);
/**
* 根據(jù)query查找記錄
*
* @param query
* 查詢條件
* @param firstResult
* 起始行
* @param maxResults
* 結(jié)束行
*/
public <E extends Serializable> List<E> query(Query query, int firstResult, int maxResults);
/**
* 根據(jù)query查找記錄
*
* @param query
* 查詢條件
*/
public <E extends Serializable> List<E> query(Query query);
/**
* 執(zhí)行更新操作的jpql語句
*
* @param jpql
* 要執(zhí)行的jpql語句
*/
public <E extends Serializable> List<E> query(String jpql);
public <E extends Serializable> List<E> queryAll(Class clazz);
public <E extends Serializable> List<E> query(String jpql, int firstResult, int maxResults);
/**
* 執(zhí)行查詢操作的sql語句
*
* @param sql
* 要執(zhí)行的sql語句
*/
public <E extends Serializable> List<E> queryBySql(String sql);
public <E extends Serializable> List<E> queryBySql(String sql, int firstResult, int maxResults);
/**
* 查詢記錄
*
* @param clazz
* 要查詢的實(shí)體類
* @param hqlCondition
* 查詢條件
*/
public <E extends Serializable> List<E> query(Class clazz, String hqlCondition);
/**
* 執(zhí)行更新操作的sql語句
*
* @param sql
* 要執(zhí)行的sql語句
*/
public Integer updateSql(String sql);
public Integer updateJpql(String jpql);
public Page queryPageByJpql(String hql, int pageNo, int rowsPerPage);
public void updateJpql(String jpql, List paramList);
}
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
spring?boot項(xiàng)目自定義參數(shù)校驗(yàn)規(guī)則示例詳解
這篇文章主要介紹了spring boot項(xiàng)目如何自定義參數(shù)校驗(yàn)規(guī)則,自定義校驗(yàn)規(guī)則和自帶的規(guī)則實(shí)現(xiàn)方式一樣，先自定義一個(gè)注解，然后指定校驗(yàn)類，在校驗(yàn)類里實(shí)現(xiàn)具體的校驗(yàn)規(guī)則，本文結(jié)合示例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2023-07-07
java實(shí)現(xiàn)sftp客戶端上傳文件以及文件夾的功能代碼
本篇文章主要介紹了java實(shí)現(xiàn)sftp客戶端上傳文件以及文件夾的功能代碼，具有一定的參考價(jià)值，有興趣的可以了解一下。2017-02-02
Spring更簡(jiǎn)單的存儲(chǔ)方式與獲取方式詳解
Spring是一個(gè)輕量級(jí)的IoC和AOP容器框架,是為Java應(yīng)用程序提供基礎(chǔ)性服務(wù)的一套框架,目的是用于簡(jiǎn)化企業(yè)應(yīng)用程序的開發(fā),它使得開發(fā)者只需要關(guān)心業(yè)務(wù)需求,下面這篇文章主要給大家介紹了關(guān)于Spring更簡(jiǎn)單的存儲(chǔ)方式與獲取方式的相關(guān)資料,需要的朋友可以參考下2022-06-06
解決springboot 實(shí)體類String轉(zhuǎn)Date類型的坑
這篇文章主要介紹了解決springboot 實(shí)體類String轉(zhuǎn)Date類型的坑，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-10-10
IntelliJ IDEA2023中運(yùn)行Spring Boot找不到VM options進(jìn)
這篇文章主要介紹了IntelliJ IDEA2023中運(yùn)行Spring Boot找不到VM options進(jìn)行端口的修改的問題解決,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),需要的朋友可以參考下2023-11-11
解析Orika的MapperFacade 屬性賦值的使用問題
在我們實(shí)際開發(fā)中，常常會(huì)有對(duì)象與對(duì)象之間的轉(zhuǎn)化，或者把一個(gè)對(duì)象的數(shù)據(jù)轉(zhuǎn)化到另一個(gè)數(shù)據(jù)之中，如果我們手動(dòng)的一個(gè)一個(gè)的set就會(huì)比較麻煩，代碼段看起來也會(huì)比較長。而Orika的MapperFacade就是解決這個(gè)問題的，實(shí)現(xiàn)對(duì)象屬性的復(fù)制2021-12-12
這篇文章主要介紹了redis 獲取 list 中的所有元素操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-03-03
這篇文章主要為大家介紹了RabbitMQ消息確認(rèn)機(jī)制剖析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-08-08
Java 靜態(tài)綁定與動(dòng)態(tài)綁定深入分析
這篇文章主要介紹了Java 靜態(tài)綁定與動(dòng)態(tài)綁定深入分析的相關(guān)資料,這里對(duì)java 的動(dòng)態(tài)綁定和靜態(tài)綁定做了詳細(xì)的介紹，對(duì)其進(jìn)行總結(jié)整理，需要的朋友可以參考下2016-11-11
印象中Java?接口就沒有論述私有方法這回事。既然?Java?9?添加了這項(xiàng)特性，那么，應(yīng)該就有它的用途，我們一起來看看?Java?9?中的接口的私有方法是什么樣的吧2023-04-04