MyBatis Mapper.XML 标签使用小结

直接将值返回给对象

<select id="list" resultType="com.vipsoft.base.entity.UserInfo">
   SELECT Id,Title FROM User
</select>

如果字段和属性名不一致时，通过 resultMap 做映射

<resultMap id="StudentResult" type="com.myBATis3.domain.Student">
  <id column="stud_id" property="studId"  jdbcType="VARCHAR"/>
  <result column="user_name" property="name"  jdbcType="VARCHAR"/>
  <result column="email"  property="email"  jdbcType="VARCHAR" />
  <result column="phone"  property="phone"  jdbcType="VARCHAR" />
  <result column="status" property="status" jdbcType="INTEGER" />
  <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
</resultMap>
<select id="findAllStudents" resultMap="StudentResult" >
  SELECT * FROM STUDENTS
</select>

实体引用另一个实体的查询结果返回

<resultMap type="Course" id="CourseResult">
  <id column="course_id" property="courseId"/>
  <result column="name" property="name"/>
  <result column="description" property="description"/&http://www.devze.comgt;
  <result column="start_date" property="startDate"/>
  <result column="end_date" property="endDate"/>
</resultMap>
<resultMap type="Tutor" id="TutorResult">
  <id column="tutor_id" property="tutorId"/>
  <result column="tutor_name" property="name"/>
  <result column="email" property="email"/>
  <collection property="courses" resultMap="CourseResult"/>
</resultMap>
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
  SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID, C.NAME, DESCRIPTION, START_DATE, END_DATE
  FROM TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID
  LEFT OUTER JOIN COURSES C ON T.TUTOR_ID=C.TUTOR_ID
  WHERE T.TUTOR_ID=#{tutorId}
</select>

choose 该方式适用于多个条件中选择一个满足条件的来生成sql

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
  SELECT * FROM COURSES
  <choose>
    <when test="searchBy == 'Tutor'">
      WHERE TUTOR_ID= #{tutorId}
    </when>
    <when test="searchBy == 'CourseName'">
      WHERE name like #{courseName}
    </when>
    <otherwise>
      WHERE TUTOR start_date  >= now()
    </otherwise>
  </choose>
</select>

where 适用于从多个条件中选择所有满足条件的来构成condtions，

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
  SELECT * FROM COURSES
  <where>  
    <if test=" tutorId != null ">
      TUTOR_ID= #{tutorId}
    </if>
    <if test="courseName != null">
      AND name like #{courseName}
    </if>
    <if test="startDate != null">
      AND start_date  >= #{startDate} <!--大于等于 开始时间-->
    </if>
    <if test="endDate != null">
      AND end_date  <= #{endDate} <!--小于等于 结束时间-->
 python   </if>
  </where>
</select>

foreach

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
select * from user
  <where>
    <!--
    collection:指定输入对象中的集合属性.该属性的值有三种：list,array,map，根据传入的集合类型而设定该值。
    item:每次遍历生成的对象
    index:当前迭代的次数
    open:开始遍历时的拼接字符串
    close:结束时拼接的字符串
    separator:遍历对象之间需要拼接的字符串
    select * from user where 1=1 and id in (1,2,3)
    -->
    <foreach collection="list" item="id" open="(" separator="," close=")">
     #{id}
    </foreach>
  </where>
</select>

select * from user where id=1 or id=2 or id=3

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
select * from user
  <where>
    <foreach collection="list" item="id" open="and (" close=")" separator="or">
       id=#{id}
    </foreach>
  </where>
</select>

< sql >标签

该标签主要定义复用的sql语句片段，在执行的sql语句标签直接引用即可。可以提高编码效率、简化代码和提高可读性。

需要配置id熟悉，表示该sql片段的唯一标识。

引用:通过<include refid=" " / >标签引用，refid的值就是< sql>的id属性的值。

<sql id="Base_Column_List">
  id, question, answer 
</sql>
<select id="selectByPrimaryKey" parameterType="Java.lang.Long" resultMap="BaseResultMap">
  select 
  <include refid="Base_Column_List" />
  from java
  where id = #{id,jdbcType=BIGINT}
</select>

<set> : 主要用来替换sql语句中的set字段，一般在update中使用。

<update>
  update user 
  <set>
    <if test="name != null and name.length()>0">name = #{name},</if>
    <if test="age != null and age .length()>0">age = #{age },</if>
  </set>
  where id = #{id}
</update>

批量更新，一次执行多条SQLandroid

<update>
    <foreach collection="list" item="item" index="index" separator=";">
        update user 
        <set>
            <if test="name != null and name.length()>0">name = #{name},</if>
            <if test="age != null and age .length()>0">age = #{age },</if>
        </set>
        <where>
            <if test="id != null and id != ''">
                AND id = #{id,jdbcType=VARCHAR}
            </if>
            <if test="userName != null and userName != ''">
                <bind name="bindUserName" value="'%' + userName + '%'" />
                AND UserName like #{bindUserName}
            </if> 
            <if test="status != null">
                AND Status = #{status,jdbcType=INTEGER}
            </if>
        </where>
    </foreach>
</update>

<trim> : 是一个格式化的标记，可以完成set或者是where标记的功能。

select * from user 
<trim prefix="WHERE" prefixoverride="AND |OR">
  <if test="name != null and name.length()>0"> AND name=#{name}</if>
  <if test="age != null and age.length()>0"> AND age=#{age}</if>
</trim> 
假如说name和age的值都不为null的话打印的SQL为：select * from user where name = ‘xx' and age = ‘xx'
在where的后面是不存在第一个and的，上面两个属性的意思如下：
　　prefix：前缀　　　　　　
　　prefixoverride：去掉第一个and或者是or

update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
  <if test="name != null and name.length()>0"> name=#{name} , php</if>
  <if test="age!= null and age.length()>0"> age=#{age} ,  </if>
</trim>
假如说name和age的值都不为null的话打印的SQL为：update user set name=‘xx' , age=‘xx' where id=‘x'
在age='xx'的后面不存在逗号，而且自动加了一个set前缀和where后缀，上面三个属性的意义如下，其中prefix意义如上：
　　 suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）
　 　suffix：后缀

 choose & foreach list 有一个值，条件=，有多个值 in 

<update id="updateStatus" parameterType="java.util.ArrayList" >
  update User
  set Status = 1,UpdateTime=(select GETDATE())
  <choose>
    <when test="idList != null and idList.size==1">
      WHERE Id= #{id,jdbcType=VARCHAR}
    </when>
    <when test="idList != null and idList.size>1">
      WHERE Id in
      <foreach collection="idList" item="id" open="(" close=")" separator=",">
        <if test="id != null and id != '' ">
          #{id,jdbcType=VARCHAR}
        </if>
      </foreach>
    </when>
    <otherwise>
      WHERE 1=0
    </otherwise>
  </choose>
</update>

到此这篇关于MyBatis Mapper.XML 标签使用小结的文章就介绍到这了,更多相关MyBatis Mapper.XML 标签内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！