mybatis动态sql之遍历集合（学习foreach标签（初探））

EmployeeMapperDynamicsql.java

package com.gong.mybatis.mapper;

import java.util.List;
 java.util.Map;

 org.apache.ibatis.annotations.MapKey;
 org.apache.ibatis.annotations.Param;

 com.gong.mybatis.bean.Employee;

public interface EmployeeMapperDynamicsql {
    public List<Employee> getEmpByConditionIf(Employee employee);
    
    public List<Employee> getEmpByForeach(@Param("ids") List<Integer> ids);
}

EmployeeMapperDynamicsql.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
<mapper namespace="com.gong.mybatis.mapper.EmployeeMapperDynamicsql">
    <!-- 查询，要查那个就带上那个条件 -->
    select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee">
        select * from tbl_employee 
        where>
            choose>
                when test="id!=null"
                    id=#{id}
                </when="lastName!=null"
                    last_name like #{lastName}
                ="email!=null"
                    email=#{email}
                otherwise>
        select>
    
    ="getEmpByForeach"
        select * from tbl_employee where id in
        foreach collection="ids" item="item_id" separator="," open="(" close=")"
            #{item_id}
        foreach>
mapper>

说明：

item：集合中元素迭代时的别名，该参数为必选。
index：在list和数组中,index是元素的序号，在map中，index是元素的key，item是元素的value，该参数可选
open：foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选
separator：元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql 错误，如in(1,2,)这样。该参数可选。
close: foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。
collection: 要做foreach的对象，作为入参时，List对象默认用"list"代替作为键，数组对象有"array"代替作为键，Map对象没有默认的键。当然在作为入参时可以使用@Param("keyName")来设置键，设置keyName后，list,array将会失效。除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：如果User有属性List ids。入参是User对象，那么这个collection = "ids".如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"

也就是说整个sql语句是这样的：select * from tbl_employee where id in (1,3)

注意的是我们在接口中定义了传入参数的名称，因此在collection中可以传入该名称。

进行测试：

 com.gong.mybatis.test;

 java.io.IOException;
 java.io.InputStream;
 java.util.ArrayList;
 java.util.Arrays;
 org.apache.ibatis.io.Resources;
 org.apache.ibatis.session.sqlSession;
 org.apache.ibatis.session.sqlSessionFactory;
 org.apache.ibatis.session.sqlSessionFactoryBuilder;
 org.junit.Test;

 com.gong.mybatis.bean.Employee;
 com.gong.mybatis.mapper.EmployeeMapperDynamicsql;

class TestMybatis3 {
    
    public sqlSessionFactory getsqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream is = Resources.getResourceAsStream(resource);
        return new sqlSessionFactoryBuilder().build(is);
    }

    @Test
    void test()  IOException {
        sqlSessionFactory sqlSessionFactory = getsqlSessionFactory();
        sqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicsql mapper = openSession.getMapper(EmployeeMapperDynamicsql.);
            List<Employee> es = mapper.getEmpByForeach(Arrays.asList(1,3));
            for(Employee e:es) {
                System.out.println(e);
            }
            openSession.commit();
        } finally {
            openSession.close();
        }
        
    }
    
}

最终结果：

DEBUG 01-21 15:31:16,176 ==>  Preparing: select * from tbl_employee where id in ( ?,?,? )   (BaseJdbcLogger.java:145) 
DEBUG 01-21 15:31:16,241 ==> Parameters: 1(Integer),2(Integer),3(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 01-21 15:31:16,335 ==      Total: 3  (BaseJdbcLogger.java:145) 
Employee [id=1,lastName=dema,1)">genderemail=dema@qq.com,1)">dept=null]
Employee [id=2,1)">=jack,1)">=675544321@qq.com,1)">=3,1)">=小红,1)">=0,1)">=xiaohong@qq.com,1)">=null]

mybatis动态sql之遍历集合（学习foreach标签（初探））

猜你在找的Mybatis相关文章