Try with resources Statement for JDBC in java

Question

Useful piece of code for Hive JDBC:

       Connection con = null;
       Statement stmt = null

        try {
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            con = DriverManager.getConnection(connectionUri, userName, password);
            stmt = con.createStatement();
            stmt.executeUpdate(query);

        } catch (ClassNotFoundException cex) {
            cex.printStackTrace();

        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

I want to remove try - catch in finally block.

So I tried The try-with-resources Statement.

        try (Class.forName("org.apache.hive.jdbc.HiveDriver");
            Connection con = DriverManager.getConnection(connectionUri, userName, password);
            Statement stmt = con.createStatement();){

            stmt.executeUpdate(query);

        } catch (ClassNotFoundException cex) {
            cex.printStackTrace();

        } catch (SQLException e) {
            e.printStackTrace();
        }

I think this is not the right way.

Class.forName("org.apache.hive.jdbc.HiveDriver") should not be in try. Should I make a separate try-catch for this?

       try {
            Class.forName("org.apache.hive.jdbc.HiveDriver");

        } catch (ClassNotFoundException cex) {
            cex.printStackTrace();
        }
        try (Connection con = DriverManager.getConnection(connectionUri, userName, password);
            Statement stmt = con.createStatement();){

            stmt.executeUpdate(query);

        } catch (SQLException e) {
            e.printStackTrace();
        }

Is this right way or am I missing any thing?

score 6 · Accepted Answer · answered Feb 01 '16 at 12:29

The idea behind try-with-ressource is to close an AutoCloseable class. So every usage of a class which should be closed after using it (a Ressource) can be used with try-with-ressource (like Connection for example). You don't have to take care of closing it manually (in an finally block for example).

So yes, your idea is right:

try/catch for Class.forName("org.apache.hive.jdbc.HiveDriver"); - because this is not AutoCloseable
try-with-ressource for Connection con = DriverManager.getConnection(connectionUri, userName, password); Statement stmt = con.createStatement();- because Connection and Statement implement AutoCloseable

Reference: https://docs.oracle.com/javase/7/docs/api/java/lang/AutoCloseable.html

Steve C · Answer 2 · 2016-02-01T12:39:50.380

2

When you're using Java 6 or better and the Apache Hive JDBC driver is JDBC 4 compliant or better* then you do not need the Class.forName("org.apache.hive.jdbc.HiveDriver") stuff at all.

Therefore you can just remove the entire try/catch block from your second solution and you're good to go with just:

try (Connection con = DriverManager.getConnection(connectionUri, userName, password);
     Statement stmt = con.createStatement()) {

    stmt.executeUpdate(query);

} catch (SQLException e) {
    e.printStackTrace();
}

* Which is the case for version 1.2.0 or newer of the Hive JDBC driver

edited Feb 01 '16 at 12:39

answered Feb 01 '16 at 12:33

Steve C

18,876
5
34
37

Why is this? Shouldn't Java still have to instantiate the driver? I am using the jtds driver and am noticing the same thing, when I forget to instantiate it, the program still runs normally. – jabe May 06 '16 at 22:25
@jabeoogie, this is answered at [Why we use Class.forName(“oracle.jdbc.driver.OracleDriver”) while connecting to a DataBase?](http://stackoverflow.com/questions/20078586/why-we-use-class-forname-oracle-jdbc-driver-oracledriver-while-connecting-to) – Steve C May 07 '16 at 13:18

Try with resources Statement for JDBC in java

2 Answers2