Mybatis动态SQL的使用

创建实体类

package com.zzx.model;
/**
 * @auther ZhengZiXuan
 * @date 2021/3/8 11:29
 * @desc
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private String username;
    private String password;

    public User(Integer id, String name, Integer age, String sex, String username, String password) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.username = username;
        this.password = password;
    }

    public User() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

配置db.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/dog
jdbc.username=root
jdbc.password=123456

配置sqlconfigtion.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--加载配置文件-->
    <properties resource="db.properties"></properties>
    <!--添加缓存-->
    <settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>

    <!--类型别名-->
    <typeAliases>
        <!--每个单独的类取别名-->
        <!--<typeAlias type=""alias=""/>-->

        <!--给包下的所有类起别名-->
        <package name="com.zzx"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--第一种写法-加载包下指定是映射文件 -->
        <!--<mapper resource="com/zzx/mapper/UserMapper.xml"/>-->

        <!--第二种写法-加载包下的所有的映射文件 -->
        <package name="com.zzx.mapper"/>
    </mappers>
</configuration>

创建UserMapper接口

import com.zzx.model.User;
import org.apache.ibatis.annotations.Param;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @auther ZhengZiXuan
 * @date 2021/3/8 11:41
 * @desc
 */
public interface UserMapper {
	
	 //模糊查询(动态)
    List<User> findKeyWord1(@Param("keyword") String keyword);

    //通过多个参数进行模糊查询
    List<User> findKeyWord2(HashMap<String, Object> map);

    List<User> findUserByID(ArrayList<Integer> list);

    List<User> findKeyWords(@Param("keyword") String keyword);

}

创建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.zzx.mapper.UserMapper">
    <!--id:映射接口的方法名
        resultType"" 返回值类型,如果是自定义类,需要写全路径
        parameterType:入参类型,与映射接口方法内的参数类型一致
        SQL语句:
            需要传入参数的位置,使用#{}站稳-->

<!--使用if判断写的动态SQL-->
<select id="findKeyWord1" parameterType="String" resultType="User">
    select * from user
    <if test ="keyword != null">
        where username like concat('%',#{keyword},'%')
    </if>
</select>

    <!--使用if判断写的动态SQL-->
<select id="findKeyWord2" parameterType="Map" resultType="User">
    select * from user where 1=1
           <choose>
                <when test="username != null">
                    and username like concat('%',#{username},'%')
                </when>
                <when test="name != null">
                    and `name` like concat('%',#{name},'%')
                </when>
                <otherwise>
                    and sex like concat('%',#{sex},'%')
                </otherwise>
           </choose>
</select>

<!--使用where标签-->
<select id="findKeyWords" parameterType="String" resultType="User">
    select * from user
    <where>
        <if test="keyword !=null">
            username like concat('%',#{keyword},'%')
        </if>
    </where>
</select>


<select id="findUserByID" parameterType="list" resultType="User">
    select * from user where id in
    <!--
        collection="list" 中的list是固定写法,
        item="is" 的是需要遍历的值
        open="("  给SQL语句自动拼接上(
        separator=","  给多个数据间添加分割的,
        close=")" 给SQL语句自动拼接上)
    -->
    <foreach collection="list" item="is" open="(" separator="," close=")">
        #{is}
    </foreach>
</select>

</mapper>

创建测试类

package com.zzx.test;

import com.zzx.mapper.UserMapper;
import com.zzx.model.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.ArrayList;
import java.util.HashMap;
import java.util.List;


/**
 * @auther ZhengZiXuan
 * @date 2021/3/8 11:55
 * @desc
 */
public class MainClass {
    private SqlSession sqlSession = null;
    @Before
    public void beforTest() throws IOException {
        String path = "sqlconfigtion.xml";
        // 1 加载配置文件
        InputStream stream = Resources.getResourceAsStream(path);
        // 2 通过配置文件,创建sqlsession工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder( ).build(stream);
        // 3 创建出sqlsession
        sqlSession = sqlSessionFactory.openSession( );
    }
     @Test
    public void KeyWord1(){
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> keyword = mapper.findKeyWord1("");
        System.out.println(keyword);
    }

    @Test
    public void KeyWord2(){
        //获得代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //执行
        HashMap<String, Object> map = new HashMap<>();
        map.put("username",null);
        map.put("name","苏曼");
        map.put("sex",null);
        List<User> list=mapper.findKeyWord2(map);
        System.out.println(list);
        sqlSession.commit();
    }

    @Test
    public void findUserByID(){
        //获得代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //执行
        ArrayList<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        list.add(3);
        List<User> all = mapper.findUserByID(list);
        System.out.println(all);
        sqlSession.commit();
    }

    @Test
    public void KeyWords(){
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> keyword = mapper.findKeyWords("");
        System.out.println(keyword);
    }

    @After
    public void afterTest(){
        //关闭会话
        sqlSession.close();
    }
}

动态SQL使用介绍

if(需求:单个字段的模糊查询):

choose when otherwise(需求:多字段模糊查询):

where

错误用法:


那么我们就用到了< where > 标签:

条件成立时,会自动拼接where;若条件不成立时,where标签内的SQL语句不会执行;

foreach标签(需求:根据id查id,是页面通过复选框传递到后天的,所以不确定有多少id,所以将id存入List,即参数是List):