Mybatis动态sql语句

要成为大神的小菜鸟Simon

已于 2022-06-20 20:35:20 修改

阅读量101

点赞数 3

文章标签： mybatis sql java

于 2022-06-20 20:28:16 首次发布

本文链接：https://blog.csdn.net/Smion778/article/details/125379612

版权

老样子配置好mybatis环境过后，将接口类、mapper.xml文件对应好，在测试类中写好相应的@Test方法测试，对数据库中的数据执行crud操作。

1、接口文件中写好方法

package wg.study.mybatis.mapper;

import org.apache.ibatis.annotations.Param;
import wg.study.mybatis.entity.Student;

import java.util.List;

public interface StudentMapper {
//查询所有
    List<Student> selectAll();
//根据学号更新信息
    Integer updateByStuNo(Student student);
//搜索
    List<Student> search(Student student);
//批量删除
    Integer deleteMore(@Param("ids") List<String> ids);
//搜索
    List<Student> search2(Student student);

}

2、写好相应sql语句

查询所有 selectAll

 <select id="selectAll" resultType="student">
        select * from stu1
    </select>

搜索 search

代码一：使用where标签

<!--    where 标签
    1、 当查询条件全部为null 则自动去除where关键词
    2、如果有查询条件 但是第一个查询条件有and或者or 则自动去除这些关键词
-->

<select id="search" resultType="wg.study.mybatis.entity.Student">
        select * from stu1
        <where>
        <if test="stuNo!=null">stu_no= #{stuNo}</if>

        <if test="stuName!=null"> and stu_name=#{stuName}</if>

        <if test="stuSex!=null">and stu_sex=#{stuSex}</if>
        </where>

    </select>

代码二：trim标签加上前缀where 去掉后缀and

<select id="search" resultType="wg.study.mybatis.entity.Student">
        select * from stu1

        <trim prefix="where" prefixOverrides="and">

            <if test="stuNo!=null">stu_no= #{stuNo}</if>

            <if test="stuName!=null"> and stu_name=#{stuName}</if>

            <if test="stuSex!=null">and stu_sex=#{stuSex}</if>

        </trim>

    </select>

代码三：使用sql片段

 <select id="search" resultType="wg.study.mybatis.entity.Student">
        select * from stu1

      <where>
          <include refid="selectStudents"></include>
      </where>


    </select>
<!--sql片段 提高sql语句使用 减少冗余代码量-->
    <sql id="selectStudents">
        <if test="stuNo!=null">
            stu_no= #{stuNo}
        </if>

        <if test="stuName!=null">
            and stu_name=#{stuName}
         </if>

        <if test="stuSex!=null">
         and stu_sex=#{stuSex}
        </if>
    </sql>

搜索 search2

当搜索的条件出现优先级时

<!--    查询条件有优先级别时 只会使用一个条件进行查询操作-->
    <select id="search2" resultType="wg.study.mybatis.entity.Student">
        select * from stu1
        <where>
            <choose>
                <when test="stuNo!=null">stu_no= #{stuNo}</when>
                <when test="stuName!=null">and stu_name=#{stuName}</when>
                <otherwise>and stu_sex=#{stuSex}</otherwise>
            </choose>
        </where>
    </select>

更新数据 updateByStuNo

代码一：使用set标签，作用原理与where标签相似

 <update id="updateByStuNo" parameterType="student">
        update stu1
        <set>
        <if test="stuAge!=null">stu_age = #{stuAge},</if>

        <if test="stuSex!=null">stu_sex = #{stuSex},</if>

        <if test="stuName!=null"> stu_name=#{stuName} </if>
        </set>
         where
         stu_no =#{stuNo}
    </update>

代码二：使用trim标签

<update id="updateByStuNo" parameterType="student">
        update stu1
        <trim prefix="set" suffixOverrides=",">
            <if test="stuAge!=null">stu_age = #{stuAge},</if>

            <if test="stuSex!=null">stu_sex = #{stuSex},</if>

            <if test="stuName!=null"> stu_name=#{stuName} </if>
        </trim>
        where
        stu_no =#{stuNo}
    </update>

批量删除 deleteMore

代码一：使用collection标签对传递的id集合进行遍历后再拼接sql语句

<delete id="deleteMore" parameterType="list">

        delete from stu1 where stu_no in (
        <foreach collection="ids"  item="id" separator=",">
            #{id}
        </foreach>
        )
    </delete>

代码二：与代码一类似使用了open和close

<delete id="deleteMore" parameterType="list">

        delete from stu1 where stu_no in
        <foreach collection="ids" open="(" item="id" separator="," close=")">
            #{id}
        </foreach>

    </delete>

代码三：改掉sql语句语法，使用 =...or 语法

<delete id="deleteMore" parameterType="list">

        delete from stu1 where
        <foreach collection="ids"  item="id" separator="or" >
            stu_no = #{id}
        </foreach>

    </delete>

总结一下：

1、接口中每一种方法在.xml中有且仅有一个id与之方法名相对应的sql语句，上面我列举的有些方法包含了代码一、代码二的，他们在运行时肯定是只能存在一个的

2、where 与set标签类似，根据Test文件中传进的参数来判断if标签中test是否为true，为true时if标签中的参数才会被拼接进sql语句中，为false则不会

3、where和set都可以被trim标签代替，具体用法在上面代码中

4、sql语句片段可以大大减少冗余的代码段，提高sql语句的利用率

最后附上我的Test类文件

public class TestStudentMapper {
    SqlSessionFactory factory;


    @Before
    public void init() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("config/mybatis-config.xml");
        factory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    }

//查询所有
    
    @Test
    public void test01(){
        //开启事务自动提交
        SqlSession sqlSession = factory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.selectAll();
        students.forEach(student -> System.out.println("student = " + student));


    }

//更新
    @Test
    public void test02() {
        //开启事务自动提交
        SqlSession sqlSession = factory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student();
//        student.setStuName("刘士杰");
        student.setStuNo("HFYY1831203");
        student.setStuAge(17);
        Integer integer = mapper.updateByStuNo(student);
        System.out.println(integer>0?"true":"false");

    }


    //动态sql查询 搜索
    @Test
    public void test03(){
        //开启事务自动提交
        SqlSession sqlSession = factory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student();
        student.setStuSex(1);
//        student.setStuNo("HFYY1831203");
//        student.setStuName("刘士杰");
        List<Student> search = mapper.search(student);
        search.forEach(student1 -> System.out.println("student = " + student1));
    }

//批量删除
    @Test
    public void test04() {
        //开启事务自动提交
        SqlSession sqlSession = factory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<String> ids = Arrays.asList("HFYY1831203", "HFYY1831205", "HFYY1831206");
        Integer integer = mapper.deleteMore(ids);
    }

//搜索
    @Test
    public void test05(){
        //开启事务自动提交
        SqlSession sqlSession = factory.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student();
        student.setStuSex(1);
//        student.setStuNo("HFYY1831203");
//        student.setStuName("刘士杰");
        List<Student> search = mapper.search2(student);
        search.forEach(student1 -> System.out.println("student = " + student1));
    }








}