Mybatis SQL语句编写

1.插入多条数据并获取主键

一个非常简单的插入sql
<code>insert into table (aa,bb,cc) values(xx,xx,xx),(yy,yy,yy)<code>
在Mapper中写入对应的sql模板

   <insert id="insertUser" keyProperty="id" useGeneratedKeys="id">  
   insert into xxxx (xx) value (xxx)
   </insert>

2.批量插入数据 并返回主键

    <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true">
        <selectKey resultType="long" keyProperty="id" order="AFTER">
            SELECT
            LAST_INSERT_ID()
        </selectKey>
        insert into table 
        (aa, bb,cc)
        values
        <foreach collection="list" item="item" index="index"
            separator=",">
            (  #{item.aa},#{item.bb},#{item.cc}  )
        </foreach>
    </insert>

3.批量执行多条sql

首先数据库连接字符串需要加上如下参数,允许一次提交多条sql
allowMultiQueries=true

<update id="batchUpdate" parameterType="java.util.Map">  
    <!-- 接收list参数,循环着组装sql语句,注意for循环的写法  
         separator=";" 代表着每次循环完,在sql后面放一个分号  
         item="cus" 循环List的每条的结果集  
         collection="list" list 即为 map传过来的参数key -->  
    <foreach collection="list" separator=";" item="cus">  
        update t_customer set  
        c_name = #{cus.name},  
        c_age = #{cus.age},  
        c_sex = #{cus.sex},  
        c_ceroNo = #{cus.ceroNo},  
        c_ceroType = #{cus.ceroType}  
        where id = #{cus.id}  
    </foreach>  
</update> 

4.通过 resultMap 获取一个包含一个子属性是javabean的查询结果List

<resultMap id="studentResultMap" type="com.text.Student">
<id property="id" column="id">
<result property="name" column="name" />
<result property="age" column="age" />
<association proprety="clazz" column="clazz_id" javaType="com.test.clazz" select="selectClazzWithId" />
</resultMap>

<select id="selectStudent" resultMap="StudentResultMap">
select *  from student
</select>

<select id ="selectClazzWithId" resultType="com.test.clazz">
select * from clazz where  id = #{id}
</select>

5.通过 resultMap 获取一个包含一个子属性是List的查询结果List

<resultMap id="clazzResultMap" type="com.text.Clazz">
<id property="id" column="id">
<result property="code" column="code" />
<collection properety="students" javaType="ArrayList" column="id" ofType="com.test.Student" select="selectStudentWithId">
</resultMap>

<select id="selectStudentWithId" >
select *  from student where clazz_id = #{id}
</select>

<select id ="selectClazz" resultType="com.test.clazz" resultMap="clazzResultMap">
select * from clazz where  
</select>

6.一对多查询中使用懒加载模式

1.sql 属性 添加 fetchType="lazy"
2.mybatis设置开启懒加载

7.动态创建sql

语法: if choose(when otherwise) set foreach bind


if

:条件判断语句 一般作为where子句的一部分
test的值为一个表达式 多个逻辑判断使用and连接

 
<select id="saveOldPassword" parameterType="Map">
 UPDATE user_account SET `password` = #{password}
 <if test="id != null">
  WHERE id = #{id}
 </if>
</select>

choose(when otherwise):

choose,when,otherwise组合使用,达到使用switch的效果

<select id="findUserInfoByOneParam" parameterType="Map" resultMap="UserInfoResult">  
        select * from userinfo   
       <choose>  
           <when test="searchBy=='department'">  
                where department=#{department}  
           </when>  
           <when test="searchBy=='position'">  
                where position=#{position}  
           </when>  
           <otherwise>  
                where gender=#{gender}  
            </otherwise>  
       </choose>  
    </select>   

where:

where元素主要解决多个if判断内包含where条件的问题,加入第一个if的where条件不成立,则之后条件成立的if语句内的sql会多一个and,因为它以及变整个sql上的第一个where条件了。

例如:

  select *  from user  where
    <if test="id ! = null">
     id = #{id}
    </if>
   <if test="name != null">
     and name = #{name}
   </if>

假如id值为null,name则变成 select * from user where and name = #{name}

现在改成如下代码,则在id为null的情况下,会自动过滤第二个条件中的and

select *  from user  where
 <where>
    <if test="id ! = null">
     id = #{id}
    </if>
   <if test="name != null">
     and name = #{name}
   </if>
 </where>

set:

set用于update语句,在update语法中,有个set关键词用于设置值,比如 update users set name = 'new name' where id = #{id}
我们发现更新的字段和更新的值都是一一对应的,假如我的sql并不确定要更新那些列,需要根据传入的参数,如果值不为null则更新。这个是要就可以使用set元素。

      update user
        <set>
            <if test="name">name = #{name}
            <if test="age">name = #{gae}
            <if test="sex">name = #{sex}
            <if test="class">name = #{class}
        </set>
        where id = #{id}

foreach:

foreach 主要用于遍历一个集合。
collection表示要遍历的元素 index 表示索引 item 表示每个子元素 open close 表示 首位 用什么字符串包括,separator表示分隔符

 select * from user where id in
<foreach collection="ids" index="index" item="id" open="(" close=")" separator=",">
#{id}
</foreach>

bind:

在sql定义内部定义一个临时变量

 
<select>
<bind name="parrern" value="'%'+ _parameter.getName()+'%'">
select * from user where name like #{pattern}
</select>

trim:

trim配合set或者where完成格式化,主要解决多个动态列之间的关联问题
因为使用if条件之后并不确定最终那些字段是开头,那个字段是末尾。可能导致开头会多个and 末尾多个逗号。
trim的属性使用prefix设置起始字符串,suffix设定末尾字符串 prefixoverride指定需要消除的多余的起始字符串,suffixoverride指定需要消除的多余的末尾字符串

 <trim prefix="values (" suffix=")" suffixOverrides="," >
          #{id, jdbcType=VARCHAR},
          #{memberId, jdbcType=VARCHAR},
          <if test='messageClassify != null and messageClassify != "" '>
            #{messageClassify, jdbcType=VARCHAR},
          </if>
          <if test='messageCode != null and messageCode != "" '>
            #{messageCode, jdbcType=VARCHAR},
          </if>
          <if test='messageContent != null and messageContent != "" '>
            #{messageContent, jdbcType=VARCHAR},
          </if>
 </trim>  

8.使用Provider注解调用自定义的sql构建方法

mybatis中具体某个查询的定义可以写在一个mapper中,也可以写在查询定义接口的注解上。
如果在mapper中,则可以使用 foreach when之类的语法动态构建SQL。
而使用注解定义SQL的话,则可以使用@SelectProvider @UpdateProvider等注解自定义动态SQL的构建方法。
相应的属性使用@Options定义。Provider类一般命名为【***DynaSqlProvider】

例如:

  @SelectProvider(type = UrlInterceptor.class,method = "getList")
  List getList();

  public String getList() {
       return new SQL() {{
           SELECT("*");
           FROM("table_name");
           WHERE("status = 1");
       }}.toString();
   }

在SQL类中直接使用了实例初始化块构建了一个SQL,这也是一种比较常见的类初始化方式。在SQL类中,提供了 SELECT FROM JOIN LEFT_JOIN OR AND 等语法。基本和sql的语法一致。
使用了Provider之后,我们可以通过java代码实现动态SQL构建,而不是一定要在mapper中定义。