JAVA, SQL, insert into no-duplicates

问题:

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++;
}

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

回答2:

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.

回答3:

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.