Mybatis之@Select注解

@Select注解基本用法
@Select注解的目的是为了取代xml中的select标签，只作用于方法上面。
抛弃了传统的xml形式

例如（简单的sql）

public interface UserMapper {
    @Select("SELECT id, name, age FROM user WHERE id = #{userId}")
    User getUserById(int userId);
}
1
2
3
4

如果要想实现复杂的逻辑判断（比如if判断、for循环），则必须需要使用<script>标签，如下所示：
但是在<script> 和</script>内不能用>、<=、<、<=等判断语句要用gt;和lt;等转义字符，
具体字符自行百度查看。
以下使用的@Results是为了做出参的映射（xml形式里面也是有这个操作的）

//查询的例子
@Select({"<script>",
            "SELECT T.*," +
                    "        T2.HOLDER_TYPE," +
                    "        T2.HOLDER_ID_TYPE," +
                    "        T2.HOLDER_ID," +
                    "        T2.HOLDER_NAME," +
                    "        T2.LEGAL_REPRESENTATIVE," +
                    "        T2.CONTACT_ID," +
                    "        T2.CONTACT_NAME," +
                    "        T2.CONTACT_PHONE" +
                    "        FROM MATERIAL_INSTANCE T LEFT JOIN MATERIAL_HOLDER T2 ON (T.HOLDER_UNID=T2.UNID)" +
                    "WHERE DELETE_FLAG = 'N'" +
                    "<if test='unid != null and unid !=\"\" '>" +
                    "and T.UNID=#{unid}" +
                    "</if>" +
                    "<if test='unids != null and unids !=\"\" '>" +
                    "and T.UNID IN" +
                    "<foreach collection='unids' item='unid' open='(' separator=',' close=')'> " +
                    "'#{unid}'" +
                    "</foreach>" +
                    "</if>" +
                    "<if test='materialName != null and materialName!=\"\" '>" +
                    "and T.MATERIAL_NAME LIKE CONCAT('%',#{materialName},'%')" +
                    "</if>" +
                    "<if test='licenseNum != null and licenseNum!=\"\" '>" +
                    "and T1.CONTACT_ID=#{licenseNum}" +
                    "</if>" +
                    "<if test='projId != null and projId !=\"\" '>" +
                    "and T.PROJ_ID LIKE CONCAT('%',#{projId},'%')" +
                    "</if>" +
                    "<if test='bizFromSystem != null and bizFromSystem!=\"\" '>" +
                    "and T.BIZ_FROM_SYSTEM=#{bizFromSystem}" +
                    "</if>" +
                    "<if test='catalogCode != null and catalogCode!=\"\" '>" +
                    "and T.CATALOG_CODE LIKE CONCAT('%',#{catalogCode},'%')" +
                    "</if>" +
                    "<if test='materialUnid != null and materialUnid!=\"\" '>" +
                    "and T.MATERIAL_UNID=#{materialUnid}" +
                    "</if>" +
                    "<if test='holderId != null and holderId!=\"\" '>" +
                    "and T2.HOLDER_ID like CONCAT('%',#{holderId},'%')" +
                    "</if>" +
                    "<if test='level != null and level!=\"\" '>" +
                    "and T.LEVEL=#{level}" +
                    "</if>" +
                    "<if test='labels != null and labels!=\"\" '>" +
                    "and T.LABELS=#{labels}" +
                    "</if>" +
                    "<if test='holderName != null and holderName!=\"\" '>" +
                    "and T2.HOLDER_NAME LIKE CONCAT('%',#{holderName},'%')" +
                    "</if>" +
                    "<if test='catalogName != null and catalogName!=\"\" '>" +
                    "and T.CATALOG_NAME LIKE CONCAT('%',#{catalogName},'%')" +
                    "</if>" +
                    "ORDER BY T.CREATE_TIME DESC" +
                    "<if test='limitStartNum != null'>" +
                    "limit ${limitStartNum}, ${countPerPage}" +
                    "</if>",
            "</script>"})
    @Results({
            @Result(column = "UNID", property = "unid"),
            @Result(column = "HOLDER_UNID", property = "holderUnid"),
            @Result(column = "CATALOG_CODE", property = "catalogCode"),
            @Result(column = "CATALOG_NAME", property = "catalogName"),
            @Result(column = "PROJ_ID", property = "projId"),
            @Result(column = "SERVICE_UNID", property = "serviceUnid"),
            @Result(column = "SERVICE_CODE", property = "serviceCode"),
            @Result(column = "MATERIAL_SRC_TYPE", property = "materialSrcType"),
            @Result(column = "MATERIAL_NAME", property = "materialName"),
            @Result(column = "MATERIAL_TYPE", property = "materialType"),
            @Result(column = "VALID_TERM", property = "validTerm"),
            @Result(column = "FILE_UNID", property = "fileUnid"),
            @Result(column = "STATUS", property = "status"),
            @Result(column = "BIZ_FROM_SYSTEM_CODE", property = "bizFromSystemCode"),
            @Result(column = "BIZ_FROM_SYSTEM", property = "bizFromSystem"),
            @Result(column = "BIZ_AREA", property = "bizArea"),
            @Result(column = "RESOURCE_DIR_CODE", property = "resourceDirCode"),
            @Result(column = "APPLY_ROLE", property = "applyRole"),
            @Result(column = "REUSE_ENABLE_FLAG", property = "reuseEnableFlag"),
            @Result(column = "MATERIAL_UNID", property = "materialUnid"),
            @Result(column = "INSTANCE_MATERIAL_UNID", property = "instanceMaterialUnid"),
            @Result(column = "HOLDER_TYPE", property = "holderType"),
            @Result(column = "HOLDER_ID_TYPE", property = "holderIdType"),
            @Result(column = "HOLDER_ID", property = "holderId"),
            @Result(column = "HOLDER_NAME", property = "holderName"),
            @Result(column = "LEGAL_REPRESENTATIVE", property = "legalRepresentative"),
            @Result(column = "CONTACT_ID", property = "contactId"),
            @Result(column = "CONTACT_NAME", property = "contactName"),
            @Result(column = "CONTACT_PHONE", property = "contactPhone"),
            @Result(column = "LEVEL", property = "level"),
            @Result(column = "labels", property = "labels"),
            @Result(column = "CREATE_TIME", property = "createTime"),
            @Result(column = "RECEIVE_TIME", property = "receiveTime"),
            @Result(column = "SERVICE_NAME", property = "serviceName"),
            @Result(column = "MTC_SOURCE_ID", property = "mtcSourceId"),
            @Result(column = "MATERIAL_FORM", property = "materialForm"),
            @Result(column = "LABELS", property = "labels"),
            @Result(column = "EXPAND1", property = "expand1"),
            @Result(column = "EXPAND2", property = "expand2"),
            @Result(column = "EXPAND3", property = "expand3"),
            @Result(column = "EXPAND4", property = "expand4"),
            @Result(column = "EXPAND5", property = "expand5"),
    })
    List<MaterialInstanceCfgFoshanDO> queryForConfig(Map<String, Object> params);
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

