SSM框架之MyBatis模糊查询动态SQL（详细使用案例）

目录

一、什么是动态SQL

 二、动态SQL用法

 三、模糊查询

---

一、什么是动态SQL

sql语句根据条件而发生改变。

 

 二、动态SQL用法

以题为例

CREATE TABLE `student` ( `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '', PRIMARY KEY (`s_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 测试类

import com.exy.dao.StudentDao;
import com.exy.entity.Student;
import com.github.pagehelper.PageHelper;
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.Before;
import org.junit.Test;

import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @program: mybatis07
 * @description:
 * @author: jdy
 * @create: 2021-12-02 22:09
 **/

public class STest {
    private SqlSession session;
    @Before
    public void before() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session=sessionFactory.openSession();
    }

    @Test
    public void test() throws Exception{
        StudentDao studentDao = session.getMapper(StudentDao.class);
        PageHelper.startPage(0,2);
        List<Student> list = studentDao.selectAll();
        System.out.println(list);
    }
    @Test
    public void test01() throws Exception{
        StudentDao studentDao = session.getMapper(StudentDao.class);
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("name","");
        map.put("sex","女");
        List<Student> list = studentDao.selectna(map);
        System.out.println(list);
    }
    @Test
    public void test02() throws Exception{
        StudentDao studentDao = session.getMapper(StudentDao.class);
        Student student = new Student(01,"赵雷","1999","女");
        int list = studentDao.update(student);
        session.commit();
        System.out.println(list);
    }
    @Test
    public void test03() throws Exception{
        StudentDao studentDao = session.getMapper(StudentDao.class);
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("name","赵雷");
        map.put("sex","女");
        List<Student> list = studentDao.selectns(map);
        System.out.println(list);
    }
    @Test
    public void test04(){
        StudentDao studentDao = session.getMapper(StudentDao.class);
        int [] ids ={01,02,03};
        List<Student> list = studentDao.selectid(ids);
        System.out.println(list);
    }
    @Test
    public void test05() throws Exception{
        StudentDao studentDao = session.getMapper(StudentDao.class);
        List<Student> list = studentDao.selectlike("雷");
        System.out.println(list);
    }
    
}

 

 第一题

 

 

 第二题

 第三题

第四题

 代码块

<?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.exy.dao.StudentDao">
    <select id="selectAll" resultType="com.exy.entity.Student">
        select * from student
    </select>


    <select id="selectlike" resultType="com.exy.entity.Student">
        select * from student where s_name like concat('%',#{s_name},'%')
    </select>

    <select id="selectna" resultType="com.exy.entity.Student">
        select * from student
        <where>
            <choose>
                <when test="name!=null and name!=''">
                    and s_name=#{name}
                </when>
                <when test="sex!=null and sex!=''">
                    and s_sex=#{sex}
                </when>
                <otherwise>

                </otherwise>
            </choose>
        </where>

    </select>

    <update id="update">
        update student
        <set>
            <if test="s_name!=null and s_name!=''">
                s_name=#{s_name},
            </if>
            <if test="s_birth!=null and s_birth!=''">
                s_birth=#{s_birth},
            </if>
            <if test="s_sex!=null and s_sex!=''">
                s_sex=#{s_sex},
            </if>

        </set>
        where s_id=#{s_id}
    </update>


    <select id="selectns" resultType="com.exy.entity.Student">
        select * from student
        <where>
            <choose>
                <when test="name!=null and name!=''">
                    and s_name=#{name}
                </when>
                <when test="sex!=null and sex!=''">
                    and s_sex=#{sex}
                </when>
            </choose>
        </where>

    </select>

    <select id="selectid"  resultType="com.exy.entity.Student">
        select * from student where s_id in
        <foreach collection="ids" item="s_id" open="(" close=")" separator=",">
            #{s_id}
        </foreach>
    </select>



</mapper>

 三、模糊查询

根据部分值得出全部like和%配合