mybati动态sql之trim、where、foreach、set-CSDN博客

本文链接：https://blog.csdn.net/m0_55955242/article/details/118708559

本文介绍了MyBatis中Mapper接口的使用，包括查询所有学生、按条件查询、更新学生信息等操作。展示了如何在XML映射文件中使用<select>, <update>等标签，以及如何处理条件判断和集合遍历。同时，通过测试类展示了这些方法的实际调用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

基于上一篇文章，这里就不重新配置

StudentMapper.xml

package com.xiao.mapper;

import com.xiao.entity.StudentInfo;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface StudentMapper {

//    查询所有的学生
    public List<StudentInfo> findAll();

//    查询某个学生信息
    public StudentInfo findOneStuById(String sid);

//    根据学生姓名模糊查询 (如果这个不加注解，报异常会找不到字段)
    public List<StudentInfo> findStuByName(@Param("sname")String name);

//    根据学生年龄和姓名查询学生信息
    public List<StudentInfo> findStuByAgeName(@Param("age") String age, @Param("name") String name);

    public List<StudentInfo> findStuByAgeNameSex(@Param("age") String age, @Param("name") String name,@Param("sex") String sex);

//    修改某个学生信息
    public int updateStu(StudentInfo stuid);

//   foreach
    public List<StudentInfo> findAllByArray(int[] array);
}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xiao.mapper.StudentMapper">
    <resultMap id="studentlist" type="StudentInfo">
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="ssex" column="ssex"/>
        <result property="sage" column="sage"/>
        <result property="scid" column="scid"/>
        <association property="classInfo" javaType="ClassInfo" resultMap="class"></association>
    </resultMap>
    
    <resultMap id="class" type="ClassInfo">
        <result property="cid" column="cid"/>
        <result property="cname" column="cname"/>
    </resultMap>

    <!--查询所有的学生-->
    <select id="findAll" resultMap="studentlist">
        SELECT student.*,class.cname FROM student ,class  WHERE student.scid= class.cid
    </select>

    <!--查询某个学生信息-->
    <select id="findOneStuById" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student where sid=#{sid}
    </select>

    <!--根据学生姓名模糊查询-->
    <select id="findStuByName" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student
        <where>
            <if test="sname!=null and sname !=''">
            sname LIKE concat('%',concat(#{sname},'%'))
            </if>
        </where>
        <!-- where 和trim在这里用法效果一样-->
        <!--<trim prefix="where" prefixOverrides="and">-->
        <!--<if test="sname!=null and sname !=''">-->
            <!--sname LIKE concat('%',concat(#{sname},'%'))-->
        <!--</if>-->
        <!--</trim>-->
    </select>

    <!--根据学生年龄和姓名查询学生信息-->
    <select id="findStuByAgeName" parameterType="String" resultType="StudentInfo">
        SELECT * FROM student where 1=1
        <if test="name!=null and name !='' ">
            and sname LIKE concat('%',concat(#{name},'%'))
        </if>
        <if test="age!=null and age !='' ">
            AND sage>#{age}
        </if>
    </select>

    <!--switch...catch-->
    <select id="findStuByAgeNameSex"  resultType="StudentInfo">
        SELECT * FROM student where 1=1
        <choose>
            <when test="name!=null and name !=''">
                and sname LIKE concat('%',concat(#{name},'%'))
            </when>
            <when test="age!=null and age !=''">
                AND sage>#{age}
            </when>
            <when test="sex!=null and sex !=''">
                AND ssex=#{sex}
            </when>
        </choose>
    </select>

    <!--修改某个学生信息-->
    <update id="updateStu" parameterType="StudentInfo">
      UPDATE student
      <set>
         <if test="sname!=null and sname!=''"> sname=#{sname},</if>
         <if test="sage!=null and sage!=''"> sage=#{sage},</if>
         <if test="scid!=null and scid!=''"> scid=#{scid}</if>
          <!-- where 和if在这里用法效果一样-->
         <!--<if test="sid!=null and sid!=''"> where sid=#{sid}</if>-->
         <where>
             <if test="sid!=null and sid!=''">sid=#{sid}</if>
         </where>
      </set>
    </update>

    <!--foreach-->
    <select id="findAllByArray" resultType="StudentInfo">
        SELECT * FROM student where scid in
        <foreach collection="array" item="scid" open="(" separator="," close=")">
            #{scid}
        </foreach>
    </select>

</mapper>

测试类

package com.xiao.test;

import com.xiao.entity.ClassInfo;
import com.xiao.entity.StudentInfo;
import com.xiao.mapper.ClassMapper;
import com.xiao.mapper.StudentMapper;
import com.xiao.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test01 {
    public static void main(String[] args) {
//        test01();
//        test02();
//        test03();
//        test04();
//        test05();
//        test06();
        test07();
    }

    private static void test07() {
        int [] array = {2,1};
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> allByArray = session.getMapper(StudentMapper.class).findAllByArray(array);
        for (StudentInfo a : allByArray) {
            System.out.println(a.getSid()+"\t"+a.getSname());
        }
    }

    private static void test06() {
        SqlSession session = MybatisUtils.getSession();
        StudentInfo oneStuById = session.getMapper(StudentMapper.class).findOneStuById("6");
        oneStuById.setSage("17");
        int i = session.getMapper(StudentMapper.class).updateStu(oneStuById);
        if (i>0){
            System.out.println("OOOKKKupdate");
        }
        session.commit();
        MybatisUtils.closeSession(session);
    }


    private static void test05() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> nameStu = session.getMapper(StudentMapper.class).findStuByName("胡");
        for (StudentInfo s : nameStu) {
            System.out.println(s.getSid() + "\t" + s.getSname() + "\t" + s.getSage() + "\t");
        }
    }

    private static void test04() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> stu = session.getMapper(StudentMapper.class).findStuByAgeNameSex(null, null, "女");
        for (StudentInfo s : stu) {
            System.out.println(s.getSid()+"\t"+s.getSname());
        }

    }

    private static void test03() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> ageName = session.getMapper(StudentMapper.class).findStuByAgeName("23", null); //name="胡"
        for (StudentInfo s : ageName) {
            System.out.println(s.getSid()+"\t"+s.getSname());
        }
    }

    private static void test02() {
        SqlSession session = MybatisUtils.getSession();
        List<ClassInfo> allClass = session.getMapper(ClassMapper.class).findAllClass();

        for (ClassInfo aClass : allClass) {
            System.out.println(aClass.getCid()+"\t"+aClass.getCname()+"\t"+aClass.getStudentInfos().size());
            List<StudentInfo> studentInfos = aClass.getStudentInfos();
            for (StudentInfo studentInfo : studentInfos) {
                System.out.println(studentInfo.getSname()+"\t"+studentInfo.getScid());
            }
        }
    }

    private static void test01() {
        SqlSession session = MybatisUtils.getSession();
        List<StudentInfo> all = session.getMapper(StudentMapper.class).findAll();
        for (StudentInfo info : all) {
            System.out.println(info.getSid()+"\t"+info.getClassInfo().getCname());
        }
    }
}