JDBC returning empty result set

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)

开发者_JAVA技巧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.

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.

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.

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.

In java.sql.connection you should call this method after you create your connection :

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

maybe there is a similar method in Oracle.

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.

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.

I then double checked the query (copied straight from debugger) on the database to make sure it returns data.

I've had engineers with this problem demonstrate this verification in front of me. It turns out they were logged in with one database account in the program and with a different database account in the interactive SQL shell. [This was Oracle 8.]

Yeah, I had the same problem as the OP. It happens when you have two or more open connections with the database on the same user. For example one connection in SQL Developer and one connection in Java. The result is always an empty resultset.

EDIT: Also, I noticed that it happens when you execute procedure or insert in the databse and you don't commit your transactions.

Please check whether the connection and statement Object alive until you iterate the result set, some times we may close unknowingly.

It might be a condition that your table is not committed . Try inserting new records, then commit in your SQL Run Command Window and run your code.

I had logged in to server using plsql client and while I tried to connect from my code.

I was successfully connecting but there were no records in the result-set.

Only after logging out from the server the result-set was populated.

I agree with @dpsthree, Oracle should provide appropriate error/warning message.

For me the problem was that on the creation of the primary key column I had NOT NULL ENABLE. As in ...

CREATE TABLE SYSTEM_SETTINGS  ( 
  SYSTEM_SETTING_ID NUMBER(9,0) NOT NULL ENABLE, 
    "KEY" VARCHAR2(50 BYTE), 
    "VALUE" VARCHAR2(128 BYTE),
     CONSTRAINT "PK_SYSTEM_SETTINGS" PRIMARY KEY (SYSTEM_SETTING_ID)) 
TABLESPACE USERS;

When I recreated the table without that as in

CREATE TABLE SYSTEM_SETTINGS  ( 
  SYSTEM_SETTING_ID NUMBER(9,0), 
    "KEY" VARCHAR2(50 BYTE), 
    "VALUE" VARCHAR2(128 BYTE),
     CONSTRAINT "PK_SYSTEM_SETTINGS" PRIMARY KEY (SYSTEM_SETTING_ID)) 
TABLESPACE USERS;

It started working via JDBC. I am using ojdbc6.jar for the jdbc driver.

The resultSet returns false even though you should get row values for the query and rs.next() gives false because you might have not written commit; on your sql terminal for the query. After doing this you will get rs.next() as True.

This is because the data entered will not be save in the table. Just commit the table once the values are inserted in the tables. This acts similar to save option in our file system.

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.

in my case the query which worked in sql developer didn't work in JAVA.

*select * from table where process_date like '2014-08-06%'* (worked in sql developer)

formating process_date to char helped to make it work in JAVA

*select * from table where to_char(process_date) = '06-AUG-14'*