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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
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?

Improve this answer

1 Comment

HSQL is only example. I look for universal solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.