CODEWITHSUNDEEP
javamysqlsqljdbc
cyrus
cyrus

Reputation: 51

Java SQL query problems, ResultSet doesn't contain anything

I am trying to do SQL with Java, this is my first time. I am trying to select all of a table and put it in a ResultSet, but the ResultSet seems empty. The same query on the MySQL client does show results, which leaves me wondering what is wrong with my code. It could either be the way the query is being done, or the way I am trying to get the data from the result set.

The query in the command line MySQL client...

mysql> SELECT * FROM accounts.sites;
+----------+-----------------------+--------------------------+--------------+----------------+
| username | domain                | directory                | fpm-template | nginx-template |
+----------+-----------------------+--------------------------+--------------+----------------+
| cyrus    | cyrustheg.org         | /var/www/sites/cyrustheg | standard     | drupal         |
| cyrus    | tornews.org           | /var/www/sites/tornews   | standard     | standard       |
| cyrus    | oletf.org             | /var/www/sites/oletf     | standard     | standard       |
| taf      | theabsolutefinest.org | /var/www/sites/taf       | standard     | wordpress      |
| taf      | bitsplit.org          | /var/www/sites/bitsplit  | bitsplit     | bitsplit       |
+----------+-----------------------+--------------------------+--------------+----------------+
5 rows in set (0.00 sec)

I am using this code to build a resultSet with that query...

private void read() throws Exception {
    try {  
        Class.forName("com.mysql.jdbc.Driver");

        String dburl = "jdbc:mysql://" + dbHost + "/" + dbName +
                        "?user=" + dbUser + "&password=" + dbPass;

        connect = DriverManager.getConnection(dburl);

        resultSet = statement.executeQuery("SELECT * from accounts.sites");
    } catch (Exception e) {
        throw e;
    }
}

This code iterates through the resultSet, looking for every domain for every user. I know the problem is either in the former code or this or the former code, because I've added a print statement to the while loop, and it is never called. This is why I conclude the resultSet is empty.

public HashSet<String> listSites(String user)
        throws Exception, ExcSiteNosites {
    HashSet<String> list = new HashSet<String>();
    boolean exists = false;

    try { 
        read();

        while (resultSet.next()) {
            if (resultSet.getString("username").equals(user)) {
                exists = true;
                list.add(resultSet.getString("domain"));
            }
        }

        if (!exists) {
            throw new ExcSiteNosites(user);
        }
    } catch (Exception e) {
        throw e;
    } finally {
        resultSet.close();
        statement.close();
        connect.close();
    }

    return list;
}

The entire class: http://pastebin.com/0DSbFey7 The unit test for that class: http://pastebin.com/9UYLEeMB

I found the bug running that unit test, which makes the following output...

Listing cyrus's sites...
Listing taf's sites...
java.lang.NullPointerException
    at database.Sites.listSites(Sites.java:96)
    at test.dbSites.listall(dbSites.java:28)
    at test.dbSites.main(dbSites.java:51)
java.lang.NullPointerException
    at database.Sites.listSites(Sites.java:96)
    at test.dbSites.listall(dbSites.java:28)
    at test.dbSites.main(dbSites.java:53)

The NullPointerException I think might be an unrelated problem. I've not done any Java in a while, and I've not worked that out either. Though the problem I want help with (unless related) is just that the ResultSet seems empty.

Upvotes: 1

Views: 888

Answers (2)

TA Nguyen
TA Nguyen

Reputation: 473

You need to create a statement from the connection before use it to executeQuery().... 

Statement statement = connection.createStatement();

Upvotes: 1

Florent Bayle
Florent Bayle

Reputation: 11890

You never initialize statement in your code, it's a private variable and you have no setter on it, so I think that you are not injecting it either.

So, when you call read(), you will have a NullPointerException on

resultSet = statement.executeQuery("SELECT * from accounts.sites");

this will be catched and thrown again by your catch.

So, in your listSites(), you will catch it, and throw it again, and in the finally, you will have another NullPointerException on

statement.close();

and it's the one in your stack trace.

Upvotes: 2

Related Questions