ID of inserted record

[Luis Filipe Neves]

    Hello all,

    I just discovered the safeJDBC project and I like it already.
    Normally, to encapsulate my JDBC code I use a variation of the code
    found in this article:
    <http://www.javaworld.com/javaworld/jw-05-1999/jw-05-cooltools_p.html>

    But safeJDBC  is more complete and feature rich and I will probably
    start using it. I have on question though, what is the idiom for getting
    the ID of a "just inserted record"?
    For example, lets say I have an "Orders" table and a "OrdersDetails"
    table. Using safeJDBC how can I accomplish this:

        - Insert a record in Orders table
        - Get the OrderID of the newly inserted record
        - For each item on the order insert a record in OrderDetails
        with the above OrderID

    I always have problems doing this because each database as it's own
    quirks.

    In PostgreSQL it's something like this:

        <snip>

        connection.setAutoCommit(false);

        connection.prepareStatement("INSERT INTO Orders ...")
        stm.execute();
        connection.prepareStatement("select currval('orders_orderid_key');");

        ResultSet rs = stm.executeQuery();
        rs.next();
        int orderID = rs.getInt(1);

        connection.setSQL("INSERT INTO OrderDetails (orderid, .....");
        connection.executeUpdate();
        connection.commit();

        </snip>

    But the code for MS SQL Server is something like:

        <snip>

        connection.setAutoCommit(false);

        connection.prepareStatement("INSERT INTO Orders ...")
        stm.execute();
        int nUpdateCount = data.getUpdateCount();
        boolean bMore = data.getMoreResults();
        if (bMore)
        {
            ResultSet rs = data.getResultSet();
            rs.next();
            orderID = rs.getInt(1);
        }
        connection.setSQL("INSERT INTO OrderDetails (orderid, .....");
        connection.executeUpdate();
        connection.commit();

        </snip>

    I don't know of safeJDBC has a standard way of doing this, but if
    doesn't have can I add a feature request? :-)

    Thanks for all your work, its much appreciated.

    Best regards,

    Luis Neves

[Jan Hermanns]

Hello Luis,

to cut a long story short there is no way generic way to integrate this feature, because every DB has it's own way to retrieve automaticlly generated PKs (as you mentioned). And as far as I know there is no way to get this information via JDBC.

So the only way is to define an abstract interface to provide those methods. But I'm not sure how such an interface should look like.

Any  suggestions?

regards, Jan