Mybatis动态sql语句

实现的功能

1.动态sql语句
2.SQL循环语句

一、UserMapper.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.william.dao.UserMapper">
 <!--
    foreach：循环
    属性：
    collection：array（数组类型），list(集合类型)
    index:索引
    open :前缀
    close:后缀
    item:子项目（数组或者集合中的每一个元素）
    separator:分隔符
    -->
    <!--delete from user id in (1，2，3)-->
    <delete id="deleteByArray" parameterType="int[]">
        delete from user where id
        <foreach collection="array" open="in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>
    <delete id="deleteByList" parameterType="list">
        delete from user where id
        <foreach collection="list" open="in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>
    <!--动态SQL语句查询-->
    <!--多条件直接查询Param注解版-->
    <select id="findByManyConditionByParam"  resultType="user">
        select * from user where 1=1
        <if test="username!=null">
           and username like "%"#{username}"%"
        </if>
        <if test="sex!=null">
            and sex=#{sex}
        </if>
    </select>
    <!--更新用户数据-->
    <update id="update" parameterType="user" >
        update  user
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="sex != null">
                sex = #{sex},
            </if>
            <if test="birthday != null">
                birthday = #{birthday}
            </if>
        </set>
        where id  = #{id}
    </update>
</mapper>

二、测试类

package com.william;

import com.william.dao.UserMapper;
import com.william.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

/**
 * @author ：lijunxuan
 * @date ：Created in 2019/7/12  15:41
 * @description ：
 * @version: 1.0
 */
public class TestCRUD {
 /**
     * 通过id删除多个用户信息（参数为list集合类型）
     * @throws IOException
     */
    @Test
    public void deleteList() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis-configuration.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<Integer> ids = new ArrayList<>();
        ids.add(47);
        ids.add(49);
        mapper.deleteByList(ids);
        sqlSession.close();
    }
    /**
     * 通过id删除多个用户信息（参数为数组类型）
     * @throws IOException
     */
    @Test
    public void deleteArray() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis-configuration.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Integer[] ids={43,41};
        mapper.deleteByArray(ids);
        sqlSession.close();
    }
    /**
     * 更新用户数据
     * @throws IOException
     */
    @Test
    public void update() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("Mybatis-configuration.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(47);
        user.setUsername("jiajia");
        user.setPassword("1111");
        user.setBirthday(new Date());
        user.setSex("女");
        mapper.update(user);
        sqlSession.close();
    }

    /**
     * 根据条件模糊查询用户信息
     * @throws IOException
     */
    @Test
    public void findByManyConditionByParam() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("Mybatis-configuration.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findByManyConditionByParam("a", null, 0, 10);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

三、UserMapper接口

package com.william.dao;

import com.william.domain.User;
import org.apache.ibatis.annotations.Param;

import java.util.HashMap;
import java.util.List;

public interface UserMapper {
/**
     * 通过id删除多个用户信息（参数为数组类型）
     * @param ids
     */
    public void deleteByArray(Integer[] ids);
    /**
     * 通过id删除多个用户信息（参数为list集合类型）
     * @param ids
     */
    public void deleteByList(List<Integer> ids);
    /**
     * 多条件直接查询Param注解版
     * @param username
     * @param sex
     * @param startIndex
     * @param pageSize
     * @return
     */
    public List<User> findByManyConditionByParam(@Param("username") String username, @Param("sex") String sex, @Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);


    /**
     * 更新用户信息
     * @param user
     */
    public void update(User user);

    /**
     * 多参数封装到map中，多条件查询
     * @param map
     * @return
     */
    List<User> findByConditionByMap(HashMap<String, Object> map);
}