CODEWITHSUNDEEP
javamysqljdbc
Jens Elbaek
Jens Elbaek

Reputation: 11

Why do I get a java.sql.PreparedStatement that is closed from an opened connection to MySQL?

Why do I get a java.sql.PreparedStatement that is closed from an opened connection to MySQL?

This is my code:

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;



public class MySqlTest1 
{
    Connection connection = null;
    PreparedStatement stmt = null;
    public MySqlTest1() 
    {
        System.out.println("Loading driver...");

        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded!");
        } catch (ClassNotFoundException e) {
            throw new IllegalStateException("Cannot find the driver in the classpath!", e);
        }
        String url = "jdbc:mysql://localhost:3306/world?autoReconnect=true&useSSL=false";
        String username = "jee";
        String password = "????????";

        System.out.println("Connecting database...");

        try (Connection connection =  DriverManager.getConnection(url, username, password)) 
        {
            if (connection.isClosed())
            {
                System.out.println("Returned connection is closed");
                return;
            }
            System.out.println("Database connected!");
            System.out.println("create statement ...");
            **stmt = connection.prepareStatement("select * from city");**
        } catch (SQLException e) {
            throw new IllegalStateException("Cannot connect the database!", e);
        }

        System.out.println("Selecting data ...");
        ResultSet rs = null;
        try {
            System.out.println("execute query ...");
            rs = stmt.executeQuery();
            if (rs != null)
            {
                System.out.println("Data selected");
            }
        }
        catch (SQLException ex)
        {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState: " + ex.getSQLState());
            System.err.println("VendorError: " + ex.getErrorCode());
            return;
        }

The result from that code is

Loading driver...
Driver loaded!
Connecting database...
Database connected!
create statement ...
Selecting data ...
execute query ...
****SQLException: No operations allowed after statement closed.****
SQLState: S1009
VendorError: 0

I have tried with the Statement as well and looked into its values and and found that "isClosed" is true. I have looked into the MySQL log but found nothing.

Upvotes: 1

Views: 669

Answers (1)

Mureinik
Mureinik

Reputation: 311188

You are opening the connection in a try-with-resource block. Once the block is terminated, the connection is closed, and implicitly, all the statements created from it. Just extend this block to include the usage of the statement, and you should be OK.

Upvotes: 5

Related Questions