06 mybatis映射文件(动态SQL及sql片段抽取)

动态 SQL 之<if>

• 编写映射文件UserMapper.xml

在 resource 文件下创建li.chen.com.mipper 目录，再创建普通文件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="li.chen.com.business.mapper.UserMapper">

    <!--查询操作 动态组合查询-->
    <select id="findByCondition" parameterType="user" resultType="user">
        select * from test_user
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="userName!=null">
                and userName=#{userName}
            </if>
            <if test="passWord!=null">
                and passWord=#{passWord}
            </if>
        </where>
    </select>

</mapper>

• 编写dao层

package li.chen.com.business.mapper;

import li.chen.com.business.entity.User;
import java.util.List;

public interface UserMapper {
    public List<User> findByCondition(User user);

}

• 测试文件

注意查看DEBUG 的sql语句

package li.chen.com.test;

import li.chen.com.business.entity.User;
import li.chen.com.business.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 javax.sound.midi.MidiSystem;
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
        User condition = new User();
        condition.setId(1);
//        condition.setUserName("zhansgan");
//        condition.setPassWord("1345545");

        List<User> userList = mapper.findByCondition(condition);
        System.out.println(userList);
    }
}

//-----------------------
14:33:03,725 DEBUG findByCondition:159 - ==>  Preparing: select * from test_user WHERE id=? 
14:34:10,833 DEBUG findByCondition:159 - ==> Parameters: 1(Integer)

package li.chen.com.test;

import li.chen.com.business.entity.User;
import li.chen.com.business.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 javax.sound.midi.MidiSystem;
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
        User condition = new User();
        condition.setId(1);
       condition.setUserName("zhansgan");
//        condition.setPassWord("1345545");

        List<User> userList = mapper.findByCondition(condition);
        System.out.println(userList);
    }
}

//-----------------------
14:34:10,795 DEBUG findByCondition:159 - ==>  Preparing: select * from test_user WHERE id=? and userName=? 
14:34:10,833 DEBUG findByCondition:159 - ==> Parameters: 1(Integer), zhansgan(String)

动态 SQL 之<foreach>

• 编写映射文件UserMapper.xml

在 resource 文件下创建li.chen.com.mipper 目录，再创建普通文件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="li.chen.com.business.mapper.UserMapper">

    <!--查询操作 查询集合-->
    <select id="findByIds" parameterType="list" resultType="user">
        select * from test_user
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>


</mapper>

• 编写dao层

删除接口的实现类

package li.chen.com.business.mapper;

import li.chen.com.business.entity.User;
import java.util.List;

public interface UserMapper {
    public List<User> findByIds(List<Integer> ids);
}

• 测试文件
  注意查看DEBUG 的sql语句

package li.chen.com.test;

import li.chen.com.business.entity.User;
import li.chen.com.business.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 javax.sound.midi.MidiSystem;
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
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(7);

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

//-----------------------
14:49:57,117 DEBUG findByIds:159 - ==>  Preparing: select * from test_user WHERE id in( ? , ? ) 
14:49:57,118 DEBUG findByIds:159 - ==> Parameters: 1(Integer), 7(Integer)

sql片段抽取

Sql 中可将重复的 sql 提取出来，使用时用 include 引用即可，最终达到 sql 重用的目的

UserMapper.xml

    <!--sql语句抽取-->
    <sql id="selectUser">select * from test_user</sql>
    
    <!--查询操作 组合查询-->
    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="userName!=null">
                and userName=#{userName}
            </if>
            <if test="passWord!=null">
                and passWord=#{passWord}
            </if>
        </where>
    </select>