Spring Data Jpa 複雜查詢總結 (多表關聯 以及 自定義分頁 )

只是做一個總結所以就不多說廢話了
實體類

@Entity
@Table(name = "t_hotel")
@Data
public class THotel {
    @Id
    private int id;
    private String name;
    private String address;
    /**
     * 城市id
     */
    private String city;
}

@Entity
@Table(name = "t_city")
@Data
public class TCity {
    @Id
    private int id;
    private String name;
    private String state;
    private String country;
    private String map;

}

在啟動SpringBoot的時候 SpringDataJpa會自動的在資料庫中生成表結構.
為了查詢要求,我隨便的增加了一些資料,如下圖所示

新建介面

public interface TCityRepository extends JpaRepository<TCity, Integer>, JpaSpecificationExecutor<TCity> {
    
    
}

單元測試類

@RunWith(SpringRunner.class)
@SpringBootTest
public class TCityRepositoryTest{

     @Autowired
    private TCityRepository tCityRepository;
}

1.查詢出Id小於3,並且名稱帶有shanghai的記錄.

/**
 * 查詢出Id小於3,並且名稱帶有`shanghai`的記錄.
 *
 * @param id   id
 * @param name 城市名稱
 * @return 城市列表
 */
List<TCity> findByIdLessThanAndNameLike(int id, String name);

單元測試

  
@Test
public void findByIdLessThanAndNameLike() throws Exception {
    List<TCity> shanghai = tCityRepository.findByIdLessThanAndNameLike(3, "%shanghai%");
    Assert.assertTrue(shanghai.size() > 0);
}

2.通過旅店名稱分頁查詢旅店以及城市的所有資訊

/**
 * 通過旅店名稱分頁查詢旅店以及城市的資訊
 *
 * @param name     旅店名稱
 * @param pageable 分頁資訊
 * @return Page<Object[]>
 */
@Query(value = "select t1.name as cityName,t2.name as hotelName\n" +
        "from t_city t1\n" +
        "  left join t_hotel t2 on t2.city = t1.id\n" +
        "where t2.name = :name",
        countQuery = "select count(*)" +
                "from t_city t1 \n" +
                "  left join t_hotel t2 on t2.city = t1.id\n" +
                "where t2.name = :name"
        , nativeQuery = true)
Page<Object[]> findCityAndHotel(@Param("name") String name, Pageable pageable);

為了節約時間 我只在select 與 from 之間 分別查詢了城市的名稱以及旅店的名稱如果要查所有的資訊,可以換成
t1.* ,
t2.*

單元測試

  @Test
public void findCityAndHotel() throws Exception {
    Page<Object[]> cityAndHotel = tCityRepository.findCityAndHotel("酒店", new PageRequest(0, 10));
    Assert.assertTrue(cityAndHotel.getTotalElements() > 0);
}    

關於把List<Object[]> 轉換成List<物件> 的方法 我已經在上一篇JPA的文章中比較清楚的說了,因此我就不再重複的敘述一遍了

3.HQL通過旅店名稱查詢旅店以及城市的所有資訊

3和2其實是一樣的,為了方便我就不作出分頁查詢了

HQL可以用map來接受返回的引數,具體的用法如下所示:

/**
 * HQL通過旅店名稱查詢旅店以及城市的所有資訊
 *
 * @return
 */
@Query(value = "select new map(t1,t2) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
List<Map<String, Object>> findCityAndHotelByHQL(@Param("name") String name);

測試方法和2是差不多的 我就不貼上了

Map<String.Object> 轉換實體類的方法也挺多的我就不多說了,如果是直接返回給前臺的話,也沒有必要轉換成物件.

4.HQL通過旅店名稱查詢旅店以及城市的所有資訊 直接返回實體類

/**
 * 關聯查詢
 *
 * @return
 */
@Query(value = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
List<CityHohel> findCityAndHotelByHQLResultObj(@Param("name") String name);

為了方便CityHohel我只封裝了2個屬性,這和HQL查詢的欄位是完全一致的,也必須要保持一致.

/**
* Created by ZhuPengWei on 2018/5/12.
*/
@Data
public class CityHohel {

        private String cityName;
        private String hotelName;
        
        public CityHohel(String cityName, String hotelName) {
            this.cityName = cityName;
            this.hotelName = hotelName;
        }
}

當然這個帶參的構造方法是必須要寫的,否則會丟擲轉換實體的異常

單元測試

@Test
public void findCityAndHotelByHQLResultObj() throws Exception {

    List<CityHohel> cityAndHotelByHQLResultObj = tCityRepository.findCityAndHotelByHQLResultObj("酒店");
    Assert.assertTrue(cityAndHotelByHQLResultObj.size() > 0);
}

4.HQL通過旅店名稱分頁查詢旅店以及城市的所有資訊 直接返回實體類

/**
 * 關聯查詢
 *
 * @return
 */
@Query(value = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name",
        countQuery = "select count(*) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name =:name")
Page<CityHohel> findCityAndHotelAllSelf(@Param("name") String name, Pageable pageable);

    @Test
public void findCityAndHotelAllSelf() throws Exception {
    Page<CityHohel> cityAndHotelAllSelf = tCityRepository.findCityAndHotelAllSelf("酒店", new PageRequest(0, 10));

    Assert.assertTrue(cityAndHotelAllSelf.getTotalElements() > 0);
}

5.動態查詢旅店以及城市的所有資訊 直接返回實體類

如果是動態查詢的話當然首先得構造一條sql去查詢了,當然如果不是自定義實體物件的話這樣的網上一大堆我就不寫了.

直接走測試

@Autowired
@PersistenceContext
private EntityManager entityManager;

@Test
public void testDynamic() throws Exception {
        String sql = "select new pers.zpw.domain.CityHohel(t1.name AS cityName,t2.name AS hotelName) from  TCity t1 left  join THotel t2 on t1.id=t2.city where t2.name ='酒店'";
        Query query = entityManager.createQuery(sql);
        List resultList = query.getResultList();
        Assert.assertTrue(resultList.size() > 0);
}

這樣測試是通過的,因此可以知道在業務層的方法中我們可以動態的構造SQL語句. 比如說可以在介面中這樣子來定義一個方法

  /**
 * 自定義查詢
 * @param sql
 * @param entityManager
 * @return
 */
default List customQuery(String sql, EntityManager entityManager) {
    return entityManager.createQuery(sql).getResultList();
}

然後在測試類中動態的根據條件去拼接SQL語句去呼叫