Assigning resultset column value to a variable for use in another SQL Statement?? Java

Question

I am creating a data centric webservice in Java for deployment to Glassfish. All of my methods so far are working correctly except for one.

I am attempting to assign a value from a result set to a variable to use in another SQL statement as per the below code. I am not sure if its possible, or if perhaps my SQL is wrong, but any ideas would be appreciated.

ResultSet rset1 = stmt1.executeQuery("SELECT * 
                                        FROM WorkOrder 
                                       WHERE WorkOrderID = '"+workOrderID+"'");
Integer custID = rset1.getInt(3);

ResultSet rset2 = stmt2.executeQuery("SELECT * 
                                        FROM Customer 
                                       WHERE CustID = '"+custID+"'");
Integer quoteID = rset1.getInt(2);

ResultSet rset3 = stmt3.executeQuery("SELECT * 
                                        FROM Quote 
                                       WHERE QuoteID = '"+quoteID+"'");

Is there a reason you do not just issue a statement with joins in it? — Andrew
– Andrew, Commented Oct 21, 2011 at 3:37
@Dave, basically the value of the column stored to the variable is never reaching the statement that follows. If I pass it the variables through the method params it works fine though. — TJOB
– TJOB, Commented Oct 21, 2011 at 3:43

KV Prajapati · Accepted Answer · 2011-10-21 03:41:30Z

2

You foget to invoke ResultSet.next().

if(rset1.next())
 {
   Integer custID = rset1.getInt(3);
   ....
 }

answered Oct 21, 2011 at 3:41

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2011-10-21 03:41:31Z

What you posted can and should be done in a single query - less complex, and less [unnecessary] traffic back & forth with the database:

SELECT q.*
  FROM QUOTE q
 WHERE EXISTS (SELECT NULL 
                FROM CUSTOMER c 
                JOIN WORKORDER wo ON wo.custid = c.custid
               WHERE c.quoteid = q.quoteid
                 AND wo.workorderid = ?)

The reason this didn't use JOINs is because there'd be a risk of duplicate QUOTE values if there's more than one workorder/customer/etc related.

Additionally:

Numeric data types (quoteid, custid, etc) should not be wrapped in single quotes - there's no need to rely on implicit data type conversion.
You should be using parameterized queries, not dynamic SQL

allingeek · Accepted Answer · 2011-10-21 04:48:38Z

0

The note provided by OMG Ponies was really important to take note of, but does not really answer the question. AVD was also correct. I've cleaned it up a bit and included prepared statements. Please use prepared statements. They will help you sleep at night.

PreparedStatement pstmt1 = con.prepareStatement(
    "SELECT * FROM WorkOrder WHERE WorkOrderID = ?");
PreparedStatement pstmt2 = con.prepareStatement(
    "SELECT * FROM Customer WHERE CustID = ?");
PreparedStatement pstmt3 = con.prepareStatement(
    "SELECT * FROM Quote WHERE QuoteID = ?");

pstmt1.setInt(1, workOrderId)
ResultSet rset1 = pstmt1.executeQuery();

// test validity of rset1 
if(rset1.next()) {
    pstmt2.setInt(1, rset1.getInt(3))
    ResultSet rset2 = pstmt2.executeQuery();

    // test validity of rset2
    if(rset2.next()) {
        pstmt3.setInt(1, rset1.getInt(2))
        ResultSet rset3 = pstmt3.executeQuery();
    }
}

edited Oct 21, 2011 at 4:48

answered Oct 21, 2011 at 4:12

allingeek

1,3988 silver badges16 bronze badges

5 Comments

TJOB Over a year ago

Where you've assigned the resultset value to the variable seems wrong to me. How can it assign the variable a value from the resultset if the resultset hasn't been created yet, or the statement run???

allingeek Over a year ago

Yeah, I forgot to remove that bit of code I pasted before refactoring. Thanks for the note.

onedaywhen Over a year ago

"[@OMG Ponies] does not really answer the question" -- Joe Celko: "If you look at postings on the SQL newsgroups, many programmers just want to get a kludge for an immediate problem and not actually obtain a true long-term solution. If these were woodworking groups, their questions would be the equivalent of, "What are the best kind of rocks to use to pound screws into fine furniture?" When someone tells them to use large chunks of granite, they are happy, but if you try to them then about screwdrivers…" (Joe Celko's SQL Programming Style). Looks like you made @TJOB happy ;)

allingeek Over a year ago

Yeah. I kinda wanted to let TJOB know that they really should study OMG's answer, but I knew that would be a bit of an investment for them. Like most of these type of questions, the asker is usually just looking for the direct answer to their question. Hopefully I at least turned TJOB on to prepared statements.

TJOB Over a year ago

Actually yeah I moved onto prepared statements for the queries I needed to run. I did try you solution (and tried to understand it, which did indeed take an investment of time :)) but unfortunately it didn't work as there was more code after it that prevented it from working correctly, specifically a while loop that used rset.next() to assign the values to an arraylist. All sorted now though. Thanks for the help guys.

Collectives™ on Stack Overflow

Assigning resultset column value to a variable for use in another SQL Statement?? Java

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related