Mybatis动态SQL语句if、where、foreach用法

Java 1369℃

1.准备工作

创建maven、添加jar包坐标、编写实体类、MyBatis主配置文件、log4j的配置文件等准备工作可以参考：IDEA中Maven工程的MyBatis快速入门

2.编写实体类的持久层映射接口

这里对应的User类持久层映射接口取名UserMapper，放在src/main/java下，代码如下：

package com.gqzzw.mapper;

import com.gqzzw.domain.QueryVo;
import com.gqzzw.domain.User;

import java.util.List;

public interface UserMapper {
    //动态sql语句，if标签的使用
    List<User> findByCondition(User user);
    //动态sql语句，where标签的使用
    List<User> findByCondition2(User user);
    //动态sql语句，foreach标签的使用
    List<User> findByCondition3(QueryVo queryVo);
}

3.编写包装对象

在src/main/java下，编写包装类QueryVo.class，内容如下：

package com.gqzzw.domain;

public class QueryVo {
    private int[] ids;

    public int[] getIds() {
        return ids;
    }

    public void setIds(int[] ids) {
        this.ids = ids;
    }
}

4.编写映射接口的配置文件

放在src/main/resources下，必须与久层接口包路径和文件名相同，即：com.gqzzw.mapper.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.gqzzw.mapper.UserMapper">
    <!-- 动态sql语句，if标签的使用 -->
    <select id="findByCondition" parameterType="com.gqzzw.domain.User" resultType="com.gqzzw.domain.User">
        select * from user where 1=1
        <if test="username != null">
           and username=#{username}
        </if>
    </select>
    <!-- 动态sql语句，where标签的使用 -->
    <select id="findByCondition2" parameterType="com.gqzzw.domain.User" resultType="com.gqzzw.domain.User">
        select * from user
        <where>
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="sex != null">
                and sex=#{sex}
            </if>
        </where>
    </select>
    <!-- 动态sql语句，foreach标签的使用 -->
    <select id="findByCondition3" parameterType="com.gqzzw.domain.QueryVo" resultType="com.gqzzw.domain.User">
        select * from user
        <where>
            <foreach collection="ids" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

5.编写测试类

放在src/test/java下，编写测试类，内容如下：

package com.gqzzw.mapper;

import com.gqzzw.domain.QueryVo;
import com.gqzzw.domain.User;
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.After;
import org.junit.Before;
import org.junit.Test;

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

public class UserMapperTset {
    private InputStream in;
    private SqlSession sqlSession;
    private UserMapper userMapper;

    @Before
    public void init() throws IOException {
        //读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory的构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //使用构建者创建工厂对象SqlSessionFactory
        SqlSessionFactory factory = builder.build(in);
        //使用SqlSessionFactory产SqlSession对象生
        sqlSession = factory.openSession();
        //产SqlSession对象时可以传true代表自动提交事务
        //sqlSession = factory.openSession(true);
        //使用SqlSession创建映射接口的代理对象
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void destroy() throws IOException {
        //提交事务，释放资源
        sqlSession.commit();
        sqlSession.close();
        in.close();
    }
    @Test
    //动态sql语句，if标签的使用
    public void findByCondition(){
        User user = new User();
        user.setUsername("老王");
        List<User> users = userMapper.findByCondition(user);
        System.out.println(users);
    }
    @Test
    //动态sql语句，if标签的使用
    public void findByCondition2(){
        User user = new User();
        user.setSex('女');
        List<User> users = userMapper.findByCondition2(user);
        System.out.println(users);
    }
    @Test
    //动态sql语句，foreach标签的使用
    public void finByCondition3(){
        QueryVo queryVo = new QueryVo();
        int[] array = {41,42};
        queryVo.setIds(array);
        List<User> users = userMapper.findByCondition3(queryVo);
        System.out.println(users);
    }
}

转载请注明：运维博客 » Mybatis动态SQL语句if、where、foreach用法

相关文章