**
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&¶meter.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&¶meter.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的封装,所以也有一级缓存,需要注意下内存快照的问题,持久态对象需要注意成员变量重新负值后就会被持久化到数据库里。