• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Design question

 
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,
I have a requirement where i query the data with select statement, then check if these results exist in other table, if not, then insert them in it. This is what im thinking. I declare member varibles in the class called String name, String age. and then when i get my resultset through the select statement, i create a new object of the class and store that object in the arraylist, so everytime, i can check if the value already exists by using the contains(object) method in the arraylist like this.


I have just wrote this sample program here, so if there are any compile problems here, i could solve that (thats not what i'm worried about). My question is : is this a good approach to fulfill my requirement, if not, could someone please guide me towards a better approach. Also, im stuck using jdk1.3(it could be jdk 1.2, not sure) and can't upgrade it. Any replies would be greatly appreciated, thanks a lot for your time.

Mala
 
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can do this in a single SQL query (at least the select and the check).

If you have 2 tables A and B and you need to check whether field C from table A is there in field D of table B, do something like this:


Not 100% certain of the SQL (might also be DB specific for optimisations) but something like this should work.
You DO want to have an index on both field.

You can then insert records into B using the entire resultset you get from A as it's already filtered to not contain records matching data available in B.
 
Mala Sharma
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Jeroen,
Thanks alot for your suggestion. I have two fields called itemno, location(one itemno could have multiple locations),would it still give me desired results. BUt, what if you hadn't told me this sql statement and i would've have gone with my original design. Would that have been okay as well? or theres another good solution for someone who doesn't know sql that much. Could you please shed some light on it. Again, i really appreciate your help. Thanks


Mala
[ January 08, 2005: Message edited by: Mala Sharma ]
 
author
Posts: 799
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In order for the code-based solution to work, your test class (which should be named Test, not test, per Java convention) will need to have a .equals method. Otherwise the ArrayList will end up containing every object retrieved from the database. The contains method uses .equals for comparison purposes.

In your equals method, you will probably want to base your comparisons on the name field.

-j-
 
Mala Sharma
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Jeff,
Thanks for your reply . I'm not sure if i get you when u say:

your test class (which should be named Test, not test, per Java convention) will need to have a .equals method.


What i'm doing (see the code above) is saving the object in the arraylist and then checking to see if that ojects already exists by using the contain(object) method. Could you please shed some more light on what you're saying. THanks for your help.

Mala
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you call "a.contains(t)" the ArrayList a calls the equals() method on the Test objects it already contains to look for a match. The default implementation of equals() compares the object refereneces, returning true only if both references point to the same object. In your case, this will always return false.

To fix this, you need to override the equals() method to compare two Tests. For example, if two Tests are equal if their names are equals, you could add the following to the Test class.
 
Mala Sharma
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello David,

Thanks for your reply. I just found out today that this class is going to run everyday. So, i don't think i can use ArrayList. Because evertime the class is run, ArraYlist starts from empty and then fills it. My requirement is:
1. Do a query on table A and extract the fields b and c.
2. Check if those fields exist in table D (fields name here are e, f)
3 if not, insert them in the table D

Now, i was going to use "not in" clause in step 1 so it would only give me results that are not present in table d. But i don't think i can use "not in" with 2 fields. So, now what i have decided to do is:
1. query table D first and store all the result in the Arraylist like:



Now, i run my orginal query on table A (store it in new arrayList) and then check to see if the results already exist in ArrayList a mentioned above like:


When i run this program, it still tries to insert all the rows so i get primary key violation exception. I hope im clear on my requirement, what i have done and what i'm stuck on. If someone can please help me out, i would truely appreciate it. Thanks.

Mala
 
Mala Sharma
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
any ideas???
 
Ranch Hand
Posts: 1071
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would highly suggest you look at the SQL statement solution.

If you insist on doing it in code you could build two Collections, one from each table. Perform and .indexOf() and a remove() for each element in the Collection. Once that is done you will have a Collection with only the extra objects. Iterate through that and place them into the table.

If you can do it in SQL it will perform much better with less overhead.
 
reply
    Bookmark Topic Watch Topic
  • New Topic