MyBatis中常用的SQL语句详解_Java

mybatis 中常用的 sql 语句与标准 sql 语句基本一致，但 mybatis 提供了一些额外的特性和标签来更方便地构建和管理 sql 语句。

以下列出 mybatis 中常用的 sql 语句，并结合 mybatis 的特性进行说明：

1. select (查询)

基本查询：

<select id="selectuserbyid" parametertype="int" resulttype="user">
  select id, username, password, email from users where id = #{id}
</select>

id: 该 sql 语句的唯一标识符，用于在 java 代码中引用。
parametertype: 传入参数的类型 (可选，mybatis 可以自动推断)。
resulttype: 查询结果映射的 java 类型 (pojo)。
#{id}: 占位符，表示传入的参数，mybatis 会自动进行参数绑定和类型转换，防止 sql 注入。

多条件查询 (动态 sql)：

<select id="selectusers" parametertype="map" resulttype="user">
  select id, username, password, email from users
  <where>
    <if test="username != null and username != ''">
      and username like concat('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
      and email = #{email}
    </if>
  </where>
</select>

<where>: 自动添加 where 关键字，并处理多余的 and 或 or。
<if>: 根据条件判断是否包含该 sql 片段。
test: 条件表达式，使用 ognl 表达式。
like: 模糊查询，concat('%', #{username}, '%') 用于构建模糊查询条件。

关联查询 (join)：

<select id="selectuserwithorders" parametertype="int" resultmap="userwithordersresultmap">
  select
    u.id as user_id,
    u.username,
    o.id as order_id,
    o.order_date
  from users u
  left join orders o on u.id = o.user_id
  where u.id = #{id}
</select>

<resultmap id="userwithordersresultmap" type="user">
  <id property="id" column="user_id"/>
  <result property="username" column="username"/>
  <collection property="orders" oftype="order">
    <id property="id" column="order_id"/>
    <result property="orderdate" column="order_date"/>
  </collection>
</resultmap>

left join: 左连接，获取用户及其所有订单。
resultmap: 自定义结果映射，用于处理复杂的关联关系。
<collection>: 映射一对多关系，将订单列表映射到 user 对象的 orders 属性。

分页查询 :

<!-- mysql -->
<select id="selectusersbypage" parametertype="map" resulttype="user">
  select id, username, password, email from users limit #{offset}, #{pagesize}
</select>

<!-- oracle -->
<select id="selectusersbypage" parametertype="map" resulttype="user">
  select * from (
    select t.*, rownum rn from (
      select id, username, password, email from users
    ) t where rownum <= #{endrow}
  ) where rn > #{startrow}
</select>

limit: mysql 中的分页关键字。
rownum: oracle 中的伪列，用于分页。
需要根据不同的数据库方言使用不同的分页语法。 mybatis 提供了 dialect 接口来支持不同的数据库方言。

2. insert (插入)

基本插入:

<insert id="insertuser" parametertype="user">
  insert into users (username, password, email) values (#{username}, #{password}, #{email})
</insert>

获取自增主键:

<insert id="insertuser" parametertype="user" usegeneratedkeys="true" keyproperty="id">
  insert into users (username, password, email) values (#{username}, #{password}, #{email})
</insert>

usegeneratedkeys="true": 启用获取自增主键。
keyproperty="id": 指定将自增主键值设置到 user 对象的哪个属性 (通常是 id)。

批量插入 (foreach):

<insert id="batchinsertusers" parametertype="java.util.list">
  insert into users (username, password, email) values
  <foreach collection="list" item="user" separator=",">
    (#{user.username}, #{user.password}, #{user.email})
  </foreach>
</insert>

<foreach>: 循环遍历 list 集合，生成多个插入值。
collection: 指定要遍历的集合。
item: 集合中每个元素的别名。
separator: 分隔符，用于分隔每次循环生成的 sql 片段。

3. update (更新)

基本更新:

<update id="updateuser" parametertype="user">
  update users set username = #{username}, password = #{password}, email = #{email} where id = #{id}
</update>

动态更新 (set + if):

<update id="updateuserselective" parametertype="user">
  update users
  <set>
    <if test="username != null and username != ''">
      username = #{username},
    </if>
    <if test="password != null and password != ''">
      password = #{password},
    </if>
    <if test="email != null and email != ''">
      email = #{email},
    </if>
  </set>
  where id = #{id}
</update>

<set>: 自动添加 set 关键字，并处理多余的逗号。
只更新传入的非空字段。

4. delete (删除)

基本删除:

<delete id="deleteuserbyid" parametertype="int">
  delete from users where id = #{id}
</delete>

批量删除 (foreach):

<delete id="deleteusersbyids" parametertype="java.util.list">
  delete from users where id in
  <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</delete>

open: 循环开始前添加的字符串。
close: 循环结束后添加的字符串。

5. 其他常用标签和特性

<sql>: 定义可重用的 sql 片段。

<sql id="usercolumns">
  id, username, password, email
</sql>

<select id="selectuserbyid" parametertype="int" resulttype="user">
  select <include refid="usercolumns"/> from users where id = #{id}
</select>

#{} vs ${}:
- #{}: 预编译参数，防止 sql 注入，推荐使用。
- ${}: 字符串替换，直接将值拼接到 sql 语句中，有 sql 注入风险，谨慎使用 (例如，用于动态表名或列名)。
typealiases: 定义类型别名，简化 resulttype 和 parametertype 的书写。
<cache>: 开启二级缓存，提高查询性能。
result maps (结果映射): 处理复杂类型和关联关系的映射。