Error with returning value using Java to perform Upsert in PostgreSQL

Question

I have seen several post about this topic (post1,post2,post3). In all of them the solution looks very easy, but I'm not able to get the value of the returning value.

This is what I have tried so far:

Option 1:

int affectedRows =pstmt.executeUpdate();    
    ResultSet rs = pstmt.getResultSet(); 
    if (rs.next())
        updated = rs.getBoolean(1);

Result: java.lang.NullPointerException cause by if (rs.next())

Option 2:

ResultSet rs =pstmt.executeQuery(); 
    if (rs.next())
        {updated = rs.getBoolean(1);}

Result: org.postgresql.util.PSQLException: No results were returned by the query

Option 3:

boolean hasResult =pstmt.execute(); 
logger.info("hasResult: " + hasResult);
//if (hasResult) {
    ResultSet rs = pstmt.getResultSet(); 
    if (rs.next())
        updated = rs.getBoolean(1);             
//}
logger.info("updated: " + updated);

Result: java.lang.NullPointerException cause by if (rs.next()) also notice the hasResult: false

The query created into the Java code looks like the following:

INSERT INTO public."tablename"( "field1", "field2", ...,"field n")
VALUES('value1','value2',...,'value n') 
ON CONFLICT ("UniqueID") 
DO UPDATE SET "field1" = 'value3' ,..., "Updated"=true 
RETURNING "Updated"

If I execute the query using, for example, pgadmin, I see the RETURNING "Updated" value

Note: In each option the query is executed without problem, but I can not get the "Updated" value

This are the maven dependency:

<dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.12</version>
</dependency>
<dependency>
        <groupId>com.google.cloud.sql</groupId>
        <artifactId>postgres-socket-factory</artifactId>
        <version>1.0.15</version>
</dependency>

And the connection string:

jdbc:postgresql://google/postgres?cloudSqlInstance=<...>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<...>&password=<...>

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Apr 09 '20 at 17:59
Hi, @a_horse_with_no_name I have added the dependency I'm currently using to the main post — IoT user, Apr 14 '20 at 07:32
That's not the (native) Postgres driver, so maybe that's the problem. — , Apr 14 '20 at 07:33

score 2 · Accepted Answer · answered Apr 14 '20 at 13:54

The problem was here:

PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);

When I created the PreparedStatement pstmt object I used Statement.RETURN_GENERATED_KEYS

The use of Statement.RETURN_GENERATED_KEYS constant is to used with getGeneratedKeys to get the auto-generated keys of all rows created by that execution.

Like I want an specific returned element I should use:

PreparedStatement pstmt = conn.prepareStatement(SQL);

score 0 · Answer 2 · answered Apr 09 '20 at 15:26

0

Try something like ResultSet rs=stmt.executeQuery(sql).
Cannot see where sql is passed.
or
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet result = statement.executeQuery();

answered Apr 09 '20 at 15:26

Traian GEICU

1,750
3
14
26

Error with returning value using Java to perform Upsert in PostgreSQL

2 Answers2