JAVA, SQL, insert into no-duplicates

Question

I am trying to insert into a table without duplicates. I am using SQL derbyclient in Java. The code is not working (error with 'where not exists'). Any idea?

Connection connection = DriverManager.getConnection("jdbc:derby://localhost:1527/PetroleumDataStorageSystemDB;create=true");
PreparedStatement statement;
int i = 1;
int progress = 0;
for (Refuel refuelList1 : refuelList) {
     progress = (i / refuelList.size()) * 100;
     String sql = "INSERT INTO refuel (id_tank, time, fuel_volume, "
                  + "refueling_speed) VALUES (?,?,?,?) "
                  + "WHERE NOT EXISTS (SELECT * FROM refuel WHERE "
                  + "id_tank = ? AND time = ? AND fuel_volume = ? AND "
                  + "refueling_speed = ?)";
     statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
     statement.setInt(1, refuelList1.getTankID());
     statement.setString(2, refuelList1.getTime());
     statement.setDouble(3, refuelList1.getFuelVolume());
     statement.setDouble(4, refuelList1.getRefuelingSpeed());
     statement.execute();
     i++;
}

I presume the ID is unique? if that's the case then remove it from your where not exists as it would be different? — Dane, Sep 22 '15 at 15:26

Juan Carlos Oropeza · Accepted Answer · 2015-09-22T15:40:37.653

The problem is how you build your query. That isnt a valid INSERT syntaxis

Try something like this on your db first with dummy values.

INSERT INTO refuel (id_tank, time, fuel_volume, refueling_speed) 
 SELECT ?, ?, ?, ? 
 FROM refuel
 WHERE NOT EXISTS (SELECT 
                   FROM refuel 
                   WHERE
                       id_tank = ? 
                   AND time = ? 
                   AND fuel_volume = ?
                   AND refueling_speed = ?);

score 0 · Answer 2 · answered Sep 22 '15 at 15:34

0

There are at least 8 question mark in your statement.

You have to set all of them!

After that you can check again and see if there are other errors.

answered Sep 22 '15 at 15:34

Davide Lorenzo MARINO

26,420
4
39
56

If you find the answer is correct and useful you can upvote it and flag it as correct answer with the green flag. Thank you! – Davide Lorenzo MARINO Sep 22 '15 at 15:44

sstan · Answer 3 · 2015-09-22T15:47:28.450

You can't have a WHERE clause after a VALUES list.

Have a look here for valid Derby INSERT statement syntax.

Try something like this instead (notice the use of sysibm.sysdummy1, which guarantees that you only ever insert a single record at most. If you put an actual table name in there, you may potentially insert multiple records a time. Careful with that.):

INSERT INTO refuel (id_tank, tank, fuel_volume, refueling_speed)
SELECT ?, ?, ?, ?
  FROM sysibm.sysdummy1
 WHERE NOT EXISTS (SELECT *
                     FROM refuel
                    WHERE id_tank = ?
                      AND time = ?
                      AND fuel_volume = ?
                      AND refueling_speed = ?)

Also, as noted by Davide Lorenzo MARINO, make sure you set all the binding parameters correctly.

JAVA, SQL, insert into no-duplicates

3 Answers3

Linked