Insert or update table using JDBC

Question

I have some records to import. It's ok the first time when they are being inserted. If I try to import the same data again I receive a org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint. How can I update the records in the database if the data is the same/or changed and insert if it's new data using JDBC?

public void store(Object entity) throws Exception {
    try {
        if (this.updateEntity((XEntity) entity) == 0) {
            this.insertEntity((XEntity) entity);
        }

        ...

    } catch (SQLException sqlEx) {
        ...
    }
}

private int updateEntity(XEntity entity) throws SQLException {
    PreparedStatement prepStmt = this.getUpdatePreparedStmt();
    ...
    return prepStmt.executeUpdate();
}

private void insertEntity(XEntity entity) throws SQLException {
    ...
    this.getInsertPreparedStmt().executeUpdate();
}

The problem is fixed now. I've provided an answer below.

You seem to be in the fairly early stages of your implementation. Have you considered using JPA or Hibernate instead to handle persistence? Are there any particular requirements that necessitates low-level JDBC-access? — Kim Burgaard, Dec 07 '10 at 07:56
You should figured out why "this.updateEntity((XEntity) entity) == 0". — pinichi, Dec 07 '10 at 08:00
XEntity is my data that is imported. I'm modifying a project that is done with JDBC, so I can't use Hibernate. — Seitaridis, Dec 07 '10 at 08:02
Data will be imported and for each record a decision should be made to insert or update into my table — Seitaridis, Dec 07 '10 at 08:07
the code looks like it could work. So the problem lies probably in your table contraints and/or the inserted data. — rompetroll, Dec 07 '10 at 10:28

score 1 · Answer 1 · answered Dec 07 '10 at 07:54

1

You can try using postgres SQL 'MERGE' or 'REPLACE'

answered Dec 07 '10 at 07:54

mauretto

3,183
3
27
28

Yes, just execute the merge or replace query through JDBC (maybe in your updateEntity method?) – mauretto Dec 07 '10 at 08:09

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

You can pass the UPDATE command as a string through JDBC.

According to this SO post, you will have to write 2 statements.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 07 '10 at 08:10

npinti

51,780
5
72
96

Is there a way to detect that it is a update or insert. Or can I try to update and it it fails on the catch exception block insert a record. Is it a recommended approach? – Seitaridis Dec 07 '10 at 08:41

score 0 · Answer 3 · answered Dec 07 '10 at 10:20

If you want to use the same method to insert and update your data, you'll need to check if the data exists first. The SQL command used to insert a new object is INSERT, whereas the one used to update an element is UPDATE. So, what you could do is do a SELECT to check if your data is already here, and then do an INSERT or UPDATE based on the results.

However, this is a workaround. You would really need to clarify your implementation, and make different methods whether you are adding or updating data. Business-side, these are clearly two very different functions, so one method for both seems to me like a design problem.

score 0 · Accepted Answer · answered Dec 07 '10 at 11:57

This test logic works.

if (this.updateEntity((XEntity) entity) == 0) {
    this.insertEntity((XEntity) entity);
}

The problem was in the method that updated the record. The WHERE clause in the update prepared statement was using different data(data containing spaces) so updateEntity would always return 0. That was the reason why only inserts were made, instead of updates. Thank you very much for your help.

Insert or update table using JDBC

4 Answers4