SSM13【Mybatis：Mybatis映射文件深入--动态sql语句&sql片段抽取】

动态sql语句

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

package com.haifei.mapper;

import com.haifei.domain.User;

import java.util.List;

public interface UserMapper {

    public List<User> findByCondition(User user);
    public List<User> findByIds(List<Integer> ids);

}

<?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.haifei.mapper.UserMapper">

    <!--<select id="findByCondition" parameterType="user" resultType="user">
        select * from user where id=#{id} and username=#{username} and password=#{password}
    </select>-->

    <!--<select id="findByCondition" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
    </select>-->
    <!--
        <where>标签可以动态添加查询条件
        当没有条件时sql语句中就没有where，有哪个条件就where哪个条件
        可以代替 "where 1=1"
    -->
    <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>

    <!--select * from user where id in(1,2,3)-->
    <!--<foreach>标签中，当传入的参数是列表时collection值为list，当传入的是数组时collection值为array-->
    <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>

</mapper>

 

 

package com.haifei.test;

import com.haifei.domain.User;
import com.haifei.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;

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

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 mapper = sqlSession.getMapper(UserMapper.class);

        //模拟查询条件
        User condition = new User();
        condition.setId(1);
        condition.setUsername("zhangsan");
        condition.setPassword("123");

        List<User> userList = mapper.findByCondition(condition);
        System.out.println(userList); //[User{id=1, username='zhangsan', password='123'}]
    }

    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //模拟查询条件
        User condition = new User();
        condition.setId(1);
//        condition.setUsername("zhangsan");
//        condition.setPassword("123");

        List<User> userList = mapper.findByCondition(condition);
        System.out.println(userList); //[User{id=1, username='zhangsan', password='123'}]
    }

    @Test
    public void test3() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //模拟ids数据
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);

        List<User> userList = mapper.findByIds(ids);
        System.out.println(userList);
    }

}

 

 

 

 

 

 

 

 

 

 

 

 

 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.haifei.mapper.UserMapper">

    <!--sql语句抽取-->
    <sql id="selectUser">select * from user</sql>

    <!--<select id="findByCondition" parameterType="user" resultType="user">
        select * from user where id=#{id} and username=#{username} and password=#{password}
    </select>-->

    <!--<select id="findByCondition" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
    </select>-->
    <!--
        <where>标签可以动态添加查询条件
        当没有条件时sql语句中就没有where，有哪个条件就where哪个条件
        可以代替 "where 1=1"
    -->
    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"/>
        <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 * from user where id in(1,2,3)-->
    <!--<foreach>标签中，当传入的参数是列表时collection值为list，当传入的是数组时collection值为array-->
    <select id="findByIds" parameterType="list" resultType="user" >
        <include refid="selectUser"/>
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>