This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes how to read different data from one table and compare it Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how to read different data from one table and compare it" Watch "how to read different data from one table and compare it" New topic

how to read different data from one table and compare it

xiaopeng shi

Joined: Nov 25, 2002
Posts: 3
hi, all, I have problem when i try to read different type of data from one table and compare it, it the data is matched, then insert into another table. My sample coding as folowing shows:
String bb1;
String bb2;
String strSQL="select * from testbuy where status='buy'";
ResultSet buy=stmt.executeQuery(strSQL);
String strSQL2="select * from testbuy where status='sell'";
while ({
String strSQL2="select * from testsell";
while ({
if (bb1.equals(bb2)){
String ss="insert into testmsg values ('bb1')";
When I try to run it, it insert nothing, anyone can give me some advice?
Thank you very much!
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
It appears from the code you posted, that you are using the same Statement object for every operation. This could be the cause of your problem. The
Javadoc states: "Only one ResultSet object per Statement object can be open at any point in time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All statement execute methods implicitly close a statment's current ResultSet object if an open one exists."
Thus, when you execute

the ResultSet referenced by 'buy' is closed. And when you execute

The ResultSet referenced by 'sell' is closed.
Also, unless you are absolutely sure that the Strings all have the same case, you may want to use equalsIgnoreCase() rather than just equals() for your String compares.
Also, rather than opening two ResultSets and iterating through them both, you might want to try just using a single query. Something like this:

This query gets all the rows from testbuy where status is buy and joins them to all the rows from testsell where the two fields are equal. Thus in a single query, you get all the data that your code needed two queries to get. Now all you need is a single loop to go through each row, retrieve the appropriate data, and insert that data into a new table (remember to use a different Statement object).
[ November 25, 2002: Message edited by: Kevin Mukhar ]
xiaopeng shi

Joined: Nov 25, 2002
Posts: 3
hi, Kevin Mukhar, thank you for your help, but when I put it in my real program, it seems it can read data from database, but can not write data. Could you help me to check it? Thank you very much!!!
The following is my source code
--------------------------------------------------String [][] buycol=new String[99][12];
String [][] sellcol=new String[99][12];
int [] buyagid=new int[99];
int [] sellagid=new int[99];
int [] buylprice=new int[99];
int [] buyhprice=new int[99];
int [] selllprice=new int[99];
int [] sellhprice=new int[99];

String strSQL="select agentbuy.*, agentsell.* from agentbuy, agentsell where agentbuy.iname=agentsell.iname and agentbuy.brand=agentsell.brand and and and agentbuy.hprice>agentsell.lprice and agentbuy.accinfo=agentsell.accinfo and agentbuy.status='no' and agentsell.status='no'";
ResultSet sellbuy=stmt.executeQuery(strSQL);
//Statement state=cont.createStatement();
int i=1;
while ( && i<99){
buyagid[i]=sellbuy.getInt "agentbuy.agid");

//System.out.println("sellcol[i][5]" + sellcol[i][5]);
for (int j=1; j<99; j++) {
String idsql="select msgid from numagid";
ResultSet id=stmt.executeQuery(idsql);
int msgid1=id.getInt("msgid");
int msgid2=msgid1+1;

String sellsql="insert into msg values ('"+ msgid2 +"','"+ buyagid[j] +"','"+ sellcol[j][1] +"','"+ buycol[j][2] +"','"+ buycol[j][1] +"','buy','"+ buycol[j][4] +"','"+ buycol[j][5] +"','"+ buycol[j][6] +"','"+ buylprice[j] +"','"+ buyhprice[j] +"')";
ResultSet sell=stmt.executeQuery(sellsql);//insert value

int msgid3=msgid1+2;
String buysql="insert into msg values ('"+ msgid3 +"','"+ sellagid[j] +"','"+ buycol[j][1] +"','"+ sellcol[j][2] +"','"+ sellcol[j][1] +"','sell','"+ sellcol[j][4] +"','"+ sellcol[j][5] +"','"+ sellcol[j][6] +"','"+ selllprice[j] +"','"+ sellhprice[j] +"')";
ResultSet buy=stmt.executeQuery(buysql);

String upagid="update numagid set msgid=msgid+2";
ResultSet update=stmt.executeQuery(upagid);
String upbuy="update agentbuy set status='yes' where agid=buyagid[j]";
ResultSet upbuy1=stmt.executeQuery(upbuy);
String upsell="update agentsell set status='yes' where agid=sellagid[j]";
ResultSet upsell1=stmt.executeQuery(upsell);
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

what is the SQLException and where is it occurring?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

just a quick note;
This won't work:
ResultSet id=stmt.executeQuery(idsql);
int msgid1=id.getInt("msgid");
After executing a query, you need to call to move the cursor to the first record, otherwise you will get an error.
xiaopeng shi

Joined: Nov 25, 2002
Posts: 3
hi,Jamie Robertson,
Thank you for your reply, after I tried many times, I found an error message with is Column not found, when I put a test message after buyagid[i]=sellbuy.getInt("agentbuy.agid");, the test message will not work, is it correct for this line? Am I use the correct get data method?
[ November 29, 2002: Message edited by: xiaopeng shi ]
I agree. Here's the link:
subject: how to read different data from one table and compare it
It's not a secret anymore!