Check registered data before inserting? JAVA

Question

I have a database in SQL with a table named "Vehicle". It has a primary key as a VARCHAR. What I'm triying to do is validate whether the ID i'm entering is already registered. "plate" is my Primary Key.

This is my "Insert" method on Vehicle class:

int match = 0;
       try {
        String query = "insert into vehicle values  ('"+this.plate+"','"+this.maker+"','"+this.model+"','"+this.color+"','"+this.year+"','"+ this.price+"') ";

        state = con.createStatement();

        match = state.executeUpdate(query);

        return match;
    } catch (SQLException ex) {
        Logger.getLogger(Vehicle.class.getName()).log(Level.SEVERE, null, ex);
    }
    return match;

This is my "Insert" code on a Internal Frame:

String plate = txtPlate.getText().trim();
    String model = txtModel.getText().trim();
    String maker = txtMker.getText().trim();
    String color = txtColor.getText().trim();
    String year = txtYear.getText().trim();
    String price = txtPrice.getText().trim();

    if(plate.equals("") || maker.equals("") || model.equals("") || color.equals("") || year.equals("") || price.equals("") )
        JOptionPane.showMessageDialog(rootPane, "Cant leave blank spaces");

    else{

        try {
            int year = Integer.parseInt(year);
            int price = Integer.parseInt(price);

            Vehicle v1 = new Vehicle(plate, model, maker, color, year, price);


        if (v1.insert() > 0) 
            {
                JOptionPane.showMessageDialog(rootPane, "Register succes!");
            }else
            {
                JOptionPane.showMessageDialog(rootPane, "Register failed");
            }

        }catch (NumberFormatException exc)
        { 
            JOptionPane.showMessageDialog(this, "Error, insert numbers on 'YEAR and PRICE'");

        }

    }

I tried catching the an exception, but didn't work. Every time I add a repeated Plate(PK) my programs throws an exception, though I can't seem to use it.

This is a bit of the log after trying to add a vehicle with the same plate(PK)

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'ARP22' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

What does `insert` actually throw/return? How does the code work? — MadProgrammer, Dec 08 '15 at 01:28
That's for validating if the user inputs a string instead of numbers. — Midori_hige, Dec 08 '15 at 01:29
so does your code throw the `SQLException` ? What does it log? — Scary Wombat, Dec 08 '15 at 01:37
v1.insert() is my method of inserting inside my Vehicle class. Updated with a few of the log. — Midori_hige, Dec 08 '15 at 01:41

score 1 · Answer 1 · answered Dec 08 '15 at 02:41

Use INSERT IGNORE (MySQL specific), or use the SQLException as signal for duplicate keys. INSERT IGNORE does not throw the exception.

Also better use a PreparedStatement. Besides being a security measure against SQL injection, it escapes special chars like apostrophe and backslash. And allows types.

    String query = "insert ignore into"
        + " vehicle(plate, maker, model, color, year, price)"
        + " values(?, ?, ?, ?, ?, ?)";

    try (PreparedStatement stm = con.prepareStatement(query)) {
        stm.setString(1, this.plate);
        stm.setString(2, this.maker);
        stm.setString(3, this.model);
        stm.setString(4, this.color);
        stm.setInt(5, this.year);
        stm.setInt(6, this.price);
        match = state.executeUpdate();
    }

Listing the column names ensures that database changes remain traceable.

How can I show a message that the input vehicle is already registered? I can't see where. — Midori_hige, Dec 08 '15 at 02:53
`match` will be given the update count of the insert statement, `> 0` means the insert succeeded. So your code should work if I got it right. — Joop Eggen, Dec 08 '15 at 04:03

score 0 · Answer 2 · edited May 23 '17 at 12:15

You basically have two options.

First option is to check if the primary key (PK) already exists in the database by doing a SELECT. If it exists, then UPDATE the row, if not INSERT a new row.

Second option is to use INSERT ON DUPLICATE KEY UPDATE. This will automatically detect when the PK already exists and will do the UPDATE if needed. If I guess the table columns name it will probably look like :

   String query = "insert into vehicle values  ('"+this.plate+"','"+this.maker+"','"+this.model+"','"+this.color+"','"+this.year+"','"+ this.price+"') "+
                  "on duplicate key update vehicle.maker = values(maker), vehicle.model = values(model), vehicle.color = values(color), vehicle.year = values(year), vehicle.price = values(price) ";

Side notes :

The code you provided seems vulnerable to SQL injection.
Relying on an exception for regular code flow is a bad practice.

By using the 2nd option, it updates the data if the Plate is the same. I need to show a message if this happens. I'll go with the first option I guess. This is better to be done under the "Vehicle" class "Insert" method, right? — Midori_hige, Dec 08 '15 at 02:36
Go for first option if you wish but if you have concurrency you should wrap the two SQL commands inside a transaction. — ForguesR, Dec 08 '15 at 02:58

score 0 · Answer 3 · edited May 23 '17 at 11:44

Update for race conditions on insert

As @david pointed out, there is a possibility that my suggested solution below could still be error prone if a second source attempts to insert a row with the same PK as the row you are currently trying to insert. To remedy this, you can:

Lock tables prior to inserting
Or, if you don't care which insert wins, you can INSERT IGNORE. MySQL will not return errors (or warnings for that matter) for a duplicate key error when using INSERT IGNORE
Finally, you can use INSERT ... ON DUPLICATE KEY UPDATE. This will insert the row if the PK doesn't exist, otherwise it will update the existing one if it does.

To prevent the exception when inserting into the database, simply query for the plate number that will be inserted prior to attempting to insert it:

select 1 from vehicle where plate = <vehicle_plate>

If anything is returned from that query, then you know the vehicle ID already exists. The query above is assuming that plate is the field is the PK for the vehicle.

However, and more importantly, the code that interacts with the database is vulnerable to SQL injection.

It is never a good idea to pass user input in a query string to the database. Please use parameterized queries. Additional link: parameterized queries in Java.

Check registered data before inserting? JAVA

3 Answers3