目录
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 !=''">
<!-- 大于等于>= >= 小于等于 <= <= -->
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>
插入数据返回主键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>