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

ゞ浴缸里的玫瑰 2022-11-22 12:48 258阅读 0赞

**目录**

in 查询：

模糊 like查询：

字符串转时间区间查询：

批量插入：

--------------------

### **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));
        }

<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));
        }

<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 !=''">
            
            
    
            
            
            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 !=''">
              
              
    
              
              
    
              
              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 !=''">
    
            
            and update_time <=  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>

详见github：[https://github.com/chenping-1993/springmvc-mybatis][https_github.com_chenping-1993_springmvc-mybatis]

[https_github.com_chenping-1993_springmvc-mybatis]: https://github.com/chenping-1993/springmvc-mybatis