Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Check for duplicates in database before insert

 
Igor Juric
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anybody?
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Igor Juric
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Igor Juric wrote:...But I still get similar error.
Can you show us that error?
 
Igor Juric
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can do the check after this line:

 
Igor Juric
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, but how?



This still throws me duplicate error.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you have experience with jdbc, and with executing sql select statement in java?
 
Igor Juric
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not much...
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic