MyBatis常用sql语句编写（基于MySQL数据库）

创建一张表t_user用于作示例：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别：1.男 2.女 3.保密',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标识：0.正常 1.删除 2.审核',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据库对应实体类：

package cn.com.javatest.entity;

import java.util.Date;

import org.apache.commons.lang3.builder.ReflectionToStringBuilder;

/**
 * 用户实体类
 * 
 * @author  Rodge
 * @time    2018年11月9日 下午12:05:35
 * @version 1.0.0
 */
public class User {

	/**
	 * 主键ID
	 */
	private int id;
	
	/**
	 * 姓名
	 */
	private String name;
	
	/**
	 * 性别：1.男 2.女 3.保密
	 */
	private String sex;
	
	/**
	 * 生日
	 */
	private Date birthday;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	
        @Override  
        public String toString() {  
            return ReflectionToStringBuilder.toString(this);  
        }
}

新增sql语句：

<insert id="save" parameterType="cn.com.javatest.entity.User">
    INSERT INTO t_user (
	    name,
	    sex,
	    birthday
    ) VALUES (
	    #{name},
	    #{sex},
	    #{birthday}
    )
</insert>

新增返回ID的sql语句（ID为整型）：

<insert id="save" parameterType="cn.com.javatest.entity.User">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">  
	    SELECT LAST_INSERT_ID()
    </selectKey>
    INSERT INTO t_user (
	    name,
	    sex,
	    birthday
    ) VALUES (
	    #{name},
	    #{sex},
	    #{birthday}
    )
</insert>

新增返回ID的sql语句（ID为字符串）：

<insert id="save" parameterType="cn.com.javatest.entity.User">
    <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String">  
	    SELECT REPLACE(UPPER(UUID()), '-', '')
    </selectKey>
    INSERT INTO t_user (
	    name,
	    sex,
	    birthday
    ) VALUES (
	    #{name},
	    #{sex},
	    #{birthday}
    )
</insert>

批量新增sql语句：

<insert id="saveBatch" parameterType="java.util.List">
    INSERT INTO t_user (
        name,
        sex,
        birthday
    ) VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.name}, 
         #{item.sex},
         #{item.birthday})
    </foreach>
</insert>

更新sql语句：

<update id="update" parameterType="cn.com.javatest.entity.User">
    UPDATE t_user SET
        name = #{name},
        sex = #{sex},
        birthday = #{birthday}
    WHERE del_flag = '0' AND id = #{id}	
</update>

动态更新sql语句：

<update id="update" parameterType="cn.com.javatest.entity.User">
    UPDATE t_user
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex},
        </if>
        <if test="birthday != null">
            birthday = #{birthday},
        </if>
    </set>
    WHERE del_flag = '0' AND id = #{id}
</update>

批量更新sql语句：

<update id="updateBatch" parameterType="cn.com.javatest.entity.User">
    <foreach collection="list" item="item" separator=";">
        UPDATE t_user SET
            name = #{item.name},
            sex = #{item.sex},
            birthday = #{item.birthday}
        WHERE del_flag = '0' AND id = #{item.id}
    </foreach>
</update>

删除sql语句（逻辑删除）：

<update id="delete">
    UPDATE t_user SET del_flag = '1' WHERE id = #{id}
</update>

删除sql语句（物理删除）：

<delete id="delete">
    DELETE FROM t_user WHERE id = #{id}
</delete>

批量删除sql语句：

<delete id="deleteBatch">
    DELETE FROM t_user WHERE id IN
    <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

根据主键ID查询sql语句：

<select id="findById" resultType="cn.com.javatest.entity.User">
    SELECT
        id,
        name,
        sex,
        birthday
    FROM t_user 
    WHERE del_flag = '0' AND id = #{id}
</select>

多条件动态查询sql语句：

<select id="selectList" resultType="cn.com.javatest.entity.User">
    SELECT
        t.id,
        t.name,
        t.sex,
        t.birthday
    FROM t_user t
    <where>
        t.del_flag = '0'
        <if test="name != null and name != ''">
            AND t.name = #{name}
        </if>
        <if test="sex != null and sex != ''">
            AND t.sex = #{sex}
        </if>
        <if test="birthday != null">
            AND t.birthday = #{birthday}
        </if>   
    </where>
</select>

分页查询sql语句：

<select id="selectList" resultType="cn.com.javatest.entity.User">
    SELECT
        t.id,
        t.name,
        t.sex,
        t.birthday
    FROM t_user t
    <where>
        t.del_flag = '0'
        <if test="name != null and name != ''">
            AND t.name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="sex != null and sex != ''">
            AND t.sex = #{sex}
        </if>
        <if test="startDay != null">
            AND t.birthday &gt;= #{startDay}
        </if>
        <if test="endDay != null">
            AND t.birthday &lt;= #{endDay}
        </if>
    </where>
    <choose>
        <when test="sidx != null and sidx != '' and order != null and order != ''">			
            ORDER BY ${sidx} ${order}
        </when>
        <otherwise>
            ORDER BY t.birthday DESC
        </otherwise>
    </choose>	
</select>

根据主键ID批量查询sql语句：

<select id="selectList" resultType="cn.com.javatest.entity.User">
    SELECT
        t.id,
        t.name,
        t.sex,
        t.birthday
    FROM t_user t
    WHERE t.del_flag = '0' AND t.id IN
    <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>	
</select>

多参数批量查询sql语句：

<select id="findByParams" resultType="cn.com.javatest.entity.User">
    <foreach collection="list" item="item" separator="UNION ALL">
        SELECT
            t.id,
            t.name,
            t.sex,
            t.birthday
        FROM t_user t
        WHERE t.del_flag = '0' 
        AND t.name = #{item.name}
        AND t.sex = #{item.sex}
        AND t.birthday = #{item.birthday}
    </foreach> 
</select>