关于SQL

场景：

当需要自定义查询sql语句的时候，sql语句可以写到任何的sql的xml文件里面，格式如下：

<mapper namespace="com.chinacreator.c2.sw.hc.hospitalinfo.Repair_BillDispartMapper">
//复杂的sql语句尽量进行命名别名的形式
    <sql id="totolSelectSql">
		B.rb_Id AS rbId,
        B.rb_priority AS rbPriority,
        B.rb_status AS rbStatus,
        B.rp_Name AS rpName,
        B.rp_Phone AS rpPhone,
        B.pp_ID AS ppID,
        B.rp_adress AS rpAdress,
        B.rb_desc AS rbDesc,
        B.rb_files AS rbFiles,
        B.rb_endTime AS rbEndtime,
        B.rb_upTime AS rbUptime,
        B.rb_repairApprCentent AS repairApprCentent,
        B.rb_repairApprLevel AS repairApprLevel,
        B.rb_repairPName AS rbRepairpname,
        B.rb_repairDesc AS rbRepairdesc,
        pro.pp_Code AS ppCode,
        pro.pp_Model AS ppModel,
        pro.pp_Brand AS ppBrand,
        pro.pp_Name AS ppName,
        ORG.org_name AS orgName,
        T.pType_Name AS pTypeName
	</sql>
	
    <sql id="totalFromSql">
		hc_repair_billinfo as B,hc_repair_personinfo as P,hc_repair_propertyinfo AS PRO, td_sm_organization as ORG,hc_repair_propertytype as T
	</sql>
//这边进行resultmap作出映射，这样数据库字段才能和对线中字段进行对应，尤其是数据库有下划线的，对象中不能使用（据试验， mybatis可以自动将下划线转换掉）
<resultMap id="AssociatedEquipmentResult" type="com.chinacreator.c2.SparePartsManagement.AssociatedEquipment.AssociatedEquipment">
	<id property="id" column="id"/>
	<result property="assetid" column="assetid"/>
	<result property="accessoriesid" column="accessoriesid"/>
	<result property="parsid" column="parsid"/>
	<result property="actualcycle" column="actualcycle"/>
	<result property="createBy" column="create_by"/>
	<result property="createDate" column="create_date"/>
	<result property="delFlag" column="del_flag"/>
	<result property="frequency" column="frequency"/>
	<result property="levelas" column="levelas"/>
	<result property="maxdate" column="maxdate"/>
	<result property="mindate" column="mindate"/>
	<result property="nexttime" column="nexttime"/>
	<result property="planningcycle" column="planningcycle"/>
	<result property="remarks" column="remarks"/>
	<result property="updateBy" column="update_by"/>
	<result property="updateDate" column="update_date"/>
	<result property="warehouse" column="warehouse"/>
	<result property="officeName" column="office_Name"/>
	<result property="moffceName" column="moffce_Name"/>
	<result property="maxdateStartDate" column="maxdateStartDate"/>
	<result property="maxdateEndDate" column="maxdateEndDate"/>
	<result property="nexttimeStartDate" column="nexttimeStartDate"/>
	<result property="nexttimeEndDate" column="nexttimeEndDate"/>
	<result property="EarlierDate" column="Earlier_Date"/>
	<result property="RecentDate" column="Recent_Date"/>
	<result property="ReplacementFrequency" column="Replacement_Frequency"/>

</resultMap>
//进行增删改查的查询语句
<select id="selectAllRepair" parameterType="map"
resultType="com.chinacreator.c2.sw.hc.hospitalinfo.common.result.app.repairBill.RepairBillDispatch">
        SELECT
        <include refid="totolSelectSql"/>
        FROM
        <include refid="totalFromSql"/>
        <where>
            <if test="status!=null">  B.rb_status in (
                <foreach collection="status" item="statu" index="index" separator=",">
                    #{statu}
                </foreach>
                )and
            </if>
            p.rp_role=#{role} AND 
            B.rb_repairPid=#{rpId} AND 
            B.rb_repairPid = P.rp_Id AND PRO.pp_ID = B.pp_ID AND B.rp_OrgId = ORG.org_id AND
            PRO.pType_ID = T.pType_ID
        </where>
        ORDER BY B.rb_upTime DESC
    </select>

//像这两种的查询语句的parameterType=入参的类型 resultType= 构造的实体对象的链接地址（没有的话映射不到对象）即返回出去的类型（一般是实体对象，包含拓展字段）

<update id="updateNull" parameterType="com.chinacreator.c2.sw.hc.hospitalinfo.Hc_repair_billinfo">
	update hc_repair_billinfo
<set>
	<if test="ppId != null">pp_id=#{ppId,jdbcType=NUMERIC},</if>
	<if test="rbPid != null">rb_pid=#{rbPid,jdbcType=NUMERIC},</if>
	<if test="rpName != null">rp_name=#{rpName,jdbcType=VARCHAR},</if>
	<if test="rpOrgid != null">rp_orgid=#{rpOrgid,jdbcType=VARCHAR},</if>
	<if test="rpPhone != null">rp_phone=#{rpPhone,jdbcType=VARCHAR},</if>
	<if test="rbDesc != null">rb_desc=#{rbDesc,jdbcType=VARCHAR},</if>
	<if test="rbFiles != null">rb_files=#{rbFiles,jdbcType=VARCHAR},</if>
	<if test="rbUptime != null">rb_uptime=#{rbUptime,jdbcType=TIMESTAMP},</if>
	<if test="rbEndtime != null">rb_endtime=#{rbEndtime,jdbcType=DATE},</if>
	<if test="rbStatus != null">rb_status=#{rbStatus,jdbcType=CHAR},</if>
								rb_priority=#{rbPriority},
								rb_repairpid=#{rbRepairpid},
								rb_repairpname=#{rbRepairpname},
	<if test="rbRepairendtime != null">rb_repairendtime=#{rbRepairendtime,jdbcType=TIMESTAMP},</if>
	<if test="rbRepairdesc != null">rb_repairdesc=#{rbRepairdesc,jdbcType=VARCHAR},</if>
	<if test="rbRepairapprlevel != null">rb_repairapprlevel=#{rbRepairapprlevel,jdbcType=CHAR},</if>
	<if test="rbRepairapprcentent != null">rb_repairapprcentent=#{rbRepairapprcentent,jdbcType=VARCHAR},</if>
	<if test="rpAdress != null">rp_adress=#{rpAdress,jdbcType=VARCHAR}</if>
</set>
<where>
	<choose>
        <when test="rbId != null">and rb_id = #{rbId}</when>
        <otherwise>and rb_id = </otherwise>
    </choose>
</where>
</update>




</mapper>

---

原因分析：

controller层调用自定义sql语句的时候：

//billinfoDao.getSession().功能语句的标识（insert、update）（namespace.sql语句的id，入参）入参必须是对象有的属性，或者入参整个对象。
billinfoDao.getSession().update("com.chinacreator.c2.sw.hc.hospitalinfo.Repair_BillDispartMapper.updateNull",billinfo);

提示：）

---