MyBatis书写规范

johnrui

已于 2024-12-26 17:12:04 修改

阅读量204

点赞数 1

分类专栏： JAVA-ORM 文章标签： java mybatis

于 2024-10-10 15:14:51 首次发布

本文链接：https://blog.youkuaiyun.com/johnrui/article/details/142822589

版权

JAVA-ORM 专栏收录该内容

4 篇文章

订阅专栏

1.MyBatis 查询书写

符号：

大于等于 <![CDATA[ >= ]]>
小于等于 <![CDATA[ <= ]]>
不等于<![CDATA[ <> ]]>

1.1 单查询、批查询

    <select id="getDeviceInfo" parameterType="com.dahua.evo.channel.vo.alarm.VirtualAlarmParam" resultType="com.dahua.evo.brm.client.message.device.Device">
        select
          DEVICE_CODE as deviceCode,
          DEVICE_NAME as deviceName,
          DEVICE_SN as deviceSn,
          DEVICE_IP as deviceIp
        from evo_brm.device
        <where>
             1=1
     DOMAIN_CN like CONCAT('%', #{searchKey},'%')
            <if test="deviceCode!=null and deviceCode!= ''">
                and DEVICE_CODE = #{deviceCode}
            </if>
            <if test="deviceMac!=null and deviceMac!= ''">
                and DEVICE_MAC = #{deviceMac}
            </if>
            <if test="deviceMacList != null and deviceMacList.size() > 0">
                and DEVICE_MAC in
                <foreach item="macId" collection="deviceMacList" open="(" separator="," close=")">
                    #{macId}
                </foreach>
            </if>
        </where>
    </select>

1.1.1 条件查询

  <select id="getAllAccessControlChannelByCycleOrgCode" resultMap="accessControlChannelMap" parameterType="map">
        select 
		<include refid='channelWithRelatedPropSelectStatement' />
        where A.ORG_CODE like CONCAT(#{orgCode}, '%')
        <!-- 用户权限过滤 -->
        <choose>
          <when test="orgCodes!=null and orgCodes.size()>0">
            and
            <foreach collection="orgCodes" item="oCode" index="index" open="(" close=")" separator=" or ">
              A.ORG_CODE like CONCAT(#{oCode},'%')
            </foreach>
          </when>
          <otherwise>
            and 1=2
          </otherwise>
        </choose>
   </select>

1.1.2 sql_id <include refid

  <sql id='channelWithRelatedPropSelectStatement'>
  	A.ID as ID, A.DEVICE_CODE as DEVICE_CODE, A.CHANNEL_CODE as CHANNEL_CODE, A.CHANNEL_NAME as CHANNEL_NAME, A.CHANNEL_SEQ as CHANNEL_SEQ, A.VALID_FLAG as VALID_FLAG, A.ORG_CODE as ORG_CODE, A.DELAY_TIME as DELAY_TIME, 
    A.CLOSE_DOORS_PLAN_ID,A.OPEN_DOORS_PLAN_ID,A.STATUS as STATUS,B.DEVICE_NAME as DEVICE_NAME,C.NAME as ORG_NAME,CASE WHEN B.DEVICE_MODEL = '12' THEN A.CHANNEL_STATUS ELSE B.DEVICE_STATUS END as ONLINE_STATUS,A.BINDING_CHN_ID,A.BINDING_CHN_NAME,B.DEVICE_TYPE,B.DEVICE_MODEL as DEVICE_MODEL,A.WORK_MODE as WORK_MODE,
	A.CHANNEL_SN as CHANNEL_SN,A.ROOM_NUM AS ROOM_NUM,A.CHANNEL_CAPACITY AS CHANNEL_CAPACITY,A.CHANNEL_PERSON_TYPE AS CHANNEL_PERSON_TYPE,A.CHANNEL_BIND_ORG AS CHANNEL_BIND_ORG,A.PSD_FLAG AS PSD_FLAG,A.PHYSICS_ID,B.DEVICE_GENRE,
	A.GPS_X as GPS_X, A.GPS_Y as GPS_Y, A.GPS_Z as GPS_Z, A.ENABLE_MODE as ENABLE_MODE, A.TIMEOUT_ALARM_ENABLE as TIMEOUT_ALARM_ENABLE, A.CLOSE_TIMEOUT as CLOSE_TIMEOUT
    from acs_access_control_chn A
    LEFT JOIN rsc_base_device B ON A.DEVICE_CODE=B.DEVICE_CODE
    LEFT JOIN rsc_organization C ON A.ORG_CODE=C.ORG_CODE
  </sql>



  <select id="getAccessControlChannelByChannelCode" resultMap="accessControlChannelMap" parameterType="String" >
    select 
    <include refid='channelWithRelatedPropSelectStatement' />
    where A.CHANNEL_CODE = #{channelCode,jdbcType=VARCHAR}
  </select>

1.2 模糊查询

        select
        ORG_CODE as orgCode,
        ORG_NAME as orgName,
        ORG_SN as orgSn
        from evo_brm.organization
        <where>
            1=1
            <if test="orgCode!=null and orgCode!= ''">
                and ORG_CODE like CONCAT( #{orgCode},'%')
                and LENGTH(ORG_CODE) = LENGTH(#{orgCode})+3
            </if>
        </where>

1.3 insert 插入

  <insert id="addAccessControlChannel" parameterType="AccessControlChannel" >
    insert into acs_access_control_chn
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        ID,
      </if>
      <if test="deviceCode != null" >
        DEVICE_CODE,
      </if>
      <if test="channelCode != null" >
        CHANNEL_CODE,
      </if>
      <if test="channelName != null" >
        CHANNEL_NAME,
      </if>
      <if test="channelSeq != null" >
        CHANNEL_SEQ,
      </if>
      <if test="validFlag != null" >
        VALID_FLAG,
      </if>
      <if test="orgCode != null" >
        ORG_CODE,
      </if>
      <if test="delayTime != null" >
        DELAY_TIME,
      </if>
      <if test="status != null" >
        STATUS,
      </if>
      <if test="workMode != null" >
        WORK_MODE,
      </if>
	  <if test="channelSN != null">
        CHANNEL_SN,
      </if>
      <if test="roomNum != null">
        ROOM_NUM,
      </if>
      <if test="onlineStatus != null">
        CHANNEL_STATUS,
      </if>
      <if test="channelCapacity != null">
        CHANNEL_CAPACITY,
      </if>
      <if test="channelPersonType != null">
        CHANNEL_PERSON_TYPE,
      </if>
      <if test="channelBindOrg != null">
        CHANNEL_BIND_ORG,
      </if>
      <if test="psdFlag != null">
        PSD_FLAG,
      </if>
      <if test="physicsId != null">
        PHYSICS_ID,
      </if>
      <if test="gpsX != null">
        GPS_X,
      </if>
      <if test="gpsY != null">
        GPS_Y,
      </if>
      <if test="gpsZ != null">
        GPS_Z
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="deviceCode != null" >
        #{deviceCode,jdbcType=VARCHAR},
      </if>
      <if test="channelCode != null" >
        #{channelCode,jdbcType=VARCHAR},
      </if>
      <if test="channelName != null" >
        #{channelName,jdbcType=VARCHAR},
      </if>
      <if test="channelSeq != null" >
        #{channelSeq,jdbcType=BIGINT},
      </if>
      <if test="validFlag != null" >
        #{validFlag,jdbcType=INTEGER},
      </if>
      <if test="orgCode != null" >
        #{orgCode,jdbcType=VARCHAR},
      </if>
      <if test="delayTime != null" >
        #{delayTime,jdbcType=INTEGER},
      </if>
      <if test="status != null" >
        #{status,jdbcType=INTEGER},
      </if>
      <if test="workMode != null" >
        #{workMode,jdbcType=INTEGER},
      </if>
      <if test="channelSN != null">
        #{channelSN,jdbcType=VARCHAR},
      </if>
      <if test="roomNum != null">
        #{roomNum,jdbcType=VARCHAR},
      </if>
      <if test="onlineStatus != null">
        #{onlineStatus},
      </if>
      <if test="channelCapacity != null">
        #{channelCapacity,jdbcType=INTEGER},
      </if>
      <if test="channelPersonType != null">
        #{channelPersonType,jdbcType=INTEGER},
      </if>
      <if test="channelBindOrg != null">
        #{channelBindOrg,jdbcType=BIGINT},
      </if>
      <if test="psdFlag != null">
        #{psdFlag,jdbcType=BIGINT},
      </if>
      <if test="gpsX != null">
        #{gpsX,jdbcType=VARCHAR},
      </if>
      <if test="gpsY != null">
        #{gpsY,jdbcType=VARCHAR},
      </if>
      <if test="gpsZ != null">
        #{gpsZ,jdbcType=VARCHAR},
      </if>
      <if test="physicsId != null">
        #{physicsId,jdbcType=VARCHAR}
      </if>
    </trim>
  </insert>

1.4 更新update

  <update id="updateAccessControlChannel" parameterType="AccessControlChannel" >
    update acs_access_control_chn
    <set >
      <if test="deviceCode != null" >
        DEVICE_CODE = #{deviceCode,jdbcType=VARCHAR},
      </if>
      <if test="channelCode != null" >
        CHANNEL_CODE = #{channelCode,jdbcType=VARCHAR},
      </if>
      <if test="channelName != null" >
        CHANNEL_NAME = #{channelName,jdbcType=VARCHAR},
      </if>
      <if test="channelSeq != null" >
        CHANNEL_SEQ = #{channelSeq,jdbcType=BIGINT},
      </if>
      <if test="validFlag != null" >
        VALID_FLAG = #{validFlag,jdbcType=INTEGER},
      </if>
      <if test="orgCode != null" >
        ORG_CODE = #{orgCode,jdbcType=VARCHAR},
      </if>
      <if test="delayTime != null" >
        DELAY_TIME = #{delayTime,jdbcType=INTEGER},
      </if>
      <if test="status != null" >
        STATUS = #{status,jdbcType=INTEGER},
      </if>
      <if test="workMode != null" >
        WORK_MODE = #{workMode,jdbcType=INTEGER},
      </if>
      <if test="channelSN != null">
        CHANNEL_SN = #{channelSN,jdbcType=VARCHAR},
      </if>
      <if test="roomNum != null">
        ROOM_NUM = #{roomNum,jdbcType=VARCHAR},
      </if>
      <if test="onlineStatus != null">
        CHANNEL_STATUS = #{onlineStatus,jdbcType=VARCHAR},
      </if>
      <if test="channelCapacity != null">
        CHANNEL_CAPACITY = #{channelCapacity,jdbcType=INTEGER},
      </if>
      <if test="channelPersonType != null">
        CHANNEL_PERSON_TYPE = #{channelPersonType,jdbcType=INTEGER},
      </if>
      <if test="psdFlag != null">
        PSD_FLAG = #{psdFlag,jdbcType=TINYINT},
      </if>
      <if test="physicsId != null">
        PHYSICS_ID = #{physicsId,jdbcType=VARCHAR},
      </if>
      <if test="gpsX != null">
        GPS_X = #{gpsX,jdbcType=VARCHAR},
      </if>
      <if test="gpsY != null">
        GPS_Y = #{gpsY,jdbcType=VARCHAR},
      </if>
      <if test="gpsZ != null">
        GPS_Z = #{gpsZ,jdbcType=VARCHAR},
      </if>
      <if test="enableMode != null">
        ENABLE_MODE = #{enableMode,jdbcType=INTEGER},
      </if>
      <if test="timeoutAlarmEnable != null">
        TIMEOUT_ALARM_ENABLE = #{timeoutAlarmEnable,jdbcType=INTEGER},
      </if>
      <if test="closeTimeOut != null">
        CLOSE_TIMEOUT = #{closeTimeOut,jdbcType=INTEGER},
      </if>
      CHANNEL_BIND_ORG = #{channelBindOrg,jdbcType=BIGINT}
    </set>
    where ID = #{id,jdbcType=BIGINT}
  </update>

1.5 mybatis 特殊符号

大于等于 <![CDATA[ >= ]]>  
小于等于 <![CDATA[ <= ]]> 
不等于<![CDATA[ <> ]]>