您的位置 首页 java

JPA动态查询语句(代码详解)

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动态查询语句(代码详解)

文章地址:https://www.zhihuclub.com/14772.shtml

关于作者: 智云科技

热门文章

网站地图