mybatis利用xml编写sql语句

﹏ヽ暗。殇╰゛Y 2024-04-17 16:05 41阅读 0赞

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.....">
        <insert id="insert" parameterType="com..xdo.">
            insert into user(
            <include refid="sqlKey"/>
            ) values(
            <include refid="sqlValue"/>
            )
        </insert>
            <sql id="sqlKey">
            <trim suffixOverrides=",">
                <if test="xdo.name!=null and xdo.name !=''">
                    `name`,
                </if>
                <if test="xdo.phone!=null">
                    phone,
                </if>
            </trim>
        </sql>
        <sql id="sqlValue">
            <trim suffixOverrides=",">
                <if test="xdo.name!=null">
                    #{xdo.name},
                </if>
                <if test="xdo.phone!=null">
                    #{xdo.phone},
                </if>
            </trim>
        </sql>
    
    
        <delete id="delete" parameterType="com..xdo.">
            delete from user
            <include refid="sqlWhere"/>
        </delete>
        <update id="update" parameterType="com..xdo.">
            update user
            <include refid="sqlSet"/>
            <include refid="sqlWhere"/>
        </update>
         <sql id="sqlSet">
            <if test="xdo != null">
                <set>
                    <if test="xdo.name!=null">
                        `name`=#{xdo.name},
                    </if>
                    <if test="xdo.phone!=null ">
                        phone=#{xdo.phone},
                    </if>    
                </set>
            </if>
        </sql>
            <sql id="sqlWhere">
            <if test="where != null">
                <where>
                    <if test="id != null and id !=''">AND id=#{id}</if>
                    <if test="name != null and name !=''">AND `name`=#{name}</if>
                    <if test="phone != null and phone !=''">AND phone=#{phone}</if>
                </where>
            </if>
        </sql>
        
        <select id="selectList" resultMap="resultMapBase" parameterType="com...xdo.">
            select
            <include refid="sqlBase"/>
            from user
            LEFT JOIN depart d on user.id=d.order
            <include refid="sqlSelectWhere"/>
            <include refid="sqlSuffix"/>
        </select> 
        <resultMap id="resultMapBase" type="com..DO">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </resultMap>
        <sql id="sqlBase">
            id,`name`,phone
        <sql id="sqlSuffix">
                <if test="suffix != null">
                    ${suffix}
                </if>
        </sql>
            <sql id="sqlSelectWhere">
                <where>
                    <if test="id != null">AND id=#{id}</if>
                    <if test="name != null">AND name=#{name}</if>
                    <if test="phone != null">AND phone=#{phone}</if>
                </where>
        </sql>
    </mapper>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a7fec1a8408d4f829ff021c568592f87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/5ed8089cde90464a98f73d6b505659c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4OTI5Mw_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/4c08bda0950049d195a371fd47d48aac.png