当前位置 : 主页 > 编程语言 > java >

Mybatis注解方式操作Oracle数据库详解

来源:互联网 收集:自由互联 发布时间:2021-04-03
1.新增多行数据 @Insert({"scriptinsert all " + "foreach collection=\"list\" index=\"index\" item=\"item\" open=\"\" separator=\"\" close=\"\"" + " into s_user (user,username) values " + "(#{item.user},#{item.username}) " + "/foreach" +

1.新增多行数据

@Insert({"<script>insert all " +
   "<foreach collection=\"list\" index=\"index\" item=\"item\" open=\"\" separator=\"\" close=\"\">" +
   " into s_user (user,username) values " +
   "(#{item.user},#{item.username}) " +
   "</foreach>" +
   " select 1 from dual " +
   "</script>"})

2.执行多条SQL语句

特别注意:open属性设置为begin,close设置为;end;

@Update({"<script>" +
   "<foreach collection=\"list\" separator=\";\" item=\"item\" open=\"begin\" close=\";end;\">" +
   "update s_user set user=#{item.user},username=#{item.username} where id=#{item.id}" +
   "</foreach>" +
   "</script>"})

ORACLE中通过begin...end;来执行多行sql语句,各条sql之间用;分割

补充知识:使用mybatis注解批量插入Oracle数据库与批量插入MySQL数据库区别

批量插入MySQL语法

insert into table_name values (column01, column02, ...), (value01, value02, ...),

(value01, value02, ...);

例子:

@Select({"<script>" ,
   "insert into t_sales_target (area_id, area_name, shop_id, shop_name, year) values " ,
   " <foreach collection='saleTargetList' item='item' index='index' separator=','>",
   " (",
   " #{item.areaId},",
   " #{item.areaName},",
   " #{item.shopId},",
   " #{item.shopName},",
   " #{item.year},"
   " )",
   " </foreach>",
   "</script>"
 } )

批量插入Oracle语法

insert into table_name (column01, column02, ...)
 select * from (
  select value01, value02, ... from dual
  union
  select value01, value02, ... from dual
  union
  select value01, value02, ... from dual
 )

例子:

 @Insert({"<script>",
   "insert into t_sales_date_amount (amount, sale_date, shop_id, area_id) select A.* from (",
   " <foreach collection='salesDateAmountList' item='item' index='index' separator='UNION ALL'>",
   " SELECT",
   " #{item.salesAmount},",
   " #{item.salesDateTime},",
   " #{item.shopId},",
   " #{item.areaId}",
   " from dual",
   " </foreach>",
   " )A",
   "</script>"
 })

以上这篇Mybatis注解方式操作Oracle数据库详解就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持易盾网络。

网友评论