JPA Example 默认 join

起因：

由于同事增加了一个对象关联。并且采用了Example查询，导致了一个Null的问题。

 

主表：BoBookingorder

关联表：BoJobcontainerinfo

 

一 关联如下：

@Entity
@Table(name = "bo_bookingorder",catalog = "kintech_bo")
public class BoBookingorder  {
    private String bookingOrderUUID;
    
    private BoJobcontainerinfo boJobcontainerinfo=new BoJobcontainerinfo();
     
    @Id
    public String getBookingOrderUUID() {
        return bookingOrderUUID;
    }
 
    
    @OneToOne
    @JoinColumn(name = "bookingOrderUUID", referencedColumnName = "orderUUID")
    public BoJobcontainerinfo getBoJobcontainerinfo() {
        return boJobcontainerinfo;
    }
 
    public void setBoJobcontainerinfo(BoJobcontainerinfo boJobcontainerinfo) {
        this.boJobcontainerinfo = boJobcontainerinfo;
    }
 
    
}

 

二 使用三种查询方式，看sql生成

1 根据bookingOrderNo(非主键)查询

生成的sql

// 生成了2段SQL
 
select
        * 
    from
        bo_bookingorder  
    where
        BookingOrderNo=? 
        and IsValid=1  
    order by
        CreateTime desc  limit 1 
 
======================================================
 
select
        *
    from
        kintech_bo.bo_jobcontainer bobookingo0_ 
    where
        bobookingo0_.orderUUID=?

PS：

很明显，自己写@Query注解sql (select * from xxx where .....)，分成了2次查询，

这意味着，先查询了主表：BoBookingorder，然后填充了关联表：BoJobcontainerinfo

 

2 Example查询

查询代码：还是根据bookingOrderNo 查询

        BoBookingorder entity = new BoBookingorder();
        entity.setBookingOrderNo(bookingOrderNo);
        Example<BoBookingorder> ex = Example.of(entity);
        BoBookingorder bo1 = bookingorderJpaService.findOne(ex);

生成的sql

    select
        bo.*
    from
        kintech_bo.bo_bookingorder bo 
    inner join
        kintech_bo.bo_jobcontainerinfo boc 
            on bo .bookingOrderUUID=boc .OrderUUID 
    where
        bo.BookingOrderNo=? 

PS:

注意  inner join ，这个就是罪魁祸首。由于是inner join ，导致如果 jobcontainerinfo表没有数据，则bookingorder也返回null

 

3 常规findOne 

调用Jpa自带的findOne，通过主键查询，看看是否会inner join 关联

代码：

BoBookingorder bo2=bookingorderJpaService.findOne("xxx");

生成的sql

select
        bo.* 
    from
        kintech_bo.bo_bookingorder bo 
    left outer join
        kintech_bo.bo_jobcontainerinfo boc 
            on bo.bookingOrderUUID=boc.OrderUUID 
    where
        bo.BookingOrderUUID=?

PS:

可以看到，JPA自带的findOne是left join，所以没有引发不适。

 

三  结论：慎用Example来做查询条件。