直接将值返回给对象
<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"/>
<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}
</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>
批量更新，一次执行多条SQL
<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} , </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>
