Spring Data JPA 实现多表关联查询

两种常用方式

|
|-1.创建一个组合结果类来接收连表查询后的结果                               //适用一对一
|
|-2.使用 nativeQuery = true 指定使用原生 SQL 进行查询,返回 List<Object[]>  //适用多对多

构造组合结果类方式实现步骤(以User和Address一对一关系拼装为例)

|
|-创建domain|--UserInfo.java    (类注解:@Entitity,@Table(name="tb_user");     主键注解:@Id,@GeneratedValue(strategy=GenerationType.IDENTITY))
|           |--Address.java     (类注解:@Entitity,@Table(name = "tb_address");主键注解:@Id,@GeneratedValue(strategy=GenerationType.IDENTITY))
|           
|-构造结果集类  |--import java.io.Serializable;
|               |--@Data
|               |--@NoArgsConstructor
|               |--@AllArgsConstructor 
|               |--public class ViewInfo implements Serializable {
|               |--  private static final long serialVersionUID = -6347911007178390219L;
|               |--  private UserInfo userInfo;
|               |--  private Address  address;
|               |--}
|
|-关联JpaRepository|--package com.johnfnash.learn.repository;
|                  |  
|                  |  import org.springframework.data.jpa.repository.JpaRepository;
|                  |  import com.johnfnash.learn.domain.Address;
|                  |  
|                  |  public interface AddressRepository extends JpaRepository<Address, Long> {
|                  |  
|                  |  }
|                  |
|                  |--package com.johnfnash.learn.repository;
|                  |  
|                  |  import java.util.List;
|                  |  import org.springframework.data.jpa.repository.JpaRepository;
|                  |  import org.springframework.data.jpa.repository.Query;
|                  |  import com.johnfnash.learn.domain.UserInfo;
|                  |  import com.johnfnash.learn.domain.Address;
|                  |  import com.johnfnash.learn.domain.ViewInfo;
|                  |  
|                  |  public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {
|                  |  
|                  |    //这里的 ViewInfo 类用来一个用来接收多表查询结果集的类（使用 new + 完整类名构造函数）
|                  |    @Query(value = "SELECT new com.johnfnash.learn.domain.ViewInfo(u, a) FROM UserInfo u, Address a WHERE u.addressId = a.addressId")
|                  |    public List<ViewInfo> findViewInfo();
|                  |  
|                  |  }

使用nativeQuery实现示例

package com.johnfnash.learn.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import com.johnfnash.learn.domain.Book;

public interface BookRepository extends JpaRepository<Book, Integer> {

  @Query(nativeQuery = true, value = "SELECT b.id, b.name, a.name as authorName from book b, author a, book_author ba"
      + " where b.id = ba.book_id and a.id = ba.author_id and b.name like ?1 group by b.id, b.name")
    List<Object[]> findByNameContaining(String name);

}