sqlmap动态sql优化,避免传参失误批量修改和删除操作!

浏览数：104 / 时间：2015年06月09日

分析以下的sqlmap存在问题：

<delete id="deletePartspic" parameterClass="TblSpPartspic">
        delete  from  tbl_sp_partspic
        <dynamic prepend="where">
            <isNotEmpty property="id" prepend="and">
                id = #id#
            </isNotEmpty>
            <isNotEmpty property="fPartsinfoId" prepend="and">
                f_partsinfo_id = #fPartsinfoId#
            </isNotEmpty>
            <isNotEmpty property="picUrl" prepend="and">
                pic_url = #picUrl#
            </isNotEmpty>
        </dynamic>
    </delete>
     
    <update id="updatePartspic" >
        update   tbl_sp_partspic 
        <dynamic prepend="set">
             <isNotEmpty  property="picUrl"  prepend=","  >   
                pic_url=#picUrl#
             </isNotEmpty>
             <isNotEmpty property="fPartsinfoId" prepend="," > 
                f_partsinfo_id=#fPartsinfoId#
             </isNotEmpty>
              <isNotEmpty property="flag" prepend="," > 
                flag=#flag#
             </isNotEmpty>
        </dynamic>
        <dynamic prepend="where">
            <isNotEmpty property="id" prepend="and" > 
                id = #id#
             </isNotEmpty>
             <isNotEmpty property="picUrl" prepend="and" > 
                pic_url = #picUrl#
             </isNotEmpty>
        </dynamic> 
    </update>

如果没有传递参数，导致的结果就是删除整个表的数据，或修改整个表的数据，如果项目处理上线阶段，这样的问题将会很严重。

查询，添加不会出现以上问题。

所以我们要避免该种问题，要做一个限定条件，虽然sqlmal动态参数有它的灵活性，但是面对这样的情况，还是要尽量少用。

--解决方法: 分拆成多个sql语句,在dao层来判断执行。操作都要带上where条件（限定），就算没有传参，也只会报sql语法异常。--

[SQL] delete FROM `tbl_sp_partspic` where id = ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘ at line 1

在dao层做判断，分别调用不同的sql

public boolean deletePartsPic(Map map) throws DataAccessException{
        boolean flag = false;
        Object object = null;
        PartsPicPO po = new PartsPicPO();
        po.setId((Integer)map.get("id"));
        po.setfPartsinfoId( (Integer)map.get("fPartsinfoId"));
        po.setPicUrl( (String)map.get("picUrl") );
        
        if(po.getId() != null){
            object = this.getSqlMapClientTemplate().delete("deletePartspicById", po);
            log.info("删除返回的信息" + object);
        }else if(po.getfPartsinfoId() != null){
            object = this.getSqlMapClientTemplate().delete("deletePartspicByFid", po);
            log.info("删除返回的信息" + object);
        }else if(po.getPicUrl() != null){
            object = this.getSqlMapClientTemplate().delete("deletePartspicByPicUrl", po);
            log.info("删除返回的信息" + object);
        }
        
        
        if (object != null) {
            flag = true;
        }
        return flag;
    }

    @Override
    public boolean updatePartsPic(Map<String, Object> map) throws DataAccessException{
        boolean flag = false;
        Object object = null;
        PartsPicPO po = new PartsPicPO();
        po.setId((Integer) map.get("id"));
        po.setPicUrl((String) map.get("picUrl"));
        po.setfPartsinfoId((Integer) map.get("fPartsinfoId"));
        po.setFlag((Integer) map.get("flag"));
        
        if(po.getId() != null){
            object = this.getSqlMapClientTemplate().update("updatePartspicById", po);
            log.info("更新信息的返回：" + object + "，影响行数");
            flag = true;
        }else if(po.getPicUrl() != null){
            object = this.getSqlMapClientTemplate().update("updatePartspicByPicUrl", po);
            log.info("更新信息的返回：" + object + "，影响行数");
            flag = true;
        }
        
        return flag;
        
    }

拆分的sqlmap文件:

<delete id="deletePartspicById" parameterClass="TblSpPartspic">
        delete  from  tbl_sp_partspic where id = #id#
    </delete>
    
    <delete id="deletePartspicByFid" parameterClass="TblSpPartspic">
        delete  from  tbl_sp_partspic where f_partsinfo_id = #fPartsinfoId#
    </delete>
    
    <delete id="deletePartspicByPicUrl" parameterClass="TblSpPartspic">
        delete  from  tbl_sp_partspic where pic_url = #picUrl#
    </delete>
     
    <update id="updatePartspicById" >
        update   tbl_sp_partspic 
        <dynamic prepend="set">
             <isNotEmpty  property="picUrl"  prepend=","  >   
                pic_url=#picUrl#
             </isNotEmpty>
             <isNotEmpty property="fPartsinfoId" prepend="," > 
                f_partsinfo_id=#fPartsinfoId#
             </isNotEmpty>
              <isNotEmpty property="flag" prepend="," > 
                flag=#flag#
             </isNotEmpty>
        </dynamic>
        where id = #id#
    </update>
    
    <update id="updatePartspicByPicUrl" >
        update   tbl_sp_partspic 
        <dynamic prepend="set">
             <isNotEmpty  property="picUrl"  prepend=","  >   
                pic_url=#picUrl#
             </isNotEmpty>
             <isNotEmpty property="fPartsinfoId" prepend="," > 
                f_partsinfo_id=#fPartsinfoId#
             </isNotEmpty>
              <isNotEmpty property="flag" prepend="," > 
                flag=#flag#
             </isNotEmpty>
        </dynamic>
        where pic_url = #picUrl#
    </update>