CODEWITHSUNDEEP
javajdbcresultset
user3337629
user3337629

Reputation: 189

ResultSet is empty, but it shouldn't be

There must be something extremely subtle that I am missing.

I have a class called LogRecord. It has a members String _pk1 and String _userID. I get _pk1 from a log file, then I use _pk1 to query a database and look up _userID.

The code that I am using to achieve this:

private void setUserID() throws Exception
{
    String userID = "";
    if(_pk1.equalsIgnoreCase(""))
    {
        _userID = "";
        return;
    }
    java.sql.Statement statement = _connection.createStatement();

    String userIDQuery = "select user_id from users where pk1 = '" + _pk1.trim() + "'";

    java.sql.ResultSet userIDRS = statement.executeQuery(userIDQuery);
    query = userIDQuery; //edit

    if(userIDRS.next())
    {
        userID = userIDRS.getString("user_id");
        userIDRS.close();
    }
    else
    {
        userID = "query failed";
    }

    _userID = userID;
    userIDRS.close();

    statement.close();
}

In practice, I have an ArrayList of these LogRecords and they are bound to a TableView JavaFX control. I can see that this query is only returning a result about 25% of the time. (I set the _userID to query failed in the case where _pk1 was not blank so that I could identify cases where the query should have returned a userID.

To further debug, I added this code to the TableView's click() method so that I could examine the suspicious records.

private void tableRightClick(MouseEvent event) {
    LogRecord selectedLogRecord = tblRecords.getSelectionModel().getSelectedItem();
    //System.out.println(selectedLogRecord.getLogRecord());
    System.out.println("User's PK1:    " + selectedLogRecord.getPK1());
    System.out.println("Username:      " + selectedLogRecord.getUserID());
    System.out.println("User query:    " + selectedLogRecord.query);
}

I can tell from the output that _pk1 is indeed being captured correctly and that the query being executed is correct. (And, as I mentioned, it works about 25% of the time.)

An example of the console output when I click the suspicious record:

User's PK1:    459498
Username:      query failed
User query:    select user_id from users where pk1 = '459498'

If I copy the query and paste it into Microsoft SQL Server Management Studio, it returns the correct userID. (Thus, I should be getting something in the ResultSet.)

I hope that somebody can explain why I am getting empty resultsets most of the time.

I am using sqljdbc4, JDK 8, and SQLServer 2008 R2.

Upvotes: 1

Views: 731

Answers (2)

vbail
vbail

Reputation: 364

Use a preparedStatement and bind the parameters correctly. The error can be produced because of the type of the column pk1.

Look which type of column is pk1 and use the pertinent set.

    String userIDQuery = "select user_id from users where pk1 = ?";
    java.sql.PreparedStatement statement = _connection.prepareStatement(userIDQuery);
    statement.setString(1, _pk1.trim());

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109547

The original code closes twice, and the exception handling is suspicious. On an exception no close is done. Try the following.

private void setUserID() throws Exception {
    if (_pk1.equalsIgnoreCase("")) {
        _userID = "";
        return;
    }
    String userIDQuery = "select user_id from users where pk1 = ?";
    try (PreparedStatement statement = _connection.prepareStatement(userIDQuery)) {
        statement.setString(1, _pk1);
        try (ResultSet userIDRS = statement.executeQuery()) {
            String userID;
            if (userIDRS.next()) {
                userID = userIDRS.getString("user_id");
                //query = userIDQuery + " | " + _pk1;
            } else {
                userID = "query failed";
            }
            _userID = userID;
        } // userIDRS.close();
    } // statement.close();
}

Maybe catch and rethrow the exception, logging it.

Upvotes: 0

Related Questions