springboot结合MyBatis中使用foreach

原创

道可道非常道

修改于 2019-05-22 18:06:39

1.5K0

修改于 2019-05-22 18:06:39

文章被收录于专栏：Java Web学习

废话少说，直接建项目上代码

先建一个springboot web项目或者模块，目录结构如下

在pom.xml中自行加入需要的依赖，application.properties的配置如下

server.port=8888
mybatis.mapper-locations=classpath*:mapper/*Mapper.xml
spring.datasource.url=jdbc:mysql://localhost:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=Panbing936@
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

实体类User.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private int id;
    private String name;
}

UserMapper.xml中

使用foreach接收list参数

 <!-- 使用foreach接收list参数 -->
    <select id="getUsersByListParam" resultType="cn.niit.mybatisforeach.pojo.User">
        select * from t_user
        <foreach collection="list" item="item" open="where id in(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

使用foreach批量插入用户

<!-- 使用foreach批量插入用户-->
    <insert id="insertMultiUsers">
        insert into t_user(id,name)
        values
        <foreach collection="users" item="user" separator=",">
            (#{user.id},#{user.name})
        </foreach>
    </insert>

使用foreach接收array数组

<!--使用foreach接收array数组-->
    <select id="getUserByArrayList" parameterType="java.util.ArrayList" resultType="cn.niit.mybatisforeach.pojo.User">
        select * from t_user where id in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

使用foreach接收Map

<!--使用foreach接收Map-->
    <select id="getUserByMap" parameterType="java.util.HashMap"
            resultType="cn.niit.mybatisforeach.pojo.User">
        select * from t_user where  id in
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

UserMapper.java

@Mapper
@Component
public interface UserMapper {
    /**
     * 多个参数查询user集合
     */
    List<User> getUsersByListParam(@Param("list") List list);
    /**
     * 使用foreach批量插入
     */
    int insertMultiUsers(@Param("users") List<User> users);

    /**
     * 使用foreach接收数组参数
     */
    List getUserByArrayList(int[] ids);

    /**
     * 使用foreach接收map
     */
    List getUserByMap(Map params);
}

测试代码

MybatisForeachApplicationTests.java 中的代码如下

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

    @Test
    public void contextLoads() {
    }
    /**
     * 动态sql学习---foreach标签使用（查询）
     */
    @Autowired
    private UserMapper userMapper;
    @Test
    public void dynamicSQLToForeach() throws IOException {
            List<User> users = userMapper.getUsersByListParam(Arrays.asList(1,2,3,4));
            for(User user:users) {
                System.out.println(user);
            }
    }

    /**
     * 动态sql学习---foreach标签使用（新增）
     */
    @Test
    public void insertMultiUsers() throws IOException {
            List<User> users = new ArrayList<User>();
            users.add(new User(6,"陈大小姐"));
            users.add(new User(7,"少主"));
            users.add(new User(8,"天龙人"));
            int count = userMapper.insertMultiUsers(users);
            System.out.println("成功插入"+count+"条记录");
    }

    @Test
    public void getUserByArrayList() {
       int[] ids = new int[] {1,3,6,9};
        List users = userMapper.getUserByArrayList(ids);
        for (Object user:users)
        {
            System.out.println(user);
        }
    }

    @Test
    public void getUserByMapTest() {
        final List ids = new ArrayList();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(6);
        ids.add(7);
        ids.add(9);
        Map params = new HashMap();
        params.put("ids", ids);
        List users = userMapper.getUserByMap(params);
        for (Object user : users)
            System.out.println(user);
    }
}