Search code examples
javasqlitejdbcresultset

How do I check for value existence / why is ResultSet closed in SQLite?

Setup

I am using JDBC SQLite for my server database.

What I am trying to do

I want to check if a value (for example an ID or a username) already exists in a table.

My Code

PreparedStatement preparedStatement = connection.prepareStatement("select * from " + tableName + " where " + columnName + "=? limit 1");
preparedStatement.setString(1, value);
ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
try { // this was just for testing and is dumb because it prints an error every time the value does not exist.
      // my code actually wasn't wrong. I just tested it badly .. 
      // it's replacable with if (resultSet.next())
    resultSet.getString(1);
    System.out.println("check true");
    return true;
} catch (Exception e) {
    e.printStackTrace();
    System.out.println("check false");
    return false;
}

Here I want to check if a specific value already exists in my column(Name) in my table.

The Problems

This code will always throw a SQLException: "ResultSet closed" at resultSet.getString(columnName). It does not matter if the value already exists in the table or not. My query could also be wrong because I don't get what I should put after select. I had a 1 there once, but everyone was using * for "all" and it changed nothing. I seriously don't get this because it is too complicated for me and I just wanted to know what the argument after select does..

Approaches

I tried many things and read a lot of threads, but the only thing I came across was that I need to first call resultSet.next() and that the ResultSet won't throw ResultSet closed exception that way, but it didn't change anything. If there is a better way to check if a value already exists in a table, please tell me.

Visualisation

a table I have a table here (the NULL's don't matter they could be some random values) and I want to check ggffvgvg exists or not.

Complete StackTrace

java.sql.SQLException: ResultSet closed
at org.sqlite.core.CoreResultSet.checkOpen(CoreResultSet.java:69)
at org.sqlite.core.CoreResultSet.markCol(CoreResultSet.java:96)
at org.sqlite.jdbc3.JDBC3ResultSet.getString(JDBC3ResultSet.java:436)
at Database.check(Database.java:50)
at ClientHandler.run(ClientHandler.java:72)
Solution

  • resultSet.next() returns a boolean which tells you whether there is a next record or not. Ignoring this returned value is like shooting your own foot with a rocket launcher.

    So, if the value you are looking for is not in the result set, (either because it really isn't, or because you did not ask for it properly,) resultSet.next() will return false, and your code will blow up with the exception that you are receiving.

    Your resultSet.getString(1) clause will fetch the first column in the result set, (if there is a current row,) and it will return it to you assuming that it is in fact a string. The first column of most tables is usually an integer, or some other kind of key data type, which means that it is rarely a string. If you are lucky, it will be something that the JDBC driver can convert to a string, but you are tempting your fate by assuming that.

    If you are only going to check the value of a single column, then your query statement must select that column only. This means that instead of

    "select * from " + tableName + " where " + columnName + "=? limit 1"

    you must do

    "select " + columnName + " from " + tableName + " where " + columnName + "=? limit 1"

    However, if I understand correctly what you are trying to achieve, you do not even need to fetch the field and check its value. Simply the true or false result of resultSet.next() should suffice.