Returning generated keys in MySql with JDBC PreparedStatement [duplicate]

Question

I'm programming with plain JDBC a DAO layer because I only have 61.38 MB on Java Memory in my Tomcat (service hosting). I have a table with an AUTO_INCREMENT column in MySQL. The current implementation has no problems. But I would like to know the value that was generated in the AUTO_INCREMENT column.

The current code looks as follows in the method that inserts a new row.

public Integer store(MyBean bean) throws DAOException {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = getConnection();
        ps = conn.prepareStatement("INSERT ...");
        if (bean.getSomeProperty() != null) {
            ps.setShort(1, bean.getSomeProperty());
        } else {
            ps.setNull(1, Types.SMALLINT);
        }

        /* Other fields */

        int rows = ps.executeUpdate();
        if (rows == 1) {
            // RETURN the generated value
        }
        return null;
    } catch (SQLException e) {
        throw new DAOException(e);
    } finally {
        ...
    }
}

I have seen that this is possible in Hibernate, but because I have little memory, is not a feasible option.

I appreciate the help.

Answer 1

There are two ways:

• Using the JDBC concept of getGeneratedKeys(). See http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html#connector-j-examples-autoincrement-getgeneratedkeys

• Or use the MySQL function LAST_INSERT_ID(). See http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-last-insert-id.html#connector-j-examples-autoincrement-select