MyBatis 技巧收集

1. 循环插入

<insert id="importFromSource" parameterType="list">
    <if test="list!=null and list.size()>0">
        INSERT INTO `table` (....) VALUES
        <foreach collection="list" index="index" item="item" separator=",">
        (#{item.id}, #{item.name}, #{item.date}, ....)
        </foreach>
        ON DUPLICATE KEY UPDATE <!--重复的主键，就更新-->
            `name`=VALUES(`name`),
            `date`=VALUES(`date`),
    </if>
</insert>

2. if elseif else

<choose>
    <when test="item.updateTime==null">
        now()
    </when>
    <otherwise>
        #{item.updateTime}
    </otherwise>
</choose>

<choose>
    <when test="item.parentId==null">
        0
    </when>
    <when test="item.parentId==item.userId">
        0
    </when>
    <otherwise>
        #{item.parentId}
    </otherwise>
</choose>

3. 存入表用逗号分割，实体为数组，读写自动转换

• 创建 IntegerListTypeHandler

@MappedJdbcTypes(JdbcType.VARCHAR) // 数据库类型
@MappedTypes({List.class}) // Java 数据类型
public class IntegerListTypeHandler extends BaseTypeHandler<List<Integer>> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<Integer> parameter, JdbcType jdbcType) throws SQLException {
        if (parameter==null || parameter.size()==0) {
            ps.setString(i, "");
        }
        else {
            ps.setString(i, parameter.stream().map(String::valueOf).collect(Collectors.joining(",")));
        }
    }

    @Override
    public List<Integer> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        if (rs.wasNull()) return Collections.emptyList();
        String str = rs.getString(columnName);
        if (str==null || str.equals("")) return Collections.emptyList();
        return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
    }

    @Override
    public List<Integer> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        if (rs.wasNull()) return Collections.emptyList();
        String str = rs.getString(columnIndex);
        if (str==null || str.equals("")) return Collections.emptyList();
        return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
    }

    @Override
    public List<Integer> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        if (cs.wasNull()) return Collections.emptyList();
        String str = cs.getString(columnIndex);
        if (str==null || str.equals("")) return Collections.emptyList();
        return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
    }
}

• 配置 mapper

<resultMap id="user" type="xyz.5163.project.User">
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="rule_ids" property="ruleIds" typeHandler="xyz.5163.project.handler.IntegerListTypeHandler"/>
</resultMap>

<select id="getUsers" resultMap="user">
    SELECT * FROM `user`
</select>