jpa动态查询语句 （代码详解）
我们现在在做一个oa系统，将新增的那些数据都写到数据库的时候是采用jpa规范的，（不太理解jpa的相关知识点，今天看下相关知识，然后再补充jpa的知识点），现在记录jpa中的动态查询语句，其实这些语句都是可以用sql语句写的，但是sql语句写得查询，删除，插入数据等操作不安全，所以采用jpa的语句。我们的项目是分为三层结构，第一层是实体层，在该层中专门定义某一实体的相关字段，它的set()，get()方法。第二层是服务层，将service和dao都放在一个组件中,在dao层中定义和数据库相关的操作方法，在service层中定义相关的业务逻辑层要调用的方法。第三层是restful层，在这层定义的是和前端交互的组件。
首先讲讲第一层：实体层
定义一个实体
/*** 邮件实体**/ @entity @table(name = "mail_tbl") public class innermails implements serializable { private static final long serialversionuid = 4999674279957375152l; @id @generatedvalue private long id; private string subject;// 主题 private string tomails;// 收件人 格式 ：姓名<userid>;姓名<userid> private string urgency;// 紧急程度 @column(name = "senddate") @temporal(temporaltype.timestamp) private date senddate;// 发布日期 private string content;// 邮件内容 // 原文附件 @onetomany(cascade={ cascadetype.merge,cascadetype.remove}) @joincolumn(name = "mail_id") @orderby(value = "id desc")//注释指明加载attachment时按id的降序排序 private set<appendfile> appendfiles=new hashset<appendfile>();// 附件 private string mailuser;// 邮件拥有者 格式：userid private string sendmail;// 邮件发送者 格式：姓名<userid> private int type;// 状态标示：-1删除；0草稿；1发送；2未读收件，3已读收件 public long getid() { return id; } public void setid(long id) { this.id = id; } public string getsubject() { return subject; } public void setsubject(string subject) { this.subject = subject; } public string gettomails() { return tomails; } public void settomails(string tomails) { this.tomails = tomails; } public string geturgency() { return urgency; } public void seturgency(string urgency) { this.urgency = urgency; } public date getsenddate() { return senddate; } public void setsenddate(date senddate) { this.senddate = senddate; } public string getcontent() { return content; } public void setcontent(string content) { this.content = content; } public string getmailuser() { return mailuser; } public void setmailuser(string mailuser) { this.mailuser = mailuser; } public set<appendfile> getappendfiles() { return appendfiles; } public void setappendfiles(set<appendfile> appendfiles) { this.appendfiles = appendfiles; } public string getsendmail() { return sendmail; } public void setsendmail(string sendmail) { this.sendmail = sendmail; } public int gettype() { return type; } public void settype(int type) { this.type = type; } }
定义查询实体：
package com.gzydt.oa.commons; import java.util.arraylist; import java.util.hashmap; import java.util.list; import java.util.map; /*** 分页查询参数* * @author huangzhenwei* @since 2014-11-21* */ public class queryparam { // 排序字段，以“+”、“-”符号连接排序字段名：“+key”表示 按“key”字段升序，“-key”表示按“key”字段降序。 private list<string> sorts = new arraylist<string>(); // 起始记录下标，从0开始计算 private int first = 0; // 每页最大记录数 private int max = 10; // 是否分页标志 private boolean ispage = true; // 查询参数 private map<string, string> param = new hashmap<string, string>(); public queryparam() { } public int getfirst() { return first; } public void setfirst(int first) { this.first = first; } public int getmax() { return max; } public void setmax(int max) { this.max = max; } public map<string, string> getparam() { return param; } public void setparam(map<string, string> param) { this.param = param; } public boolean ispage() { return ispage; } public void setpage(boolean ispage) { this.ispage = ispage; } public list<string> getsorts() { return sorts; } public void setsorts(list<string> sorts) { this.sorts = sorts; } }
第二层：服务层
dao层：定义和数据库相关操作的方法
package com.gzydt.oa.dao; import java.util.list; import com.gzydt.oa.commons.queryparam; import com.gzydt.oa.entity.appendfile; import com.gzydt.oa.entity.innermails; /*** 邮件发送dao接口**/ public interface innermaildao { /** * 保存邮件 * @param mail * @return */ public innermails save(innermails mail); /** * 更新邮件 * @param mail * @return */ public innermails update(innermails mail); /** * 删除邮件 * @param id */ public void delete(long id); /** * 查询邮件 * @param queryparam * @return */ public list<innermails> getlist(queryparam queryparam); /** * 获取单个邮件 * @param id * @return */ public innermails get(long id); /** * 获取满足条件的邮件的总数 * @param queryparam * @return */ public int getcount(queryparam queryparam); /** * 新增附件 * @param id * @param appendfile */ public void addattach(long id,appendfile appendfile); }
package com.gzydt.oa.dao.impl; import java.text.dateformat; import java.text.simpledateformat; import java.util.arraylist; import java.util.date; import java.util.hashset; import java.util.iterator; import java.util.list; import java.util.map; import java.util.set; import javax.persistence.entitymanager; import javax.persistence.typedquery; import javax.persistence.criteria.criteriabuilder; import javax.persistence.criteria.criteriaquery; import javax.persistence.criteria.predicate; import javax.persistence.criteria.root; import com.gzydt.oa.commons.queryparam; import com.gzydt.oa.dao.innermaildao; import com.gzydt.oa.entity.appendfile; import com.gzydt.oa.entity.innermails; /*** 邮件实现类*/ public class innermaildaoimpl implements innermaildao{ private entitymanager entitymanager; public void setentitymanager(entitymanager entitymanager) { this.entitymanager = entitymanager; } /** * 保存邮件 * @param mail * @return */ @override public innermails save(innermails mail) { try { entitymanager.persist(mail); entitymanager.flush(); return mail; } catch ( exception e ) { e.printstacktrace(); return null; } } /** * 更新邮件 * @param mail * @return */ @override public innermails update(innermails mail) { try { entitymanager.merge(mail); return mail; } catch ( exception e ) { e.printstacktrace(); return null; } } /** * 删除邮件 * @param id */ @override public void delete(long id) { entitymanager.createquery("delete from phonerecord e where e.id=:id").setparameter("id", id).executeupdate(); } /** * 查询邮件 * @param queryparam * @return */ @override public list<innermails> getlist(queryparam queryparam) { criteriabuilder cb = entitymanager.getcriteriabuilder(); criteriaquery<innermails> criteriaquery = cb.createquery(innermails.class); root<innermails> register = criteriaquery.from(innermails.class); // 过滤条件 predicate[] predicates = createpredicate(queryparam, cb, register); criteriaquery.where(predicates); int start = queryparam.getfirst(); int end = queryparam.getmax(); typedquery<innermails> typedquery = entitymanager.createquery(criteriaquery); typedquery.setfirstresult(start).setmaxresults(end); return typedquery.getresultlist(); } //设置查询条件 private predicate[] createpredicate(queryparam queryparam, criteriabuilder cb, root<innermails> entity) { list<predicate> predicates=new arraylist<predicate>(); //取出查询条件 map<string, string> param= queryparam.getparam(); for(map.entry entry:param.entryset()){ string key=entry.getkey().tostring(); string value=entry.getvalue().tostring(); if(key.equals("senddate")){ predicate conditiontime = createoperatetime(key,cb,value,entity); if(null!=conditiontime){ predicates.add(conditiontime); } }else{ predicates.add(cb.like(entity.<string> get(key),"%"+value+"%")); } } return predicates.toarray(new predicate[0]); } /** * 将时间作为查询条件的方法 * @param cb * @param value * @param entity */ private predicate createoperatetime(string key,criteriabuilder cb, string value, root<innermails> entity) { if(null == value){ return null; } string[] operatetime=value.split("~"); if(operatetime.length!=2){ return null; } try { dateformat df=new simpledateformat("yyyy-mm-dd hh:mm:ss");//格式一定要写正确， date t1=df.parse(operatetime[0] + " 00:00:00"); date t2=df.parse(operatetime[1] + " 23:59:59"); return cb.between(entity.<date> get(key), t1, t2); } catch ( exception e ) { e.printstacktrace(); } return null; } /** * 获取单个邮件 * @param id * @return */ @override public innermails get(long id) { innermails innersmails=entitymanager.find(innermails.class, id); iterator<appendfile> iterator=innersmails.getappendfiles().iterator(); set<appendfile> attachs=new hashset<appendfile>(); while(iterator.hasnext()){ appendfile appendfile=new appendfile(); appendfile=iterator.next(); attachs.add(appendfile); } innersmails.setappendfiles(attachs); return innersmails; } /** * 获取满足条件的邮件的总数 * @param queryparam * @return */ @override public int getcount(queryparam queryparam) { criteriabuilder cb = entitymanager.getcriteriabuilder(); criteriaquery<long> criteriaquery = cb.createquery(long.class); root<innermails> mails = criteriaquery.from(innermails.class); criteriaquery.select(cb.countdistinct(mails)); // 过滤条件 predicate[] predeicates = createpredicate(queryparam, cb, mails); criteriaquery.where(predeicates); typedquery<long> typedquery = entitymanager.createquery(criteriaquery); int count = 0; try { count = typedquery.getsingleresult().intvalue(); } catch ( exception e ) { e.printstacktrace(); } return count; } /** * 新增附件 * @param id * @param appendfile */ @override public void addattach(long id, appendfile appendfile) { innermails entity=this.get(id); entity.getappendfiles().add(appendfile); entitymanager.merge(entity); } }
动态查询语句的相关知识
1：查询user表中的字段adminlever的小于给定值的数据
第一种写法：（安全，推荐使用这种）
/** * 查询某一级别以上的用户 */ @override public list<user> getonleveruper(int lever) { criteriabuilder cb =entitymanager.getcriteriabuilder(); criteriaquery<user> criterquery=cb.createquery(user.class); root<user> entity=criterquery.from(user.class); path<integer> adminlever=entity.<integer> get("adminlever") ; criterquery.where(cb.lessthan(adminlever, lever)); typedquery<user> typedquery=entitymanager.createquery(criterquery); return typedquery.getresultlist(); }
第二种写法：(不太安全，)
/** * 查询某一级别以上的用户 */ @override public list<user> getonleveruper(int lever) { list<user> users=entitymanager.createquery("from user u where u.adminlever<:adminlever") .setparameter("adminlever",lever).getresultlist(); return users; }
第二种删除数据（有时候会由于某实体和另一实体设置了一对一或者多对一，或者多对多的关系而导致不能正常删除数据），解决方法：
/** * 删除登记信息 * * @param id * 登记编号 */ @override public void handledelete(long id) throws exception { receiptentity entity = entitymanager.find(receiptentity.class, id); entitymanager.remove(entity); //下面的的方法删除应为存在外键关联会删除失败 /*entitymanager.createquery("delete from receiptentity e where e.id=:id"). setparameter("id", id).executeupdate();*/ }
service层：接口
package com.gzydt.oa.service; import java.util.list; import com.gzydt.oa.commons.queryparam; import com.gzydt.oa.entity.appendfile; import com.gzydt.oa.entity.innermails; /*** 内部邮件接口**/ public interface innermailservice { /** * 保存邮件 * @param mail * @return */ public innermails save(innermails mail); /** * 更新邮件 * @param mail * @return */ public innermails update(innermails mail); /** * 删除邮件 * @param id */ public void delete(long id); /** * 查询邮件 * @param queryparam * @return */ public list<innermails> getlist(queryparam queryparam); /** * 获取单个邮件 * @param id * @return */ public innermails get(long id); /** * 获取满足条件的邮件的总数 * @param queryparam * @return */ public int getcount(queryparam queryparam); /** * 发邮件 * @param content *//* public void sendmail(string content);*/ /** * 新增附件 * @param id * @param appendfile */ public void addattach(long id,appendfile appendfile); }
service层:实现类
package com.gzydt.oa.service.impl; import java.util.list; import com.gzydt.oa.commons.queryparam; import com.gzydt.oa.dao.innermaildao; import com.gzydt.oa.entity.appendfile; import com.gzydt.oa.entity.innermails; import com.gzydt.oa.service.innermailservice; /*** 内部邮件服务类**/ public class innermailserviceimpl implements innermailservice{ private innermaildao maildao; public void setmaildao(innermaildao maildao) { this.maildao = maildao; } /** * 保存邮件 * @param mail * @return */ @override public innermails save(innermails mail) { return maildao.save(mail); } @override public innermails update(innermails mail) { return maildao.update(mail); } /** * 删除邮件 * @param id */ @override public void delete(long id) { maildao.delete(id); } /** * 查询邮件 * @param queryparam * @return */ @override public list<innermails> getlist(queryparam queryparam) { return maildao.getlist(queryparam); } /** * 获取单个邮件 * @param id * @return */ @override public innermails get(long id) { return maildao.get(id); } /** * 获取满足条件的邮件的总数 * @param queryparam * @return */ @override public int getcount(queryparam queryparam) { return maildao.getcount(queryparam); } /* @override public void sendmail(string content) { }*/ /** * 新增附件 * @param id * @param appendfile */ @override public void addattach(long id, appendfile appendfile) { maildao.addattach(id, appendfile); } }
在服务层中定义相关的服务配置
<?xml version="1.0" encoding="utf-8"?> <blueprint default-activation="eager" xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:jpa="http://aries.apache.org/xmlns/jpa/v1.0.0" xmlns:tx="http://aries.apache.org/xmlns/transactions/v1.0.0" xsi:schemalocation=" http://www.osgi.org/xmlns/blueprint/v1.0.0 http://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd http://cxf.apache.org/blueprint/jaxrs http://cxf.apache.org/schemas/blueprint/jaxrs.xsd http://cxf.apache.org/blueprint/core http://cxf.apache.org/schemas/blueprint/core.xsd"> <!-- this gets the container-managed entitymanager and injects it into the serviceimpl bean. --> <!-- dao --> <bean id="maildao" class="com.gzydt.oa.dao.impl.innermaildaoimpl"> <jpa:context unitname="com.gzydt.jpa.persistence" property="entitymanager" /> <tx:transaction method="*" value="required" /> </bean> <!--新增结束 --> <!-- bean --> <bean id="mailservice" class="com.gzydt.oa.service.impl.innermailserviceimpl"> <property name="maildao" ref="maildao" /> </bean> <!--新增结束 --> <!-- service --> <service ref="mailservice" interface="com.gzydt.oa.service.innermailservice" /> <!-- this bundle makes use of karaf commands to demonstrate core persistence operations. feel free to remove it. --> <command-bundle xmlns="http://karaf.apache.org/xmlns/shell/v1.1.0"> <command name="msg/add"> <action class="com.gzydt.oa.command.addmessage"> <property name="messageservice" ref="messageservice" /> </action> </command> <command name="msg/list"> <action class="com.gzydt.oa.command.getmessage"> <property name="messageservice" ref="messageservice" /> </action> </command> <command name="msg/delete"> <action class="com.gzydt.oa.command.deletemessage"> <property name="messageservice" ref="messageservice" /> </action> </command> <command name="dept/add"> <action class="com.gzydt.oa.command.deptaddcommand"> <property name="deptservice" ref="deptservice" /> </action> </command> <command name="dept/list"> <action class="com.gzydt.oa.command.deptgetcommand"> <property name="deptservice" ref="deptservice" /> </action> </command> <command name="dept/delete"> <action class="com.gzydt.oa.command.deptdeletecommand"> <property name="deptservice" ref="deptservice" /> </action> </command> </command-bundle> </blueprint>
第三层：restful层
package com.gzydt.oa.resource; import java.text.parseexception; import java.util.list; import javax.ws.rs.consumes; import javax.ws.rs.delete; import javax.ws.rs.get; import javax.ws.rs.headerparam; import javax.ws.rs.post; import javax.ws.rs.put; import javax.ws.rs.path; import javax.ws.rs.pathparam; import javax.ws.rs.produces; import javax.ws.rs.queryparam; import javax.ws.rs.core.mediatype; import javax.ws.rs.core.response; import org.apache.cxf.jaxrs.ext.multipart.attachment; import org.apache.cxf.jaxrs.ext.multipart.multipart; /*** 内部邮件的restful*/ @path("/mails") public interface innermailsresource { /** * 新增邮件 * @param content * @return */ @post @path("/") @consumes("multipart/form-data") public response save(@multipart("content") string content, list<attachment> attachments) throws parseexception ; /** * 更新邮件 * @param id * @param content * @return */ @put @path("/{id}") @consumes("multipart/form-data") public response update(@pathparam("id") long id,@multipart("content") string content, list<attachment> attachments) throws parseexception; /** * 查询邮件 * @param id * @return */ @get @path("/{id}") public response get(@pathparam("id") long id); /** * 查询邮件列表 * @param range * @param query * @return */ @get @path("/list") public response getlist(@headerparam("range") string range,@queryparam("query") string query); /** * 删除邮件 * @param id * @return */ @delete @path("/{id}") public response delete(@pathparam("id") long id); /** * 发送邮件 * @param content * @return 数据格式：{"data":{},"tomail":""} */ @post @path("/sendmails/{id}") public response sendmail(@pathparam("id")long id) ; /** * 下载附件 * @param id(附件的id) * @return */ @get @path("/getattach/{id}") @produces(mediatype.application_octet_stream) public response downloadattach(@pathparam("id") long id); }
实现类：
package com.gzydt.oa.resource.impl; import java.io.file; import java.io.fileinputstream; import java.io.fileoutputstream; import java.io.ioexception; import java.io.inputstream; import java.io.outputstream; import java.text.parseexception; import java.text.simpledateformat; import java.util.date; import java.util.hashset; import java.util.list; import java.util.set; import java.util.regex.matcher; import java.util.regex.pattern; import javax.activation.datahandler; import javax.ws.rs.webapplicationexception; import javax.ws.rs.core.mediatype; import javax.ws.rs.core.response; import javax.ws.rs.core.response.status; import javax.ws.rs.core.streamingoutput; import net.sf.json.jsonarray; import net.sf.json.jsonobject; import net.sf.json.jsonconfig; import org.apache.cxf.jaxrs.ext.multipart.attachment; /*import org.json.jsonarray;*/ import com.gzydt.oa.commons.queryparam; import com.gzydt.oa.entity.appendfile; import com.gzydt.oa.entity.innermails; import com.gzydt.oa.resource.innermailsresource; import com.gzydt.oa.service.appendfileservice; import com.gzydt.oa.service.innermailservice; import com.gzydt.oa.util.constant; import com.gzydt.oa.util.queryutil; public class innermailsresourceimpl implements innermailsresource { private innermailservice emailservice; public void setemailservice(innermailservice emailservice) { this.emailservice = emailservice; } private appendfileservice appendfileservice; public void setappendfileservice(appendfileservice appendfileservice) { this.appendfileservice = appendfileservice; } private static final string path = "data/oa/upload/mails"; @override public response save(string content, list<attachment> attachments) throws parseexception { //去掉懒加载字段 jsonconfig jsonconfig = constant.jsondateconfig; jsonconfig.setexcludes(new string[] { "appendfiles"}); jsonobject preceinfo = jsonobject.fromobject(content); jsonobject backinfo = new jsonobject(); innermails entity = new innermails(); date senddate = null; if ( preceinfo.optstring("senddate") != null && preceinfo.optstring("senddate") != "" ) { //这里的mm必须是要大写，若是写为mm,则是分钟，，格式一定要按照正规的来写<span class="con">yyyy-mm-dd hh:mm:ss</span>， //该大写就大写，小写就小写，并且中间有空格等，都不能错误。不然时间会出错 simpledateformat df = new simpledateformat("yyyy-mm-dd"); senddate = df.parse(preceinfo.optstring("senddate")); } preceinfo.put("senddate", senddate); entity = (innermails) jsonobject.tobean(preceinfo, innermails.class); if ( !preceinfo.has("type") ) { entity.settype(0); } entity = emailservice.save(entity); // 新增附件到附件表中 set<appendfile> appfiles=addattach(attachments, entity); entity.setappendfiles(appfiles); entity=emailservice.update(entity); if ( null != entity ) { backinfo = jsonobject.fromobject(entity); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } backinfo.put("message", "保存失败"); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } // 保存并关联附件 private set<appendfile> addattach(list<attachment> attachments,innermails entity){ set<appendfile> appenfiles=new hashset<appendfile>(); for (attachment attachment : attachments) { if (attachment.getcontenttype().tostring().startswith("application/octet-stream")) { datahandler dh = attachment.getdatahandler(); long time = new date().gettime(); string filename = null; try { filename = new string(dh.getname().getbytes("iso-8859-1"), "utf-8"); writefile(dh, filename); } catch (exception e) { e.printstacktrace(); } appendfile file = new appendfile(); file.setserialnumber(time);// 唯一标识 file.setfilename(filename);// 文件名 file.setextension(filename.substring(filename.lastindexof(".") + 1));// 文件后缀 file.settype("email");// 文件类型 emailservice.addattach(entity.getid(), file); appendfile result = null; result = appendfileservice.getbynumber(time); appenfiles.add(result); } } return appenfiles; } // 写文件 private void writefile(datahandler dh, string filename) throws ioexception { inputstream is = dh.getinputstream(); file file = new file(path); if ( !file.exists() ) { file.mkdirs(); } // log.info("附件目录：" + file.getabsolutepath()); writetofile(is, path + filename); } private void writetofile(inputstream is, string path) throws ioexception { file file = new file(path); outputstream out = new fileoutputstream(file); int len = 0; byte[] bytes = new byte[1024]; while ( (len = is.read(bytes)) != -1 ) { out.write(bytes, 0, len); } out.flush(); out.close(); } @override public response update(long id, string content, list<attachment> attachments) throws parseexception { innermails entity = emailservice.get(id); jsonobject preceinfo = jsonobject.fromobject(content); jsonobject backinfo = new jsonobject(); if ( null != entity ) { entity.setsubject(preceinfo.optstring("subject")); entity.settomails(preceinfo.optstring("tomails")); entity.seturgency(preceinfo.optstring("urgency")); date senddate = null; if ( preceinfo.optstring("senddate") != null && preceinfo.optstring("senddate") != "" ) { simpledateformat df = new simpledateformat("yyyy-mm-dd"); senddate = df.parse(preceinfo.optstring("senddate")); } //保存附件 set<appendfile> appfiles=addattach(attachments, entity); entity.setappendfiles(appfiles); entity.setsenddate(senddate); entity.setcontent(preceinfo.optstring("content")); entity.setmailuser(preceinfo.optstring("mailuser")); entity.setsendmail(preceinfo.optstring("sendmail")); entity.settype(preceinfo.optint("type")); addattach(attachments, entity); entity = emailservice.update(entity); if ( entity != null ) { backinfo = jsonobject.fromobject(entity); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } else { backinfo.put("message", "修改失败"); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } } backinfo.put("message", "没有找到指定的邮件"); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } @override public response get(long id) { jsonobject backinfo = new jsonobject(); innermails entity = emailservice.get(id); jsonobject jo; /*jsonconfig jsonconfig = constant.jsondateconfigwithhour; jsonconfig.setexcludes(new string[] {"appendfiles"});*/ // 去掉延迟加载的字段 jo = jsonobject.fromobject(entity); //修改状态为已读 entity.settype(3); emailservice.update(entity); if ( null != entity ) { backinfo = jsonobject.fromobject(jo); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } backinfo.put("message", "没有找到指定的内部邮件"); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } @override public response getlist(string range, string query) { queryparam queryparam = new queryparam(); int from = 0; int to = 9; try { string[] ranges = range.replace("items=", "").split("-"); from = integer.parseint(ranges[0]); to = integer.parseint(ranges[1]); } catch ( exception e ) { e.printstacktrace(); } queryparam.setfirst(from); int max = to - from + 1; if ( max > 0 ) { queryparam.setmax(max); } if(null!=query){ queryutil.preparequery(query, queryparam); } int count=emailservice.getcount(queryparam); list<innermails> list=emailservice.getlist(queryparam); jsonconfig jsonconfig=constant.jsondateconfig; jsonconfig.setexcludes(new string[] {"appendfiles"}); string contentrange=string.format("items %d-%d/%d", from,to,count); jsonarray ja = jsonarray.fromobject(list,jsonconfig); string entity = ja.tostring(); return response.ok(entity, mediatype.application_json).header("content-range", contentrange).build(); } @override public response delete(long id) { jsonobject backinfo=new jsonobject(); try { emailservice.delete(id); backinfo.put("message", "删除成功"); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } catch ( exception e ) { backinfo.put("message","删除失败"); return response.ok(backinfo.tostring(),mediatype.application_json).build(); } } @override public response sendmail(/*string content,list<attachment> attachments*/long id){ jsonobject backinfo=new jsonobject(); //通过id找到对应的邮件 innermails entity=emailservice.get(id); //将a的邮件mail状态改为发送 entity.settype(1); entity=emailservice.update(entity); //找到收件人，根据收件人的个数来新增多条邮件 string tomail=entity.gettomails(); string[] tomails=tomail.split(","); for(string tomail:tomails){ //新增邮件，修改mail1的拥有者，修改状态为未读 innermails newmails=new innermails(); newmails.setsubject(entity.getsubject()); newmails.settomails(entity.gettomails()); newmails.seturgency(entity.geturgency()); newmails.setappendfiles(entity.getappendfiles()); newmails.setsenddate(entity.getsenddate()); newmails.setcontent(entity.getcontent()); newmails.setsendmail(entity.getsendmail()); newmails.settype(2); newmails.setmailuser(getnofromchar(tomail)); emailservice.save(newmails); } backinfo.put("发送邮件的人数", tomails.length); return response.ok(backinfo.tostring(), mediatype.application_json).build(); } //截取字符串中的数字 private string getnofromchar(string params) { string regex="[^0-9]"; pattern p=pattern.compile(regex); matcher m=p.matcher(params); return m.replaceall("").trim(); } @override public response downloadattach(long id) { //根据附件名称去指定路径中找附件 appendfile appendfile=appendfileservice.get(id); if ( null == appendfile ) { return response.status(status.not_found).entity("找不到文件").build(); } final file file=new file(path, appendfile.getfilename()); jsonobject preceinfo=new jsonobject(); if(!file.exists()||!file.isfile()){ preceinfo.put("message","没有找到指定的文件"); return response.status(status.not_found).entity("找不到文件："+file.getname()).build(); } //下载附件 streamingoutput entity=download(file); string filename=file.getname().tolowercase(); string type=mediatype.application_octet_stream; if(filename.endswith(".jpg")||filename.endswith(".png")){ type="image/jpeg"; }else if(filename.endswith(".doc")){ type="application/msword;charset=utf-8"; }else if(filename.endswith(".pdf")){ type="application/pdf;charset=utf-8"; } try { //结局中文名字乱码的问题 filename=new string(file.getname().getbytes("utf-8"),"iso-8859-1"); } catch ( exception e ) { // todo: handle exception } return response.ok(entity, type).header("content-disposition", "inline;filename="+filename).build(); } //下载附件方法 private streamingoutput download(final file file) { streamingoutput entity=new streamingoutput() { @override public void write(outputstream output) throws ioexception, webapplicationexception { int len=0; byte[] buffer=new byte[1024]; inputstream intpstream=new fileinputstream(file); while((len = intpstream.read(buffer))>0){ output.write(buffer, 0,len); } intpstream.close(); output.flush(); output.close(); } }; return entity; } }
restful层的配置文件：
<?xml version="1.0" encoding="utf-8"?><blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:jaxrs="http://cxf.apache.org/blueprint/jaxrs"xmlns:cxf="http://cxf.apache.org/blueprint/core"xsi:schemalocation=" http://www.osgi.org/xmlns/blueprint/v1.0.0 http://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd http://cxf.apache.org/blueprint/jaxrs http://cxf.apache.org/schemas/blueprint/jaxrs.xsd http://cxf.apache.org/blueprint/core http://cxf.apache.org/schemas/blueprint/core.xsd"><jaxrs:server id="oarestservice" address="/oa"> <jaxrs:servicebeans> <ref component-id="mailrestservice" /> </jaxrs:servicebeans> <jaxrs:providers> <ref component-id="authfilter" /> </jaxrs:providers></jaxrs:server><!-- implements oauthdataprovider --><bean id="oauthprovider" class="com.gzydt.oa.auth.oauthmanager" /><bean id="authorizationservice" class="org.apache.cxf.rs.security.oauth2.services.authorizationcodegrantservice"> <property name="dataprovider" ref="oauthprovider" /></bean><jaxrs:server id="appserver" address="/myapp"> <jaxrs:servicebeans> <ref component-id="authorizationservice" /> </jaxrs:servicebeans></jaxrs:server><!-- <cxf:bus> <cxf:features> <cxf:logging /> </cxf:features> </cxf:bus> --><!-- we are using the osgi blueprint xml syntax to define a bean that we referred to in our jax-rs server setup. this bean carries a set of jax-rs annotations that allow its methods to be mapped to incoming requests. --><bean id="authrestservice" class="com.gzydt.oa.resource.impl.authresourceimpl"> <property name="userservice" ref="userservice" /></bean><bean id="authfilter" class="com.gzydt.oa.auth.authenticationfilter"></bean><bean id="backlogrestservice" class="com.gzydt.oa.resource.impl.backlogresourceimpl"> <property name="registerservice" ref="registerservice" /></bean><bean id="securityresource" class="com.gzydt.oa.resource.impl.securityresourceimpl" scope="singleton" init-method="init" destroy-method="destroy"> <property name="userservice" ref="userservice" /> <property name="deptservice" ref="deptservice" /> <property name="dutyusersservice" ref="dutyusersservice" /> <property name="unitservice" ref="unitservice" /></bean><!--添加bean --><bean id="mailrestservice" class="com.gzydt.oa.resource.impl.innermailsresourceimpl"> <property name="emailservice" ref="emailservice" /> <property name="appendfileservice" ref="appendfileservice" /></bean><!--添加bean结束 --><reference id="emailservice" interface="com.gzydt.oa.service.innermailservice" /> <!--添加reference结束 --></blueprint>
解析前端传来的参数：
package com.gzydt.oa.util;import java.util.arraylist;import java.util.hashmap;import java.util.iterator;import java.util.list;import java.util.map;import net.sf.json.jsonobject;import com.gzydt.oa.commons.queryparam;public class queryutil { /** * 解析url中的查询条件的参数 * * @param query * :查询标示 * @param queryparam * :url中的查询参数 * @return 为空 */ public static void preparequery(string query, queryparam queryparam) { try { jsonobject jo = jsonobject.fromobject(query); map<string, string> param = new hashmap<string, string>(); list<string> sorts = new arraylist<string>(); for ( @suppresswarnings("unchecked") iterator<string> iterator = jo.keyset().iterator(); iterator.hasnext(); ) { string key = iterator.next(); string value = jo.optstring(key); if ( !value.isempty() ) { if ( "sort".equals(key) ) { for ( string s : value.split(",") ) { if ( null != s ) { if ( s.startswith("8") ) {// 前端无法传“+” s = "+" + s.substring(1, s.length()); } else { s = "-" + s.substring(1, s.length()); } sorts.add(s); } } } else { param.put(key, value); } } } queryparam.setparam(param); queryparam.setsorts(sorts); } catch ( exception e ) { e.printstacktrace(); } }}
内部邮件的测试类：
package com.gzydt.oa.resource;import java.io.file;import java.io.filenotfoundexception;import java.io.ioexception;import java.io.unsupportedencodingexception;import java.util.arraylist;import java.util.list;import org.apache.commons.httpclient.httpclient;import org.apache.commons.httpclient.httpexception;import org.apache.commons.httpclient.namevaluepair;import org.apache.commons.httpclient.methods.deletemethod;import org.apache.commons.httpclient.methods.getmethod;import org.apache.commons.httpclient.methods.postmethod;import org.apache.commons.httpclient.methods.putmethod;import org.apache.commons.httpclient.methods.requestentity;import org.apache.commons.httpclient.methods.stringrequestentity;import org.apache.commons.httpclient.methods.multipart.filepart;import org.apache.commons.httpclient.methods.multipart.multipartrequestentity;import org.apache.commons.httpclient.methods.multipart.part;import org.apache.commons.httpclient.methods.multipart.stringpart;import org.apache.commons.httpclient.params.httpmethodparams;import org.json.jsonobject;import org.junit.assert;import org.junit.test;import org.slf4j.logger;import org.slf4j.loggerfactory;public class mailtest extends tester { private static final string test_url = "http://localhost:8181/cxf/oa/mails"; private static final logger log = loggerfactory.getlogger(mailtest.class); /** * 登记信息写邮件 * * @throws filenotfoundexception */ @test public void uploadorigtext() throws filenotfoundexception { /* * jsonobject jo = new jsonobject(); jo.put("subject", "周末计划");// 主题 * jo.put("tomails", "aa<13>,bb<2>");// 收件人 格式 ：姓名<userid>;姓名<userid> * jo.put("urgency", "加急");// 紧急程度 jo.put("senddate", "2015-4-11");// * 发布日期 jo.put("content", "周末购物");// 邮件内容 jo.put("mailuser", "14");// * 邮件拥有者 格式：userid jo.put("sendmail", "cc<14>");// 邮件发送者 * 格式：姓名<userid>,若只是新建，则不需要改字段 jo.put("type", "0");// * 状态标示：-1删除；0草稿；1发送；2未读收件，3已读收件 //新增不需要增加type */ // 要上传的文件 string path = "f:\\1.doc"; string path1 = "f:\\3.doc"; long start = system.currenttimemillis(); file file = new file(path); file filetext = new file(path1); // 'type': '0', string content = "{ 'content': '周末野炊','senddate': '2015-04-11','tomails': 'aa<13>,bb<2>','mailuser': '14','subject': '周末计划','sendmail': '','urgency': '加急'}"; part[] parts = { new filepart("file", file, "application/octet-stream", "utf-8"), new filepart("file", filetext, "application/octet-stream", "utf-8"),new stringpart("content", content, "utf-8") }; postmethod post = new postmethod(test_url); post.setrequestentity(new multipartrequestentity(parts, post.getparams())); httpclient httpclient = new httpclient(); string res = ""; try { int result = httpclient.executemethod(post); log.info("response status code: " + result); log.info("response body: "); res = getstringfrominputstream(post.getresponsebodyasstream()); log.info(res); } catch ( exception e ) { log.error("error connecting to {}", test_url); assert.fail("connection error"); } finally { // release current connection to the connection pool once you // are // done post.releaseconnection(); } log.info("断言：验证成功返回【ok】响应！"); assert.asserttrue("ok".equals(res)); long end = system.currenttimemillis(); log.info("验证用时（毫秒）：" + (end - start)); } /** * 发邮件 * @throws exception * @throws filenotfoundexception */ @test public void sendemail() throws exception { long id = 2l; log.info("开始测试发送邮件"); postmethod post = new postmethod(test_url +"/sendmails/"+ id); post.addrequestheader("accept", "application/json"); post.getparams().setparameter(httpmethodparams.http_content_charset, "utf-8"); /* jsonobject jo = new jsonobject(); jo.put("subject", "周末计划");// 主题 jo.put("tomails", "aa<13>,bb<2>");// 收件人 格式 ：姓名<userid>;姓名<userid> jo.put("urgency", "加急");// 紧急程度 jo.put("senddate", "2015-4-11");// 发布日期 jo.put("content", "周末购物");// 邮件内容 jo.put("mailuser", "14");// 邮件拥有者 格式：userid jo.put("sendmail", "cc<14>");// 邮件发送者 格式：姓名<userid>,若只是新建，则不需要改字段*/ // log.debug("设置请求参数：" + jo.tostring()); jsonobject jo = new jsonobject(); requestentity entity = new stringrequestentity(jo.tostring(), "application/json", "utf-8"); post.setrequestentity(entity); httpclient httpclient = new httpclient(); string res = ""; log.info("发送post请求"); int result = httpclient.executemethod(post); log.info(" 响应状态：" + result); res = this.getstringfrominputstream(post.getresponsebodyasstream()); log.info("响应结果：：" + res); log.info("断言："); } // 将邮件放进回收站，是将状态改为-1 @test public void updatetest() throws filenotfoundexception { log.info("开始测试更新"); long id = 1; putmethod put = new putmethod(test_url + "/" + id); // 要上传的文件 string path = "f:\\1.doc"; string path1 = "f:\\3.doc"; long start = system.currenttimemillis(); file file = new file(path); file filetext = new file(path1); string content = "{ 'content': '周末加班','senddate': '2015-4-11','tomails': 'aa<13>,bb<2>','mailuser': '14','subject': '周末计划','type': '0','sendmail': '','urgency': '加急'}"; part[] parts = { new filepart("file", file, "application/octet-stream", "utf-8"), new filepart("file", filetext, "application/octet-stream", "utf-8"),new stringpart("content", content, "utf-8") }; put.addrequestheader("accept", "application/json"); put.getparams().setparameter(httpmethodparams.http_content_charset, "utf-8"); put.setrequestentity(new multipartrequestentity(parts, put.getparams())); httpclient httpclient = new httpclient(); string res = ""; try { int result = httpclient.executemethod(put); log.info("response status code: " + result); log.info("response body: "); res = getstringfrominputstream(put.getresponsebodyasstream()); log.info(res); } catch ( exception e ) { log.error("error connecting to {}", test_url); assert.fail("connection error"); } finally { put.releaseconnection(); } log.info("断言：验证成功返回【ok】响应！"); assert.asserttrue("ok".equals(res)); long end = system.currenttimemillis(); log.info("验证用时（毫秒）：" + (end - start)); } /** * 根据特定的id来找到值班人员的用户信息 */ @test public void findtest() { long id = 15l; getmethod get = new getmethod(test_url + "/" + id); httpclient client = new httpclient(); string res = ""; try { int retcode = client.executemethod(get); log.info("响应状态 " + retcode); res = this.getstringfrominputstream(get.getresponsebodyasstream()); log.info("响应结果" + res); } catch ( exception e ) { log.error("该url路径出错，服务未开启，请检查", test_url + "/" + id); assert.fail("连接失败."); } finally { get.releaseconnection(); } } @test public void querytest() { log.info("开始测试分页查询"); getmethod get = new getmethod(test_url + "/list"); get.getparams().setparameter(httpmethodparams.http_content_charset, "utf-8"); list<namevaluepair> params = new arraylist<namevaluepair>(); // 设置分页有信息 get.setrequestheader("range", "items=0-9"); jsonobject jo = new jsonobject(); log.debug("请求参数：：" + jo.tostring()); // jo.put("mailuser", "14"); jo.put("senddate", "2015-01-10~2015-01-13"); /* * jo.put("type", "0"); jo.put("content","周末"); */ // jo.put("issuedate", "2015-01-10~2015-02-24"); // jo.put("sendfiledate", "2015-01-14~2015-02-04"); // jo.put("getfiledate", "2015-01-11~2015-02-04"); // jo.put("fromunit", "scgovernment"); /* jo.put("issuedate", "2015-3") */ // jo.put("number","yfp"); // jo.put("refnumber", "a11111"); // jo.put("sendunit", "shengbangongting"); // jo.put("title","22222"); jsonobject jb = new jsonobject(); params.add(new namevaluepair("query", jo.tostring()));// 从0开始的 get.setquerystring(params.toarray(new namevaluepair[0])); httpclient httpclient = new httpclient(); string res = ""; try { int result = httpclient.executemethod(get); log.info("响应状态 " + result); res = this.getstringfrominputstream(get.getresponsebodyasstream()); log.info("响应结果 " + res); } catch ( exception e ) { log.error("该url路径出错，服务未开启，请检查", test_url); assert.fail("连接失败."); } finally { get.releaseconnection(); } } /** * 测试删除周知事项 */ @test public void testdelete() { log.info("开始测试删除通知"); long id = 1l; deletemethod delete = new deletemethod(test_url + "/" + id); httpclient client = new httpclient(); string res = ""; try { log.info("发送delete请求删除通知"); int retcode = client.executemethod(delete); log.info("响应状态：" + retcode); res = this.getstringfrominputstream(delete.getresponsebodyasstream()); log.info("响应结果: " + res); } catch ( exception e ) { log.error("测试错误", e); assert.fail("连接出错"); } finally { /* 释放url的资源 */ delete.releaseconnection(); } log.info(res); }}
在添加一个正常的测试新增的方法：
@testpublic void testadd(){ log.info("开始测试增加"); postmethod post = new postmethod(test_url); post.addrequestheader("accept", "application/json"); post.getparams().setparameter(httpmethodparams.http_content_charset, "utf-8"); try { jsonobject jo = new jsonobject(); jo.put("day", "2015-4-10"); jo.put("type", "0"); jo.put("content", "gfdz参加了2014年广东省某某某某活动"); jo.put("mainleaderids", "2,3"); jo.put("relevantleaderids", "5,6"); // date jo.put("goverevent", true); jo.put("ownevent", false); jo.put("ownerid", "2"); log.debug("设置请求参数：" + jo.tostring()); requestentity entity = new stringrequestentity( jo.tostring(), "application/json", "utf-8"); post.setrequestentity(entity); httpclient httpclient = new httpclient(); string res = ""; log.info("发送post请求"); int result = httpclient.executemethod(post); log.info(" 响应状态：" + result); res = this.getstringfrominputstream(post.getresponsebodyasstream()); log.info("响应结果：：" + res); assert.asserttrue(res.contains("增加值班表")); } catch (exception e) { log.error("测试错误", e); assert.fail("连接出错"); } finally { post.releaseconnection(); }}
感谢大家的阅读，希望大家收益多多。
本文转自： http://community.itbbs.cn/thread/758207/
推荐教程：《java视频教程》
以上就是jpa动态查询语句（代码详解）的详细内容。