Mybatis之sql语句

引用sql定义：

前置sql语句：
<sql id = "xxxYYY">
  字段1，字段2，
  f_staffname_byid(a.staff_id) staffName,
  f_managername_byid('传参名称',a.manager_id) managerName
</sql>

<sql id="Base_Column_List">
    log_id, log_type, staff_id, manager_id,...
</sql>

</sql>
    <sql id="Base1_Column_List">
     params, exceptions
 </sql>
 
1、通过id调用：
<select id = "xxxYYY" resultMap="BaseResultMap" parameterType = "java.lang.long">
  select
  //parameterType 可以是整型，字符串等，当返回值为实体类时，可不用该ameterType参数
  <include refrid = "xxxYYY" />
  from table 
  where mo_id = #{moId,JdbcType=DECIMAL}
</select>

2、多个sql调用
<select id="selectByExampleWithBLOBs" parameterType="SysForm"
            resultMap="XXXX">
        select
        <if test="distinct">
            distinct
        </if>
        
        <include refid="Base_Column_List"/>,  
        <include refid="Base1_Column_List"/>
        
        from SysForm
        
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
</select>

1、查询语句

查询1：
<select id="selectByInfoType" resultMap="BaseResultMap">
        select
        a.id,
        a.uri,
        decode(a.type,
        1,
        '数据标识1',
        2,
        '数据标识2',
        3,
        '数据表示3') type,
        a.addr,
        a.method,
        a.create
        f_staffname_byid(a.staff_id) staffName,
        f_managername_byid(a.manager_id) managerName
        from sys a
        WHERE 1=1
        <if test="logType != null">
            AND a.type = #{type}
        </if>
        <if test="createDate != null">
            AND a.create <![CDATA[>=]]> #{create}
        </if>
        <if test="createDateTo != null">
            AND a.create <![CDATA[<=]]> #{createDateTo}
        </if>
        ORDER BY a.create DESC
    </select>
 查询总数：
 <select id="countByExample" parameterType="实体类" resultType="java.lang.Integer">
        select count(*) from Sys
        <if test="_parameter != null">
            <include refid="xxxYyy"/>
        </if>
</select>

2、插入语句

插入1：
<insert id="insert1" parameterType="实体类" useGeneratedKeys="false">
        insert into SYS_LOG
        //截取数据后面的符号，避免sql出错
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="logId != null">
                LOG_ID,
            </if>
            <if test="logType != null">
                LOG_TYPE,
            </if>
            <if test="staffId != null">
                STAFF_ID,
            </if>
            <if test="managerId != null">
                MANAGER_ID,
            </if>         
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="logId != null">
                #{logId,jdbcType=VARCHAR},
            </if>
            <if test="logType != null">
                #{logType,jdbcType=VARCHAR},
            </if>
            <if test="staffId != null">
                #{staffId,jdbcType=DECIMAL},
            </if>
            <if test="managerId != null">
                #{managerId,jdbcType=DECIMAL},
            </if>
        </trim>
    </insert>
 
 插入2：
 <insert id="insert2" parameterType="实体类" useGeneratedKeys="false">
    insert into SYS_LOG (LOG_ID, LOG_TYPE, STAFF_ID
    values (
            #{logId,jdbcType=VARCHAR}, 
            #{logType,jdbcType=VARCHAR}, 
            #{staffId,jdbcType=DECIMAL},
           )
  </insert>

3、更新语句

更新1：
<update id="updateByExampleSelective" parameterType="map">
        update SYS_LOG
        <set>
            <if test="record.logId != null">
                LOG_ID = #{record.logId,jdbcType=VARCHAR},
            </if>
            <if test="record.logType != null">
                LOG_TYPE = #{record.logType,jdbcType=VARCHAR},
            </if>
            <if test="record.staffId != null">
                STAFF_ID = #{record.staffId,jdbcType=DECIMAL},
            </if>
        </set>
        <if test="_parameter != null">
            <include refid="xxxYyy"/>
        </if>
</update>
更新2：
<update id="实体类名" parameterType="map">
   update 表名
   set
        字段=#{record.logId,jdbcType=VARCHAR},
        LOG_TYPE = #{record.logType,jdbcType=VARCHAR},
        STAFF_ID = #{record.staffId,jdbcType=DECIMAL},
   <if test="_parameter != null">
       <include refid="xxxYyy"/>
   </if>
</update>