Mybatis-动态SQL

目录

需求：

if标签的使用

实例操作

:循环遍历标签

实例操作

sql片段的抽取

需求：

if标签的使用

实例操作

//多条件查询
    public abstract List selectCondition(Student stu);

<select id="selectCondition" resultType="student" parameterType="student">
    SELECT * FROM student
    <where>
        <if test="sid != null">
            sid = #{sid}
        if>
        <if test="name != null">
            AND name = #{name}
        if>
        <if test="age != null">
            AND age = #{age}
        if>
    where>
select>

@Test
    public void selectConditon() throws Exception{
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
 
        Student stu = new Student();
        stu.setSid(2);
        stu.setName("李四");
        //stu.setAge(24);
        List list = mapper.selectCondition(stu);
        for(Student student : list){
            System.out.println(student);
        }
        sqlSession.close();
        is.close();
    }

:循环遍历标签

实例操作

//根据多个id查询
    public abstract List selectByIds(List sids);

<select id="selectByIds" resultType="student" parameterType="list">
    SELECT * FROM student
    <where>
        <foreach collection="list" open="sid IN (" close=")" item="sid" separator=",">
            #{sid}
        foreach>
    where>
select>

@Test
    public void selectByIds() throws Exception{
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
 
        List sids = new ArrayList<>();
        sids.add(1);
        sids.add(2);
        List list = mapper.selectByIds(sids);
        for(Student student : list){
            System.out.println(student);
        }
        sqlSession.close();
        is.close();
    }

sql片段的抽取

<sql id="select">SELECT * FROM studentsql>

<select id="selectAll" resultType="student">
    <include refid="select"/>
select>
<select id="selectByIds" resultType="student" parameterType="list">
    <include refid="select"/>
    <where>
        <foreach collection="list" open="sid IN (" close=")" item="sid" separator=",">
            #{sid}
        foreach>
    where>
select>