Just wondering if there is a way to insert into a table and the last 2 columns of the table are foreign keys to a lookup table.
at the moment i’m doing this…
-select from lookup table
-loop through insert document array
-loop through the select lookup table
-if a certain value from the insert document array[i] = lookup table array[i] value then populate values from the lookup table to the insert document array
after all of that then the insert document now has the last two column values populated to insert.
Just wondering is there a more effecient way? I dont want to loop connect to the db too many times.
Have you thought about using hash table ? Create a hash table with lookup table and then pull the values from hash table using keys in insert document array.
I don’t suggest nested JDBC calls. First get the lookup data and store it in memory. Then get the actual data, lookup, and then batch insert. As Talha Khan suggested any of Java Hash table or HashMap will serve this purpose.
Does anyone have any examples on a java service which creates a hash table? My idea is the first service takes a document array and returns an object (created hash table) and then another service will take an object as an input with a string as a key and a string as the value…(this service will lookup the hashtable)
You can use a static hashmap variable with the declaration private static Map paramsTable = Collections.synchronizedMap(new HashMap()); in shared source section.
use one java service that accepts lookup data as doclist, feeds this hasmap with it.
create one more java service (in same folder as the above) and this either accepts each key at a time and returns value
–or–
accepts an entire insert doclist, for each doc gets the corresponding value from lookup hashmap, add it to the document and finally outputs the finished doc list which you can do a batch insert.
if you want you can create one more java service to explicitly clear the hashmap (i suggest) after the operation.