JAVA，SQL，插入无重复项 [英] JAVA, SQL, insert into no-duplicates

问题描述

我试图插入没有重复的表中.我在Java中使用SQL derbyclient.该代码不起作用('where not exists'错误).有什么主意吗?

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

推荐答案

问题是如何构建查询.那不是有效的INSERT语法

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

这篇关于JAVA，SQL，插入无重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！