基本

Mapper

    // 查询
    int select(long userId);

    // 插入
    boolean insert(Homework homework);

    // 批量插入
    boolean insertBatch(List<Homework> homeworkList);
    
    // 更新
    boolean update(long id);

    // 批量更新
    boolean updateBatch(List<Long> ids);

    // 插入（有则更新）
    boolean insertOrUpdate(long id);

XML

<?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">
<mapper namespace="com.demo.mapper.HomeMapper">
    
    <!-- 查询 -->
    <select id="select" resultType="int" parameterType="long">
        SELECT COUNT(1) FROM user WHERE id=#{userId}
    </select>

    <!-- 插入 -->
    <insert id="insert" parameterType="com.demo.pojo.Homework"
            useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `homework` (user_id, create_time)
        VALUES (#{userId}, #{createTime})
    </insert>

    <!-- 批量插入 -->
    <insert id="insertBatch" parameterType="com.demo.pojo.Homework">
        INSERT INTO `homework` (user_id, create_time)
        VALUES
        <foreach collection="list" item="hk" separator=",">
            (
            #{hk.userId},
            #{hk.createTime}
            )
        </foreach>
    </insert>

    <!-- 更新 -->
    <update id="update" parameterType="long">
        UPDATE `homework`
        SET `deleted`=1
        WHERE id = #{id}
    </update>

     <!-- 批量更新 -->
    <update id="update" parameterType="long">
        <foreach collection="list" item="item" separator=";">
            UPDATE `homework`
            SET `deleted`=1
            WHERE id = #{id}
        </foreach>
    </update>

    <!-- 插入或更新 -->
    <!--IFNULL(MAX(id),0) 如果没有符合条件的记录 select语句的结果就是 `N/A` ifnull()处理不了 N/A，再加个max()，当查询结果为 N/A时，max(id)的值为null-->
    <update id="insertOrUpdate" parameterType="com.demo.pojo.Homework">
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            select IFNULL(MAX(id),0) from `homework` where `id`= #{id}
        </selectKey>
        <if test="count==0">
             INSERT INTO `homework` (user_id, create_time)
            VALUES (#{userId}, #{createTime})
        </if>
        <if test="count>0">
            UPDATE `homework` SET `deleted`=1 WHERE id = #{id}
        </if>
    </update>

</mapper>

条件判断 if

<if test="param != null">
    // sql
</if>

多条件判断 choose

Mybatis 中没有 else 要用 chose when otherwise 代替，可以有多个when

<choose>
    <when test="">
        //...
    </when>
    <otherwise>
        //...
    </otherwise>
</choose>

insert实体类返回主键

两种方式，推荐第一种。

1、添加属性 useGeneratedKeys="true" keyProperty="id"

boolean insert(Homework homework);

<insert id="insert" parameterType="com.demo.pojo.Homework" useGeneratedKeys="true" keyProperty="id">

2、添加代码

<selectKey resultType="java.lang.Short" order="AFTER" keyProperty="id">
    SELECT LAST_INSERT_ID() AS id
</selectKey>

批量插入

 boolean insertHomeworkBatch(List<HomeworkPojo> homeworkList);

 <insert id="insertHomeworkBatch" parameterType="com.demo.pojo.Homework">
        INSERT INTO `homework` (
        user_id,
        create_time
       )
        VALUES
        <foreach collection="list" item="hk" separator=",">
            (
            #{hk.userId},
            #{hk.createTime}
            )
        </foreach>
    </insert>

批量查询

    List<User> findByList(List<Integer> list);
    List<User> findByArr(String[] ids);

    <select id="findByList" resultType="com.xia.domo.User" parameterType="java.util.List">
        SELECT * FROM user
        <where>
            <if test="list != null and list.size() > 0">
                AND id in
                <foreach collection="list" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

    <select id="findByArr" resultType="com.xia.domo.User">
        select * from user
        where id in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

int参数判断

Mybatis if 判断参数类型为Integer，参数值为0时，结果为false，不进入if标签：

<if test="state != null and state != '' ">
   AND state =#{state}
</if>

解决方案:

把state != '' 去掉，当参数数据类型Int，并且传入的值为0时，在Mybatis会将其转化为空字符串，所以判断为false不能and连接条件。

<if test="state != null">
   AND state =#{state}
</if>

符号转义

参考：

https://www.cnblogs.com/poterliu/p/11582605.html
https://blog.csdn.net/yjclsx/article/details/84318397
https://blog.csdn.net/xjszsd/article/details/121912152
http://www.45fan.com/article.php?aid=1CZUJBWkH46PTVcn
https://blog.csdn.net/qq_42112618/article/details/123592158

本文链接： https://jianz.xyz/index.php/archives/177/