Spirng Data JPA 联表查询以及获取指定字段

项目中有的地方需要多表关联查询，spring data jpa适合单表查询，但也提供了方法，此文记录关联查询方法，以防后用。

1.Spirng Data JPA 联表查询

**两表联查 TvCatTaskInfo TvCatChannelInfo**
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;
// TvCatChannelInfo 主键 cat_id   
@Data
@DynamicInsert
@DynamicUpdate
@Entity
@Table(name = "lp_tv_cat_channel_info")
public class TvCatChannelInfo implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 爬取id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cat_id")
    private Long catId;

    /**
     * 爬取名称
     */
    @Column(name = "name_")
    private String name;

    /**
     * 状态  0:停用  1:启用
     */
    @Column(name = "status_")
    private Integer status;
}

// TvCatTaskInfo 关联主键 cat_id   
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalDateTime;
/**
 * 电视猫任务表
 */
@Data
@DynamicInsert
@DynamicUpdate
@Entity
@Table(name = "lp_tv_cat_task_info")
public class TvCatTaskInfo implements Serializable {
    private static final long serialVersionUID = 1L;
    /**
     * 任务id
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "task_id")
    private Long taskId;
    /**
     * 爬取id
     */
    @Column(name = "cat_id")
    private Long catId;
    
    @OneToOne(targetEntity = TvCatChannelInfo.class)
    @JoinColumn(name =      "cat_id",referencedColumnName="cat_id",insertable=false,updatable=false)
    private TvCatChannelInfo tvCatChannelInfo;
}
//动态查询，返回TvCatTaskInfo，属性包括TvCatChannelInfo
Specification<TvCatTaskInfo> specification = new Specification<TvCatTaskInfo>() {
    @Override
    public Predicate toPredicate(Root<TvCatTaskInfo> root, CriteriaQuery<?> query,
                                         CriteriaBuilder criteriaBuilder) {
 List<Predicate> predicates = new ArrayList<>();
 //动态查询条件拼接
Join<TvCatTaskInfo, TvCatChannelInfo> join = root.join("tvCatChannelInfo", JoinType.INNER);
//定时器状态状态  0:停用  1:启用
if (!StringUtil.isNullStr(taskSelectVo.getStatus())) {
    //join.get("status")取得参数是TvCatChannelInfo对象中的参数
    predicates.add(criteriaBuilder.equal(join.get("status").as(Integer.class),       taskSelectVo.getStatus()));
   }
return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
 Page<TvCatTaskInfo> pageable1 = 
     tvCatTaskInfoRepository.findAll(specification, pageable);

2.Spirng Data JPA 联表查询返回特定字段

1. Spirng Data JPA自定义sql

   //自定义需要字段的vo对象 ProgramListVo 
    @Query(value = "select new com.wondertek.oes.live.manage.vo.ProgramListVo(o.outChannelId,o.name,o1.name,o1.startTime,o1.endTime,o1.isLookback,o1.isShield,o1.synchro,o1.id) " +
   "from OutChannelInfo  o , OutChannelPlaybill   o1   where o.outChannelId=o1.outChannelId  order by o1.createdTime desc ")
    List<ProgramListVo> findOutChannelPlaybills(Integer isShield, Integer isLookback, Integer synchro, String channelName, LocalDateTime start, LocalDateTime end, Pageable pageable);
   

2. 原生sql实现动态查询特定字段

 @Query(value = "SELECT o1.out_channel_id, o1.name_ as channelname,o2.name_ ,o2.start_time,"
            + "o2.end_time,o2.is_shield,o2.is_lookback,o2.synchro_,o2.id_"
            + " from lp_out_channel_playbill as o2,lp_out_channel_info as o1 \n"
            + "where o1.out_channel_id = o2.out_channel_id  and if(?1 is not null,o2.is_shield =?1, 1=1)"
            + " and if(?2 is not null,o2.is_lookback =?2, 1=1) "
            + "and if(?3 is not null,o2.synchro_ =?3, 1=1) and if(?4 is not null,o1.name_ like %?4%, 1=1)"
            + " and if(?5 is not null,o2.start_time >=?5, 1=1) and if(?6 is not null,o2.end_time <= ?6, 1=1)"
            + " and if(?7 is not null,o2.name_ like %?7%, 1=1)", nativeQuery = true)
List<Object[]> getOutchannelPlaybills(Integer isShield, Integer isLookback, Integer synchro, String channelName,LocalDateTime start, LocalDateTime end, String keywords, Pageable pageable);

以上两种方法实现关联查询及获取特定字段，如有错误，烦请指出，谢谢。