mybatis 动态注解中使用in 或like 及对象

秒速五厘米 2021-12-03 11:53 689阅读 0赞

# 1. @Insert 入参为List #

@Insert("<script> insert into tb_student
    (school_id, student_id, student_name, age, created_at,updated_at,version) values  " +
            "  <foreach collection='studentList' item='item' separator=',' > " +
            "  (#{item.schoolId},#{item.studentId},#{item.studentName},#{item.age},now(),now(),0)" +
            "  </foreach> </script>")
    int bulkInsertStudents(@Param("studentList") List<studentInfo> studentList);

# 2.@Select 语句中使用in, like(也可提前参数中加入‘%’来实现) #

@Select("<script> select student_id as studentId, student_name as studentName, age from tb_student where studentId in " +
         "<foreach collection='studentIdList' item='id' open='(' separator=',' close=')'>" +
         "#{id}" +
         "</foreach> </script> ")
        List<StudentInfo> selectStudentByIdList(@Param("studentIdList") List<Long> studentIdList);
    
    @Select("select student_id as studentId, student_name as studentName, age from tb_student where student_name like CONCAT('%',#{studentName},'%')")
        List<StudentInfo> selectStudentByName(@Param("studentName") String studentName);
    
    @Select({"<script>",
                "SELECT id, shop_id, area_name as areaName, locate_type as locateType, map_id as mapId, shape, shape_data as shapeData FROM tb_area_v2",
                "WHERE school_id=#{schoolId}",
                "<choose>",
                "<when test='status== 1'>",
                "AND address is null",
                "</when>",
                "<otherwise>",
                "AND address is not null",
                "</otherwise>",
                "</choose>",
                "<if test='studentName!=null'>",
                "AND  LOCATE(#{studentName},student_name)",
                "</if>",
                "order by id desc",
                "</script>"})
    List<StudentInfo> selectStudentByName(@Param("schoolId") String schoolId, @Param("studentName") String studentName, @Param("status") int status);