SSM系列——Mybatis详细映射文件，sql抽取day6-1

映射文件深入

动态sql语句

有些时候业务逻辑复杂时，我们的SQL是动态变化的,此时在前面的学习中我们的SQL就不能满足要求了

常见有：

1. if
2. foreach
3. choose(when,otherwise)
4. trim(where,set)
5. 等

这里我写最常用的if和foreach
我们可以在Mybatis的官网上找到其他的动态sql

动态sql< if >标签

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.example.mybatis.mapper.UserMapper">
    <select id="findById" resultType="user" parameterType="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>

UserMapper接口

package com.example.mybatis.mapper;

import com.example.mybatis.entity.User;

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

public interface UserMapper {

    public List<User> findAll() throws IOException;
    public List<User> findById(User user);
}

测试

package com.example.mybatis;

import com.example.mybatis.entity.User;

import com.example.mybatis.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Application {
    public static void main(String[] args) throws IOException {
        InputStream resource = Resources.getResourceAsStream("config/sqlMapperConfi.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resource);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = mapper.findAll();
        User user1 = new User();
        user1.setId(2L);
        user1.setUsername("wan3gwu");
        user1.setPassword("l345sa");
        List<User> user = mapper.findById(user1);
        user.forEach(System.out::println);
        System.out.println("-----------------");
        list.forEach(System.out::println);
    }
}

动态sql< foreach >

原始sql例子

select * from user id in(1,3,4)

UserMapper.xml

解释：其中collection表示用什么去接收，open为以什么开始，close为以什么结束，item为collection中的变量接收名如item为id，那么代表 id = Arraylist[0] = xxx,separator表示以什么去分割open和close中间的量

<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>

UserMapper接口

 public List<User> findByIds(List<Long> ids);

测试

List<Long> list1 = new ArrayList<>();
list1.add(5L);
list1.add(7L);
List<User> byIds = mapper.findByIds(list1);
byIds.forEach(System.out::println);

sql抽取

我们可以将一些重复性很高的sql进行抽取，然后以配置的方式使用

< sql >标签

用于进行sql语句的抽取，使用id指定名称

    <sql id="selectUser">
        select *
        from user
    </sql>

< include >标签

用于进行抽取的sql的引入，使用refid获取sql标签的id名来引入

 <select id="findAll" resultType="user">
        <include refid="selectUser"></include>
    </select>

修改后的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.example.mybatis.mapper.UserMapper">
    <sql id="selectUser">
        select *
        from user
    </sql>

    <select id="findAll" resultType="user">
        <include refid="selectUser"></include>
    </select>
    <select id="findById" resultType="user" parameterType="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>
    <insert id="insertOne" parameterType="user">
        insert into user value (#{id},#{username},#{password})
    </insert>
    <update id="upOne" parameterType="user">
        update user
        set username=#{username},
            password=#{password}
        where id = #{id}
    </update>

    <delete id="deleteOne" parameterType="long">
        delete
        from user
        where id = #{id};
    </delete>
</mapper>