Mybatis mapper.xml_mapper 里可以写getdate-CSDN博客

本文链接：https://blog.csdn.net/SakeOfWhat/article/details/85244126

本文介绍了在Mybatis中如何在执行插入操作后获取自增主键ID，以及展示了如何进行多行数据的批量删除和在SQL Server中进行时间筛选及分页查询的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用insert后获得自增id的方法

  <insert id="addTravellerFootAtDao" useGeneratedKeys="true" keyProperty="footId">
        INSERT INTO travellerfoot(footTitle, footDate, footText, travellerId) VALUES (#{footTitle},#{footDate},#{footText},#{travellerId});
  </insert>

public int addTravellerFootAtDao(TravellerFootBean addFoot) {
        TravellerFootDao dao=getSqlSession().getMapper(TravellerFootDao.class);
        int result=dao.addTravellerFootAtDao(addFoot);
        int id=addFoot.getFootId();
        System.out.println("dao层结果 addTravellerFootAtDao："+result);
        System.out.println("返回自增主键："+id);
        return id;
    }
keyProperty为自增的id字段。调用insert后自动将自增id赋值进insert调用的实体类中

多行批量删除

<delete id="deleteDate" parameterType="java.util.List">
	delete from sys_menus where 1>2 or MENU_ID in
	<foreach collection="list" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</delete>

public int deleteDate(ArrayList<Integer> data);

sqlserver时间筛选

<if test="beginTime != null and beginTime != '' ">
	and  CONVERT(CHAR(10), OPER_DATE, 120)  &gt;= #{beginTime}
</if>
<if test="endTime !=null  and endTime != '' ">
	and  CONVERT(CHAR(10), OPER_DATE, 120)  &lt;= #{endTime}
</if>

数据库时间类型Date

<if test="beginTime != null and beginTime != '' and endTime !=null  and endTime != '' ">
    and OPER_DATE between CAST(#{beginTime} as datetime) and CAST(#{endTime} as datetime)
</if>

参数格式：2018-12-12

sqlserver分页查询

<select id="queryLogByConditionAtMapper" resultMap="BaseResultMap">
	select * from(
		select *,ROW_NUMBER() OVER(order by LOG_ID) as RowId from log where 1=1
		<if test="user_id !=null and user_id != ''">
			and USER_ID like ('%'+#{user_id}+'%')
		</if>
		<if test="menu_id >=0">
			and MENU_ID=#{menu_id}
		</if>
		<if test="beginTime!=null and beginTime!=''">
			and CONVERT(CHAR(10),OPER_DATE,120) &gt;= #{beginTime}
		</if>
		<if test="endTime!=null and endTime!=''">
			and CONVERT(CHAR(10),OPER_DATE,120) &lt;= #{endTime}
		</if>
	)as b where ROWId between #{beginRow} and #{endRow}
</select>