Java try with resources jdbc

How to Use Try-With-Resources with Jdbc

There’s no need for the outer try in your example, so you can at least go down from 3 to 2, and also you don’t need closing ; at the end of the resource list. The advantage of using two try blocks is that all of your code is present up front so you don’t have to refer to a separate method:

public List getUser(int userId) String sql = "SELECT id, username FROM users WHERE List users = new ArrayList<>(); 
try (Connection con = DriverManager.getConnection(myConnectionURL); 
PreparedStatement ps = con.prepareStatement(sql)) ps.setInt(1, userId); 
try (ResultSet rs = ps.executeQuery()) while(rs.next()) users.add(new User(rs.getInt("id"), rs.getString("name"))); 
> 
> 
> catch (SQLException e) e.printStackTrace(); 
> 
return users;
>

JDBC connection try with resources use

Your code as shown will only close the prepared statement, and leak the connection. If you want to close both, you need to use a statement per resource in the resources block:

try (Connection connection = ds.getConnection(); 
PreparedStatement prepare = connection.prepareStatement(statement)) // your code here
>

JDBC with try with resources

When you use try-with-resources, variables pointing to Closeable resources must be declared inside try-with-resources block.

Moreover, returning rs is a bad idea, it would be closed after method is done. So you might get an SQLException outside your method (something like «ResultSet is closed»). You should parse rs inside try-with-resources block and return SQL agnostic object from your method:

public ResultSet retrieveSQLQuery(String sqlQuery) < 
try (Connection conn = DriverManager.getConnection(dbUrl, user, password); 
Statement statement = conn.createStatement(); 
ResultSet rs = statement.executeQuery(sqlQuery)) MyResult result = . ; // parse rs here 
return myResult; 
> catch (SQLException e) logger.info(e.getMessage()); 
// return something (empty MyResult or null) from here or rethrow the exception 
// I'd recommend to get rid of this catch block and declare the SQLException on method signature 
> 
>

You’re getting compile-time error on incorrect try-with-resources syntax, that’s it.

Java 9
Java 9 provides more flexible syntax for try-with-resources . You can declare Closeable resource outside the try (. ) block:

public ResultSet retrieveSQLQuery(String sqlQuery) Connection conn = DriverManager.getConnection(dbUrl, user, password); 
try (conn; ResultSet rs = conn.createStatement().executeQuery(sqlQuery)) MyResult result = . ; // parse rs here 
return myResult; 
> catch (SQLException e) // handle error 
> 
>

try with resources — parameterized PreparedStatements

Using inner try with resource, after the external try with resource, init the statement and then use the inner try with resource for the resultSet.

public UserBean getUserDevices(final String emailAddress, final String uuid) throws SQLException UserBean userBean = null;
 
String query = "SELECT user.id, user.emailAddress, device.uuid, device.active, device.user_id FROM user " + 
"LEFT JOIN device ON user.id = device.user_id AND device.uuid = ? " + 
"WHERE user.emailAddress = ?";
 
try (Connection connection = dataSource.getConnection(); 
PreparedStatement preparedStatement = connection.prepareStatement(query); 
) preparedStatement.setString(1, uuid); 
preparedStatement.setString(2, emailAddress);
 
try(ResultSet resultSet = preparedStatement.executeQuery();) while(resultSet.next()) if(userBean == null) < // initialize user once while iterating for devices
userBean = new UserBean(resultSet.getInt("user.id"), resultSet.getString("user.emailAddress"), null); 
> 
if(resultSet.getString("device.uuid") != null) < // if device exists (it's a LEFT JOIN) then add
userBean.getDevices().add(new DeviceBean(0, resultSet.getString("device.uuid"), resultSet.getBoolean("device.active"), resultSet.getInt("device.user_id"))); 
> 
> 
> 
> 
return userBean;
>

Try-with-resources — Does it automatically close the connection? Java

No, the try with resources does not close the Connection that is used inside the PreparedStatement .
Only the PreparedStatement and its ResultSet are closed.

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

It is possible to reuse a connection to execute many PreparedStatements . Each of which is closed after usage. When the connection is no longer needed it can be closed as well.

You could perhaps check it like this:

public void query(String queryString, List queryParams, Consumer sqlConsumer)
 Connection connection; 
try (PreparedStatement preparedStatement = this.configureStatement(queryString, queryParams)) 
 connection=preparedStatement.getConnection(); 
sqlConsumer.accept(preparedStatement.executeQuery()); 
> catch(SQLException exception) 
 exception.printStackTrace(); 
> 
if(connection!=null) System.out.println("Is Connection closed:"+connection.isClosed()); 
>
>

private PreparedStatement configureStatement(String query, List queryParams) throws SQLException
 PreparedStatement preparedStatement = this.getConnection().prepareStatement(query); 
for (int i = 0; i < queryParams.size(); ++i)
preparedStatement.setString(i, queryParams.get(i));
 
return preparedStatement;
>

A refactoring that closes connections by using the try-with-resources with multiple statements:

public void query(String queryString, List queryParams, Consumer sqlConsumer) 
 
try ( Connection connection=this.getConnection(); 
PreparedStatement preparedStatement = this.configureStatement(connection, queryString, queryParams);) 
 sqlConsumer.accept(preparedStatement.executeQuery()); 
> catch(SQLException exception) 
 exception.printStackTrace(); 
> 
if(connection!=null) connection.close(); 
> 
>

private PreparedStatement configureStatement( Connection connection,String query, List queryParams) throws SQLException 
 PreparedStatement preparedStatement = connection.prepareStatement(query); 
