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
– Dane, Commented Sep 22, 2015 at 15:26

Juan Carlos Oropeza · Accepted Answer · 2015-09-22 15:40:37Z

1

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 = ?);

edited Sep 22, 2015 at 15:40

answered Sep 22, 2015 at 15:31

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Davide Lorenzo MARINO · Accepted Answer · 2015-09-22 15:34:55Z

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, 2015 at 15:34

Davide Lorenzo MARINO

27k4 gold badges45 silver badges58 bronze badges

1 Comment

Davide Lorenzo MARINO Over a year ago

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!

sstan · Accepted Answer · 2015-09-22 15:47:28Z

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.

Collectives™ on Stack Overflow

JAVA, SQL, insert into no-duplicates

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related