如何将参数传递给子查询 [英] how to transfer parameter to sub query

问题描述

我只使用findAllregions参数的userIdx来findFavoriteStore

i just use userIdx of findAllregions's parameter to findFavoriteStore

这是我的查询

@Select("SELECT * FROM region")
@Results(value = {
        @Result(property = "regionIdx", column = "regionIdx"),
        @Result(property = "stores", javaType = Store.class, column = "storeIdx",
                many = @Many(select = "com.travely.travely.mapper.StoreMapper.findFavoriteStore", fetchType = FetchType.LAZY))
})
List<Region> findAllRegions(@Param("userIdx") final Long useridx);



@Select("SELECT s.* FROM store as s NATURAL JOIN favorite as f WHERE f.userIdx=#{userIdx} AND f.isFavorite = 1")
    List<Store> findFavoriteStore(@Param("userIdx") final long userIdx);

它可以选择"findAllRegions"的区域 但无法选择"findFavoriteStore"的商店

it works to select region of 'findAllRegions' but doesn't work to select store of 'findFavoriteStore'

推荐答案

该查询不起作用，因为column属性配置不正确.

The query does not work because the column attribute is configured incorrectly.

这是列属性文档的相关部分. :

Here is relevant part of the column attribute documentation:

数据库中的列名，或具有别名的列标签 保留将作为以下形式传递给嵌套语句的值: 输入参数.

The column name from the database, or the aliased column label that holds the value that will be passed to the nested statement as an input parameter.

如您所见，只能使用主查询结果中的列.

As you see only the column from the main query result can be used.

这意味着您需要在查询中包括人工列，并像这样在@Result.column中使用它:

It means that you either need to include the artificial column to the query and use it in the @Result.column like this:

@Select("SELECT #{userIdx} userIdx, r.* FROM region r")
@Results(value = {
     @Result(property = "regionIdx", column = "regionIdx"),
     @Result(
        property = "stores", javaType = Store.class, column = "userIdx",
        many = @Many(
                 select = "com.travely.travely.mapper.StoreMapper.findFavoriteStore",
                 fetchType = FetchType.LAZY))
})
List<Region> findAllRegions(@Param("userIdx") final Long useridx);

或者，如果使用Java 8+，则可以使用默认接口方法来获取关联/集合，如下所示:

Alternatively if java 8+ is used you can use default interface methods to fetch associations/collections like this:

interfact MyMapper {

  @Select("SELECT * FROM region")
  @Results(value = {
        @Result(property = "regionIdx", column = "regionIdx")
  })
  List<Region> findAllRegions();

  default List<Region> findAllRegions(Long userIdx) {
      List<Region> regions = findAllRegions();
      List<Strore> favoriteStores = findFavoriteStore(userIdx);
      for(Region region:regions) {
          region.setStores(favoriteStores);
      }
      return regions;
  }

  @Select("SELECT s.* FROM store as s NATURAL JOIN favorite as f WHERE f.userIdx=#{userIdx} AND f.isFavorite = 1")
  List<Store> findFavoriteStore(@Param("userIdx") final long userIdx);

}

请注意，这不使用对喜欢的商店的延迟获取.似乎不需要这样做，因为在不需要收藏夹商店的情况下，可以使用没有userIdx的查询(并且应该使用should0.

Note that this does not use lazy fetching of the favourite stores. It seems that this is not needed as in the contexts that favorite stores are not needed the query without userIdx can (and should0 be used.

这篇关于如何将参数传递给子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！