CODEWITHSUNDEEP
javasqljdbchsqldb
Jakob Nielsen
Jakob Nielsen

Reputation: 5198

How to get Statement.getGeneratedKeys working

I have a problem with the method getGeneratedKeys() of java.sql.Statement

First of all my code:

create.sql: (HSQLDB)

create table Ticket (
    ticketID  INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    persID INTEGER NOT NULL REFERENCES Mitarbeiter(persID),
    zeitpunkt TIMESTAMP(0) NOT NULL,
    betreff VARCHAR(100) NOT NULL,
    text VARCHAR(1000) NOT NULL,
    wichtig BOOLEAN NOT NULL,
    istGeloest BOOLEAN NOT NULL
);

The create Method from the DAO:

@Override
public Ticket create(Ticket t) throws PersistenceException {
    if( t == null ) {
        log.error("PersistenceTicket.create(Ticket t) wurde ein null parameter übergeben");
        throw new PersistenceException("TicketParameter ist null");
    }
    PreparedStatement pst = null;

    try {
        pst = con.prepareStatement("INSERT INTO ticket(persID, zeitpunkt, betreff, text, wichtig, istGeloest) VALUES (?,?,?,?,?,?)");

        pst.setInt(1, t.getPersID());
        pst.setTimestamp(2, t.getZeitpunkt());
        pst.setString(3, t.getBetreff());
        pst.setString(4, t.getText());
        pst.setBoolean(5, t.isWichtig());
        pst.setBoolean(6, t.istGeloest());

        int result = pst.executeUpdate();
        if( result > 1 ) {
            return null;
        }

        ResultSet rs = pst.getGeneratedKeys();
        if( rs.next() ) {
            t.setTicketID(rs.getInt(1));
        }
        else {
            log.debug("No keys generated");
        }
        log.info("Ticket mit Betreff " + t.getBetreff() + " in die Datenbank gespeichert" );

        pst.close();
    } catch (SQLException e) {
        log.error("Error beim create: " + e.getMessage());
    }
    return t;
}

Then I have a Unit Test testing the generated keys:

@Test
public void specialCreateTest() throws PersistenceException {
    Ticket a = this.persT.create(this.testTicket);
    Ticket b = this.persT.create(this.testTicket);
    log.debug(a.getTicketID() +  " < " +  b.getTicketID());
    assertTrue(a.getTicketID() < b.getTicketID());
}

When I run the test I get an Failure and the following logs:

DEBUG at.ac.tuwien.sepm.persistence.PersistenceTicketImpl  - No keys generated
DEBUG at.ac.tuwien.sepm.persistence.PersistenceTicketImpl  - No keys generated
DEBUG at.ac.tuwien.sepm.unittests.APersistenceTicketTest  - 0 < 0

So the problem ist that the .getGeneratedKeys() method returns an empty set, otherwise we wouldnt see the log hat no keys are generated

Upvotes: 0

Views: 1912

Answers (1)

user432
user432

Reputation: 3534

Change

pst = con.prepareStatement("INSERT INTO ticket(persID, zeitpunkt, betreff, text, wichtig, istGeloest) VALUES (?,?,?,?,?,?)");

to

pst = con.prepareStatement("INSERT INTO ticket(persID, zeitpunkt, betreff, text, wichtig, istGeloest) VALUES (?,?,?,?,?,?)", 
        Statement.RETURN_GENERATED_KEYS);

Otherwise the PreparedStatement does not return the keys.

Upvotes: 2

Related Questions