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); } /* @Overridepublic 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 */@Testpublic 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// donepost.releaseConnection();}LOG.info("断言：验证成功返回【ok】响应！");Assert.assertTrue("ok".equals(res));long end = System.currentTimeMillis();LOG.info("验证用时（毫秒）：" + (end - start));}/** * 发邮件 * @throws Exception * @throws FileNotFoundException */@Testpublic 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");// 邮件拥有者 格式：userIdjo.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@Testpublic 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来找到值班人员的用户信息 */@Testpublic 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();}}@Testpublic 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();}}/** * 测试删除周知事项 */@Testpublic 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动态查询语句（代码详解）的详细内容，更多请关注24课堂在线网其它相关文章！
