mybatis sql in 查询 mapper与service层写法

最新推荐文章于 2025-07-27 14:29:36 发布

原创最新推荐文章于 2025-07-27 14:29:36 发布 · 1w 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis

eclispe j2ee 同时被 2 个专栏收录

43 篇文章

订阅专栏

mysql

10 篇文章

订阅专栏

本文介绍了在MyBatis中进行IN查询时，如何在Mapper和Service层处理参数，特别是当参数为List或Array时的注意事项。通过示例展示了在XML映射文件中如何设置collection属性，并提到了当查询参数为多个时使用Map的重要性。

当查询的参数只有一个时
findByIds(List ids)
1.1 如果参数的类型是List, 则在使用时，collection属性要必须指定为 list（要是Repository 层指定参数必须与参数一致）
Xml代码收藏代码

<select id="findByIdsMap" resultMap="BaseResultMap">  
 Select  
 <include refid="Base_Column_List" />  
 from jria where ID in  
 <foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
  #{item}  
 </foreach>  
</select>

findByIds(Long[] ids)
1.2 如果参数的类型是Array,则在使用时，collection属性要必须指定为 array（要是Repository 层指定参数必须与参数一致）
Xml代码收藏代码

<select id="findByIdsMap" resultMap="BaseResultMap">  
select  
<include refid="Base_Column_List" />  
from tabs where ID in  
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">  
 #{item}  
</foreach>  
   </select>

当查询的参数有多个时,例如 findByIds(String name, Long[] ids)
这种情况需要特别注意，在传参数时，一定要改用Map方式, 这样在collection属性可以指定名称
下面是一个示例
Map

<select id="findByIdsMap" resultMap="BaseResultMap">  
 select  
 <include refid="Base_Column_List" />  
 from tabs where ID in  
 <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">  
  #{item}  
 </foreach>  
</select>

下面看一下部分代码
讲的是当查询参数只有一个而且是List的情况我，我们用的最多

@Repository
public interface OrgnizationRepository {
    1. List<Orgnization> selectListIds(@Param("lists") List<Long> ids);

    2. List<Orgnization> selectListIds( List<Long> ids);
}

public interface OrgnizationService {
    public List<OrgnizationModel> selectListIds(List<Long> listLong);
}

@Service
public class OrgnizationServiceImpl implements OrgnizationService {

    @Autowired
    private BeanMapper beanMapper;

    @Autowired
    private OrgnizationRepository orgnizationRepo;
    @Override
    public List<OrgnizationModel> selectListIds(List<Long> ids) {
        List<Orgnization> list = orgnizationRepo.selectListIds(ids);
        return beanMapper.mapAsList(list, OrgnizationModel.class);
    }
}

public class OrgnizationServiceImplTest extends BaseTest {

    @Autowired
    OrgnizationService orgnizationService;

    @Test
    public void testSelectListIds() {
        List<Long> list = new ArrayList<>();
        list.add(Long.valueOf("633465951"));
        list.add(Long.valueOf("633465952"));
        List<OrgnizationModel> selectListIds =         orgnizationService.selectListIds(list)；
    }
}


 <select id="selectListIds" resultMap="BaseResultMap">
   select
     1. <include refid="Base_Column_List"/> 
      from orgnization where is_deleted = 0 and orgn_id in 
      <foreach item="item" index="index" collection="lists" open="(" separator="," close=")">  
          #{item}  
      </foreach>  
  </select>

   <select id="selectListIds" resultMap="BaseResultMap">2.必须list
   select
    2.  <include refid="Base_Column_List"/> 
      from orgnization where is_deleted = 0 and orgn_id in 
      <foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
          #{item}  
      </foreach>  
  </select>