How can I use last insert id SQL from Java?

Question

Here is my code:

String sql="INSERT INTO ventas(prefijo,fecha,hora,vtanum,vlrfactura,cambio) VALUES(?,?,?,?,?,?);";
try {
    ps=con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, vta.getPrefijo());
    ps.setDate(2, vta.getFecha());
    ps.setTime(3, vta.getHora());
    ps.setInt(4, vta.getVtanum());
    ps.setInt(5,vta.getVlrfactura());
    ps.setInt(6,vta.getCambio());
    ps.execute();
    return true;
} catch (SQLException ex) {
    Logger.getLogger(OperacionesBD.class.getName()).log(Level.SEVERE, null, ex);
    return false;
}

I want get the PK of that sentence and then use it in another statement like this:

SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO pagmundimotos.pagos(id_venta,frmpag) VALUES (@last_id_in_table1, '9'); 

But I don't know how to obtain the last_insert_id in Java.

Answer 1

Use ResultSet object to fetch the GeneratedKeys on Statement

ResultSet rs = ps.getGeneratedKeys();

if (rs.next()) {
    long id = rs.getLong(1);
    System.out.println("Inserted ID -" + id); // display inserted record
}

For Oracle :

String key[] = {"ID"}; //put the name of the primary key column

ps = con.prepareStatement(insertQuery, key);
ps.executeUpdate();

rs = ps.getGeneratedKeys();
if (rs.next()) {
    generatedKey = rs.getLong(1);
}