CODEWITHSUNDEEP
javahsqldb
Paul
Paul

Reputation: 1441

GetgeneratedKeys not working although row inserted

I use HSQLDB - Tried version 2.3.4 and 2.4.

I have this Java code:

            String sqlquery = "MERGE INTO bewertung pu USING "
            .concat("(VALUES ?,?,?) ")
            .concat("temp (tid,jurorid,runde) ")
            .concat("ON temp.tid = pu.tid and temp.jurorid=pu.jurorid and temp.runde=pu.runde ")
            .concat("WHEN NOT MATCHED THEN ")
            .concat("INSERT (tid,jurorid,runde) ")
            .concat("VALUES (temp.tid,temp.jurorid,temp.runde)");

        PreparedStatement preparedStatement = sql.getConnection().prepareStatement(sqlquery,
                                      Statement.RETURN_GENERATED_KEYS);            


        preparedStatement.setInt(1, eineBewertung.getTanzID()); //TID
        preparedStatement.setInt(2, eineBewertung.getJurorID()); //JURORID
        preparedStatement.setInt(3, eineBewertung.getRunde()); //RUNDE

        int rowsAffected = preparedStatement.executeUpdate();           

        if (rowsAffected == 0) {
            //UPDATE
            //DO SOMETHING
        }else{
            //INSERT
            try (ResultSet rs = preparedStatement.getGeneratedKeys()) {
               if (rs.next()) {      
                   eineBewertung.setBewertungsid(rs.getInt(1));
               }
            }catch (SQLException ex) {
                this.controller.error_ausgeben(ex);
            }
        }

It works. If I insert a new row I get rowsAffected = 1. I check the database and the insert worked.

But, I do not get anything back in the resultset getGeneratedKeys() It is every time empty.

I have found some tips to replace Statement.RETURN_GENERATED_KEYS with the primary key. But this didn`t work for me.

    PreparedStatement preparedStatement = sql.getConnection().prepareStatement(sqlquery,
                                  new String[]{"BEWERTUNGSID"});

This is how I create the table:

            statement.execute("create table PUBLIC.BEWERTUNG"
                .concat("(BEWERTUNGSID INTEGER IDENTITY,")
                .concat("TID INTEGER FOREIGN KEY REFERENCES Tanz(Tanzid),")
                .concat("JURORID INTEGER not null FOREIGN KEY References JUROR(JURORID),")  
                .concat("RUNDE INTEGER not null,")
                .concat("primary key(BEWERTUNGSID)")
                .concat(")"));

Why do I not get any generated keys back? Thank you

//EDIT If I replace my sqlquery with an insert statement it is working.

            sqlquery = "INSERT INTO BEWERTUNG(TID, JURORID, RUNDE) VALUES(22, 2, 2)";

Why is merge not working in the sqlquery?

Upvotes: 1

Views: 520

Answers (1)

fredt
fredt

Reputation: 24352

With versions of HSQLDB up to 2.4.0, generated keys are not available when inserting data using a MERGE statement. Code has been committed to allow this in the next version.

Upvotes: 1

Related Questions