I have a problem in JDBC driver for SQLite.
I am executing a query with SELECT statement.
If I get an empty ResultSet
(0 rows) then I see a "Closed ResultSet" exception thrown when calling getString(1)
.
Without much prior JDBC experience, my theory (which I could not confirm via JavaDocs for ResultSet
) is that
getString(1)
does NOT work on an empty (zero-row) resultset (by design or due to a bug)ResultSet
's "open" flag is set to false
on zero rows (again, by design or a bug)I saw this bug report but am not sure if it's related.
My qeustions are:
ResultSet
in all JDBC drivers?For #4, my solution was to use isFirst()
call right after executeQuery()
to check whether any rows are there in result set. Is this the best practice approach?
(I could also have simply selected a count insetad since I didn't really need a result set, merely zero-nonzero flag, but I want to know the correct thingh to do if I did care about select's results)
Thanks!
Empty or not, but doing the following is always faulty:
resultSet = statement.executeQuery(sql);
string = resultSet.getString(1); // Epic fail. The cursor isn't set yet.
This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet's cursor using next()
before being able to access any data.
If you're actually interested whether the supposedly unique row exist or not, then just check the outcome of next()
. For example in a fictive UserDAO
class:
public boolean exist(String username, String password) throws SQLException {
boolean exist = false;
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
) {
statement.setString(1, username);
statement.setString(2, password);
try (ResultSet resultSet = statement.executeQuery()) {
exist = resultSet.next();
}
}
return exist;
}
If you actually expect only zero or one row, then just do something like:
public User find(String username, String password) throws SQLException {
User user = null;
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user WHERE username = ? AND password = MD5(?)");
) {
statement.setString(1, username);
statement.setString(2, password);
try (resultSet = statement.executeQuery()) {
if (resultSet.next()) {
user = new User(
resultSet.getLong("id"),
resultSet.getString("username"),
resultSet.getString("email"),
resultSet.getDate("birthdate"));
}
}
}
return user;
}
and then just handle it accordingly in the business/domain object, e.g.
User user = userDAO.find(username, password);
if (user != null) {
// Login?
}
else {
// Show error?
}
If you actually expect only zero or many rows, then just do something like:
public List<User> list() throws SQLException {
List<User> users = new ArrayList<User>();
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user");
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
users.add(new User(
resultSet.getLong("id"),
resultSet.getString("username"),
resultSet.getString("email"),
resultSet.getDate("birthdate")));
}
}
return users;
}
and then just handle it accordingly in the business/domain object, e.g.
List<User> users = userDAO.list();
if (!users.isEmpty()) {
int count = users.size();
// ...
}
else {
// Help, no users?
}