Search code examples
javapostgresqlgoogle-cloud-sql

Error with returning value using Java to perform Upsert in PostgreSQL

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

pgadmin

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=<...>
Solution

  • 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);