直接将值返回给对象
<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>