//插入的例子
@Insert({"<script>",
            "insert into material_instance (" +
                    "          UNID," +
                    "          HOLDER_UNID," +
                    "          CATALOG_CODE," +
                    "          CATALOG_NAME," +
                    "          PROJ_ID," +
                    "          SERVICE_UNID," +
                    "          SERVICE_CODE," +
                    "          MATERIAL_SRC_TYPE," +
                    "          MATERIAL_NAME," +
                    "          MATERIAL_TYPE," +
                    "          VALID_TERM," +
                    "          FILE_UNID," +
                    "          STATUS," +
                    "          BIZ_FROM_SYSTEM," +
                    "          BIZ_AREA," +
                    "          RESOURCE_DIR_CODE," +
                    "          APPLY_ROLE," +
                    "          REUSE_ENABLE_FLAG," +
                    "          CREATE_TIME," +
                    "          MATERIAL_UNID," +
                    "        INSTANCE_MATERIAL_UNID," +
                    "        MODIFY_TIME," +
                    "        FINISH_DATE," +
                    "        LEVEL," +
                    "        FROM_FLAG," +
                    "        RECEIVE_TIME," +
                    "        SERVICE_NAME," +
                    "        MTC_SOURCE_ID," +
                    "        HOLDER_ID," +
                    "        VERSION," +
                    "        MATERIAL_FORM," +
                    "        LABELS," +
                    "  EXPAND1," +
                    "  EXPAND2," +
                    "  EXPAND3," +
                    "  EXPAND4," +
                    "  EXPAND5" +
                    ")" +
                    "    values (" +
                    "  #{params.unid,jdbcType=VARCHAR}," +
                    "  #{params.holderUnid,jdbcType=VARCHAR}," +
                    "  #{params.catalogCode,jdbcType=VARCHAR}," +
                    "  #{params.catalogName,jdbcType=VARCHAR}," +
                    "  #{params.projId,jdbcType=VARCHAR}," +
                    "  #{params.serviceUnid,jdbcType=VARCHAR}," +
                    "  #{params.serviceCode,jdbcType=VARCHAR}," +
                    "  #{params.materialSrcType,jdbcType=VARCHAR}," +
                    "  #{params.materialName,jdbcType=VARCHAR}," +
                    "  #{params.materialType,jdbcType=CHAR}," +
                    "  #{params.validTerm,jdbcType=INTEGER}," +
                    "  #{params.fileUnid,jdbcType=LONGVARCHAR}," +
                    "  #{params.status,jdbcType=CHAR}," +
                    "  #{params.bizFromSystem,jdbcType=VARCHAR}," +
                    "  #{params.bizArea,jdbcType=VARCHAR}," +
                    "  #{params.resourceDirCode,jdbcType=VARCHAR}," +
                    "  #{params.applyRole,jdbcType=CHAR}," +
                    "  #{params.reuseEnableFlag,jdbcType=CHAR}," +
                    "          #{params.createTime,jdbcType=VARCHAR}," +
                    "          #{params.materialUnid,jdbcType=VARCHAR}," +
                    "          #{params.instanceMaterialUnid,jdbcType=VARCHAR}," +
                    "          #{params.modifyTime,jdbcType=VARCHAR}," +
                    "          #{params.finishDate,jdbcType=VARCHAR}," +
                    "          #{params.level,jdbcType=INTEGER}," +
                    "          #{params.fromFlag,jdbcType=VARCHAR}," +
                    "          #{params.receiveTime,jdbcType=VARCHAR}," +
                    "          #{params.serviceName,jdbcType=VARCHAR}," +
                    "          #{params.mtcSourceId,jdbcType=VARCHAR}," +
                    "          #{params.holderId,jdbcType=VARCHAR}," +
                    "          #{params.version,jdbcType=INTEGER}," +
                    "          #{params.materialForm,jdbcType=VARCHAR}," +
                    "          #{params.labels,jdbcType=VARCHAR}," +
                    "          #{params.expand1,jdbcType=VARCHAR}," +
                    "          #{params.expand2,jdbcType=VARCHAR}," +
                    "          #{params.expand3,jdbcType=VARCHAR}," +
                    "          #{params.expand4,jdbcType=VARCHAR}," +
                    "          #{params.expand5,jdbcType=VARCHAR}" +
                    "    )",
            "</script>"})
    int insertInstance(@Param("params") MaterialInstanceFoshanDO params);
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

参考
【1】
https://blog.csdn.net/qq_23126581/article/details/127995866?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_baidulandingword~default-4-127995866-blog-106617681.235^v39pc_relevant_yljh&spm=1001.2101.3001.4242.3&utm_relevant_index=7
【2】
https://blog.csdn.net/qq_34134299/article/details/117651500?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-117651500-blog-124945028.235%5Ev39%5Epc_relevant_yljh&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1-117651500-blog-124945028.235%5Ev39%5Epc_relevant_yljh&utm_relevant_index=2