mybatis实战

配置

自动下划线驼峰

使用mybatis的自动下划线驼峰转换
mybatis有一个选项叫mapUnderscoreToCamelCase，当表中的字段名与对象的属性名相同只是下划线和驼峰写法的差异时适用。

配置了mapUnderscoreToCamelCase之后mybatis在将ResultSet查出的数据设置到对象的时候会尝试先将下划线转换为驼峰然后前面拼接set去设置属性。

然后查询：

/**

• 根据ID查询，开了自动驼峰转换
• @param id
• @return
  */
  @Select(“SELECT * FROM t_user WHERE id=#{id}”)
  User loadByIdAutoAlias(Long id);

查看打印的结果，birth_day属性填充到了对象中：

MyBatis解析的SQL和实际传参不符的问题

传参是整型，结果是false

传参is_finished =1 但是查询确实false

==>  Preparing: select RES.* from item RES  where RES.project_id = ? and RES.is_finished = ? and RES.is_qualified = ? order by RES.plan_end_time DESC limit ?, ?
==> Parameters: 1586971605296447490(String), 1(Integer), false(Boolean), 0(Integer), 10(Integer)

参考：记一次MyBatis解析的SQL和实际传参不符的问题

原因是后面排序判断的时候少了一个等号
错误写法

<choose>
            <when test="isFinished = null or isFinished == 0">
                order by RES.plan_start_time asc
            </when>
            <otherwise>
                order by RES.plan_end_time DESC
            </otherwise>
        </choose>

会导致这个地方计算的结果 isFinished =false

正确写法应该是：isFinished == null

<choose>
            <when test="isFinished == null or isFinished == 0">
                order by RES.plan_start_time asc
            </when>
            <otherwise>
                order by RES.plan_end_time DESC
            </otherwise>
        </choose>

日期比较

原文链接：https://blog.csdn.net/afkbcgz/article/details/88057869

入参是字符串

@Param("startTime") String startTime, @Param("endTime") String endTime);

xml映射

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= str_to_date( #{startTime},'%Y-%m-%d %H:%i:%s')

AND create_time < str_to_date( #{endTime},'%Y-%m-%d %H:%i:%s')

]]>

入参是Date

@Param("startTime") Date startTime, @Param("endTime") Date endTime);

xml映射

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= #{startTime,jdbcType=TIMESTAMP}

AND create_time < #{endTime,jdbcType=TIMESTAMP}

]]>

父子递归查询

原文链接：https://blog.csdn.net/m0_67402564/article/details/123987858

如果表里有一列是设置父级ID，那么我们可以根据这个父级ID进行递归，最后成为返回父子集合的list。


下面是贴上mapper的代码，我们最后只需要调用getAllMenu，就可以返回List<Menu>

<resultMap type="com.example.demo.entity.Menu" id="menuTree">
    <id column="menuId" property="menuId"/>
    <result column="menuName" property="menuName"/>
    <result column="menuAddr" property="menuAddr"/>
    <result column="menuSts" property="menuSts"/>
    <result column="menuSort" property="menuSort"/>
    <result column="menuImg" property="menuImg"/>
    <collection property="children" ofType="com.example.demo.entity.Menu" column="menuId" select="getMenuChildren"/>
</resultMap>
<!-- 先查询菜单根级目录 -->
<!-- 这里的返回结果必须为resultMap，并且值为上面构建的resultMap的id的值 -->
<select id="getAllMenu" resultMap="menuTree">
    SELECT m1.menuId,m1.menuName,m1.menuAddr,m1.menuSts,m1.menuSort,m1.menuImg
    FROM
    menu m1
    WHERE m1.supMenuId=0
</select>

<!-- 再利用上次查询结果colliection中column的值cid做递归查询，查出所有子菜单 -->
<!-- 这里的返回结果必须为resultMap，并且值为上面构建的resultMap的id的值 -->
<select id="getMenuChildren" resultMap="menuTree">
    SELECT m2.menuId,m2.menuName,m2.menuAddr,m2.menuSts,m2.menuSort,m2.menuImg
    FROM menu m2
    WHERE m2.supMenuId=#{menuId}
</select>

上下级查询

方法一

参考：https://blog.csdn.net/qq853632587/article/details/118901263

