Jump to content

Sql Resultset Not Returning *solved*

Recommended Posts

Hi,I'm creating a simple class that queries an SQL database and returns a resultSet. I'm testing the class with another java file with a main() method so I can actually create the object from my querydb class and attempt to access the resultSet.The problem is, when I try accessing the resultSet in main(), it's looking like the result set is empty. But I know for a fact that it's retrieving the records because I put a (while result.next()){ //output column_name} in the method that actually executes the SQL statement (which works in SQL btw) and I can see all the results. However, if I try to do the same in main(), it simply skips the while loop as if there are no records in the ResultSet.Here is the code of my querydb class:

/*package querydb;import java.sql.*;public class Querydb {    //Class variables    Connection conn = null;             //Connection object for MySQL connection    ResultSet queryResults;             //SQL results    //Constructor    //Query DB for events within a specific primary and secondary category    public Querydb(String event_Category_Primary, String event_Category_Secondary){        getConnection();        executeStatement(event_Category_Primary, event_Category_Secondary);        terminateConnection();    }    //Pepares SQL statement based on arguments received by constructor    //Events within a specific primary & secondary category are returned    public void executeStatement(String event_Category_Primary, String event_Category_Secondary){        try{            //Prepare and execute statement for SQL query            System.out.println("QUERYDB: Executing query for all elements under: " + event_Category_Primary + " | " + event_Category_Secondary);            Statement stmt = conn.createStatement();            queryResults = stmt.executeQuery("SELECT * FROM mydb.events " +                    "WHERE event_Category_Primary = '" + event_Category_Primary + "' AND event_Category_Secondary = '" + event_Category_Secondary + "' " +                    "ORDER BY CASE " +                    "WHEN event_startYearSuffix = 'MYA' THEN 1000000 * event_Start " +                    "WHEN event_startYearSuffix = 'AD' THEN -1 * event_Start " +                    "ELSE event_Start END;");                      //THIS WORKS - I CAN SEE ALL RESULTS AT THIS POINT IN queryResults            while (queryResults.next()){                System.out.println("QUERYDB: event_Name = " + queryResults.getString("event_Name"));            }        }        catch (Exception e)        {            System.out.println("QUERYDB: Cannot create SQL Statement: " + e);        }    }    //Initiates DB connection with MySQL server    public void getConnection(){        System.out.println("QUERYDB: Connecting to DB...");        try        {            String userName = "test";            String password = "test";            String url = "jdbc:mysql://localhost:3360/mydb";            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(url, userName, password);            System.out.println("QUERYDB: Database connection established!");        }        catch (Exception e)        {            System.err.println("QUERYDB: Cannot connect to database");            System.out.println(e);        }    }    //Terminates the MySQL DB connection    public void terminateConnection(){        //Attempt to terminate the connection        try{            System.out.println("QUERYDB: Closing DB connection...");            conn.close();        }        catch (Exception e){            System.out.println(e);        }    }     }

This is the main() java file that I use to try to access the resultSet

package querydb;import java.sql.*;public class testInterface {    public static void main(String[] args) throws SQLException{        System.out.println("TestInterface: Creating Querydb ojbect...");        Querydb qdbObj = new Querydb("What", "Arts");                String s = null;        try{         //DOES NOT WORK - I CANNOT SEE THE RESULTS         while(qdbObj.queryResults.next()){             System.out.println("If you can read this, queryResults is not null");         }        } catch (SQLException e){}    }}

Again, the SQL query works and I've triple checked the prepared select statement in my querydb class. It seems to work within the querydb class only within that method. It's as if once it leaves the method, the resultSet disappears.Any ideas? This is definitely some lousy programming on my part, but any help is appreciated :)Thank you.

Edited by infra
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...