3

I want to get auto increment id of inserted row. I know that there is a lot of examples how to do that:

link1 link2

But I use HSQL 1.8.0.10 and following code:

PreparedStatement ps = conn.prepareStatement("insert into dupa (v1) values(3)", Statement.RETURN_GENERATED_KEYS);

throws expection:

java.sql.SQLException: This function is not supported

How to get id if driver does not support the above solution. Is any other way to get auto increment key of inserted row? I want to handle as much as possible drivers. So want to use obove code in try section and use another way in catch section.

Second question: Is possible that database does not support this feature. So even if I use new driver and old database It will still not work? I tried to use hsql 2.3.2 driver but I can not to connect to 1.8.0.10 database.

Community
  • 1
  • 1
Mariusz
  • 1,907
  • 3
  • 24
  • 39

2 Answers2

2

The following code illustrates how to retrieve generated keys from HSQLDB 2.2.9 and later using the included JDBC 4 driver. This method returns a two element long[]. The first element contains the number of rows that were updated; the second contains the generated key if any:

    static final long[] doUpdate( ... ) {

        final long[] k = new long[] {0, KEY_UNDEFINED};
        PreparedStatement ps = null;

        try {
            ps = CXN.get().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            JdbcValue jv;
            for (int i = 0; i < data.size(); i++) {
                jv = data.get(i);
                jv.type().setJdbcParameter(i + 1, ps, jv);
            }

            k[NUM_OF_ROWS] = (long) ps.executeUpdate();

            if (k[NUM_OF_ROWS] > 0L) {
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    final String identColName = idCol.colName();
                    while (rs.next()) {
                        if (k[ROW_CREATED] != KEY_UNDEFINED) throw new AssertionError();
                        k[ROW_CREATED] = rs.getLong(identColName);
                    }
                }
            }

        } catch (SQLException e) { ... }

        finally {
            try { if (ps != null) ps.close(); } catch (SQLException e) { }
        }

        return k;
    }

I am unable to say whether this approach will work with old versions of HSQLDB.

scottb
  • 9,908
  • 3
  • 40
  • 56
  • I know how to retrieve generated key if driver supports this feature and I wrote it in my question. My question is: How to retrieve genrated key if driver (or database, I am not sure which is reponsible for this feature) does not support `prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)`. As i wrote in my question this code throws exception in my case. Maybe if JDBC drivers are backward compatible (in another words I can connect to old database using new driver) and all DBMS can return generated key, I only have to use JDBC 4 driver. But I don't know it. – Mariusz Dec 09 '14 at 14:21
0

You will have to use some vendor-specific solution, i.e. in mysql you would call LAST_INSERT_ID function.

I don't have valid installation of HSQL to test it, but you can give a try to the highest voted solution from this topic: how to return last inserted (auto incremented) row id in HSQL?

Community
  • 1
  • 1
Mariusz Sakowski
  • 3,232
  • 14
  • 21