How do you compare values in a database and a flat file in Java?

I have a flat file that is pipe delimited and an database table. I would like to开发者_如何学运维 create a 2nd flat file that contains all the differences between the flat file and the database.

An example would be if there exists entries in the database that do not exist in the flat file the entire row would be printed in the 2nd flat file (there will never be an entry in the flat file that is not in the database). Another example would be if the rows are similar but there is a difference in a field then the database entry would be printed in the flat file.

The best way I can think to do this is to create 2 two-dimensional arrays containing the data from the flat file and the database, if the primary key does not exist in the flat file array then print out the results from a select statement. If the primary key does exist but the other fields do not exist then print out the results from a select statement. This is the best thing i can think of, however it seems inefficient.

Is there a better way to do this? I am using Java to do this.

Since you're already comparing against a database, why not just do it all in SQL? Upload the contents of the file into the database, find the two complements (A minus B and B minus A) and export to another file. Something like:

SPOOL diff.txt
SELECT * FROM flatfile_table
MINUS
SELECT * FROM db_table
UNION
SELECT * FROM db_table
MINUS
SELECT * FROM flatfile_table
ORDER BY SortCriteria;
SPOOL OFF

in oracle you can create external table from file tutorial for external tables

then create simple select that will build diff between your data and then simply export

I would do something like this:

• Load the results from the database on a Map structure where the key is the Primary Key column and the value is the row itself
• Iterate the flat file collection, search the Map structure based on the primary key and add the differences to a list that will be your output.
• write to the output the differences you previously saved on the list.

Write Java program which gets data from database into another flat file in the same format as the file you already have.

Make sure both files are ordered by the same field, e.g., primary key of your records -- it can be achieved by awk, for example in case of such input file (sorting key is last number):

val1|2
val7|1

you can sort entries by sort -n --field-separator=\| -k 2 1 (see sort manpage for explanations of options involved).

Then use diff (or similar, if you not on Unix/Linux) utility to create file with different entries.