Mybatis常用示例

mybatis 增删改查示例：

数据库和实体类映射  column：数据库字段值  property: 实体类对应值

<resultMap id="BaseResultMap" type="com.user.api.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="mobile" property="mobile"/>
        <result column="password" property="password"/>
        <result column="gender" property="gender"/>
        <result column="status" property="status"/>
    </resultMap>

 新增：新增之后返回新增数据ID

<insert id="insert" parameterType="com.user.api.entity.User" useGeneratedKeys="true" keyProperty="id">
    insert into user (name, mobile, password, gender,status)
    values (#{name}, #{mobile},#{password}, #{gender},#{status})
 </insert>

修改：

<update id="updateUser" parameterType="com.user.api.entity.User" flushCache="true">
        update user
        <set>
            <if test="mobile != null and mobile != ''">mobile = #{mobile},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="password != null and password != ''">password = #{password}</if>
        </set>
        where id = #{id}
    </update>

删除：

<delete id="delByChannelId">
    delete from user where id = #{id}
 </delete>

查询：

<sql id="where_sql">
        <where>
            <if test="id != null and id > 0">AND id = #{id}</if>
            <if test="name != null and name != ''">
                <bind name="pattern" value="'%' + name + '%'"/>
                AND name like #{pattern}
            </if>
            <if test="mobile != null and mobile != ''">AND mobile = #{mobile}</if>
        </where>
    </sql>
   <select id="listUsers" resultMap="BaseResultMap">
        SELECT * FROM user
        <include refid="where_sql"/>
        order by id desc limit #{limit},#{offset}
   </select>

批量查询：

<select id="listById" parameterType="java.util.List" resultMap="BaseResultMap">
        select * from user
        <where>
            <foreach collection="userIds" item="item" separator="OR">
                id = #{item}
            </foreach>
        </where>
 </select>

查询： choose when otherwise 一旦when条件成立 之后的when 则不会执行，条件都不满足则执行otherwise
//当搜索条件输入了名字按照姓名查找，输入了手机号按手机号查找，否则查询name为张三的用户

<select id="queryUser" resultType="com.user.api.entity.User">
    select * from tb_user WHERE sex=1
    <choose>
        <when test="name!=null and name.trim()!=''">
            and name like '%${name}%'
        </when>
        <when test="mobile!=null and mobile !=''">
            and mobile = #{mobile}
        </when>
        <otherwise>
            and name='张三'
        </otherwise>
    </choose>
  </select>

查询： where if 判断条件如果成立则会执行对应内容

<select id="getUser" resultType="com.user.api.entity.User">
    select * from user
    <where
    1=1
    <if test="name!=null and name.trim()!=''">
        and name like '%${name}%'
    </if>
    <if test="mobile!=null and mobile !=''">
        and mobile = #{mobile}
    </if>
    </where>
 </select>

查询：left join, right join, join
left join :左连接 会查询左表的所有数据 和满足on 条件的右表数据，没查询出的右表数据用空表示
right join:右连接 会查询右表的所有数据 和满足on 条件的左表数据，没查询出的左表数据用空表示
join: 即inner join 查询满足左右两表的交集数据

对于单个字符的查询在进行if 判断时候需要写到双引号或者toString()
<if test='gender!=null and gender == "1"'>
and name like '%${name}%'
</if>
或者
<if test="gender!=null and gender == '1'.toString()">
and name like '%${name}%'
</if>
if条件判断number类型，没必要判断''的情况，只需判断null的情况
<if test="status!=null">
and status = #{status}
</if>