mybatis xml 批量插入 in查询 模糊like查询 字符串转时间区间查询等操作

目录

in 查询：

模糊 like查询：

字符串转时间区间查询：

批量插入：

插入数据返回主键id

---

in 查询：

    @Test
    public void inSelect() {
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        student.setName("小王");
        Student student1 = new Student();
        student1.setName("老王");
        list.add(student);
        list.add(student1);
        List<Student> resultList = studentMapper.selectListWithList(list);
        System.out.println(JSONObject.toJSONString(resultList));
    }

  <!-- in 查询 -->
  <select id="selectListWithList" resultType="com.example.demo.entity.Student">
    select id,name,age,city,update_time updateTime from student where name in
    <foreach collection="list" index="index" item="list" open="(" close=")" separator=",">
      #{list.name}
    </foreach>
  </select>

模糊 like查询：

    @Test
    public void likeSelect() {
        Student student = new Student();
        student.setName("王");
        List<Student> resultList = studentMapper.selectListWithLike(student);
        System.out.println(JSONObject.toJSONString(resultList));
    }

  <!-- like 查询 -->
  <select id="selectListWithLike" resultType="com.example.demo.entity.Student">
    select id,name,age,city,update_time updateTime from student
    <where>

      <if test="name != null and name !=''">
        <!-- Oracle postgresql like 使用 || -->
        <!-- and name like '%'||#{name}||'%' -->

        <!-- mysql like 使用 concat 或者 "%"直接连接条件，直接连接 "%" 注意%是使用 "" 包含-->
        <!--and name like concat('%',#{name},'%')-->
        and name like "%"#{name}"%"
      </if>

    </where>
  </select>

字符串转时间区间查询：

    @Test
    public void selectWithTime() {
        Student student = new Student();
        student.setStartTime("2020-09-29 11:10:51");
        student.setEndTime("2020-11-02 11:06:41");
        List<Student> resultList = studentMapper.selectListWithTime(student);
        System.out.println(JSONObject.toJSONString(resultList));
    }

    @Test
    public void selectWithEndTime() {
        Student student = new Student();
        student.setEndTime("2020-11-02 11:06:41");
        List<Student> resultList = studentMapper.selectWithEndTime(student);
        System.out.println(JSONObject.toJSONString(resultList));
    }

  <!-- 时间区间 查询 -->
  <select id="selectListWithTime" resultType="com.example.demo.entity.Student">
    select id,name,age,city,update_time updateTime from student
      <where>

        <if test="startTime != null and startTime !='' and endTime != null and endTime !=''">
          <!-- oracle string 转时间 to_date -->
          <!--and update_time between to_date(#{startTime},'yyyy-MM-dd hh24:mi:ss') and to_date(#{endTime},'yyyy-MM-dd hh24:mi:ss')-->

          <!-- postgresql string 转时间 to_timestamp -->
          <!-- and update_time between to_timestamp(#{startTime},'yyyy-MM-dd hh24:mi:ss') and to_timestamp(#{endTime},'yyyy-MM-dd hh24:mi:ss')-->

          <!-- mysql string 转时间 STR_TO_DATE -->
          and update_time between STR_TO_DATE(#{startTime},'%Y-%m-%d %H:%i:%s') and STR_TO_DATE(#{endTime},'%Y-%m-%d %H:%i:%s')
        </if>
      </where>
  </select>

  <!-- 时间 查询 -->
  <select id="selectWithEndTime" resultType="com.cp.entity.Student">
    select id,name,age,city,update_time updateTime from student
    <where>

      <if test="endTime != null and endTime !=''">

        <!-- 大于等于>= &gt;=   小于等于 <= &lt;= -->
        and update_time &lt;=  STR_TO_DATE(#{endTime},'%Y-%m-%d %H:%i:%s')
      </if>
    </where>
  </select>

批量插入：

    @Test
    public void batchInsert() {
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        student.setName("小刘");
        student.setCity("广东");
        student.setAge(24);
        Student student1 = new Student();
        student1.setName("老刘");
        student1.setCity("重庆");
        student1.setAge(45);
        list.add(student);
        list.add(student1);
        studentMapper.xmlBatchInsert(list);
    }

  <!-- 批量插入 -->
  <insert id="xmlBatchInsert">
    INSERT INTO student(name,age,city)  VALUES
    <foreach collection="list" item="item" index="index" separator=",">
      (#{item.name},
      #{item.age},
      #{item.city})
    </foreach>
  </insert>

插入数据返回主键id

    @Test
    public void batchInsert() {
        
        Student student = new Student();
        student.setName("小刘");
        student.setCity("广东");
        student.setAge(24);
       
        studentMapper.insertGetId(student);
        int id = student.getId();
    }

  <!-- 插入返回主键 -->
  <insert id="insertGetId" parameterType="com.cp.entity.Student" useGeneratedKeys="true" keyProperty="Id">
    INSERT INTO student(name,age,city)  VALUES
      (#{item.name},
      #{item.age},
      #{item.city})
  </insert>

 

详见github：GitHub - chenping-1993/springmvc-mybatis: 本项目集成了mybatis的三种实现方式 1、通过配置xml的方式操作数据 2、通过继承mapper接口操作数据，mapper接口操作数据有多种方式，如 tk.mybatis、mybatis-plus,本项目使用mybatis-plus 3、通过注解方式操作数据，例如使用 @SelectProvider注解动态拼装sql查询数据，适用于复杂的sql查询