CODEWITHSUNDEEP
javamysqljdbc
YahooYazooYapoo
YahooYazooYapoo

Reputation: 11

JDBC Unreachable 'create statement' statement

I am currently making a programme that communicates with an SQL database, after I make a query I obviously have to close the connections, I can close the connection fine, but I cannot close the actual statement itself. Is this necessary to close or does it close because it is a function of the connection itself? The code works perfectly fine I was just curious if this statement was needed. Here is the code, there error is highlighted towards the end of the returnEmployeeSalary function:

public class GroundControlToMajorTom {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        System.out.println(returnEmployeeSalary("ivy"));

    }

    public static String returnEmployeeSalary(String name) throws ClassNotFoundException, SQLException {
        HashMap<String, String> infoHR = connectionInfoHR();

        String query = "SELECT salary FROM employees WHERE first_name = '" + name + "'";

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(infoHR.get("url"), infoHR.get("uname"), infoHR.get("pass"));

        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);

        rs.next();
        String id = rs.getString("salary");

        return id;

        st.close(); /////////// ERROR IS HERE /////////////
        con.close();
    }

    public static HashMap<String, String> connectionInfoHR() {
        HashMap<String, String> infoHR = new HashMap();

        infoHR.put("url", "jdbc:mysql://localhost:3306/sql_hr");
        infoHR.put("uname", "root");
        infoHR.put("pass", "");

        return infoHR;
    }

}

Upvotes: 1

Views: 116

Answers (2)

Hizem
Hizem

Reputation: 181

You need to close your resultSet ,statement then your database connection to release all resources from the database. try this : 

 Connection connection = DriverManager.getConnection(infoHR.get("url"), infoHR.get("uname"), infoHR.get("pass"));
    try {
    Statement statement = connection.createStatement();

    try {
    ResultSet resultSet = statement.executeQuery(query);

    try {

     rs.next();
     return rs.getString("salary");
    } finally {
    resultSet.close();
    }
    } finally {
    statement.close();
    }
    } finally {
    connection.close();
    }

You could follow this article for more information : How to Close JDBC Resources Properly – Every Time

Update : Since Java 7 you could use this :

 try (Connection connection = DriverManager.getConnection(infoHR.get("url"), infoHR.get("uname"), infoHR.get("pass"));
        Statement statement = connection.createStatement()) {
        try (ResultSet resultSet = statement.executeQuery(query)) {

         rs.next();
         return rs.getString("salary");
        }

    }

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 108994

The problem is that you have statements after the return statement. Given return ends the normal flow of a method, the subsequent close methods aren't executed.

However, your code doesn't take into account abrupt exits of a method through an exception. In such situation, you won't properly close resources like statements and connections.

The appropriate solution is to use try-with-resources. Your code would then look like:

try (Connection con = DriverManager.getConnection(infoHR.get("url"), infoHR.get("uname"), infoHR.get("pass"));
     Statement st = con.createStatement();
     ResultSet rs = st.executeQuery(query);) {

    rs.next();
    return rs.getString("salary");
}

At the end of the try-with-resources block, the result set, statement and connection will be closed, in the right order, even if closing of one of these fails.

Upvotes: 2

Related Questions