for (int i = 0; i < queryParams.size(); ++i)
preparedStatement.setString(i, queryParams.get(i));
 
return preparedStatement; 
>

How to properly use try-with-resources in Database connection?

If you are fine to go for an additional method then it can be possible with only one try-resources

Instead of Statement statement = conn.createStatement();

Statement statement = createStatement(conn, maxRows);

Inside that new method, create Statement object and set the maxRows and return the statement obj.

Try with resources Statement for JDBC in java

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

Using try with resources for resources created without any reference

Only the ResultSet will be closed. If you want multiple resources to be closed, you need to declare them separately:

try (Connection conn = DriverManager.getConnection("jdbc. ", "user", "pass"); 
Statement stmt = conn.createStatement(); 
ResultSet resultSet = stmt.executeQuery(sql)) < 
 
// do stuff. 
>

Источник

The try-with-resources Statement

The try -with-resources statement is a try statement that declares one or more resources. A resource is as an object that must be closed after the program is finished with it. The try -with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable , which includes all objects which implement java.io.Closeable , can be used as a resource.

The following example reads the first line from a file. It uses an instance of BufferedReader to read data from the file. BufferedReader is a resource that must be closed after the program is finished with it:

static String readFirstLineFromFile(String path) throws IOException < try (BufferedReader br = new BufferedReader(new FileReader(path))) < return br.readLine(); >>

In this example, the resource declared in the try -with-resources statement is a BufferedReader . The declaration statement appears within parentheses immediately after the try keyword. The class BufferedReader , in Java SE 7 and later, implements the interface java.lang.AutoCloseable . Because the BufferedReader instance is declared in a try -with-resource statement, it will be closed regardless of whether the try statement completes normally or abruptly (as a result of the method BufferedReader.readLine throwing an IOException ).

Prior to Java SE 7, you can use a finally block to ensure that a resource is closed regardless of whether the try statement completes normally or abruptly. The following example uses a finally block instead of a try -with-resources statement:

static String readFirstLineFromFileWithFinallyBlock(String path) throws IOException < BufferedReader br = new BufferedReader(new FileReader(path)); try < return br.readLine(); >finally < if (br != null) br.close(); >>

However, in this example, if the methods readLine and close both throw exceptions, then the method readFirstLineFromFileWithFinallyBlock throws the exception thrown from the finally block; the exception thrown from the try block is suppressed. In contrast, in the example readFirstLineFromFile , if exceptions are thrown from both the try block and the try -with-resources statement, then the method readFirstLineFromFile throws the exception thrown from the try block; the exception thrown from the try -with-resources block is suppressed. In Java SE 7 and later, you can retrieve suppressed exceptions; see the section Suppressed Exceptions for more information.

You may declare one or more resources in a try -with-resources statement. The following example retrieves the names of the files packaged in the zip file zipFileName and creates a text file that contains the names of these files:

public static void writeToFileZipFileContents(String zipFileName, String outputFileName) throws java.io.IOException < java.nio.charset.Charset charset = java.nio.charset.StandardCharsets.US_ASCII; java.nio.file.Path outputFilePath = java.nio.file.Paths.get(outputFileName); // Open zip file and create output file with try-with-resources statement try ( java.util.zip.ZipFile zf = new java.util.zip.ZipFile(zipFileName); java.io.BufferedWriter writer = java.nio.file.Files.newBufferedWriter(outputFilePath, charset) ) < // Enumerate each entry for (java.util.Enumeration entries = zf.entries(); entries.hasMoreElements();) < // Get the entry name and write it to the output file String newLine = System.getProperty("line.separator"); String zipEntryName = ((java.util.zip.ZipEntry)entries.nextElement()).getName() + newLine; writer.write(zipEntryName, 0, zipEntryName.length()); >> >

In this example, the try -with-resources statement contains two declarations that are separated by a semicolon: ZipFile and BufferedWriter . When the block of code that directly follows it terminates, either normally or because of an exception, the close methods of the BufferedWriter and ZipFile objects are automatically called in this order. Note that the close methods of resources are called in the opposite order of their creation.

The following example uses a try -with-resources statement to automatically close a java.sql.Statement object:

public static void viewTable(Connection con) throws SQLException < String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) < ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); >>

The resource java.sql.Statement used in this example is part of the JDBC 4.1 and later API.

Note: A try -with-resources statement can have catch and finally blocks just like an ordinary try statement. In a try -with-resources statement, any catch or finally block is run after the resources declared have been closed.

Suppressed Exceptions

An exception can be thrown from the block of code associated with the try -with-resources statement. In the example writeToFileZipFileContents , an exception can be thrown from the try block, and up to two exceptions can be thrown from the try -with-resources statement when it tries to close the ZipFile and BufferedWriter objects. If an exception is thrown from the try block and one or more exceptions are thrown from the try -with-resources statement, then those exceptions thrown from the try -with-resources statement are suppressed, and the exception thrown by the block is the one that is thrown by the writeToFileZipFileContents method. You can retrieve these suppressed exceptions by calling the Throwable.getSuppressed method from the exception thrown by the try block.

Classes That Implement the AutoCloseable or Closeable Interface

See the Javadoc of the AutoCloseable and Closeable interfaces for a list of classes that implement either of these interfaces. The Closeable interface extends the AutoCloseable interface. The close method of the Closeable interface throws exceptions of type IOException while the close method of the AutoCloseable interface throws exceptions of type Exception . Consequently, subclasses of the AutoCloseable interface can override this behavior of the close method to throw specialized exceptions, such as IOException , or no exception at all.

Источник