java mybatis xml相关

java mybatis xml相关操作
1.mybatis xml 传入list
2.mybatis xml 传入数组
3.mybatis xml 处理日期
4.mybatis xml 转义字符
5.mybatis xml 关于传参
6.insert返回当前插入的id
7.mybatis xml 如何写if else

1.mybatis xml 传入list

List<StudentEntity> getListById(List ids);

<select id="getListById" parameterType="java.util.List" resultMap="BaseResultMap">
  SELECT * FROM student
  where 1=1
    and
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
      student_id = #{item}
    </foreach>
</select>

2.mybatis xml 传入数组

List<StudentEntity> getListById (@Param("ids")Integer[] ids);

<select id="getListById" resultMap="BaseResultMap">
  SELECT * FROM student
  where 1=1
  <if test="ids!=null and ids.length>0" >
    and
    <foreach collection="ids" item="id" index="index" separator="OR">
      student_id = #{id}
    </foreach>
  </if>
</select>

3.mybatis xml 处理日期，把字符串转为日期格式

有开始时间和结束时间，将日期时分秒设置成指定时间，如：00:00:00

<if test="null != queryStartDate and '' != queryStartDate">
  <![CDATA[
           AND start_time >= date_format(#{queryStartDate}, '%Y-%m-%d 00:00:00')
      ]]>
</if>

<if test="null != queryEndDate and '' != queryEndDate">
  <![CDATA[
           AND end_time <= date_format(#{queryEndDate}, '%Y-%m-%d 23:59:59')
      ]]>
</if>

单个日期，把字符串转为日期格式，便于数据库查询

<if test="courseDate != null and courseDate != ''">
            and date_format(ts.course_date, '%Y%m') = date_format(#{courseDate}, '%Y%m')
</if>

4.mybatis xml 转义字符

字段 符号 说明
&lt; < 小于号
&gt; > 大于号
&amp; & 和
&apos; ' 单引号
&quot; " 双引号

5.mybatis xml insert返回当前插入的id

SELECT LAST_INSERT_ID()

<insert id="insertSelective" parameterType="TeachEntity">
    <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="id">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into teach
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name != null">
        `name`,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id},
      </if>
      <if test="name != null">
        #{name},
      </if>
    </trim>
  </insert>

6.mybatis xml 关于传参
xml不指定传参类型

 Integer checkStu(@Param("studentId") Long studentId));

  <select id="checkStu"  resultType="java.lang.Integer">
      SELECT COUNT(1)
      FROM student s
      WHERE  s.student_id = #{studentId} 
  </select>

xml指定传参类型

List<TeachEntity> getByTeacherId(Long id);

 <select id="getByTeacherId" parameterType="java.lang.Long" resultType="TeachEntity">
    select
    *
    from teach
    where teacher_id = #{teacherId}
  </select>

7.mybatis xml 如何写if else
mybatis xml里面只有if标签，没有else标签，if else需要用choose和otherwise来写。

   <select id="getActiveListByParam" resultType="com.abie.framework.entity.ActiveEntity" parameterType="com.abie.framework.entity.ActiveEntity">
        SELECT
        *
        FROM active a
        <where>
            a.is_del = 0
          <choose>
            <when test="id != null and id != ''">
              AND a.id = #{id}
            </when>
            <otherwise>
              AND  a.status = 1
            </otherwise>
          </choose>
        </where>
        GROUP BY a.id
    </select>