aspose file tools*
The moose likes Beginning Java and the fly likes Design question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Design question" Watch "Design question" New topic
Author

Design question

Mala Sharma
Ranch Hand

Joined: Oct 29, 2004
Posts: 76
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
Jeroen Wenting
Ranch Hand

Joined: Oct 12, 2000
Posts: 5093
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.


42
Mala Sharma
Ranch Hand

Joined: Oct 29, 2004
Posts: 76
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 ]
Jeff Langr
author
Ranch Hand

Joined: May 14, 2003
Posts: 762
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-


Books: Agile Java, Modern C++ Programming with TDD, Essential Java Style, Agile in a Flash. Contributor, Clean Code.
Mala Sharma
Ranch Hand

Joined: Oct 29, 2004
Posts: 76
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
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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

Joined: Oct 29, 2004
Posts: 76
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

Joined: Oct 29, 2004
Posts: 76
any ideas???
Steven Bell
Ranch Hand

Joined: Dec 29, 2004
Posts: 1071
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Design question