JDBC with mySQL question -- URGENT

[yacanna]

I try to get the product id from the first query and use the id for second query.

So I have something like:

public Collection getProducts(int category_id)
{
ArrayList al = new ArrayList() ;
PreparedStatement s = null;
PreparedStatement s2= null;

ResultSet rs = null;
ResultSet rs2= null;

int prod_id = 0;

try
{
conn = getDatabaseConnection() ;
s = conn.prepareStatement("SELECT product_id, product_name from product_categories where category_id = ?" );
s.setInt(1, category_id) ;
rs = s.executeQuery();

while(rs.next())
{
prod_id = rs.getString(1);
al.add(rs.getString(2));
/* If I comment out the below codes, I will get the result from the first statement */

s2 = conn.prepareStatement("select product_image_path from product_images where product_id = ? );
s2.setInt(1, prodId) ;
rs2 = s2.executeQuery();

al.add(rs2.getString(1));
/* above is to get image path in while loop for each product id */
} // end while loop
} // end try
Catch {
.....
}
return al;
}

But seems the second preparedStatement doens't work within while(rs.next() loop -- I will get nothing when I add the second preparedStatement in while loop -- If I comment it out, I will at least get product name, ids from the first result set.

Please advise how to make it work -- How to use the product_id from the first resultset to get the other information running the second query.

Thank you very much -- Please let me know if you are not clear of my question.

 

[sedj]

Try using a separate connection for the second statement - it really is bad form attempting to use two statement objects against the same connection simultaneously.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[stefanwagner]

Let the database do the job:

"SELECT 
    product_id,   
    product_name,
    product_image_path 
FROM 
    product_categories pc
    product_images pi
WHERE 
    category_id = ?
AND pc.product_id = ?
AND pc.product_id = pi.product_id"

It should be specialized and optimized for such tasks.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[shhivaa]

Is the variable name prodId in line

s2.setInt(1, prodId) a typo, because in line,

prod_id = rs.getString(1), it is prod_id

 

[microserf]

I would lift the following line out of the while loop:

s2 = conn.prepareStatement("select product_image_path from product_images where product_id = ?");

The PreparedStatement class is, in part, designed to allow you to efficiently run similar queries multiple times. So before entering the while loop, create the PreparedStatement object and then in the loop use the setInt() method as you do. Otherwise you are just creating the same object lots of times within the loop, which is silly.

You also probably don't need the variables s *and* s2 because you don't use s once you've got the ResultSet object from it. You can probably reuse s for both purposes.

Instead of using indices for the getString() method, I'd suggest that you use the name of the column, which will make your code more readable (but slightly less efficient -- a small price to pay, especially given you were prepared to create lots of identical PreparedStatements .

If you try all this and it still doesn't work, at least you've got cleaner code that is easier to understand.