wood burning stoves 2.0*
The moose likes JDBC and the fly likes Execute multiple SQL queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Execute multiple SQL queries " Watch "Execute multiple SQL queries " New topic
Author

Execute multiple SQL queries

rayan alex
Ranch Hand

Joined: Nov 25, 2012
Posts: 33
    
    1
Item class has constructor with arraylist parameter. i want to execute 2 sql queries, fetch values from database and pass to item object. Please help to understand how to do this.


Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14074
    
  16

First of all, when your query has parameters, you should use PreparedStatement, use question marks in the SQL and then set the values for the parameters by setting them on the PreparedStatement, instead of concatenating strings as you are doing in line 3 of your code.

What is DBHandler?

In the code you have posted, you are looping over the rows in the ResultSet (lines 6 - 12), and for each row you create an Item object (line 11). However, you are not storing those Item objects anywhere. You just create them and then forget about them. Store the objects somewhere, for example in an ArrayList.

In line 20 you're looping over res but you probably wanted to loop over res2.

In line 22 you have a return statement. That will immediately return from the method, stopping the loop (lines 20 - 23). That's most likely not what you want. Also, baglist is an ArrayList<Integer>, while the method is supposed to return an Item. You'll get a compiler error because you're returning the wrong kind of object. Remove line 22.

Your method SearchItem is supposed to return one Item object. Why does it need to perform two queries?

You should have named the method searchItem instead of SearchItem. The common convention for naming Java methods is start with a lower-case letter.

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 7 API documentation
Scala Notes - My blog about Scala
rayan alex
Ranch Hand

Joined: Nov 25, 2012
Posts: 33
    
    1
Thank you Jesper . Thats such a good explanation. What i want to do get the bag sizes from database and store in a ArrayList and pass to Item Object. This is the format of Constructor of item class. item(String, String, String,String, Date, ArrayList ).

First 4 parameters collect from first SQL query and ArrayList comes from 2nd SQL query. As you can see i left it null as i dont understand how to combine these 2.
In short, i need to create ArrayList based on sql query and pass as a parameter to item object.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14074
    
  16

In that case, I'd do it like this:

- Do the second query first, to build the ArrayList<Integer> baglist.
- Then do the first query, to get the values for the Item. Don't use a while (line 6), just use an if (since you only want one Item object). When you create the Item object, pass baglist as the last argument to the constructor.
rayan alex
Ranch Hand

Joined: Nov 25, 2012
Posts: 33
    
    1
Yea. It works now. Thnkx Lot
This is the final code.




But i came up with different problem in my Update method. All the text filed update without error. But There is Primary key Duplicate error at Jlist update code.
Jlist consists Bag Sizes for the Item. Since its a multi value attribute, i placed in a separate table with item code.
When i try to update item adding or removing bagsizes to Jlist it gives Primary key duplication error.
this is sample format how date stored in bagsizes tables. If you can please help to sort this out.
Code for Update Action and method at the controller class given below.

item1 30
item1 40
item1 50

item2 30
item2 50

item3 40
item3 30

Code for Update Action




Code for database update



Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 37900
    
  22
Moving to our databases fora.
 
Consider Paul's rocket mass heater.
 
subject: Execute multiple SQL queries
 
Similar Threads
Passing multiple rows from Servlet to JSP
problem in Access database.
how to set the beans object into an arraylist
resultset next() returns false and yet record(s) exist
How to get a system date and store it in a variable?