mybatis标签用法---学习记录帖

//插入数据

<insert id="addUser" keyProperty="id" useGeneratedKeys="true" parameterType="user">

insert user (userName,age,sex,userCode,user_address) values (#{userName}, #{age}, #{sex}, #{userCode}, #{userAddress})

</insert>

//更新数据

<update id="updateUser">

update user set userName=#{name} where id=#{id}

</update>

//删除

<delete id="deleteUser" parameterType="string">

delete from user where userCode=#{code}

</delete>

//一对一

<resultMap id="userRole" type="user">

<id property="id" column="id"/>

<association property="userRole" javaType="role">

<id property="id" column="r_id"/>

<result property="roleName" column="roleName"/>

<result property="roleCode" column="roleCode"/>

</association>

//一对多

<collection property="userLinks" ofType="UserLink">

<id property="id" column="l_id"/>

<result property="linkName" column="linkName"/>

<result property="linkPhone" column="linkPhone"/>

</collection>

</resultMap>

//使用association标签 嵌套结果查询

<select id="queryUserRole" resultMap="userRole">

select u.id, u.userName,u.userCode,u.age,u.sex, r.id as r_id, r.roleName, r.roleCode, l.id as l_id,l.linkName,l.linkPhone

from user u,role r,userLink l where u.roleId=r.id and l.userId=u.id

</select>

<resultMap id="userRole2" type="user>

<id property="id" column="id"/>

<result column="roleId" property="roleId"/>

<association property="userRole" javaType="role" column="roleId" select="queryRoleById">

</association>

</resultMap>

//使用association标签 嵌套查询

<select id="queryUserRole2" resultMap="userRole2">

select u.id, u.userName,u.userCode,u.age,u.sex,u.roleId from user u

</select>

<select id="queryRoleById" parameterType="int" resultType="role">

select id, roleName, roleCode from role where id=#{id}

</select>

//trim

<select id="queryUser1" parameterType="user" resultType="user">

select id,userName,age,sex,userCode from user

<trim prefix="where" prefixOverrides="and | or" suffix=" order by id desc ">

<if test="userName != null and userName !=''">

and userName like concat('%', #{userName}, '%')

</if>

</trim>

</select>

//set案例

<update id="updateSet" parameterType="user">

update user

<set>

<if test="age != null">

age =#{age},

</if>

<if test="sex != null" >

sex =#{sex},

</if>

</set>

where id=#{id}

</update>

<update id="updateTrim">

update user

<trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">

<if test="age != null">

age =#{age},

</if>

<if test="sex != null">

sex =#{sex},

</if>

</trim>

</update>

<!--foreach案例-->

<select id="queryForEach" resultType="user">

select * from user

<where>

<!--if test="ids != null" -->

id in

<--使用foreach： (1,2,3)-->

<foreach collection="array" item="uId" open="(" separator="," close=")">

#{uId}

</foreach>

</where>

</select>

<--foreach colletion:map-key-->

<select id="queryForEach_map" resultType="user">

select * from user

<where>

id in

<!--使用foreach： (1,2,3) allIds:是调用时，自定义的map的key-->

<foreach collection="allIds" item="uId" open="(" separator="," close=")">

#{uId}

</foreach>

</where>

</select>

<!--choose案例-->

<select id="query_choose" resultType="user">

select * from user

<where>

<if test="id != null">

<choose>

<when test="id == 1">

userName like '%aa%'

</when>

<when test="id ==2">

userName like '%bb%'

</when>

<otherwise>

</otherwise>

</choose>

</if>

</where>

</select>

</mapper>

<insert id="addUser" keyProperty="id" useGeneratedKeys="true" parameterType="user">

insert user (userName,age,sex,userCode,user_address) values (#{userName}, #{age}, #{sex}, #{userCode}, #{userAddress})

</insert>

动态sql：

<if>标签：简单的逻辑判断，属性test 例：<if test="userName != null and userName != ''"></if>

<where>标签：用于查询语句中的条件拼接，<where>标签经常和<if>标签使用，where可以智能的处理sql语句中的and 和or

<trim>标签：格式化标签，等同于<set>标签、<where>标签

例：prefix="where" prefixOverrides="and | or" suffix=" order by id desc "

prefix：前缀

prefixOverrides：去掉and、or

suffix: 后缀

suffixOverrides：去掉后面的内容

<set>标签：用于update中set的设置，可以自动的处理sql中的逗号，<set>标签中没有属性

<foreach>标签：用于遍历集合，常用场景：in values

案例：select * from user where id in (1,2,3)

元素：

item：别名

collection：参数值：array、list、map-key(参数是Map集合时，collection属性：map中的key)

open：循环开始，拼接的符号，针对案例：open="("

close: 循环结束，拼接的符号, 案例：close=")"

separator: 分隔符 ,案例：separator=","

index: 索引

<choose>标签：与<when>、<otherwise>联合使用，相当于java中的switch case default