Mybatis的xml中动态sql总结

1.动态sql

a)<sql>:可以把共用的sql抽取出来

b)<where>:自动添加where关键字,去掉多余的AND

c)<set>:自动添加set关键字，去掉多余的逗号，用在修改

d)<trim>：可以替代<where>和<set>

1)前缀

2)前缀覆盖

3)后缀

4)后缀覆盖

4)<if>:判断是否为非空的

1)字符串要判断空和空字符串

2)多个条件之间用and

5)<choose>:底层用的switch..case...

6)<froeach>:可以遍历集合或者数组

1)collection:集合名称

a)传递是list:写list

b)传递是array:写array

c)用注解修饰行形参:写注解中的name

d)传递是map:写key

2)open:循环开始被调用

3)close:循环结束被调用

4)item:循环中的临时变量

5)separator:每次循环都会调用一次，最后一次不会调用

 

 

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 

<!-- namespace:接口的全类名 -->

<mapper namespace="com.qf.dao.IUserDao">

 

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

<result property="name" column="username" />

</resultMap>

 

<!-- 把共用的sql抽取出来 -->

<sql id="base_user">

id,

username,

PASSWORD,

birthday

</sql>

 

<select id="getUserById" resultMap="userMap">

SELECT

<include refid="base_user" />

FROM

t_user

WHERE

id = #{id}

</select>

 

<select id="getUserList" resultMap="userMap">

SELECT

<include refid="base_user" />

FROM

t_user

</select>

 

<select id="findUser" resultMap="userMap">

SELECT

<include refid="base_user"></include>

FROM

t_user

WHERE

1=1

<if test="id != null">

AND id = #{id}

</if>

<!--字符串需要判断null和空字符串 -->

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

AND username like concat("%",#{name},"%")

</if>

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

AND password = #{password}

</if>

</select>

 

<select id="findUser2" resultMap="userMap">

SELECT

<include refid="base_user"></include>

FROM

t_user

<where>

<if test="id != null">

AND id = #{id}

</if>

<!--字符串需要判断null和空字符串 -->

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

AND username like concat("%",#{name},"%")

</if>

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

AND password = #{password}

</if>

</where>

</select>

 

<update id="updateUser">

UPDATE

t_user

<set>

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

username = #{name},

</if>

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

password = #{password},

</if>

<if test="brithday != null">

birthday = #{brithday},

</if>

</set>

where

id = #{id}

</update>

 

<select id="findUser3" resultMap="userMap">

SELECT

<include refid="base_user"></include>

FROM

t_user

<trim prefix="WHERE" prefixOverrides="AND">

<if test="id != null">

AND id = #{id}

</if>

<!--字符串需要判断null和空字符串 -->

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

AND username like concat("%",#{name},"%")

</if>

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

AND password = #{password}

</if>

</trim>

</select>

 

<update id="updateUser2">

UPDATE

t_user

<trim prefix="set" suffixOverrides=",">

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

username = #{name},

</if>

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

password = #{password},

</if>

<if test="brithday != null">

birthday = #{brithday},

</if>

</trim>

where

id = #{id}

</update>

 

 

<!--

如果用户给了name属性就按照name的模糊查询，如果没有给就只查询name不为空

-->

<select id="findUser4" resultMap="userMap">

SELECT

<include refid="base_user"></include>

FROM

t_user

<where>

<choose>

<when test="name != null and name != ''">

username like concat("%",#{name},"%")

</when>

<otherwise>

username is not null

</otherwise>

</choose>

</where>

</select>

 

<!--

collection:集合名称

传递是list:写list

传递是array:写array

用注解修饰行形参:写注解中的name

传递是map:写key

open:循环开始被调用

close:循环结束被调用

item:循环中的临时变量

separator:每次循环都会调用一次，最后一次不会调用

-->

<delete id="deleteUser">

delete from t_user

where id in

<foreach collection="ids" open="(" close=")" item="id" separator=",">

#{id}

</foreach>

</delete>

 

</mapper>