Unable to reopen MySQL connection in Java

Question

I've got an annoying bug in Java. I'm connecting to a MySQL database and using Tomcat in Eclipse. It works brilliantly, but only the first time around. If I reload the page or run the page again, I get a com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

This is my code:

    Connection conn = null;
    String returnString = null;
    Response response = null;
    ResultSet rs = null;

    try {
        System.out.println("Trying to connect");
        conn = DbUtil.getConnection(); // Connect to the database
        System.out.println("Okay, connected");

        query = conn.prepareStatement("SELECT host_firstname FROM host"); //Crashes at this line!
        rs = query.executeQuery();

        ConvertToJson jsonConverter = new ConvertToJson();
        JSONArray jsonArray = new JSONArray();

        jsonArray = jsonConverter.convertToJsonArray(rs);


        returnString = jsonArray.toString();
        response = Response.ok(returnString).build();

    }
    catch (Exception e) {
        e.printStackTrace();
        System.out.println("hello!!!!");
    }
    finally {
        if (rs != null){
            rs.close();
        }
        if (query != null) {
            query.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    return response;

I've seen everywhere I've Googled that I have to close the result set, the prepared statement and the connection (in that order) but once I've closed the connection, I can't reopen it.

This is my DbUtil class:

    private static Connection connection = null;

    public static Connection getConnection() {

    if (connection != null) {
        return connection;
    }
    else {
        try {
            Properties prop = new Properties();
            InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("/db.properties");
            prop.load(inputStream);
            String driver = prop.getProperty("driver");
            String url = prop.getProperty("url");
            String user = prop.getProperty("user");
            String password = prop.getProperty("password");

            Class.forName(driver);

            connection = DriverManager.getConnection(url, user, password);
        }
        catch (IOException e) {
            e.printStackTrace();
        } 
        catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    return connection;
    }

Any idea what is causing this problem?

Thanks Omar :)

http://stackoverflow.com/questions/7565143/com-mysql-jdbc-exceptions-jdbc4-mysqlnontransientconnectionexception-no-operati — suiterdev, Jan 08 '15 at 14:20
Looks like you need to be looking at the session level and not the connection level possibly. This is not being thrown by your code, it's being thrown by MySQL from the JDBC driver. — suiterdev, Jan 08 '15 at 14:21

Inanda Menezes · Accepted Answer · 2015-01-08T14:32:53.183

2

This answer is just to get your code working. But the best way would be to configure a connection pool. You should look about it.

YOur condition is :

if (connection != null) {
        return connection; It's returning a closed connection
    }

Try changing it to:

if (connection != null && !connection.isClosed()) {
        return connection; 
    }

edited Jan 08 '15 at 14:32

answered Jan 08 '15 at 14:15

Inanda Menezes

1,796
13
17

I'm unable to return a connection as it's a Response the returnAllHosts method is returning – Omar.Ebrahim Jan 08 '15 at 14:21
1

@OmarEbrahim The code here is in your getConnection() method. – nos Jan 08 '15 at 14:36

Unable to reopen MySQL connection in Java

1 Answers1