How to write "INSERT IF EXISTS UPDATE" in Oracle using JAVA

Question

I have a ERROR_MSG table which stores error messages with some ids. I want to insert error message if id is not present in table and if its present update error message. Inserting using below java JDBC code.

ID ERROR_MSG
1  ERR1
2  ERR2
3  ERR3

This is my code:

insertQry = "SQL";
Connection con = null;
PreparedStatement stmt = null;
try {
    con = getDataSource().getConnection();
    stmt = con.prepareStatement(insertQry);
    for(ListingAckNackData errorList: listOfListingERROR) {
        stmt.setLong(1, eqGlobalData.getSrcMsgId());
        stmt.setString(2, errorList.getGliId());
        if (null != errorList.getListingRevisionNo()) {
            stmt.setInt(3, errorList.getListingRevisionNo());
        } else {
            stmt.setNull(3, Types.NULL);
        }
        if (null != errorList.getErrorMessage()) {
            stmt.setString(4, errorList.getErrorMessage());
        } else {
            stmt.setNull(4, Types.NULL);
        }
        stmt.addBatch();
    }
    stmt.executeBatch();
}

Possible duplicate of Oracle: how to UPSERT (update or insert into a table?), because I don't believe this is possible in a simple INSERT OR UPDATE query with Oracle. — AxelH
– AxelH, Commented Dec 11, 2017 at 7:47
Note that this answer provide a simpler solution in Java but will cost some processing... — AxelH
– AxelH, Commented Dec 11, 2017 at 7:49
UPDATE tablename SET val1 = in_val1, val2 = in_val2 WHERE val3 = in_val3; IF ( sql%rowcount = 0 ) THEN INSERT INTO tablename VALUES (in_val1, in_val2, in_val3); END IF; how to write this in string insertQry for java code above. — Arpan Paliwal
– Arpan Paliwal, Commented Dec 11, 2017 at 8:38

AxelH · Accepted Answer · 2017-12-11 08:47:45Z

1

The simplest solution in JAVA is to check if the row exist.

You start by getting a row count for the specific id you want to insert/update

select count('a') as rowExist from table where id = ?

Then, based on the result, you can easily create your query

if(rowExist > 0){
    query = "update ..";
else
    query = "insert ...";

Note that the parameters are probably not in the same order as you expect, you need to create the insert in the correct order to have the id at the end (since update need a where clause)

insert into Table (name, birthday, id) values (?, ?, ?)
update Table set name = ?, birthday = ? where id = ?

answered Dec 11, 2017 at 8:47

AxelH

14.6k2 gold badges27 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Arpan Paliwal Over a year ago

For that two sql are needed, i want it in single sql, it is not possible in ORACLE ?

AxelH Over a year ago

@ArpanPaliwal It is possible if you use a stored procedure that you called from Java using a Callable. For that, check the duplicate. There is no need to rewrite the solution here, since everything will be done in a store proc on the DB. You just need to create a Callable to call that procedure.

Arpan Paliwal Over a year ago

can't use store proc for a small change thanks for above solution.

leonardo12 Over a year ago

With this solution you have to think about transaction handling. Otherwise multiple threads may insert duplicates or run into exceptions if a matching constraint exists.

Ayush Kumar Over a year ago

Isn't having a single MERGE statement that handles both better than having two hits on database?

|

leonardo12 · Accepted Answer · 2017-12-19 16:40:50Z

0

It is possible to run a database statement as questioned. Simply use SQL command MERGE INTO... IF NOT MATCHED INSERT... IF MATCHED UPDATE ... You will find an full example and documentation here.

edited Dec 19, 2017 at 16:40

answered Dec 19, 2017 at 16:26

leonardo12

3981 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to write "INSERT IF EXISTS UPDATE" in Oracle using JAVA

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related