This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes Check for duplicates in database before insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Check for duplicates in database before insert" Watch "Check for duplicates in database before insert" New topic
Author

Check for duplicates in database before insert

Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hi!

First I would like to say I'm sory for opening new thread about this problem but I googled and also searched this site for this problem but I didn't find anything that could do job for me.
So on problem... I have two mySQL tables, T_AJPES_TR_LOG_ table(with ID,Date_import and File_import) and T_AJPES_TR table that I store data from XML files. In TR_LOG table I write filename from which I want to import file.
What I would like to do is that before I insert filename in TR_LOG, I want to check if that file wasn't imported before. If it was imported then program tries to import next file otherwise import it.

So here is my scratch code:



This is not entire code from program, only part where I check if file is already imported or not. Further is only XML parsing part.

edit: I tried to declare File_import as unique key but when it comes to uprs1.insertRow() then it throws duplicate error(if file was already imported).
Also tried to move uprs1 = stmt.executeQuery("SELECT * FROM dbtest.T_AJPES_TR_LOG WHERE File_import" + name) resultset after for loop and then it throws me error unknown column File_import ...
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Anybody?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

your code stmt.executeQuery("SELECT * FROM dbtest.T_AJPES_TR_LOG WHERE File_import" + name) does not give a correct sql statement. You get the invalid sql statement SELECT * FROM dbtest.T_AJPES_TR_LOG WHERE E File_IMPORTyourfile.xml

You can execute a PreparedStatement with query "select count(*) from T_AJPES_TR_LOG_ table where File_import = ?",
use setString(1, name) to bind the filename to the ?
and execute the statement to check if this file has already been put in the log table.


OCUP UML fundamental and ITIL foundation
youtube channel
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Thanks for answer. Yes, I noticed I made mistake and correct it. I will also optimize my code with preparedStatement but first I'll finish by this way to make it work.

But I still get similar error. Where/how to execute statement if log was already put in table?

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Igor Juric wrote:...But I still get similar error.
Can you show us that error?
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Sure, here it is:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'RTR_AJPES_20101027_POS_S_D.xml' for key 'File_import'


I realized it wasn't silimar, it's duplicate entry error.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

You can do the check after this line:

Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Ok, but how?



This still throws me duplicate error.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Igor Juric wrote:Ok, but how? ..
You execute the query to check if the file is already present in the log table.
You check the result of that query.
If the query has found a matching record in the log table, you stop, because the file is already handled.
Else you process the file, and insert a record in your log table to show that the file is handled.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hi!

I figured that later but I don't know how to see result of this query? Do I have to change this result set to int or what?



Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Do you have experience with jdbc, and with executing sql select statement in java?
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Not much...
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

Can I advise you to first get acquinted with JDBC? Once you understand how to do select, insert, update and delete records from a table with JDBC, it will be easier to understand how it fits with your code.
The basic principles are not difficult at all. But when you try to mix them with your program logic (maybe you are struggling with that too) it becomes complex.
http://download.oracle.com/javase/tutorial/jdbc/basics/index.html

Keep following the trail at least until you learned about the PreparedStatement. By then (and it will not take you long) you have mastered everything you need for your problem:
Running a query
Getting the results of a query
Inserting data in a table

Good luck.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Thanks for link and help.

I found out what was problem and fixed it and it works like a charm. Now I need to optimize my code and hope it will go well.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2501
    
    8

 
GeeCON Prague 2014
 
subject: Check for duplicates in database before insert