使用JPA进行CriteriaQuery进行查询的注意事项
程序员文章站
2022-03-13 19:33:05
目录使用jpa criteriaquery查询的注意事项1.pojo类service层查询方法封装jpa动态查询（criteriaquery）entitymanager管理器，通过spring管理pa...
使用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);
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
推荐阅读
-
php+mysqli使用预处理技术进行数据库查询的方法，mysqli预处理_PHP教程
-
为什么无法使用php中mysqli的准备语句进行数据库中数据的查询（绑定参数或者绑定结果），项目急用！该如何处理
-
SQL Server使用Merge语句当源表数据集为空时，无法进行查询的问题
-
And,Where使用提示以及用+进行左关联的提示及注意事项
-
SQL Server使用Merge语句当源表数据集为空时，无法进行查询的问题
-
在SQL中使用convert函数进行日期的查询的代码
-
And,Where使用提示以及用+进行左关联的提示及注意事项
-
php+mysqli使用预处理技术进行数据库查询的方法
-
用一张表来存储数据状态，并且可以进行多状态精确查询；使用二进制来表示数据状态，并且是可以无顺序的状态；解决使用中间表来存储数据的多状态；数据状态还可以这么玩；
-
Javascript中使用exec进行正则表达式全局匹配时的注意事项