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); } }
在添加一个正常的测试新增的方法:
@Test public 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动态查询语句(代码详解)的详细内容,更多请关注php中文网其它相关文章!
已抢17341个
抢已抢50249个
抢已抢121353个
抢已抢184617个
抢已抢66430个
抢已抢350987个
抢