mybatis的常用操作

官方网址
下面记录一些常用的操作
1、配置文件

<settings>
        <!--使用jdbc的getGeneratekeys获取自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--开启驼峰命名转换Table:create_time到 Entity(createTime)-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--结果有Null，同样返回-->
        <setting name="callSettersOnNulls" value="true"/>
</settings>

lazyLoadingEnabled：懒加载，对于有子查询来说，会根据具体查询情况判断需不需要执行

2、分页插件
https://github.com/miemiedev/mybatis-paginator
https://github.com/pagehelper/Mybatis-PageHelper

3、批量查询

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

4、条件查询

<where>
            <if test="param != null" >


            </if>
</where>

5、时间查询

        <if test="beginTime!= null" >
            and date_format(create_time,'%Y-%m-%d') &gt;=#{beginTime,jdbcType=DATE}
        </if>
        <if test="endTime!= null" >
            and date_format(create_time,'%Y-%m-%d') &lt;=#{endTime,jdbcType=DATE}
        </if>

6、批量插入

    <insert id="insertBatch" parameterType="java.util.List">  
        insert into user(name, age,create_time)
        values 
        <foreach collection="list" index="index" item="item" separator=",">  
        (
        #{item.name,jdbcType=VARCHAR}, 
        #{item.age,jdbcType=VARCHAR},  
        #{item.createTime,jdbcType=DATE}
        )
        </foreach>
    </insert>

7、插入后返回id

useGeneratedKeys="true" keyProperty="id"//此处为bean里的值

8、不存在插入，存在更新

<insert id="insertOrUpdate" parameterType="com.model.Model" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="name!= null" >
        name,
      </if>
      <if test="age!= null" >
        age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="name!= null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="age!= null" >
        #{age,jdbcType=VARCHAR},
      </if>
    </trim>
    ON DUPLICATE KEY UPDATE
    <trim suffixOverrides="," >
      <if test="name!= null" >
        name= #{name,jdbcType=VARCHAR},
      </if>
      <if test="age!= null" >
        age= #{age,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>