1

I am running an SQL statement in through an infinite for loop and but can't find a way to only execute it only once if the values don't already exist in the database. How can I resolve this?

if(phoneWeight[0] <= weight && weight <= phoneWeight[1]) {
        phoneOnScale = true;
        System.out.println("Phone is on scale");
        // write one phone to table in db.
        try {
            // create a mysql database connection
            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://localhost:3306/smartfridge";
            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, "root", "admin");

            // the mysql insert statement
            String query = " insert into fridge (name, UnitOfSale, ContentsQuantity, department, AverageSellingUnitWeight)"
                    + " values (?, ?, ?, ?, ?)";

            // create the mysql insert preparedstatement
            PreparedStatement preparedStmt = conn.prepareStatement(query);
            preparedStmt.setString(1, "Eggs");
            preparedStmt.setInt(2, 1);
            preparedStmt.setInt(3, 6);
            preparedStmt.setString(4, "Milk, Butter & Eggs");
            preparedStmt.setBoolean(5, phoneOnScale);

            // execute the preparedstatement
            preparedStmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            //System.out.println("Eggs added to Fridge");
        }
    } else {
        phoneOnScale = false;
    }
a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
jconboy
  • 25
  • 6
  • Add a `UNIQUE` constraint on the table, and let the database reject it if it's already there. – The Impaler Mar 12 '19 at 19:41
  • 1
    https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – YCF_L Mar 12 '19 at 19:41
  • @TheImpaler where exactly do I add the unique constraint? CREATE TABLE `fridge` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `UnitOfSale` int(11) DEFAULT NULL, `ContentsQuantity` int(11) DEFAULT NULL, `department` varchar(32) NOT NULL, `AverageSellingUnitWeight` double(40,1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; – jconboy Mar 12 '19 at 19:48
  • 1
    If you want to avoid repeated `name`, then I would do: `CREATE TABLE fridge ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, UnitOfSale int(11) DEFAULT NULL, ContentsQuantity int(11) DEFAULT NULL, department varchar(32) NOT NULL, AverageSellingUnitWeight double(40,1) DEFAULT NULL, PRIMARY KEY (id), constraint fridge_uq_name unique (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci`. – The Impaler Mar 12 '19 at 19:50

0 Answers0