How expensive is connection.createStatement

Question

I have to write a java program to fetch data from 3 tables. For some business reason I can not join them. I have to fetch the records one by one as follows

Fetch all records from Table1
For every record on table1 do 
     if some business-validation passes
        retrieve corresponding records from Table2
        For every record on table2 do
        if some business-validation passes
            retrieve corresponding records from Table3

I have written code as follows :

Statement statement1 = dbConnection.createStatement();
ResultSet rs1 =  statement1.executeQuery("QUERY TO RETRIEVE DATA FROM TABLE1");
while(rs1.next()){
   // Do business validation
   Statement statement2 = dbConnection.createStatement();
   ResultSet rs2 =  statement2.executeQuery("QUERY TO RETRIEVE CORRESPONDING DATA FROM TABLE2");
   while(rs2.next()){
       // Do business validation
       Statement statement3 = dbConnection.createStatement();
       ResultSet rs3 =  statement2.executeQuery("QUERY TO RETRIEVE CORRESPONDING DATA FROM TABLE3");
       while(rs3.next()){

       }
       rs3.close();
       statement3.close()
    }
    rs2.close();
    statement2.close()
 }
 rs1.close();
 statement1.close()

Each table can have hundreds of records. Now my questions are

Is it correct to do createStatement in every loop and close it ? Will it be expensive?
a. What if I create 3 statements in the beginning and simply use them to call executeQuery(). Is it advantageous?

b. In this case do I need close statement2 & statement3 it after internal use?
Which is the preferred way ? Is there any better/faster way?

_Is there any better/faster way?_ Use one statement instead of three — BackSlash, Oct 01 '14 at 18:38
Does your query2 depend on resultset of query 1? Does your query3 depend on resultset of query 2 ? — DolphinJava, Oct 01 '14 at 18:45
In general, prefer a `PreparedStatement` in loop(s); that way you can more efficiently use the database query cache. The answer to 2b is yes. You must close statements after use, or you *might* leak database cursors. — Elliott Frisch, Oct 01 '14 at 18:49
Really, why can't you join them? How can a "business reason" affect what your implementation is? — Dawood ibn Kareem, Oct 01 '14 at 19:03
@David Wallace: Each output of a result set may require some processing before executing query 2. Business validation is apparent in the code. Not everything can be done using joins. Otherwise Stored Procedures will not exist. — DolphinJava, Oct 01 '14 at 19:14
@Kaushik LeLe: Have you considered using Stored Procedures ? — DolphinJava, Oct 01 '14 at 19:15
@DavidWallace business validation can be anything like calling third party web-service to check if that records need to be handled. So that can not be done in join. This is just example. So I generalized it is "business validation" — Kaushik Lele, Oct 02 '14 at 06:29
@DolphinJava yes; I need to use information from table1 to query table2 and info of table2 into table 3. And as I said; due to business validations which are outside DB records; I can not merger it all as single procedure — Kaushik Lele, Oct 02 '14 at 06:30
Well, if you have to do stuff like calling a web service for every row returned by the first query, you probably don't want to leave that query uncommitted, because you don't know how long the web service will take to respond. I would recommend selecting all the data from the first query into some kind of structure, closing the result set, then iterating through the data. But exactly what the "best" solution is depends on many different factors, so this question is currently unanswerable. — Dawood ibn Kareem, Oct 02 '14 at 07:41

score 3 · Answer 1 · edited May 23 '17 at 11:49

Your query2 and query3 seem to be accepting parameters from the resultset of query 1 and query 2 respectively.

Why dont you use PreparedStatement for all of them. Refer to this link for benefits of using PreparedStatement over Statement

Difference between Statement and PreparedStatement

Have all three Statements declared outside of loop using PreparedStatement and then use them inside the loop. @Elliott Frisch is right regarding closing of any Statement type.

Always avoid createStatement in loop as much as possible.

You must consider Stored Procedures as well. If you can write a Stored Procedure which will have the looping logic, that may be the best approach for you. But it really depends on situation to situation.

How expensive is connection.createStatement

1 Answers1