<resultMap id="OrderResultMap" type="com.gxu.construction.business.order.dto.ImproveOrderDTO">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="level" jdbcType="INTEGER" property="level"/>
        <result column="title" jdbcType="VARCHAR" property="title"/>
        <result column="improver_name" jdbcType="VARCHAR" property="improverName"/>
        <result column="require_improve_time" jdbcType="TIMESTAMP" property="requireImproveTime"/>
        <result column="actual_improve_time" jdbcType="TIMESTAMP" property="actualImproveTime"/>
        <result column="remark" jdbcType="VARCHAR" property="reviewRemark"/>
        <collection property="files" ofType="com.gxu.construction.file.dto.FileDTO" column="id" select="queryOrderFilesList"/>

    </resultMap>

    <!-- 批量获取整改工单信息 -->
    <select id="selectListByIds" resultMap="OrderResultMap" >
        select id,title,level,improver_name,require_improve_time,actual_improve_time,remark
        from improve_order RES where id in
        <foreach collection="orderIds" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <!--级联查询订单项信息-->
    <select id="queryOrderFilesList" resultType="com.gxu.construction.file.dto.FileDTO" parameterType="java.lang.String">
        select id,file_id as fileId,file_key as fileKey from  file_record where business_key = #{id}
    </select>

方法二

原文链接：https://blog.csdn.net/tian_yu_2012/article/details/123271498

public class EntityA {
	private Integer a;
	private Integer ad;
}

public class EntityB{
	private Integer b;
	private Integer aId;
}

public class EntityAVo {
	private Integer a;
	private Integer ad;
	private List<EntityB> alist;
}

<resultMap id="aMap" type="EntityAVo">
	<result property="a" column="a"/>
	<result property="ad" column="ad"/>
	<collection property="alist" ofType="EntityB">
		<result column="b" property="b"/>
		<result column="a_id" property="aId"/>
	</collection>
</resultMap>

<select id="test" resultMap="aMap">
	SELECT 
		A.a,
		A.ad,
		B.b,
		B.a_id
	FROM
	A
	LEFT JOIN B ON B.a_id = A.a
</select>

传参数组

in

参考：https://blog.csdn.net/menghuanzhiming/article/details/79206068

    int deleteByFormIds(@Param("list") List<Integer> ids);

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

mapper中接口注解映射

https://www.bbsmax.com/A/nAJv76385r/

/**
     * 插入记录，手动分配主键
     *
     * @param user
     * @return
     */
    @Insert("INSERT INTO t_user (id, username, passwd) VALUES (#{id}, #{username}, #{passwd})")
    int addUserAssignKey(User user);

 /**
     * 插入记录，数据库生成主键
     *
     * @param user
     * @return
     */
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("INSERT INTO t_user (username, passwd) VALUES (#{username}, #{passwd})")
    int addUserGeneratedKey(User user);

/**
     * 插入记录，选择主键
     *
     * @param user
     * @return
     */
    @Insert("INSERT INTO t_user (username, passwd) VALUES (#{username}, #{passwd})")
    @SelectKey(statement = "SELECT UNIX_TIMESTAMP(NOW())", keyColumn = "id", keyProperty = "id", resultType = Long.class, before = true)
    int addUserSelectKey(User user);

/**
     * 删除记录
     *
     * @param id
     * @return
     */
    @Delete("DELETE FROM t_user WHERE id=#{id}")
    int delete(Long id);

/**
     * 修改记录
     *
     * @param user
     * @return
     */
    @Update("UPDATE t_user SET username=#{username}, passwd=#{passwd} WHERE id=#{id}")
    int update(User user);


/**
     *  根据ID查询，开了自动驼峰转换
     *
     * @param id
     * @return
     */
    @Select("SELECT * FROM t_user WHERE id=#{id}")
    User loadByIdAutoAlias(Long id);


/**
     * 使用ResultMap
     *
     * @param id
     * @return
     */
    @Results(id = "userMap", value = {
            @Result(id=true, column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "passwd", property = "passwd"),
            @Result(column = "birth_day", property = "birthDay")
    })
    @Select("SELECT * FROM t_user WHERE id=#{id}")
    User loadByIdResultMap(Long id);

/**
     * 引用其他的Result
     *
     * @param id
     * @return
     */
    @ResultMap("userMap")
    @Select("SELECT * FROM t_user WHERE id=#{id}")
    User loadByIdResultMapReference(Long id);