PreparedStatement.RETURN_GENERATED_KEYS and Mysql

Question

I am trying to fix this code to update my log table in a mysql database 5.6 Do you know how to retrieve the primary key from the database and assign it to my id variable (Log)

Thanks

Log model without getters and setters

private int id;
private Timestamp date;
private String userName;
private String event;
private String message;
private String audioPath;

Database Table

CREATE TABLE `log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00',
`user_name` char(60) NOT NULL,
`event` varchar(50) NOT NULL,
`message` text NOT NULL,
`audio_path` varchar(250) NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Add Entry Log

@Override
public int addEntry(Log log) throws SQLException {

    Connection conn = Database.getInstance().getConnection();

    PreparedStatement p = conn.prepareStatement(
            "insert into log ( id, date, user_name, event, message, audio_path) "
            + "values (?,?,?,?,?,?)", 
            PreparedStatement.RETURN_GENERATED_KEYS);

    p.setInt(1, p.getGeneratedKeys()); //error
    p.setTimestamp(2, log.getDate());
    p.setString(3, log.getUserName());
    p.setString(4, log.getEvent());
    p.setString(5, log.getMessage());
    p.setString(6, log.getAudioPath());

    int updated = p.executeUpdate();


    p.close();

    return updated;

}

score 3 · Accepted Answer · edited May 23 '17 at 12:21

Since id is declared as AUTO_INCREMENT you don't need to mention it in the query:

String sql = "insert into log ( date, user_name, event, message, audio_path) values (?,?,?,?,?)";

In this way MySQL will automatically generate new id.

Then, you should do this:

PreparedStatement ps = connection.prepareStatement(sql, RETURN_GENERATED_KEYS)
ps.setTimestamp(1, log.getDate());
// ...
ps.setString(5, log.getAudioPath());

int updated = ps.executeUpdate();

ResultSet generatedKeysResultSet = ps.getGeneratedKeys();
// first row of this set will contain generated keys
// in our case it will contain only one row and only one column - generated id
generatedKeysResultSet.next(); // executing next() method to navigate to first row of generated keys (like with any other result set)
long id = generatedKeysResultSet.getLong(1); // since our row contains only one column we won't miss with the column index :)

log.setId(id);

return updated;

Besides that, after you try and get working example I would really recommend to correctly close MySQL Connection and PreparedStatement using JDBC. Please refer to this answer on how to correctly create and close resources.

Hope this helps

Auto Key values generated are accessible after they are inserted successfully. Hence one should only call `getGeneratedKeys()` post `executeUpdate()`. — Ravinder Reddy, Sep 29 '14 at 10:29
@Yura Thanks a lot it did what I wanted. I had just to change the id from int to long. Should I change my database table accordingly? — QGA, Sep 29 '14 at 10:37
@QuentinTanioartino I'm glad it helped! I would say to use just `int` in database instead of `int(11)` unless you need special logic with digits like in following Q&A: http://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes — Yuriy Nakonechnyy, Sep 29 '14 at 12:11

PreparedStatement.RETURN_GENERATED_KEYS and Mysql

1 Answers1