ibatis UPDATE 动态XML与insert

update的时候需要写中间的这些XML是件挺蛋疼的事。  所以像了个方法


<update id="xiaoegcUpdateSave" parameterClass="com.framework.mapping.Cg_Xiaoegc">
            <![CDATA[
               update     cg_xiaoegc  set id  =#id#    
            ]]>

          <isNotEmpty property="id" prepend=",">     <![CDATA[ ID=#id#     ]]>      </isNotEmpty>
           <isNotEmpty property="xmbh" prepend=",">     <![CDATA[ XMBH=#xmbh#     ]]>      </isNotEmpty>
            <isNotEmpty property="dwmc" prepend=",">     <![CDATA[ DWMC=#dwmc#     ]]>      </isNotEmpty>  
            <isNotEmpty property="dwbh" prepend=",">     <![CDATA[ DWBH=#dwbh#     ]]>      </isNotEmpty>  
            <isNotEmpty property="dwxz" prepend=",">     <![CDATA[ DWXZ=#dwxz#     ]]>      </isNotEmpty>  
            <isNotEmpty property="lxr" prepend=",">     <![CDATA[ LXR=#lxr#     ]]>      </isNotEmpty>
             <isNotEmpty property="lxdh" prepend=",">     <![CDATA[ LXDH=#lxdh#     ]]>      </isNotEmpty>
              <isNotEmpty property="gcmc" prepend=",">     <![CDATA[ GCMC=#gcmc#     ]]>      </isNotEmpty>
               <isNotEmpty property="gcdd" prepend=",">     <![CDATA[ GCDD=#gcdd#     ]]>      </isNotEmpty>
                <isNotEmpty property="ysje" prepend=",">     <![CDATA[ YSJE=#ysje#     ]]>      </isNotEmpty>
                  <isNotEmpty property="zjly_sjbk" prepend=",">     <![CDATA[ ZJLY_SJBK=#zjly_sjbk#     ]]>
                       </isNotEmpty>  <isNotEmpty property="zjly_zc" prepend=",">     <![CDATA[ ZJLY_ZC=#zjly_zc#     ]]>
                             </isNotEmpty>  <isNotEmpty property="htje" prepend=",">     <![CDATA[ HTJE=#htje#     ]]>  
            </isNotEmpty>  <isNotEmpty property="jhkg_time" prepend=",">     <![CDATA[ JHKG_TIME=#jhkg_time#     ]]>      </isNotEmpty>
            <isNotEmpty property="jhjg_time" prepend=",">     <![CDATA[ JHJG_TIME=#jhjg_time#     ]]>      </isNotEmpty>
             <isNotEmpty property="dwmc_sg" prepend=",">     <![CDATA[ DWMC_SG=#dwmc_sg#     ]]>      </isNotEmpty>
               <isNotEmpty property="lxr_sg" prepend=",">     <![CDATA[ LXR_SG=#lxr_sg#     ]]>      </isNotEmpty>
                <isNotEmpty property="lxdh_sg" prepend=",">     <![CDATA[ LXDH_SG=#lxdh_sg#     ]]>      
                </isNotEmpty>  <isNotEmpty property="jbqk" prepend=",">     <![CDATA[ JBQK=#jbqk#     ]]>      </isNotEmpty>  
                <isNotEmpty property="bz" prepend=",">     <![CDATA[ BZ=#bz#     ]]>      </isNotEmpty>  
                <isNotEmpty property="type_gc" prepend=",">     <![CDATA[ TYPE_GC=#type_gc#     ]]>      </isNotEmpty>  
                <isNotEmpty property="status" prepend=",">     <![CDATA[ STATUS=#status#     ]]>      </isNotEmpty>
                 <isNotEmpty property="createdate" prepend=",">     <![CDATA[ CREATEDATE=#createdate#     ]]>      </isNotEmpty>
                  <isNotEmpty property="beianshijian" prepend=",">     <![CDATA[ BEIANSHIJIAN=#beianshijian#     ]]>      </isNotEmpty>
          
            
            <![CDATA[
                   where 1=1 and  id =#id#        
            ]]>
            
            

用下面的SQL可以搞定
select wm_concat(a) from (
select   
 '<isNotEmpty property="'||lower(COLUMN_NAME)||'" prepend="1234">     <![CDATA[ '|| COLUMN_NAME||'=#'||lower(COLUMN_NAME)||'#     ]]>      </isNotEmpty>'  
 as  a
 from   user_tab_columns where  table_name='CG_XIAOEGC'
)
操作过程，使用此段SQL生成XML文件后，先将“,”替换成空格，然后将1234替换成你要的字符，如AND ,OR ,逗号等


或者直接使用下面的SQL
select replace(replace(wm_concat(a) ,',','  '),'1234','你想要的字符（and ,or ,逗号）') from (
select   
 '<isNotEmpty property="'||lower(COLUMN_NAME)||'" prepend="1234">     <![CDATA[ '|| COLUMN_NAME||'=#'||lower(COLUMN_NAME)||'#     ]]>      </isNotEmpty>'  
 as  a
 from   user_tab_columns where  table_name='CG_XIAOEGC'
)

 

 

 先查询表的所有字段，然后拼接字符串，最后WM_concat变成一行后替换其中的“，”后再次替换其中的1234临时字符成需要的“AND OR 等”

 

 

 

 

生成INSERT

select 'insert into   CG_XIAOEGC values(' || wm_concat(COLUMN_NAME) ||') values('||wm_concat('#'||lower(COLUMN_NAME)||'#')||')' 
  from   user_tab_columns where  table_name='CG_XIAOEGC'

 

 

生成SELECT

select ' select ' || wm_concat(COLUMN_NAME) ||' from   CG_XIAOEGC  ' 
  from   user_tab_columns where  table_name='CG_XIAOEGC'