java mybatis xml相关_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>
1
2
3
4
5
6
7
8
9
10

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>
1
2
3
4
5
6
7
8
9
10
11
12

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>
1
2
3
4
5
6
7
8
9
10
11

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

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

4.mybatis xml 转义字符

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

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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>
1
2
3
4
5
6
7

xml指定传参类型

List<TeachEntity> getByTeacherId(Long id);

 <select id="getByTeacherId" parameterType="java.lang.Long" resultType="TeachEntity">
    select
    *
    from teach
    where teacher_id = #{teacherId}
  </select>
1
2
3
4
5
6
7
8

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17