Thread: Check for duplicates before inserting into database

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:

//DATE
        DateFormat dateF = new SimpleDateFormat("dd.MM.yyyy");
        Date date = new Date();
//DATE        
 
//DB INIT
        String URL = "jdbc:mysql://192.168.1.128:3306";
        Connection con = (Connection) DriverManager.getConnection(URL,user,pass);
        Statement stmt = (Statement) con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        Statement stmt1 = (Statement) con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String mySQL_log = ("CREATE TABLE IF NOT EXISTS dbtest.T_AJPES_TR_LOG"
            + "(ID INT unsigned PRIMARY KEY AUTO_INCREMENT, Date_import VARCHAR(45),"
            + "File_import VARCHAR(75)) ENGINE = INNODB");
        String mySQL_new_table = ("CREATE TABLE IF NOT EXISTS dbtest.T_AJPES_TR "
            + "(row_count INT PRIMARY KEY AUTO_INCREMENT,"
            + "rn CHAR(15),sSpre CHAR(5),reg CHAR(5),eno VARCHAR(10),davcna VARCHAR(15),Ime VARCHAR(75),"
            + "Priimek VARCHAR(75),ID_LOG INT unsigned,"
            + "CONSTRAINT FOREIGN KEY(ID_LOG) references T_AJPES_TR_LOG(ID) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = INNODB");
 
            stmt1.executeUpdate(mySQL_log);
            ResultSet uprs1 = stmt1.executeQuery("SELECT * FROM dbtest.T_AJPES_TR_LOG");
            stmt.executeUpdate(mySQL_new_table);
            ResultSet uprs = stmt.executeQuery("SELECT * FROM dbtest.T_AJPES_TR");
//SELECT INIT
 
//READ FILE        
        File folder = new File(readFolder);
        String[] fileName = folder.list();
 
        for (;k<fileName.length;k++) {
            name = fileName[k];
 
            if (name.contains(".xml")) {
                FileInputStream fstream = new FileInputStream(readFolder + name);
                DataInputStream in = new DataInputStream(fstream);
                BufferedReader br = new BufferedReader(new InputStreamReader(in));
//READ FILE
 
                uprs1.afterLast();
                uprs1.moveToInsertRow();
                uprs1.updateString("Date_import",dateF.format(date));
                uprs1.updateString("File_import",name);
                uprs1.insertRow();
                i=0;

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 ...

Crossposted: Check for duplicates before inserting into mySQL table - Java Forums