九亿moc
2015-08-21 01:57
采纳率: 50%
浏览 7.1k

求经验!Mybatis 针对Oracle数据库如何写“多条件”批量删除语句?

我的写法:

  <delete id="delMultiByIds2" parameterType="java.util.List">  
  delete from tb_duty where
    <foreach collection="list" item="item" index="index" separator="or">  
       ( dscd=#{item.dscd}, 
       and unit_id=#{item.unitId},
       and year=#{item.year},
       and month=#{item.month},
       and flag=#{item.flag} )
    </foreach> 
</delete>

语句的语法没错,只是无法删除数据库中的记录,肯定是语句的写法有问题。
图片说明
希望有经验的高手指教一把!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • 九亿moc 2015-08-22 09:02
    已采纳

    问题解决后兴奋地写下:
    程序批量删除无法删除的主要问题最终还是在sql语句上,虽然debug日志上能看到sql语句和参数都没有问题,但是!sql语句执行的时候并没有拿到这些个参数。经调试,将foreach中参数赋值的写法由原先的

     <foreach collection="list" item="item" index="index" separator="union all">  
         。。。B.dscd=#{item.dscd} and B.unit_id=#{item.unitId} 。。。
        </foreach>
    

    修改为:

     <foreach collection="list" item="item" index="index" separator="union all">  
         。。。B.dscd=${item.dscd} and B.unit_id=${item.unitId} 。。。
        </foreach>
    

    至此,困扰我许久的问题得以解决!
    附:Mybatis针对Oracle数据库“多条件”批量删除的mapper.xml

     <!-- 批量删除值班表 -->
     <delete id="delMultiByIds2" parameterType="java.util.List"> 
      delete from tb_duty A
      where exists 
      ( 
       select 1 from(
        <foreach collection="list" item="item" index="index" separator="union all">  
         select  B.* from tb_duty B where 1=1 and  B.dscd=${item.dscd} and B.unit_id=${item.unitId} and 
          B.year=${item.year} and B.month=${item.month} and B.flag=${item.flag} 
        </foreach>
        )S where  A.duty_id=S.duty_id
      )
    </delete>
    
    
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 嘻嘻哈哈哈0805 2015-08-21 02:04

    传递的参数后台是否能够获取的到?

    评论
    解决 无用
    打赏 举报
  • 九亿moc 2015-08-21 02:05

    这个当然能获取到,都在list里的对象里面。

    评论
    解决 无用
    打赏 举报
  • 睡觉不盖被 2015-08-21 02:10

    后台没有输出错误原因么 日志里

    评论
    解决 无用
    打赏 举报
  • 九亿moc 2015-08-21 05:43
       <!-- 批量删除值班表 -->
     <delete id="delMultiByIds2" parameterType="java.util.List" > 
      delete from tb_duty
      where exists
      ( 
        <foreach collection="list" item="item" index="index" separator="union all">  
         SELECT  <include refid="Base_Column_List" /> from tb_duty where 1=1 and  dscd=#{item.dscd} and unit_id=#{item.unitId} and 
          year=#{item.year} and month=#{item.month} and flag=#{item.flag}
        </foreach>
      )
    </delete>
    

    最新的sql在plsql里面执行没有问题,但是通过mabatis执行还是无法实现删除效果!继续等待。。。一但解决,我会里面贴上解决方案。

    评论
    解决 无用
    打赏 举报
  • 九亿moc 2015-08-21 08:35

    最新的sql:

       <!-- 批量删除值班表 -->
     <delete id="delMultiByIds2" parameterType="java.util.List" > 
      delete from tb_duty A
      where exists 
      ( 
        <foreach collection="list" item="item" index="index" separator="union all">  
         select  B.* from tb_duty B where 1=1 and  B.dscd=#{item.dscd} and B.unit_id=#{item.unitId} and 
          B.year=#{item.year} and B.month=#{item.month} and B.flag=#{item.flag} 
          and  A.duty_id=B.duty_id
        </foreach>
      ) 
    </delete>
    

    直接拿打印的sql语句(语句和参数都有)在plsql里面执行可以,但是程序上走一遍还是不行。无法删除!
    语句:

     delete from tb_duty A where exists ( select B.* from tb_duty B where 1=1 and B.dscd=? and B.unit_id=? and B.year=? and B.month=? and B.flag=? and A.duty_id=B.duty_id union all select B.* from tb_duty B where 1=1 and B.dscd=? and B.unit_id=? and B.year=? and B.month=? and B.flag=? and A.duty_id=B.duty_id union all select B.* from tb_duty B where 1=1 and B.dscd=? and B.unit_id=? and B.year=? and B.month=? and B.flag=? and A.duty_id=B.duty_id ) 
    

    参数:

     Parameters: 340100(String), 4(Integer), 2015(String), 7(String), 1(Integer), 340100(String), 4(Integer), 2015(String), 6(String), 1(Integer), 340100(String), 4(Integer), 2015(String), 5(String), 1(Integer)
    

    继续等待。。。。。。

    评论
    解决 无用
    打赏 举报
  • 九亿moc 2015-08-22 01:19

    问题很可能出在Spring和Mybatis整合事务起没起作用上!
    控制台有2句可疑的话(已标记):

     2015-08-22 09:16:21,877 DEBUG [org.mybatis.spring.SqlSessionUtils] - Creating a new SqlSession
    2015-08-22 09:16:21,877 DEBUG [org.mybatis.spring.SqlSessionUtils] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f703110] was not registered for synchronization because synchronization is not active
    2015-08-22 09:16:21,877 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
    2015-08-22 09:16:21,877 DEBUG [org.springframework.jdbc.datasource.DriverManagerDataSource] - Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@10.34.0.84:1521:orcl]
    # 2015-08-22 09:16:21,924 DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [oracle.jdbc.driver.T4CConnection@616b6449] will not be managed by Spring
    2015-08-22 09:16:21,924 DEBUG [com.sunny.dao.duty.DutyMapper.delMultiByIds2] - ==>  Preparing: delete from tb_duty A where exists ( select S.* from( select T.* from tb_duty T right join ( select B.* from tb_duty B where 1=1 and B.dscd=? and B.unit_id=? and B.year=? and B.month=? and B.flag=? union all select B.* from tb_duty B where 1=1 and B.dscd=? and B.unit_id=? and B.year=? and B.month=? and B.flag=? ) B on T.duty_id=B.duty_id ) S where A.duty_id=S.duty_id ) 
    2015-08-22 09:16:21,924 DEBUG [com.sunny.dao.duty.DutyMapper.delMultiByIds2] - ==> Parameters: 340100(String), 4(Integer), 2015(String), 9(String), 1(Integer), 340100(String), 4(Integer), 2015(String), 5(String), 1(Integer)
    2015-08-22 09:16:21,924 DEBUG [com.sunny.dao.duty.DutyMapper.delMultiByIds2] - <==    Updates: 0
    # 2015-08-22 09:16:21,924 DEBUG [org.mybatis.spring.SqlSessionUtils] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f703110]
    2015-08-22 09:16:21,924 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
    2015-08-22 09:16:21,924 DEBUG [org.springframework.web.method.HandlerMethod] - Method [delMultiByIds] returned [{"success":false}]
    
    评论
    解决 无用
    打赏 举报
  • 九亿moc 2015-08-22 01:21

    上面的标记没反应,是下面这2句:
    第一句:
    JDBC Connection [oracle.jdbc.driver.T4CConnection@616b6449] will not be managed by Spring
    第二句:
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7f703110]

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题