Mybatis动态SQL

动态SQL

if标签

<?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.Person.PersonMapper">

  <!--if 标签 表达式成立 就会拼接sql-->
    <select id="SelectID" parameterType="com.Person.hero" resultType="com.Person.hero" >
        select * from person where 1 = 1
        <if test="id>=0">
            and id = #{id}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
    </select>
</mapper>

foreach标签
接口类

public interface PersonMapper {
    List<hero> Select(List<Integer> list);
}

映射文件

<?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.Person.PersonMapper">

<!-- collection 是 要遍历的集合 item 是 变量 separator 分隔符 -->
    <select id="Select" parameterType="list" resultType="com.Person.hero" >
        select * from person
          <where>
              <foreach collection="list" close=")" open="id in("  item="id" separator=",">
                  #{id}
              </foreach>
          </where>

    </select>
</mapper>

测试

public class test {
    public static void main(String[] args) throws IOException {
        //加载 Mybatis 核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("Mybatis-config.xml");
        //创建工厂
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //创建 sqlSession 会话
        SqlSession sqlSession = build.openSession();

        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);
        ArrayList<Integer> heroes1 = new ArrayList<Integer>();
        heroes1.add(666);
        heroes1.add(333);
        heroes1.add(1024);
      List<hero> heroes = mapper.Select(heroes1);
        for (hero hero : heroes) {
            System.out.println(hero);
        }
        sqlSession.close();
        resourceAsStream.close();
    }
}

程序结果

sql 片段抽取

<?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.Person.PersonMapper">
    <!--相同的sql 抽取出来-->
    <sql id="selectPerson">
         select * from person
    </sql>
    
    <select id="Select">
        <!--引入sql-->
        <include refid="selectPerson"></include>
    </select>
</mapper>