How should I use try-with-resources with JDBC

Question

I have a method for getting users from a database with JDBC   public List lt User gt  getUser int userId        String sql    SELECT id  name FROM users WHERE id           List lt User gt  users   new ArrayList lt User gt         try           Connection con   DriverManager getConnection myConnectionURL           PreparedStatement ps   con prepareStatement sql            ps setInt 1  userId           ResultSet rs   ps executeQuery            while rs next                  users add new User rs getInt  id    rs getString  name                        rs close            ps close            con close          catch  SQLException e            e printStackTrace              return users      How should I use Java 7 try-with-resources to improve this code   I have tried with the code below  but it uses many try blocks  and doesn t improve the readability much  Should I use try-with-resources in another way   public List lt User gt  getUser int userId        String sql    SELECT id  name FROM users WHERE id           List lt User gt  users   new ArrayList lt  gt         try           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

User · Accepted Answer

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 lt User gt  getUser int userId        String sql    SELECT id  username FROM users WHERE id           List lt User gt  users   new ArrayList lt  gt         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

User · Answer

I realize this was long ago answered but want to suggest an additional approach that avoids the nested try-with-resources double block   public List lt User gt  getUser int userId        try  Connection con   DriverManager getConnection myConnectionURL            PreparedStatement ps   createPreparedStatement con  userId             ResultSet rs   ps executeQuery                   process the resultset here  all resources will be cleaned up        catch  SQLException e            e printStackTrace             private PreparedStatement createPreparedStatement Connection con  int userId  throws SQLException       String sql    SELECT id  username FROM users WHERE id           PreparedStatement ps   con prepareStatement sql       ps setInt 1  userId       return ps

User · Answer

As others have stated  your code is basically correct though the outer try is unneeded  Here are a few more thoughts   DataSource  Other answers here are correct and good  such the accepted Answer by bpgergo  But none of the show the use of DataSource  commonly recommended over use of DriverManager in modern Java    So for the sake of completeness  here is a complete example that fetches the current date from the database server  The database used here is Postgres  Any other database would work similarly  You would replace the use of org postgresql ds PGSimpleDataSource with an implementation of DataSource appropriate to your database  An implementation is likely provided by your particular driver  or connection pool if you go that route   A DataSource implementation need not be closed  because it is never    opened     A DataSource is not a resource  is not connected to the database  so it is not holding networking connections nor resources on the database server  A DataSource is simply information needed when making a connection to the database  with the database server s network name or address  the user name  user password  and various options you want specified when a connection is eventually made  So your DataSource implementation object does not go inside your try-with-resources parentheses    Nested try-with-resources  Your code makes proper used of nested try-with-resources statements   Notice in the example code below that we also use the try-with-resources syntax twice  one nested inside the other  The outer try defines two resources  Connection and PreparedStatement  The inner try defines the ResultSet resource  This is a common code structure    If an exception is thrown from the inner one  and not caught there  the ResultSet resource will automatically be closed  if it exists  is not null   Following that  the PreparedStatement will be closed  and lastly the Connection is closed  Resources are automatically closed in reverse order in which they were declared within the try-with-resource statements   The example code here is overly simplistic  As written  it could be executed with a single try-with-resources statement  But in a real work you will likely be doing more work between the nested pair of try calls  For example  you may be extracting values from your user-interface or a POJO  and then passing those to fulfill   placeholders within your SQL via calls to PreparedStatement  set    methods    Syntax notes  Trailing semicolon  Notice that the semicolon trailing the last resource statement within the parentheses of the try-with-resources is optional  I include it in my own work for two reasons  Consistency and it looks complete  and it makes copy-pasting a mix of lines easier without having to worry about end-of-line semicolons  Your IDE may flag the last semicolon as superfluous  but there is no harm in leaving it   Java 9     Use existing vars in try-with-resources  New in Java 9 is an enhancement to try-with-resources syntax  We can now declare and populate the resources outside the parentheses of the try statement  I have not yet found this useful for JDBC resources  but keep it in mind in your own work   ResultSet should close itself  but may not  In an ideal world the ResultSet would close itself as the documentation promises      A ResultSet object is automatically closed when the Statement object that generated it is closed  re-executed  or used to retrieve the next result from a sequence of multiple results    Unfortunately  in the past some JDBC drivers infamously failed to fulfill this promise  As a result  many JDBC programmers learned to explicitly close all their JDBC resources including Connection  PreparedStatement  and ResultSet too  The modern try-with-resources syntax has made doing so easier  and with more compact code  Notice that the Java team went to the bother of marking ResultSet as AutoCloseable  and I suggest we make use of that  Using a try-with-resources around all your JDBC resources makes your code more self-documenting as to your intentions   Code example  package work basil example   import java sql Connection  import java sql PreparedStatement  import java sql ResultSet  import java sql SQLException  import java time LocalDate  import java util Objects   public class App       public static void main   String   args                 App app   new App            app doIt               private void doIt                   System out println   Hello World               org postgresql ds PGSimpleDataSource dataSource   new org postgresql ds PGSimpleDataSource             dataSource setServerName   1 2 3 4             dataSource setPortNumber  5432             dataSource setDatabaseName   example db              dataSource setUser   scott             dataSource setPassword   tiger              dataSource setApplicationName   ExampleApp              System out println   INFO - Attempting to connect to database               if   Objects nonNull  dataSource                           String sql    SELECT CURRENT DATE                 try                       Connection conn   dataSource getConnection                         PreparedStatement ps   conn prepareStatement  sql                                                     make  PreparedStatement  set     calls here                  try                           ResultSet rs   ps executeQuery                                                             if   rs next                                                   LocalDate ld   rs getObject  1   LocalDate class                            System out println   INFO - date is     ld                                                                      catch   SQLException e                                 e printStackTrace                                     System out println   INFO - all done

User · Answer

What about creating an additional wrapper class   package com naveen research sql   import java sql Connection  import java sql PreparedStatement  import java sql ResultSet  import java sql SQLException   public abstract class PreparedStatementWrapper implements AutoCloseable        protected PreparedStatement stat       public PreparedStatementWrapper Connection con  String query  Object     params  throws SQLException           this stat   con prepareStatement query           this prepareStatement params              protected abstract void prepareStatement Object     params  throws SQLException       public ResultSet executeQuery   throws SQLException           return this stat executeQuery               public int executeUpdate   throws SQLException           return this stat executeUpdate                Override     public void close             try               this stat close              catch  SQLException e                e printStackTrace                            Then in the calling class you can implement prepareStatement method as   try  Connection con   DriverManager getConnection JDBC URL  prop       PreparedStatementWrapper stat   new PreparedStatementWrapper con  query                  new Object     123L   TEST                    Override             protected void prepareStatement Object    params  throws SQLException                   stat setLong 1  Long class cast params 0                     stat setString 2  String valueOf params 1                                      ResultSet rs   stat executeQuery           while  rs next            System out println String format   s   s   rs getString 2   rs getString 1       catch  SQLException e        e printStackTrace

User · Answer

Here is a concise way using lambdas and JDK 8 Supplier to fit everything in the outer try   try  Connection con   DriverManager getConnection JDBC URL  prop       PreparedStatement stmt     Supplier lt PreparedStatement gt     - gt        try           PreparedStatement s   con prepareStatement  SELECT userid  name  features FROM users WHERE userid                s setInt 1  userid           return s        catch  SQLException e    throw new RuntimeException e            get        ResultSet resultSet   stmt executeQuery

[java] How should I use try-with-resources with JDBC?

Examples related to java

Examples related to jdbc

Examples related to java-7

Examples related to try-with-resources