CODEWITHSUNDEEP
javamysqldatabase
Vu Tran
Vu Tran

Reputation: 13

How do I prevent user from adding a duplicate entry into a MySQL database?

I am working on an inventory database program using Java. I wrote a method that adds new product and quantity to the database. I am stuck on how I can prevent user from adding a duplicate entry to the database. If the user enters a name that already exists in the database, the program should display an error message and show the main menu again. The program should not ask the user to enter the data again. The program should not replace the existing entry. Thanks in advance!

public static void addData() {
    try (Connection conn = DriverManager.getConnection(db_url, user, password)) {

        String product = stringInput("Please enter the name of the product to store in inventory");
        int quantity = intInput("Please enter the quantity of the product");

        String addProduct = "INSERT INTO inventory VALUES (?,?)";
        PreparedStatement insertProduct = conn.prepareStatement(addProduct);

        insertProduct.setString(1, product);
        insertProduct.setInt(2, quantity);

        insertProduct.executeUpdate();
        conn.close();

        } catch(SQLException sqle){
            sqle.printStackTrace();
            System.exit(-1);
        }


    }

Upvotes: 1

Views: 439

Answers (3)

Lawakush Kurmi
Lawakush Kurmi

Reputation: 3006

In that case create a id column in your table and which should be unique or primary key. And handle exception SQLIntegrityConstraintViolationException to show the error message on app display.

Upvotes: 0

Ravindra Ranwala
Ravindra Ranwala

Reputation: 21124

You can define UNIQUE constraint in your DB. Then if a duplicate entry is added it will throw an SQLIntegrityConstraintViolationException. Catch the exception and handle the error in your app.

May not be applicable to you, but just for your information spring-data exposes org.springframework.dao.DataIntegrityViolationException for achieving the same. When the same issue encountered spring-data wraps the underneath exception and re-throws a DataIntegrityViolationException so that the client can handle them inside the Error handling aspect using @ControllerAdvice.

Upvotes: 1

mohsenJsh
mohsenJsh

Reputation: 2108

you must query inventory table first to check if product name already exist, something like this

"select i.id from inventory where name=?"

then check with if clause that query has result or not, if true then do whatever needed, like show error...

Upvotes: 0

Related Questions