MyBatis, batch add, modify, delete

Reprinted from: http://blog.csdn.net/sanyuesan0000/article/details/19998727

Recently need to use the MyBatis batch new Oracle database, just started on the Internet to find the method is to update MySQL, try to find not suitable for Oracle, and later found that the correct Oracle batch new SQL is:

<InsertID= "Insertattractionsbatch"ParameterType= "Java.util.List">INSERT INTO Attractions (ID, NAME, longitude, LATITUDE, Update_time)<foreachCollection= "List"Item= "Item"Index= "Index"Separator= "UNION All" >(select #{item.id,jdbctype=varchar}, #{item.name,jdbctype=varchar}, #{item.longitude,jdbctype=decimal}, #{item . Updatetime,jdbctype=timestamp} from Dual)</foreach></Insert>

It is important to note that there is no values in SQL, and in the <foreach> tab (Selece ... from dual), the SQL in MySQL is this:

New:

<InsertID= "Insertattractionsbatch"ParameterType= "Java.util.List">INSERT INTO Attractions (ID, NAME, longitude, LATITUDE, Update_time)<foreachCollection= "List"Item= "Item"Index= "Index"Separator= "UNION All" >#{item.id,jdbctype=varchar}, #{item.name,jdbctype=varchar}, #{item.longitude,jdbctype=decimal}, #{ Item.updatetime,jdbctype=timestamp}</foreach></Insert>

Oracle updates cannot be done in a normal way, as needed:

<UpdateID= "Updateattractionsbatch"ParameterType= "Java.util.List">begin<foreachCollection= "List"Item= "Item"Index= "Index"Separator=";" >Update Attractions<Set>            <ifTest= "Item.id!=null and item.id!=">id = #{item.id},</if>            <ifTest= "Item.head!=null and item.head!=">HEAD = #{item.head},</if>            </Set>WHERE id = #{item.id}</foreach>; end; </Update>

The deletion is the same as MySQL:

<DeleteID= "Deleteattractions"ParameterType= "Java.util.List">Delete from Attractions<where>  <foreachCollection= "List"Index= "Index"Item= "Item"Open="("Separator= "or"Close=")">Id=#{item.id}</foreach>  </where>  </Delete>

MyBatis, batch add, modify, delete