-1

I am learnin JDBC at the moment with IntelliJ and MySQL. Here is the code that I learned from Telusko on Youtube:

package com.company;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/customers";
        String uname = "root";
        String pass = "791005596";
        String query = "SELECT * FROM customers.registration\n" +
                "where password like '%123'";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, uname, pass);
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);

        String userData = "";
        while (rs.next()) {
            userData = rs.getInt("ID") + rs.getString(2);
            System.out.println(userData);

            st.close();
            con.close();
        }
    }
}

I already added connector in the library, as I run it on IntelliJ, it comes in this way:

"C:\Program Files\Java\jdk-14.0.1\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2020.1.1\lib\idea_rt.jar=1821:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2020.1.1\bin" -Dfile.encoding=UTF-8 -classpath "C:\Users\Jocelyn Huang\IdeaProjects\FSD\out\production\FSD;C:\Users\Jocelyn Huang\IdeaProjects\FSD\lib\mysql-connector-java-8.0.20.jar" com.company.Main
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
231245mamamoo123
Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.checkClosed(ResultSetImpl.java:445)
    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1726)
    at com.company.Main.main(Main.java:22)

Process finished with exit code 1

I have no idea what is going on here, for the first line, I try to delete this one :

Class.forName("com.mysql.jdbc.Driver");

And run again,

"C:\Program Files\Java\jdk-14.0.1\bin\java.exe" "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2020.1.1\lib\idea_rt.jar=2687:C:\Program Files\JetBrains\IntelliJ IDEA Community Edition 2020.1.1\bin" -Dfile.encoding=UTF-8 -classpath "C:\Users\Jocelyn Huang\IdeaProjects\FSD\out\production\FSD;C:\Users\Jocelyn Huang\IdeaProjects\FSD\lib\mysql-connector-java-8.0.20.jar" com.company.Main
231245mamamoo123
Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.checkClosed(ResultSetImpl.java:445)
    at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1726)
    at com.company.Main.main(Main.java:22)

Process finished with exit code 1

Could someone help me out? Thanks in advance.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Housoul
  • 91
  • 1
  • 2
  • 9
  • Maybe you could try `Class.forName("com.mysql.cj.jdbc.Driver")`? Loading the class that your error message gives you. – marstran May 28 '20 at 08:28
  • 1
    The code you learned is very bad quality. I strongly suggest you find a **non-video** tutorial from a reliable source, so you don't accidentally learn how to write bad code. Seems like this "Telusko" person has written a lot of tutorials for severa languages, meaning he's probably an expert in none of them. I would not try to learn from those videos. – Kayaman May 28 '20 at 08:29
  • 1
    @marstran That is a warning, the error is _java.sql.SQLException: Operation not allowed after ResultSet closed_, and that happens because the OP closes the statement and connection inside the loop over the result set. – Mark Rotteveel May 28 '20 at 08:36
  • Thank you you Mark, though still cannot understand your explanation, I will try to learn JAVA in a systematic way, hopefully can cathch up later. – Housoul May 28 '20 at 10:10
  • Thanks for your information Kayaman, and I will take yur advice. – Housoul May 28 '20 at 10:11

1 Answers1

1

The message to the driver is only a warning, the actual error which causes the exception is the reading of the data.

You close the connection and the statement before all data was read.
This means that the ResultSet can no longer be read when the while loop is processed again.

You must close the connection and the statement after the loop.

String userData = "";
while (rs.next()) {
    userData = rs.getInt("ID") + rs.getString(2);
    System.out.println(userData);
}

st.close();
con.close();
tgallei
  • 827
  • 3
  • 13
  • 22