使用JPA进行CriteriaQuery进行查询的注意事项

使用jpa criteriaquery查询的注意事项

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() {
    }
}

在进行聚合函数sum求和时，原来是int会自动提升为long,不做特殊处理就会报以下错误了：

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)

会提示你查询数据库返回的类型和你的构造函数类型对应不上。

service层

通过注解将entitymanager加载进来：

 @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;
    }

在进行cq.multiselect自定义返回字段时，必须在对应的pojo中给一个对应的返回字段构造函数

封装jpa动态查询（criteriaquery）

jpa动态查询（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; 
/** 要查询的模型对象 */
private class clazz;
/** 查询条件列表 */
private root from; 
private list<predicate> predicates; 
private criteriaquery criteriaquery; 
private criteriabuilder criteriabuilder; 
/** 排序方式列表 */
private list<order> orders;
/** 关联模式 */
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();
}
/** 通过类创建查询条件 */
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("子查询字段未设置");
this.subquery.put(propertyname, query);
}
private void addsubquery(query query) {
addsubquery(query.projection, query);
}
/** 增关联查询 */
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));
} 
/**
* 时间区间查询
* 
* @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<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事务管理
* @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) {
// 获得被代理对象
entitymanagerfactoryproxy emfp = (entitymanagerfactoryproxy) point
.gettarget();
entitymanager em = emfp.getentitymanager();
if (em != null) {// 如果对象已经有em了就不管
return point.proceed();
} else {
em = entitymanagerfactory.createentitymanager();
}
log.debug("jpa->hibernate open connection...");
if (istransaction) {
entitytransaction t = null;
try {
// 打开连接并开启事务
log.debug("jpa->hibernate begin transaction...");
t = em.gettransaction();
if (!t.isactive())
t.begin();
emfp.setentitymanager(em);
object obj = point.proceed();
// 提交事务
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()) {// 关闭连接
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()) {// 关闭连接
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);
}
/**
* 创建查询条件
* 
* @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分页和结果封装类

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;
/** 总条数 */
private int count;
/** 页码 */
private int pageno;
/** 每页显示多少条 */
private int rowsperpage;
/** 总页数 */
private int totalpagecount;
/** 起始条数 */
private int firstrow;
/** 结束条数 */
private int lastrow;
/** 查询结果集合形式的结果 */
private list<t> result;
/** 查询结果对象形式的结果 */
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;
}
}
/** 返回每页的条数 */
public int getcount() {
return count;
}
public list<t> getresult() {
return result;
}
public int getpageno() {
return pageno;
}
/** 返回每页的条数 */
public int getrowsperpage() {
return rowsperpage;
}
/** 返回总的页数 */
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;
}
/**
* 计算起始条数
*/
public static int calc(int pageno, int rowsperpage, int count) {
if (pageno <= 0)
pageno = 1;
if (rowsperpage <= 0)
rowsperpage = 10;
// 当把最后一页数据删除以后,页码会停留在最后一个上必须减一
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接口实现了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接口实现了basedaoimpl类<br>
*/
@suppresswarnings({ "unchecked", "rawtypes" })
public class basedaoimpl<t> extends entitymanagerfactoryproxy implements ibasedao { 
private static logger log = logger.getlogger(basedaoimpl.class); 
/** 每次批量操作数 */
private int batchsize = 50;
/** 设置每次操作数 */
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
*            要新增的数据
*/
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() + "批量增加数据" + 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
*            要删除的记录
*/
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
*            要查询的实体类
* @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 + "条数据");
}
/**
* 根据ids删除数据
* 
* @param entity
*            删除实体类
* @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();
// 当把最后一页数据删除以后,页码会停留在最后一个上必须减一
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 根据传入的带有占位符的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();
}
/**
* 统计记录
* 
* @param query
*            统计条件
*/
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
*            页号
* @param rowsperpage
*            每页显示条数
*/
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();
// 当把最后一页数据删除以后,页码会停留在最后一个上必须减一
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);
}
/**
* 根据query查找记录
* 
* @param query
*            查询条件
* @param firstresult
*            起始行
* @param maxresults
*            结束行
*/
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;
}
/**
* 根据query查找记录
* 
* @param query
*            查询条件
*/
public <e extends serializable> list<e> query(query query) {
return getentitymanager().createquery(query.newcriteriaquery()).getresultlist();
}
/**
* 获得主键名称
* 
* @param clazz
*            操作是实体对象
* @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：系统基础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
*            要删除的记录
*/
public void delete(object entity);
/**
* 删除记录
* 
* @param entity
*            要删除的记录
*/
public void delete(class entity, list ids);
/**
* 删除记录
* 
* @param entity
*            要删除的记录
*/
public void delete(class entity, string jpqlcondition);
/**
* 统计记录
* 
* @param query
*            统计条件
*/
public long getcount(query query); 
public long getcount(string jpql);
/**
* 分页查询
* 
* @param query
*            查询条件
* @param pageno
*            页号
* @param rowsperpage
*            每页显示条数
*/
public page querypage(query query, int pageno, int rowsperpage);
/**
* 根据query查找记录
* 
* @param query
*            查询条件
* @param firstresult
*            起始行
* @param maxresults
*            结束行
*/
public <e extends serializable> list<e> query(query query, int firstresult, int maxresults);
/**
* 根据query查找记录
* 
* @param query
*            查询条件
*/
public <e extends serializable> list<e> query(query query);
/**
* 执行更新操作的jpql语句
* 
* @param jpql
*            要执行的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);
/**
* 执行查询操作的sql语句
* 
* @param sql
*            要执行的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
*            要查询的实体类
* @param hqlcondition
*            查询条件
*/
public <e extends serializable> list<e> query(class clazz, string hqlcondition);
/**
* 执行更新操作的sql语句
* 
* @param sql
*            要执行的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); 
}

以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。