Mybatis sql构造

项目场景：

简单记录一些Mybatis构造的常用sql写法。

---

1.数据插入通过map传参，灵活插入表格数据

<insert id="mapInsertTemplate">
        insert into  ${TableName}
        <foreach collection="params.keys" item="key" open="(" close=")"
                 separator=",">
            ${key}
        </foreach>
        values
        <foreach collection="params.values" item="value" open="("
                 close=")" separator=",">
            #{value}
        </foreach>
    </insert>

---

2.数据插入通过map传参并返回id

<insert id="mapInsertTemplate"   parameterType="java.util.Map"  useGeneratedKeys="true" keyProperty="params.ID" keyColumn="params.ID" >
        <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="params.ID" >
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into  ${TableName}
        <foreach collection="params.keys" item="key" open="(" close=")"
                 separator=",">
            ${key}
        </foreach>
        values
        <foreach collection="params.values" item="value" open="("
                 close=")" separator=",">
            #{value}
        </foreach>

    </insert>

---

3.数据更新通过map传参

<update id="mapUpdateTemplate" parameterType="java.util.Map">
        UPDATE  ${TableName}
        SET
        <foreach item="value" index="key" collection="params" separator=",">
            <if test="key != 'ID'">
                ${key} = #{value}
            </if>
        </foreach>
        WHERE
        <foreach item="value" index="key" collection="params" separator=",">
            <if test="key == 'ID'">
                ID = #{value}
            </if>
        </foreach>
    </update>

---

4. 构造where条件

mapper接口层

List<Map> selectMapsPage(@Param(Constants.WRAPPER) Wrapper query, @Param("tableName") String tableName);

mapper实现层

<select id="selectMapsPage" resultType="java.util.Map" parameterType="java.util.Map">
        SELECT *
        FROM ${tableName}
        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>
    </select>

---

5.构造where条件和sql语句where条件混用1

mapper接口层

    /**
     * 根据自定义sql查询
     * @param query
     * @param tableName
     * @param fields
     * @param joinCustomSqlSql
     * @param orderBySql
     * @return
     */
    List<Map> selectListByCustomSql(@Param(Constants.WRAPPER) Wrapper query, @Param("tableName") String tableName, @Param("fields") String fields, @Param("joinCustomSqlSql") String joinCustomSqlSql, @Param("orderBySql") String orderBySql);

mapper接口实现层

   <select id="selectListByCustomSql" resultType="java.util.Map" parameterType="java.util.Map">
        SELECT ${fields}
        FROM ${tableName}
        <if test="ew.emptyOfWhere == false">
            <where>
                ${joinCustomSqlSql}
                <if test="ew.customSqlSegment != null and ew.customSqlSegment.startsWith('WHERE')">
                    AND
                </if>
                ${ew.sqlSegment}
            </where>
        </if>
        order by ${orderBySql}
    </select>

---

6.构造where条件和sql语句where条件混用2

mapper接口层

List<Map> paymentList(@Param(Constants.WRAPPER) Wrapper query, @Param("examId") String examId);

mapper接口实现层

<select id="paymentList" resultType="java.util.Map" parameterType="java.lang.String">
        SELECT a.`ORDNO`, a.`ORDAMT`
        FROM `exam_${examId}_examinee` a,`exam_config` cg
        <if test="ew.emptyOfWhere == false">
            <where>
                a.SS = cg.department_code
                AND cg.examid = #{examId}
                <if test="ew.customSqlSegment != null and ew.customSqlSegment.startsWith('WHERE')">
                    AND
                </if>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>