File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes Combining fields from 2 resultsets in 1 vector Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Combining fields from 2 resultsets in 1 vector" Watch "Combining fields from 2 resultsets in 1 vector" New topic
Author

Combining fields from 2 resultsets in 1 vector

Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
Hello there,

I'm trying for over a week now, but I can't seem to figure it out myself, I hope someone can help me a little further.

I want to fill a vector with data from a MySQL database called `spot`, I need that vector to display the database info in a JList later on.

The while loop for the resultset resultAllSpots works flawlessly. The problems start when I want to add an extra collumn to the vector, with data from the resultset resultId. When I do that, the programm doesn't run.

When I say VRow.addElement("test"); it does print test in my JList, so it kinda works I guess. But I when I say VRow.addElement(resultId.getString(1)); the code will not run. I hoped that the column `id` would be added in my JList.

I want to add the `id` column for testing matters. Later on I want a field that counts duplicate 'soort' fields from the database and have that added as final column to the vector. But first I want to be sure I add the Element the way I should.

Hope you can help me out here,

Thanks for your time en effort!

Patrick de Kruijf



------------------8<------------------
please cut here
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2371
    
  28

Debugger is your friend
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

Why are you making 2 queries? Why not just do:


"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." --- Martin Fowler
Please correct my English.
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
Wouter Oet wrote:Why are you making 2 queries? Why not just do:


I need to add a query that counts how many duplicates of the field 'soort' are in the database as a last column in the vector and thus JList.

But first I want to test if I am able to add only the field `id`as a final column next the other query to the JList. If I get this working, I can move on and replace the simple/test query `id` with a more complicated one that counts the duplicates of `soort`.
Claudiu Chelemen
Ranch Hand

Joined: Mar 25, 2011
Posts: 71

I couldn't help noticing that your statement contains two different kinds of apostrophes:

SELECT `id' FROM spot

Is that a valid select statement? I don't know what database you're using, I'm assuming mySql..

Claudiu
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
Claudiu Chelemen wrote:I couldn't help noticing that your statement contains two different kinds of apostrophes:

SELECT `id' FROM spot

Is that a valid select statement? I don't know what database you're using, I'm assuming mySql..

Claudiu


Thanks, that is not a valid select statement! I sure hope that this is the source of the problems I get when I want tot run the program, but I think that this is a typo I made after a lot of testing last night, and I guess there is something more to fix. I have to work all day before I cant correct the statement and test it once again . . .
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
I have corrected the select statement, but sadly it still doesn't work.
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7773
    
  21

Patrick de Kruijf wrote:I need to add a query that counts how many duplicates of the field 'soort' are in the database as a last column in the vector and thus JList.

I suggest you separate that completely from your data retrieval then. In most SQLs I know:
1. SELECT COUNT(*) FROM spot - will return the number of rows in spot.
2. SELECT COUNT(soort) FROM spot - will return the number of rows with an instance of 'soort' in spot.
3. SELECT COUNT(DISTINCT soort) FROM spot - should return the number of distinct 'soort' values in spot.

The only difference between 1 and 2 is that I believe rows with NULL 'soort' values aren't included in 2 (but I may be wrong).

Either way, the difference between 2 and 3 should be the number of duplicates.

Winston

Edit: Confirmed, at least according to w3schools: Number 2 will not include NULLs.


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Dwarka Damodaran
Greenhorn

Joined: Nov 10, 2011
Posts: 7
Hay Patrick,

your code for resultId does not seem to be doing a .next() and that might also be a problem.

Regards,
Dwarka
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
Winston Gutkowski wrote:I suggest you separate that completely from your data retrieval then. In most SQLs I know:


Thanks for the advice, I didn't know the statement would be that easy to count the duplicates, you helped me a lot!

Dwarka Damodaran wrote:Hay Patrick,

your code for resultId does not seem to be doing a .next() and that might also be a problem.

Regards,
Dwarka


Maybe I don't know exactly what you mean, but the resultId is in the while loop wich gets his .next() from resultAllSpots()



Hello Dwarka, can you please help me a little further? I really don't know how to implement the .next() to the resultId() I have tried a lot last couple of days, but I just can't get past adding the extra ellement to the Vector and displaying both queries in the JList.
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
I changed the code a bit, to make my problem perhaps a little more clear.

I have 2 resultsets; resultOne and resultTwo. Both resultsets have the same query, I've done this to make my test more clear.

The first part of my code uses resultOne to read the data from the fields `spotter`, `soort`, `datum`, `tijd` from database spot and adds this data to the first 4 elements of the the Vector VRow.
The second part of my code has to read data from resultset resultTwo and add this as element 5 of the vector VRow.
After this VRow gets added to Vector vec, which is displayed in a JList.

When I take resultOne as the source of the final .addElement() in the method, the program runs just fine. It adds the queried data to vector and the JList displays the data from the 5 fields `spotter`, `soort`, `datum`, `tijd` and `spotter` again because of the last .addelement() statement. When I use resultTwo as the source of the final .addEllement(), it doesn't compile, and that's weird, because to me resultTwo is exactly the same code as resultOne.

So this does compile:



.... but when I say VRow.addElement(resultTwo.getString(1)); instead of VRow.addElement(resultOne.getString(1)); :



. . . it doesn't compile, why is that?
Patrick de Kruijf
Ranch Hand

Joined: Mar 02, 2010
Posts: 63
Had a day off to study, just got it figured out, adding resutlTwo.next(); to the while loop did the trick Thanks for directing me the right way!

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Combining fields from 2 resultsets in 1 vector