Java Sql Exception After End of Result Set

Question

My code works fine but when I try to run the code it first shows java.sql.SQLException:After end of result set. I would like to know what is causing this and how to fix this as this is for a graded project.

public GenerateBill() 
{
    initComponents();
    try 
  {
        Class.forName("java.sql.DriverManager");
        Connection con=(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/bookstore","root","root");
        Statement stmt=(Statement)con.createStatement();
        String query, product;
        query="select * from store;";
        ResultSet rs=stmt.executeQuery(query);
        while(rs.next());
        {
            product=rs.getString("productname");
            jComboBox1.addItem(product);
        }
  } 
    catch(Exception e) 
  {
    JOptionPane.showMessageDialog(null,e.toString());
  }
}

When I execute the code a Message Dialog Box shows up first. And when I click OK, the page I'm trying to make opens and executes normally. So, I'm confused as to what it means. Also, I'm new to this site, so I don't really know how much of the code I need to add. The rest of the code is for different jButtons. The page is for Generating Bills/Receipts.

Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Nov 07 '19 at 14:48
Please, always show us ALL the error message not a summary. There is usually more useful information on these messages than you have provided — RiggsFolly, Nov 07 '19 at 14:50
@StarDragon23 as the guys mentioned above, it should be better and more useful to have full code and error messages. However, for sure your code (as it is in the example you gave us) is missing closing the connections, etc after your successful statement, so please have also a look on this on how to properly close connections, https://stackoverflow.com/questions/2225221/closing-database-connections-in-java — dZ., Nov 07 '19 at 15:16
@dZ I don't know how to close connections or that we even needed to close them as this is all I've been taught at school. Could you help me learn more? — StarDragon23, Nov 07 '19 at 16:22
what happens if you remove the semicolon in the query string? — Thallius, Nov 07 '19 at 16:30

dZ. · Accepted Answer · 2019-11-07T17:03:22.913

There are some parts in your code that could be better. Specifically,

Use com.mysql.jdbc.Driver as your DB is MySQL, instead of java.sql.DriverManager
No need to cast your Connection object.
After /bookstore you could add ?useSSL=false, although it is not mandatory, so something like jdbc:mysql://localhost:3306/bookstore?useSSL=false
Use java.sql.PreparedStatement instead of simply Statement.
Close your connection in a finally block after catch.

Eventually, your code should look somehow like the following,

public GenerateBill() {

    initComponents();

    Connection con = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bookstore?useSSL=false","root","root");

        String query = "select * from store";
        stmt = con.prepareStatement(query);

        String product;

        rs = stmt.executeQuery();

        while(rs.next())
        {
            product = rs.getString("productname");
            jComboBox1.addItem(product);
        }
  } catch(Exception e) {
        JOptionPane.showMessageDialog(null,e.toString());
  } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            LOG.error("Error closing the connection with the database...");
            e.printStackTrace();
        }
  }
}

Try the above and let me know if it is OK. If not, please post the whole exception to see what causes the issue.

Thank you for your help. It works fine now. The message about the exception is gone. — StarDragon23, Nov 07 '19 at 16:58
@StarDragon23 glad I helped! :) The most important you have to remember is to close all connections (ResultSet, PreparedStatement, Connection) after your successful operation with the database. — dZ., Nov 07 '19 at 17:01

Java Sql Exception After End of Result Set

1 Answers1