MyBatis映射文件深入

目录

1.动态sql语句

1.1动态sql语句概述

1.2动态SQL之

1.3动态SQL之

2.SQL片段抽取

3.小结

---

1.动态sql语句

1.1动态sql语句概述

Mybatis的映射文件中，有些sql语句是比较简单的，有些时候业务逻辑复杂时，SQL是动态变化的。

1.2动态SQL之<if>

根据实体类的不同取值，使用不同的SQL语句来进行查询，比如在id如果不为空时可以根据id查询，如果username不为空时还要加入用户名作为条件。。。这种情况在我们的多条件组合查询中经常会碰到。

<?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.caoyan.mapper.UserMapper">
    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>

1.3动态SQL之<foreach>

循环执行sql的拼接操作，例如：SELECT * FROM USER WHERE ID IN (1,2,5)

    <select id="findByIds" parameterType="list" resultType="user">
        select * from user
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

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

import com.caoyan.domain.User;
import com.caoyan.mapper.UserMapper;

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;

public class MapperTest {
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 模拟条件user
        User condition = new User();
        // condition.setId(1);
        // condition.setUsername("zhangsan");
        // condition.setPassword("123");
        // List<User> users = userMapper.findByCondition(condition);
        // System.out.println(users);
        // 模拟ids的数据
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(3);
        List<User> userList = userMapper.findByIds(ids);
        System.out.println(userList);
    }
}

2.SQL片段抽取

Sql中可将重复的SQL提取出来，使用时用include引用即可，最终达到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.caoyan.mapper.UserMapper">
    <!-- 抽取sql片段简化编写 -->
    <sql id="selectUser">select * from user</sql>
    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

3.小结

MyBatis映射文件配置：

• <select>：查询
• <insert>：插入
• <update>：修改
• <delete>：删除
• <where>：where条件
• <if>：if判断
• <foreach> :循环
• <sql>：sql片段抽取