生而为人

程序员的自我修养

0%

mybatis用法总结

Posted on Edited on In

selectById

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Select({
        "select",
        "id, source_type, source_id, name, creator, user_type, count, create_mode, status, detail_status, ",
        "build_start_time, build_end_time, import_start_time, import_end_time, front_end, create_time",
        "from dim_wm_grouping_status",
        "where id = #{id,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="source_type", property="sourceType", jdbcType=JdbcType.SMALLINT),
        @Result(column="source_id", property="sourceId", jdbcType=JdbcType.BIGINT),
        @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
        @Result(column="creator", property="creator", jdbcType=JdbcType.VARCHAR),
        @Result(column="user_type", property="userType", jdbcType=JdbcType.INTEGER),
        @Result(column="count", property="count", jdbcType=JdbcType.INTEGER),
        @Result(column="create_mode", property="createMode", jdbcType=JdbcType.SMALLINT),
        @Result(column="status", property="status", jdbcType=JdbcType.VARCHAR),
        @Result(column="detail_status", property="detailStatus", jdbcType=JdbcType.VARCHAR),
        @Result(column="build_start_time", property="buildStartTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="build_end_time", property="buildEndTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="import_start_time", property="importStartTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="import_end_time", property="importEndTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="front_end", property="frontEnd", jdbcType=JdbcType.LONGVARCHAR),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP)
    })
    GroupingMsgPo selectById(Integer id);

selectByCondition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
@Select({
    "<script>",
            "select",
            "id, source_type, source_id, name, creator, user_type, count, create_mode, status, detail_status, ",
            "build_start_time, build_end_time, import_start_time, import_end_time, front_end, create_time",
            "from dim_wm_grouping_status",
            "where status != '0' and create_time between #{startDate} and #{endDate}",
            "<if test='id != null'> and id=#{id} </if>",
            "<if test='name != null'> and name like concat('%', #{name}, '%') </if>",
            "<if test='creator != null'> and creator like concat('%', #{creator}, '%') </if>",
            "<if test='userType != null and userType.size > 0' >",
                "and user_type in ",
                "<foreach collection='userType' item='item' index='index' open='(' close=')' separator=','>",
                    "${item}",
                "</foreach>",
            "</if>",
            "<if test='createMode != null and createMode.size > 0' >",
                "and create_mode in",
                "<foreach collection='createMode' item='item' index='index' open='(' close=')' separator=','>",
                    "#{item}",
                "</foreach>",
            "</if>",
            "order by id desc",
            "<if test='offset != null and pageSize != null'> limit ${offset}, ${pageSize} </if>",
    "</script>"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="source_type", property="sourceType", jdbcType=JdbcType.SMALLINT),
            @Result(column="source_id", property="sourceId", jdbcType=JdbcType.BIGINT),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="creator", property="creator", jdbcType=JdbcType.VARCHAR),
            @Result(column="user_type", property="userType", jdbcType=JdbcType.INTEGER),
            @Result(column="count", property="count", jdbcType=JdbcType.INTEGER),
            @Result(column="create_mode", property="createMode", jdbcType=JdbcType.SMALLINT),
            @Result(column="status", property="status", jdbcType=JdbcType.VARCHAR),
            @Result(column="detail_status", property="detailStatus", jdbcType=JdbcType.VARCHAR),
            @Result(column="build_start_time", property="buildStartTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="build_end_time", property="buildEndTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="import_start_time", property="importStartTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="import_end_time", property="importEndTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="front_end", property="frontEnd", jdbcType=JdbcType.LONGVARCHAR),
            @Result(column="create_time", property="createTime", jdbcType=JdbcType.TIMESTAMP)
    })
    List<GroupingMsgPo> selectByCondition(UserGroupingMetaBo userGroupingMetaBo);

分页查询的两个参数，分别是偏移量和size。偏移量需要通过页数和size计算出来。

insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Insert({
        "insert into dim_wm_grouping_status (source_type, source_id, ",
        "name, creator, user_type, ",
        "count, create_mode, ",
        "status, detail_status, ",
        "build_start_time, build_end_time, ",
        "import_start_time, import_end_time, front_end, create_time)",
        "values (#{sourceType,jdbcType=SMALLINT}, #{sourceId,jdbcType=BIGINT}, ",
        "#{name,jdbcType=VARCHAR}, #{creator,jdbcType=VARCHAR}, #{userType,jdbcType=INTEGER}, ",
        "#{count,jdbcType=INTEGER}, #{createMode,jdbcType=SMALLINT}, ",
        "#{status,jdbcType=VARCHAR}, #{detailStatus,jdbcType=VARCHAR}, ",
        "#{buildStartTime,jdbcType=TIMESTAMP}, #{buildEndTime,jdbcType=TIMESTAMP}, ",
        "#{importStartTime,jdbcType=TIMESTAMP}, #{importEndTime,jdbcType=TIMESTAMP}, ",
        "#{frontEnd,jdbcType=LONGVARCHAR}, #{createTime,jdbcType=TIMESTAMP})"
    })
    @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="id", before=false, resultType=Integer.class)
    int insert(GroupingMsgPo record);

batchInsert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Insert({
    "<script>",
        "insert into dim_wm_grouping_status (source_type, source_id, ",
        "name, creator, ",
        "user_type, count, ",
        "create_mode, status, ",
        "detail_status, build_start_time, ",
        "build_end_time, import_start_time, ",
        "import_end_time, front_end, create_time)",
        "values",
        "<foreach collection='list' item='item' index='index' separator=','>",
            " (#{item.sourceType,jdbcType=SMALLINT}, #{item.sourceId,jdbcType=BIGINT}, ",
            "#{item.name,jdbcType=VARCHAR}, #{item.creator,jdbcType=VARCHAR}, ",
            "#{item.userType,jdbcType=INTEGER}, #{item.count,jdbcType=INTEGER}, ",
            "#{item.createMode,jdbcType=SMALLINT}, #{item.status,jdbcType=VARCHAR}, ",
            "#{item.detailStatus,jdbcType=VARCHAR}, #{item.buildStartTime,jdbcType=TIMESTAMP}, ",
            "#{item.buildEndTime,jdbcType=TIMESTAMP}, #{item.importStartTime,jdbcType=TIMESTAMP}, ",
            "#{item.importEndTime,jdbcType=TIMESTAMP}, #{item.frontEnd,jdbcType=LONGVARCHAR},",
            "#{item.createTime,jdbcType=TIMESTAMP})",
        "</foreach>",
    "</script>",
    })
    @Options(useGeneratedKeys=true,keyProperty="id")
    int batchInsert(@Param("list") List<GroupingMsgPo> list);

update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Update({
    "<script>",
        "update dim_wm_grouping_status",
        "set id = #{id,jdbcType=INTEGER}",
            "<if test='sourceType != null'> ,source_type = #{sourceType,jdbcType=SMALLINT}</if>",
            "<if test='sourceId != null'> ,source_id = #{sourceId,jdbcType=BIGINT} </if>",
            "<if test='name != null'> ,name = #{name,jdbcType=VARCHAR} </if>",
            "<if test='creator != null'> ,creator = #{creator,jdbcType=VARCHAR} </if>",
            "<if test='userType != null'> ,user_type = #{userType,jdbcType=INTEGER} </if>",
            "<if test='count != null'> ,count = #{count,jdbcType=INTEGER} </if>",
            "<if test='createMode != null'> ,create_mode = #{createMode,jdbcType=SMALLINT} </if>",
            "<if test='status != null'> ,status = #{status,jdbcType=VARCHAR} </if>",
            "<if test='detailStatus != null'> ,detail_status = #{detailStatus,jdbcType=VARCHAR} </if>",
            "<if test='buildStartTime != null'> ,build_start_time = #{buildStartTime,jdbcType=TIMESTAMP} </if>",
            "<if test='buildEndTime != null'> ,build_end_time = #{buildEndTime,jdbcType=TIMESTAMP} </if>",
            "<if test='importStartTime != null'> ,import_start_time = #{importStartTime,jdbcType=TIMESTAMP} </if>",
            "<if test='importEndTime != null'> ,import_end_time = #{importEndTime,jdbcType=TIMESTAMP} </if>",
            "<if test='frontEnd != null'> ,front_end = #{frontEnd,jdbcType=LONGVARCHAR} </if>",
            "<if test='createTime != null'> ,create_time = #{createTime,jdbcType=TIMESTAMP} </if>",
        "where id = #{id,jdbcType=INTEGER}",
    "</script>"
    })
    int updateById(GroupingMsgPo record);

用法对比

jdbcType=”DATE” 和 jdbcType=”TIMESTAMP” 两种类型的区别

  1. DATE只有年月日，没有时分秒