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 :)

Answer 1

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; 
    }