Use Oracle's merge and insert all syntax in Java

This type of requirement is usually met in Java development. If a piece of data already exists in the table, update it. If it does not exist, insert new data.

If you do not use the merge syntax provided by Oracle, you may first need to query the database select to see if it exists and then decide how to operate it. In this case, you need to write moreCode,

At the same time, the performance is not good, it needs to go back and forth to the database twice.

If merge is used, an SQL statement can be completed.

The following is an example of the Java code: (for simplicity, the connection is not closed for exceptions)

Private Static void testmerge () throws sqlexception {// create table testtb (ID int, Val varchar2 (10) string sqlstr = "merge into testtb" + "using (select? As newid ,? As newval from dual) newdata "+" On (testtb. id = newdata. newid) "+" when not matched then "+" insert values (newid, newval) "+" when matched then "+" update set testtb. val = newdata. newval "; connection conn = nonxadbutil. getconnection ("JDBC: oracle: thin: @ 147.151.100.19: 1521: orcl", "user", "PWD"); preparedstatement sta = Conn. preparestatement (sqlstr); Sta. setint (1, 1); Sta. setstring (2, "New Value"); sta.exe cuteupdate (); Sta. close (); Conn. commit (); Conn. close ();}
 

Similarly, Oracle's insert all syntax supports inserting an SQL statement into multiple tables at a time, for example:

Insert all into tx1 values (1000, 1) into tx2 values (2000, 1) Select * from dual; Private Static void testinsertall () throws sqlexception {string sqlstr = "insert all into tx1 values (?, ?) Into tx2 values (?, ?) Select * from dual "; connection conn = nonxadbutil. getconnection ("JDBC: oracle: thin: @ 147.xx: orcl", "XX", "XX"); preparedstatement sta = Conn. preparestatement (sqlstr); Sta. setint (1, 1000); Sta. setint (2, 1); Sta. setint (3, 2000); Sta. setint (4, 1); sta.exe cuteupdate (); Sta. close (); Conn. commit (); Conn. close ();}