Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Combining fields from 2 resultsets in 1 vector

 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Jayesh A Lalwani
Rancher
Posts: 2756
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Debugger is your friend
 
Wouter Oet
Saloon Keeper
Posts: 2700
IntelliJ IDE Opera
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you making 2 queries? Why not just do:
 
Patrick de Kruijf
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have corrected the select statement, but sadly it still doesn't work.
 
Winston Gutkowski
Bartender
Pie
Posts: 10111
56
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Dwarka Damodaran
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic