1, Example

1. Alias

For a parameter, it is not necessary to declare an alias, but if the parameter involves null judgment such as if test, it is necessary to declare an alias, otherwise an error will occur
dao

Map<String,Object> getBidResult(String projectId);
String getDicName(@Param("dicCode") String dicCode1);

mapper.xml

<select id="getBidResult" parameterType="string" resultType="map">
    select * from t_bid_result a where pms_project_id = #{projectId} limit 1
</select>

<select id="getDicName" parameterType="string" resultType="string">
    select biz_dic_name
    from t_dictionary a
    <if test="dicCode != null and dicCode != ''">
        where biz_dic_code = #{dicCode} limit 1
    </if>

</select>

2,trim

The trim tag is generally used to remove redundant and keywords and commas in sql statements, or to splice suffixes such as "where", "set" and "values" before sql statements. It can be used for selective insertion, update, deletion or conditional query.

attribute	describe
prefix	Prefix to splice sql statements
suffix	Suffix for splicing sql statements
prefixOverrides	Remove the keyword or character in front of the sql statement. The keyword or character is specified by the prefixOverrides attribute. Assuming that the attribute is specified as "AND", when the beginning of the sql statement is "AND", the trim tag will remove the "AND"
suffixOverrides	Remove the keyword or character after the sql statement, which is specified by the suffixOverrides attribute, such as the comma of set

Map<String,Object> getUserInfo(Map<String,Object> map);

<select id="getUserInfo" parameterType="map" resultType="map">
    select
           a.user_id,
           a.user_name,
           a.emp_name,
           a.emp_code,
           a.email,
           a.mobile,
           a.org_id,
           (SELECT b.org_code from t_eap_sys_organization b WHERE b.ORG_ID = a.org_id) org_code,
           (SELECT b.ORG_NAME from t_eap_sys_organization b WHERE b.ORG_ID = a.org_id) org_name
    from t_sys_user a
    <trim prefix="where" prefixOverrides="AND|OR">
        <if test="user_id != null and user_id != ''"> and a.user_id = #{user_id}</if>
        <if test="emp_code != null and emp_code != ''"> and a.emp_code = #{emp_code}</if>
    </trim>
</select>

3,foreach

The attributes of foreach elements mainly include item, index, collection, open, separator and close.

• item: alias of the element in the collection during iteration. This parameter is required.
• Index: in the list and array, index is the sequence number of the element. In the map, index is the key of the element. This parameter is optional
• open: the starting symbol of foreach code, which is generally used with (and close = ")". It is often used when in(),values(). This parameter is optional
• Separator: separator between elements. For example, when in(), separator = "," will automatically separate elements with "," "to avoid sql errors caused by manually entering commas, such as in(1,2,). This parameter is optional.
• close: the closing symbol of foreach code, which is generally used with) and open = "(". It is often used when in(),values(). This parameter is optional.
• collection: when an object to be a foreach is used as an input parameter, the list object uses "list" instead of the key by default, the array object has "array" instead of the key, and the Map object has no default key. Of course, you can use @ Param("keyName") as an input parameter to set the key. After setting the keyName, the list and array will become invalid. In addition to entering parameters, there is also a time when a field is used as a parameter object. For example: if the User has the attribute list
  ids. If the input parameter is a User object, the collection = "IDS" If User has attribute IDS
  Ids; Where Ids is an object and Ids has an attribute List id; If the input parameter is a User object, then collection = "ids.id"

When using foreach, the most critical and error prone attribute is the collection attribute, which must be specified, but the value of this attribute is different in different cases. There are three main cases:

• If a single parameter is passed in and the parameter type is a list, the collection property value is list
• If a single parameter is passed in and the parameter type is an array, the attribute value of collection is array
• If there are multiple parameters passed in, we need to encapsulate them into a map. Of course, a single parameter can also be encapsulated into a map. In fact, if you pass in a parameter, it will also be encapsulated into a map in MyBatis. The key of the map is the parameter name, So at this time, the collection attribute value is the key of the passed in List or array object in its own encapsulated map

void insertContractInfo( @Param("plan")List<Map<String, Object>> plan, @Param("pay")List<Map> pay);

<insert id="insertContractInfo">
    <if test="plan != null and plan.size() > 0">
        INSERT INTO t_performplan (
        PERFORMID,
        CONTRACT_ID,
        BASENAME,
        MONEYTYPE,
        MONEYTYPESHOW,
        STAGEACCEPTANCECONDITION,
        MONEYPERCENT,
        MONEYAMOUNT,
        CURRENCY,
        CURRENCYTYPE,
        PAYMENTNATURE,
        PAYMENTNATURESHOW,
        PLANDATE
        )
        VALUES
        <foreach collection="plan" item="it" separator=",">
            (
            #{it.objectid},
            #{it.contractid},
            #{it.basename},
            #{it.moneytype},
            #{it.moneytypeshow},
            #{it.stageacceptancecondition},
            #{it.moneypercent},
            #{it.moneyamount},
            #{it.currency},
            #{it.currencytype},
            #{it.paymentnature},
            #{it.paymentnatureshow},
            #{it.plandate}
            )
        </foreach>
        ;
    </if>

    <if test="pay != null and pay.size() > 0">
        INSERT INTO t_payment (
        ID,
        PLANID,
        PAYTYPE,
        PAYTYPESHOW,
        PAYPERCENT
        )
        VALUES
        <foreach collection="pay" item="it" separator=",">
            (
            uuid(),
            #{it.planid},
            #{it.paytype},
            #{it.paytypeshow},
            #{it.paypercent}
            )
        </foreach>
        ;
    </if>
</insert>

4. If the primary key exists, it will be updated; otherwise, it will be inserted

On duplicate key update means that if the primary key already exists, the update statement will be executed, and the updated field is the parameter specified after on duplicate key update.
values(?) The following parameters are not the parameters passed by the front end, but the fields of the database.

INSERT INTO t_file (
OBJECTID,
CONTRACTID,
BASENAME,
FILETYPE,
FILEDOWNLOADURL,
FILEVIEWURL
)
VALUES
<foreach collection="file" item="it" separator=",">
    (
    #{it.objectid},
    #{it.contractid},
    #{it.basename},
    #{it.filetype},
    #{it.filedownloadurl},
    #{it.fileviewurl}
    )
</foreach>
on DUPLICATE KEY UPDATE 
    BASENAME = values(BASENAME),FILETYPE = values(FILETYPE),
    FILEDOWNLOADURL = values(FILEDOWNLOADURL),
    FILEVIEWURL = IFNULL(values(FILEVIEWURL),FILEVIEWURL);<!--  FILEVIEWURL If the passed in parameter value is empty, it will not be updated. -->

5. Time formatting

1,Oracle

Convert string to date format

SELECT to_date('2021-09-20 08:30:45', 'yyyy-MM-dd hh24:mi:ss') FROM dual;

Convert date to string

SELECT to_char(SYSDATE, 'yyyy-MM-dd hh24:mi:ss') FROM dual;

2,Mysql

Convert string to date format

SELECT DATE_FORMAT('2021-09-20 08:30:45','%Y-%m-%d %H:%i:%S'); 

Convert date to string

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'); 

6. Fuzzy query

1,Oracle

SELECT 
* 
FROM user
 WHERE
 name LIKE CONCAT('%',#{name},'%') ;
--or 
SELECT
 * 
FROM
 user
 WHERE
 name LIKE '%'||#{name}||'%';

2,Mysql

SELECT 
* 
FROM user
 WHERE
 name LIKE CONCAT('%',#{name},'%') ;