JDBC returning empty result set

Question

I'm using JDBC for very simple database connectivity.

I have created my connection/statement and executed a query. I check the query object of the statement in the debugger to confirm that it is sending a proper query. I then double checked the query (copied straight from debugger) on the database to make sure it returns data. The returned resultset, however, gives false on .next()

Are there any common pitfalls here that I'm missing?

public List<InterestGroup> getGroups() {
    myDB.sendQuery("select distinct group_name From group_members where
            username='" + this.username + "'");
    ResultSet results = myDB.getResults();
    List<InterestGroup> returnList = new ArrayList<InterestGroup>();
    try {
        while (results.next()) {
            returnList.add(new InterestGroup(results.getString("group_name"), myDB));
        } 
        return returnList;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }

}

And the myDB class (simple wrapper that lets me drop the connection/statement code into any project)

public void sendQuery(String query){
    this.query = query;
    try {
        if(statement == null){
            statement = connection.createStatement();
        }
        results = statement.executeQuery(query);
    } catch (SQLException e) {
        System.out.println(query);
        currentError = e;
        results = null;
        printError(e, "querying");
    }

}

public ResultSet getResults(){
    return results;
}

EDIT: Based on suggestions I have mostly revamped my code but still have the same problem. Below is a simplified portion of code that has the same problem.

private boolean attemptLogin(String uName, String pWord) {

    ResultSet results;
    try{
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        connection =DriverManager.getConnection(connectionString,user,password);
        PreparedStatement statement = connection.prepareStatement("select username from users where username='testuser'");
        results = statement.executeQuery();
        if(results != null && results.next()){
            System.out.println("found a result");
            statement.close();
            return true;
        }
        System.out.println("did not find a result");
        statement.close();
        return false;
    }catch(SQLException e){
        e.printStackTrace();
        return false;
    }

}

I have also hardcoded the query in place for now to eliminate that source of error. Same problem as before (this happens with all queries). Debugger shows all objects getting instantiated and no stack traces are printed. Furthermore, I am able to use the same code (and the more complicated code listed previously) in a different project.

Solution 1

I figured it out....stupid Oracle didn't like the number of concurrent connections I had (all two of them, one for console, one for java). Unfortunately, the server is not under my control so I will just have to deal with it. You would think that Oracle would provide a better response. Instead it just returned empty result sets.

Thanks for the responses

edit Since this was asked/answered there have been a number of people pointed out that the underlying cause is more likely related to the commit/transaction settings in use. Please be sure to see other answers for additional hints and possible solutions.

Solution 2

I see a few pitfalls in your code, there are a few places where things can go wrong:

First, use of regular statements. Use prepared statements so you won't have problems with SQL injection.

Instead of

statement = connection.createStatement();

use

statement = connection.prepareStatement(String sql);

With this, your query becomes

"select distinct group_name From group_members where username= ?"

and you set username with

 statement.setString(1, username);

Next, I don't like use of your myDB class. What if results is null? You're not doing any error checking for that in your public List<InterestGroup> getGroups() method.

public void sendQuery(String query) seems to me like it shouldn't be void, but it should return a ResultSet. Also, search on the net for proper ways to do JDBC exception handling.

Also, this line:

new InterestGroup(results.getString("group_name"), myDB)

Why do you have myDB as a parameter?

I'd suggest adding more System.out.println statements in your code so you can see where things can go wrong.

Solution 3

The same happened to me. I was using SQL Developer to insert test data into my database and test-reading that using JDBC. But all I got was an empty result-set. I could get the column names and all, but had a problem with reading data. As pointed out by dpsthree earlier, I disconnected from the SQL Developer IDE and then it asked me to Commit upon exiting.

Voila! The problem was that the changes to the databases using the insert command weren't committed.

For SQL Developer this is located at Preferences > Database > Advanced > Autocommit

This solved my problem.

Solution 4

The most common is having multiple statements in the query:

desc table;
select * from sometable where etc.;

For statements which do not return results, you have to use a different construct. Even this will cause the client to choke:

select * from sometable where whatever;
select * from sometable where something else;

The two same-shaped result sets will biff the client.

Solution 5

In the past I had similar issues in code such as this:

querystr = "your sql select query string"

resultset = statement.executeQuery(querystr)

while (resultset.next())
{
//do something with the data. 
//if you do something fairly involved in this block (sequentially, in the same thread)
//such as calling a function that acts on the data returned from the resultset etc.
//it causes the resultset fetch to wait long enough for resultset.next() to 
//unexpectedly return null in the middle of everything
}

What I did in this situation was to load up all data into a local memory data structure with minimum wait on resultset.next(). Then I did whatever I had to on the data from the local data structure after gracefully closing resultset. This behavior was with Oracle 10 on Unix backend/JDK 1.6.0_22 client under Windows XP.

Hope this helps.

View more solutions

Comments

[craftsman]

"one for console, one for java", sounds strange!

[dpsthree]

Yea, I was ssh'ed into the host machine to run queries directly for testing. And when I ran my program it created another connection. Sorry if my wording sounded strange. I was getting very sleepy when dealing with this last night.

[fleetC0m]

I stumbled onto the same issue four years later. Big thanks to you, and yeah stupid Oracle.

[pringi]

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review