Requirement: Insert data in Gisdata table, if WXID data exists on update, does not exist insert
MyBatis configuration for MySQL
1 <?XML version= "1.0" encoding= "UTF-8"?>2 <!DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en"3 "Http://mybatis.org/dtd/mybatis-3-mapper.dtd">4 <Mappernamespace= "Com.test.wechat.dao.GisDataMapper">5 <SQLID= "Usercolumns">6 Wxid,ip,port,login_username,login_status,wx_num,tel,qq,email,task_name,min_longitude,max_longitude,min_ Latitude,max_latitude,clent_id,country,province,city,town7 </SQL>8 9 <InsertID= "Insertgisdata"ParameterType= "Gisdata"Usegeneratedkeys= "true">TenReplace into Gisdata (<includerefID= "Usercolumns"/>) VALUES (#{wxid},#{ip},#{port},#{loginusername},#{loginstatus},#{wxnum},#{tel},#{qq},#{email},#{taskname},# {Minlongitude},#{maxlongitude},#{minlatitude},#{maxlatitude},#{clentid},#{country},#{province},#{city},#{town} ) One </Insert> A <!--<delete id= "deletegisdata" parametertype= "int" > - Delete from Gis_data - </delete> - the </Mapper>
Oracle's MyBatis Configuration
1 <InsertID= "Insertgisdata"ParameterType= "Gisdata" 2 Merge into Gisdata GD3 using dual on (Gd.wxid=#{wxid})4 When matched then5 Update6 Set Gd.ip= #{ip},7 Gd.port=#{port},8 Gd.wx_num=#{wx_num}9 Gd.town=#{town}Ten When isn't matched then One Insert A ( - Wxid,ip,port,wx_num,town - ) the Values - ( - #{wxid},#{ip}, #{port},#{wx_num},#{town} - ) + </insert>
Note: MySQL's replace into: before inserting the data, replace into will first determine whether the same record is present based on the primary key or unique index, and if so, delete the original data before inserting the new data, or insert it directly if there are no identical records.
Oracle Merge into: similar to replace into in MySQL, the merge into will also determine the same record based on the primary key before inserting the data, which is different from the subsequent operation, the merge Into can do nothing for the same record that exists, but it can be modified, but there is no other action, and if you don't have the same record, you can do nothing, you can do the insert, and there's no other action.
But I actually verified that there was a difference between the two (all operating the same table):
MySQL replace into 1. Update existing data 2. Delete data that does not match new data 3. Inserting new data (data not matched to the original table)
Oracle's merge into can only be updated and appended
Oracle and MySQL in MyBatis insert_update