# MyBatisXml之MySQL
# 批量插入
import org.apache.ibatis.annotations.Param;
void insertBatch(@Param("records") List<Order> records);
1
2
3
2
3
<insert id="insertBatch">
<if test="records != null and records.size > 0">
insert into t_order(title,remark,create_time,create_user_id,create_user_name)
values
<foreach collection="records" item="item" separator=",">
(#{item.title},#{item.remark},#{item.createTime},#{item.createUserId},#{item.createUserName})
</foreach>
</if>
</insert>
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 批量更新
import org.apache.ibatis.annotations.Param;
void updateBatch(@Param("records") List<Order> records);
1
2
3
2
3
<update id="updateBatch">
<if test="records != null and records.size > 0">
<foreach collection="records" item="item" separator=";">
update t_order
<set>
<if test="item.title != null and item.title != ''">
title = #{item.title},
</if>
<if test="item.remark != null and item.remark != ''">
remark = #{item.remark},
</if>
</set>
where id = #{item.id}
</foreach>
</if>
</update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 批量删除
import org.apache.ibatis.annotations.Param;
void deleteBatch(@Param("ids") List<Long> ids);
1
2
3
2
3
<delete id="deleteBatch">
<if test="ids != null and ids.size > 0">
delete from t_order where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</if>
</delete>
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 时间范围查询 Date
List<Order> selectByTimeRange(@Param("startTime") Date startTime, @Param("endTime") Date endTime);
1
<select id="selectByTimeRange" resultType="cn.odboy.dal.dataobject.Order">
SELECT * FROM orders
WHERE create_time BETWEEN #{startTime} AND #{endTime}
ORDER BY create_time DESC
</select>
1
2
3
4
5
6
2
3
4
5
6
# 时间范围查询 LocalDateTime
List<Order> selectByLocalDateTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
1
<select id="selectByLocalDateTimeRange" resultType="cn.odboy.dal.dataobject.Order">
SELECT * FROM orders
WHERE create_time BETWEEN #{startTime} AND #{endTime}
ORDER BY create_time DESC
</select>
1
2
3
4
5
6
2
3
4
5
6
# 时间范围查询 动态Map
List<Order> selectByDynamicTimeRange(Map<String, Object> params);
1
<select id="selectByDynamicTimeRange" resultType="cn.odboy.dal.dataobject.Order">
SELECT * FROM orders
<where>
<if test="startTime != null">
AND create_time >= #{startTime}
</if>
<if test="endTime != null">
AND create_time <= #{endTime}
</if>
</where>
ORDER BY create_time DESC
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 【实际执行SQL】演示：最好转成字符串后再传入Xml中
// SELECT * FROM system_user WHERE (create_time > '2025-08-17T16:48:18.400+0800');
// systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, new Date()));
// SELECT * FROM system_user WHERE (create_time > '2025-08-17T16:50:16.786+0800');
// systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateTime.now()));
// SELECT * FROM system_user WHERE (create_time > '2025-08-17 16:53:46');
// systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateUtil.formatDateTime(new Date())));
// SELECT * FROM system_user WHERE (create_time > '2025-08-17');
// systemUserMapper.selectList(new LambdaQueryWrapper<SystemUserTb>().gt(SystemUserTb::getCreateTime, DateUtil.formatDate(new Date())));
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 使用日期函数进行日期部分比较
/**
* 按日期部分查询（忽略时间部分）
* @param startDate 开始日期（只包含日期部分）
* @param endDate 结束日期（只包含日期部分）
*/
List<Order> selectByDatePart(@Param("startDate") Date startDate, @Param("endDate") Date endDate);
1
2
3
4
5
6
2
3
4
5
6
<select id="selectByDatePart" resultType="cn.odboy.dal.dataobject.Order">
SELECT * FROM orders
WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate}
ORDER BY create_time DESC
</select>
1
2
3
4
5
2
3
4
5
# 如果上面的startDate，含有时间部分，但是只想比较日期部分，可以这么写
<select id="selectByDatePart" resultType="cn.odboy.dal.dataobject.Order">
SELECT * FROM orders
WHERE DATE(create_time) BETWEEN DATE(#{startDate}) AND DATE(#{endDate})
ORDER BY create_time DESC
</select>
1
2
3
4
5
6
2
3
4
5
6
# 一对多嵌套查询
<resultMap id="userWithOrdersMap" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="orders" ofType="Order" select="selectOrdersByUserId" column="id"/>
</resultMap>
<select id="selectUserWithOrders" resultMap="userWithOrdersMap">
SELECT id, name FROM users WHERE id = #{id}
</select>
<select id="selectOrdersByUserId" resultType="Order">
SELECT * FROM orders WHERE user_id = #{userId}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13