Mybatis tricks

相关资源

• mybatis products
• mybatis 源码中文注释
• mybatis 源码中文注释并附带图解架构

获取在sql中生成的主键

insert方法只会返回void或int，如果数据的id是在insert标签中使用sql生成的，而后续代码需要用到这个id，则可以使用selectKey语句生成id，这样就会将该id存入入参的相应字段中，后续使用可直接从该对象中获取id。如：

    <insert id="insertLoginRecord" parameterType="Login">        <selectKey keyProperty="id" resultType="String" order="BEFORE">            SELECT to_char(SYSDATE,'yyyyMMddHH24miss') || lpad(round(DBMS_RANDOM.value()*1000000,0),6,'0') FROM dual        </selectKey>        INSERT INTO t_pub_sys_login (            id,            username,            login_time,            ip,            port        ) VALUES (            #{id},            #{username},            SYSDATE,            #{ip},            #{port}        )    </insert>

参考：Get the id of last inserted record in mybatis

like语句用法

r.role_name LIKE '%'||#{roleObj.rolename}||'%'

批量插入

使用 foreach 语句，如：

<insert id="insertAccountRoleRelBatch" parameterType="Map">    INSERT ALL    <foreach collection="roleIdArr" item="roleId" index="index" >        INTO t_pub_sys_account_role(            account_id,            role_id        ) VALUES (            #{accountId},            #{roleId}        )    </foreach>    SELECT * FROM DUAL</insert>

<insert id="insertAccountRoleRelBatch" parameterType="Map">    INSERT INTO t_pub_sys_account_role (        account_id,        role_id    ) VALUES         <foreach collection="roleIdArr" item="roleId" index="index" open="(" separator="),("  close=")">            #{accountId},            #{roleId}        </foreach></insert>

调用存储过程

如果需要返回值，返回值也需要作为参数传入

String accountIds = "(";for (String accountId : accountIdArr) {    accountIds = accountIds + "'" + accountId + "'" + ",";}accountIds = accountIds.substring(0, accountIds.length() - 1) + ")";paramMap.put("accountIds", accountIds);paramMap.put("rowCount", rowCount);accountMapper.deleteAccount(paramMap);rowCount = (int)paramMap.get("rowCount");

<delete id="deleteAccount" parameterType="Map" statementType="CALLABLE" >    <![CDATA[        {call pkg_pub_system.account_delete(            #{accountIds, mode=IN, jdbcType=VARCHAR, javaType=java.lang.String},            #{rowCount, mode=OUT, jdbcType=INTEGER, javaType=java.lang.Integer})}    ]]></delete>

参考：
1. Calling Oracle stored procedures with MyBatis
2. MyBatis调用存储过程