Path: csiph.com!news.redatomik.org!aioe.org!.POSTED!not-for-mail
From: =?UTF-8?Q?Arne_Vajh=c3=b8j?= <arne@vajhoej.dk>
Newsgroups: comp.lang.java.programmer
Subject: Re: Is this the way to work with try with resources
Date: Tue, 8 Aug 2017 21:25:59 -0400
Organization: Aioe.org NNTP Server
Lines: 90
Message-ID: <omdob4$cif$1@gioia.aioe.org>
References: <87poc6wb9n.fsf@munus.decebal.nl>
NNTP-Posting-Host: PBuS014lhzVyHjLbXmo/ZQ.user.gioia.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1
Content-Language: en-US
X-Notice: Filtered by postfilter v. 0.8.2
Xref: csiph.com comp.lang.java.programmer:37092

On 8/8/2017 7:26 AM, Cecil Westerhof wrote:
> When using with resources you ‘should’ always work with try with
> resources. At the moment I have something like the following code:
>      private static void statistics() {
>          try (Statement stmt = conn.createStatement()) {
>              String format;
> 
>              try (ResultSet rsTotal = stmt.executeQuery(COUNT_TOTAL_PROV)) {
>                  final int count = rsTotal.getInt("no");
> 
>                  format = String.format("There are %%,%dd %%6s proverbs.%%n", String.format("%,d", count).length());
>                  System.out.printf(format, count, "total");
>              }
>              try (ResultSet rsUnused = stmt.executeQuery(COUNT_UNUSED_PROV)) {
>                  final int count = rsUnused.getInt("no");
> 
>                  if (count != 0) {
>                      System.out.printf(format, count, "unused");
>                  }
>              }
>              try (ResultSet rsUsed = stmt.executeQuery(COUNT_USED_PROV)) {
>                  final int count = rsUsed.getInt("no");
> 
>                  System.out.printf(format, count, "used");
>              }
>          } catch (SQLException e) {
>              System.err.printf("Error in statistics\n");
>              e.printStackTrace();
>              fatalError("");
>          }
>      }
> 
> It feels a bit long-winded. Is this the way to go, or is there a
> better way?

That code looks like try with resource is intended to be used

> I thought that I could rewrite it to:
>      private static void statistics() {
>          try (Statement stmt     = conn.createStatement();
>               ResultSet rsTotal  = stmt.executeQuery(COUNT_TOTAL_PROV);
>               ResultSet rsUnused = stmt.executeQuery(COUNT_UNUSED_PROV);
>               ResultSet rsUsed   = stmt.executeQuery(COUNT_USED_PROV)) {

As already mentioned by somebody else then executing multiple queries
on the same connection and statement is not safe.

And even if you modified the code to work on different connections and
statements, then it would sort of violate good database programming
practice by opening more stuff and keeping stuff open longer than
needed.

It would also require all readers of your code to know that close
is called in reverse order for the code to look good in their eyes.

Bottom line: bad!

>              int    count;
>              String format;
> 
>              count  = rsTotal.getInt("no");
>              format = String.format("There are %%,%dd %%6s proverbs.%%n", String.format("%,d", count).length());
>              System.out.printf(format, count, "total");
>              count  = rsUnused.getInt("no");
>              if (count != 0) {
>                  System.out.printf(format, count, "unused");
>              }
>              count  = rsUsed.getInt("no");
>              System.out.printf(format, count, "used");
>          } catch (SQLException e) {
>              System.err.printf("Error in statistics\n");
>              e.printStackTrace();
>              fatalError("");
>          }
>      }
> 
> That would be a little better I think, but when there are a lot of
> queries to be done, you need to define a lot of extra result-sets.
> 
> But there is a problem with that. If I do that, all three result-sets
> have the same query: the last one.
> What is the reason for this? I am using SQLite3.

New SQL statement => result from previous SQL statement is
discarded.

Arne