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)

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.

Answers


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.


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


Need Your Help

How do I efficiently search if a user has been reported on?

ruby-on-rails ruby

How do I determine if the attribute value of one model exists in the attribute value of another?

mySQL database folder backup script

mysql mysqldump database-backups

We're backing up our windows based mySQL databases over night by copying the entire mySQL database directory:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.