spring-data-jpa动态拼接sql语句实现动态的多表条件查询

**

spring-data-jpa 动态拼接sql语句

** spring-data-jpa对于简单的数据操作确实使用起来比较方便,但是对于一些比较复杂的动态的多表条件查询就不是那么简单了,对于需要些sql语句并且需要动态的添加条件的时候就得使用jpa的EntityManager来完成了.
以下为以返回EasyUI分页数据为例,

public interface VideoDao extends JpaRepository<Video, Long>, JpaSpecificationExecutor<Video>{
	public Page<VideoExtend> findVideoList(VideoParam vp,Pageable pageable);
}
**//此接口继承JpaRepository和JpaSpecificationExecutor**

**//这个实现类和上面的接口在同一级目录下,并且同一命名规则.无需用implements与上面的接口产生正式的实现关系,但是还是要规范的实现上面的接口的方法.**
package cn.folkcam.rablive.cp.dao;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Collections;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.taobao.api.internal.toplink.embedded.websocket.util.StringUtil;

import cn.folkcam.rablive.cp.entity.CommonSearchParameter;
import cn.folkcam.rablive.cp.entity.TradeDaily;
import cn.folkcam.rablive.cp.entity.Video;
import cn.folkcam.rablive.cp.entity.VideoExtend;
import cn.folkcam.rablive.cp.entity.VideoParam;

/**
 * videoDao的实现类
 */
public class VideoDaoImpl {

	@PersistenceContext
	private EntityManager entityManager;
	
	/**
	 * @param parameter
	 * @param pageable
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<VideoExtend> findVideoList(VideoParam parameter,Pageable pageable) {
		StringBuilder dataSql = new StringBuilder("SELECT v.fee_stat,v.action_id,v.video_icon_audit,"
				+ "v.video_url,v.video_wide,v.video_high,v.privilege_list,v.video_lable,"
				+ "v.praise_cnt,v.play_cnt,v.comment_cnt,v.resend_cnt,v.gift_cnt,v.reward_cnt,"
				+ "v.play_cnt,v.position_name,v.longitude,v.latitude,v.cos_lat,v.update_time,"
				+ "v.video_id,v.video_customer_id,v.video_title,v.aliyun_video_id,v.create_time,"
				+ "v.video_status,a.remark,v.video_icon,v.report_cnt,v.fee_stat,"
				+ "v.video_lable,c.sex "
				+ " FROM t_video v join t_customer c on v.video_customer_id = c.customer_id "
				+ "left join t_action a on v.action_id = a.action_id ");
			StringBuilder countSql = new StringBuilder("SELECT count(1) FROM t_video v ");
//				+ "left join t_action a on v.video_id = a.video_id ");
		
		//拼接where条件
		StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
		if (StringUtils.isNotEmpty(parameter.getCustomerId())) {
			whereSql.append(" AND v.video_customer_id = :customerId");
		}
		
		if (StringUtils.isNotEmpty(parameter.getStatus())) {
			whereSql.append(" AND v.video_status = :status");
		}

		if (parameter.getEndTime() != null && parameter.getStartTime() != null) {
			whereSql.append(" AND v.create_time >= :startTime AND v.create_time <= :endTime");
		}

		if (StringUtils.isNotEmpty(parameter.getVideoTitle())) {
			whereSql.append(" AND v.video_title like concat('%',:videoTitle,'%')");
		}													

		if(parameter.getSex()!= null&&parameter.getSex()!=-1){
			countSql.append(" join t_customer c on v.video_customer_id = c.customer_id ");
			whereSql.append(" AND c.sex = :sex");
		}
		//组装sql语句
		dataSql.append(whereSql).append(" order by v.create_time desc");
		countSql.append(whereSql);
		
		//创建本地sql查询实例
		Query dataQuery = entityManager.createNativeQuery(dataSql.toString(), VideoExtend.class);
		Query countQuery = entityManager.createNativeQuery(countSql.toString());
		
		//设置参数
		if (StringUtils.isNotEmpty(parameter.getCustomerId())) {
			dataQuery.setParameter("customerId", parameter.getCustomerId());
			countQuery.setParameter("customerId", parameter.getCustomerId());
		}
		if (parameter.getEndTime() != null && parameter.getStartTime() != null) {
			dataQuery.setParameter("startTime", parameter.getStartTime(), TemporalType.TIMESTAMP);
			dataQuery.setParameter("endTime", parameter.getEndTime(), TemporalType.TIMESTAMP);
			countQuery.setParameter("startTime", parameter.getStartTime(), TemporalType.TIMESTAMP);
			countQuery.setParameter("endTime", parameter.getEndTime(), TemporalType.TIMESTAMP);
		}
		if (StringUtils.isNotEmpty(parameter.getStatus())) {
			dataQuery.setParameter("status", parameter.getStatus());
			countQuery.setParameter("status", parameter.getStatus());
		}
		if (StringUtils.isNotEmpty(parameter.getVideoTitle())) {
			dataQuery.setParameter("videoTitle", parameter.getVideoTitle());
			countQuery.setParameter("videoTitle", parameter.getVideoTitle());
		}
		if(parameter.getSex()!= null&&parameter.getSex()!=-1){
			dataQuery.setParameter("sex",parameter.getSex());
			countQuery.setParameter("sex",parameter.getSex());
		}
		//设置分页
		dataQuery.setFirstResult(pageable.getOffset());
		dataQuery.setMaxResults(pageable.getPageSize());
		BigInteger count = (BigInteger) countQuery.getSingleResult();
		Long total = count.longValue();
		List<VideoExtend> content2 = total > pageable.getOffset() ? dataQuery.getResultList() : Collections.<VideoExtend> emptyList();
		return new PageImpl<>(content2, pageable, total);
	}	
}

简单的写写,欢迎补充纠正

上面的那个VideoExtend类就是数据表的一个实体类

@Entity
@Table(name = "t_video")
public class VideoExtend

如果遇到不要关联查询好查询可以使用object数组来返回数据，就是没那么优雅了。

上面的代码中
//创建本地sql查询实例时不加入第二个参数
		Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
//数据分装就麻烦些
		List list = dataQuery.getResultList();
        List<VideoExtend> content = new ArrayList<>();
        if (!list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
            //获取结果集的每一天数据
                Object[] obj = (Object[]) list.get(i);
                VideoExtend videoExtend = new VideoExtend();
                //这里的obj[3]对应返回数据的第四列video_url
                videoExtend.videoUrl(obj[3] != null ? String.valueOf(obj[3]) : "");
                ......
                content.add(LiveAnchorP);
            }
        } 

先写到这里，应该还有更好的方法。
还有就是jpa是hibernate的封装，所以也有一级缓存，需要注意下内存快照的问题，持久态对象需要注意成员变量重新负值后就会被持久化到数据库里。