CODEWITHSUNDEEP
javamysqljdbcconnection
Diego G.
Diego G.

Reputation: 3

Is this open/close JDBC connection code ok?

I'm not sure if this code is correct. I get it from an example of my Java course but I see that in fact it never closes the connection and the exceptions doesn't look to be catched correctly. (I call the query methods from my business tier).

public class Persistence {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/myDB";
    static final String USER = "user";
    static final String PASS = "pass";
    private static Connection con;

    static {
        openConnection();
    }

    private static boolean openConnection() {
        try {
            Class.forName(JDBC_DRIVER).newInstance();
            con = DriverManager.getConnection(DB_URL, USER, PASS);
            return true;
        } catch (InstantiationException ex) {
            ex.printStackTrace();
            return false;
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
            return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            System.err.println("SQL problem: " + e.getMessage());
            return false;
        }
    }

    //----------EXAMPLE QUERY-----------
    public static String someQuery() throws SQLException {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT column FROM myDB");
        String data;
        while (rs.next()) {
            data = rs.getString("column");
        }
        rs.close();
        st.close();
        return data;
    }
}

Should I open and close the connection inside every query method and delete the "static{}" expression? Like this? (still not sure about the exceptions):

public static String someQuery() throws SQLException {
    openConnection();
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("SELECT column FROM myDB");
    String data;
    while (rs.next()) {
        data = rs.getString("column");
    }
    rs.close();
    st.close();
    con.close();
    return data;
    }

Thanks for your answers.

Upvotes: 0

Views: 134

Answers (2)

Kumar Abhinav
Kumar Abhinav

Reputation: 6675

You do not need to create a new instance of JDBC Driver class

1) Change this to Class.forName(JDBC_DRIVER).newInstance() to Class.forName(JDBC_DRIVER)

You just need to register the class with the JVM(which involves intialization of static variables and blocks)

2)Try creating connection thread pool as suggested above

3)Use a try with resources block for Connection,Statement and ResultSet as all the three interfaces extend java.lang.AutoCloseable.As such,your resources are always closed without you having to write boiler plate code.Also,the exception in your business logic does not get masked by any exception occuring while closing a resource in finally block.

But of course you know JDK7 for that.These were implemnted as part of Project Coin.Just google it and you will get more information

Upvotes: 0

Tim B
Tim B

Reputation: 41188

The static block is only executed once, so you open a single connection and then keep it open for the duration of the program.

That does work but is flawed for a number of reasons. For example as soon as you start multi-threading it is completely useless.

Your second example is better but still flawed, the best approach would be:

  1. Use a connection pool to keep the connections open, request one from the pool when you need it.

  2. Use a try-finally block to ensure you always close the connection and/or return it to the pool when done.

Upvotes: 2

Related Questions