I have a method with this code in which I try to get the maximum id value:

  try (Connection connection = DriverManager.getConnection(dbUrl)) { try (Statement statement = connection.createStatement()) { ResultSet resultSet = statement.executeQuery("SELECT last_insert_rowid();"); if (resultSet.next()) { return resultSet.getInt(COLUMN_ID); } else { return -1; } } } catch (SQLException e) { throw new ImageReadingException(e); }

However, it’s such an error:

 Caused by: java.sql.SQLException: no such column: 'id' at org.sqlite.jdbc3.JDBC3ResultSet.findColumn(JDBC3ResultSet.java:48) at org.sqlite.jdbc3.JDBC3ResultSet.getInt(JDBC3ResultSet.java:395) at com.dugin.rostislav.database.sqlite.SQLiteDB.getLastInsertId(SQLiteDB.java:124) ... 40 more

However, there is definitely a column (screenshot from SQLite Studio), and even with direct sampling - a similar answer!

enter image description here

What is the error and how to fix it?

  • one
    try to specify from which table - Valera Kvip
  • @ValeraKvip, the same error ... - user189127
  • I swear at this line: resultSet.getInt(COLUMN_ID) - MaxU
  • @ Maxu, uh huh .. :) - user189127
  • one
    Well, your column is not called 'id'. give it a normal type name: SELECT last_insert_rowid() as ID then it will be called that - Mike

1 answer 1

Sorry, maybe my answer is not in the subject, because not good at driversmanagers, etc.

1) But why not just try?

 SELECT `id` FROM `images` ORDER BY `id` DESC LIMIT 1;

2) from Interface ResultSet and JDBC Guide v.1

Try adding a table from which you select in the query, as noted earlier by Valera Kvip

and then (or rather)

 ResultSet resultSet = statement.executeQuery("SELECT `id` FROM `images` ORDER BY `id` DESC LIMIT 1"); while (resultSet.next()) { // Возврат строки return resultSet.getInt("id"); } return -1;
  • Thanks, it helped. And “I just didn’t try it” because I don’t know SQL and wrote code with copy-paste :)). But thanks a lot, it really worked! - user189127
  • @ bukashka101 I hope you understand that this answer suggests taking the maximum id from the database. And it may be that the maximum id was inserted by someone else at the time of its receipt. Here, for example, Google suggests using the built-in JDBC method stmt.getGeneratedKeys() from the item that produced the insert - Mike
  • @Mike, well ... There is a problem here: I insert it through the same DAO, and, respectively, statement . But in this case, I made the add method synchronized, and I return the id as the result of the add method. - user189127 September
  • @ bukashka101 for one @Mike is right! Although it seems to me that the id will be inserted in order because auto_increment , while if some records are deleted, you still get the id of the last one inserted. But there is a problem, if the last record is inserted, and then it is deleted, then with such a request the id last but one record will be returned. And not the last one inserted. - Yeldar Kossynbay