Getting data From a java DB with SQL Using ResultSet

Question

I created a method to get the values from a database in java using SQL and store the information in a ResultSet and then use a while loop to store the information in a RentSendItem and store all those items in an ArrayList called sendList but when I try to run it, it gives me the error:

'ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is off'

This is my class:

public void getDataFromDB() {
    System.out.println("Wordk");
    //connecting
    Connection connection = null;
    Statement statement = null;
    try {
        System.out.println("1");
        connection = DriverManager.getConnection(url, username, password);
        statement = connection.createStatement();
        ResultSet name = statement.executeQuery("SELECT firstname,surname FROM CUSTOMER");
        ResultSet titles = statement.executeQuery("Select Title,Category From ADDDVD ");
        System.out.println(name.getString("firstname"));
        System.out.println("2");
        while (name.next()) {
            String fullName = name.getString("firstname") + " " + name.getString("surname");
            RentSendItem item = new RentSendItem(name.getString("firstname") + name.getString("surname"), titles.getString("Category"), titles.getString("title"));
            sendList.add(item);
        }
        System.out.println("3");
    } catch (Exception e) {
        System.out.println("Error" + e.getMessage());
    }
}

So just want to know what am I doing wrong and will this class do what I want it to do. If you could maybe help me, I would be grateful.

comment below line and check System.out.println(name.getString("firstname")); — Mowgli
– Mowgli, Commented Nov 2, 2020 at 12:23
try once with the comment above the line and let me know what you will get. — Mowgli
– Mowgli, Commented Nov 2, 2020 at 12:27

Abra · Accepted Answer · 2020-11-02 12:47:04Z

There are several problems in your code.

You can't call method getString() of interface java.sql.ResultSet before you call method next(). First call method next() and if that method returns "true", then you can call method getString().
You also need to call method next() on titles.
You can't call getString() twice on the same column on the same row.

Compare the below with your code.

public void getDataFromDB() {
    System.out.println("Wordk");
    // connecting
    try (Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement()) {
        System.out.println("1");
        ResultSet name = statement.executeQuery("SELECT firstname,surname FROM CUSTOMER");
        ResultSet titles = statement.executeQuery("Select Title,Category From ADDDVD ");
        System.out.println("2");
        while (name.next()) {
            String firstname = name.getString("firstname");
            String surname = name.getString("surname");
            String fullName = firstname + " " + surname;
            if (titles.next()) {
                RentSendItem item = new RentSendItem(fullName,
                                                     titles.getString("Category"),
                                                     titles.getString("title"));
                sendList.add(item);
            }
        }
        System.out.println("3");
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Also, the following are not problems but recommendations.

In the catch block, it usually preferable to print the entire stack trace rather than just the error message.
You should close the Connection and Statement once you no longer need them. In the code above, I have used try-with-resources

Collectives™ on Stack Overflow

Getting data From a java DB with SQL Using ResultSet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related