CODEWITHSUNDEEP
javamysql
damianm
damianm

Reputation: 131

INSERT INTO unique field

I'm creating parcel machine program. Every parcel has unique parcelID which is exported to mysql db. The problem is that every time when I run the program, the program is counting parcelID from 0. I'm looking for a solution which will allow me to check the last parcelID in the database and create row after the last one.

Now it looks like this: 1. I'm creating a new row in db (successfully) by java program. 2. I'm closing the program after some time. 3. I run the program again and I can't add another new row because there is error "duplicate entry '1' for key 'PRIMARY'".

public static void post() throws Exception{

    int parcelID = Parcel.generateID();
    int clientMPNumber = Parcel.typeClientNumber();
    int orderPassword = Parcel.generatePass();

    try{
        Connection con = getConnection();
        PreparedStatement posted = con.prepareStatement("INSERT INTO Parcels.Orders (parcelID, clientMPNumber, orderPassword) VALUES ('"+parcelID+"', '"+clientMPNumber+"', '"+orderPassword+"')");

        posted.executeUpdate();
    }
     catch(Exception e){
        System.out.println(e);
     }
     finally{
        System.out.println("Insert completed");
     }
}

and the method is:

public static int generateID(){
     parcelID = parcelID + 1;
     return parcelID;
 }

Upvotes: 2

Views: 100

Answers (4)

Joop Eggen
Joop Eggen

Reputation: 109547

There are a couple of things to attent to.

    // parcelID should be an INT AUTOINCREMENT primary key.
    try (PreparedStatement posted = con.prepareStatement(
            "INSERT INTO Parcels.Orders (clientMPNumber, orderPassword) "
            + "VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS);
        posted.setString(1, clientMPNumber);
        posted.setString(2, orderPassword);
        posted.executeUpdate();
        try (ResultSet rsKey = posted.getGeneratedKeys()) {
            if (rsKey.next()) {
                int parcelID = rsKey.getInt(1);
                return parcelID; // Or such
            }try-with-resources
        }
    }

The database can deal with automatic numbering best, so that two transactions at the same time do not steal the same "next" number.

You should close things like Connection, PreparedStatement and ResultSet. This can best be done using the a bit awkward syntax of try-with-resources. That closes automatically even on exception and return.

PreparedStatements should be used with placeholders ?. This takes care for escaping special characters like ' in the password. Also prevents SQL injection.

Stylistic better use SQLException above Exception. Better maybe even a throws SQLException.

Upvotes: 1

Sabir Khan
Sabir Khan

Reputation: 10132

As described here , define your primary key column to auto increment for each insert so your java code doesn't have to manually calculate primary key value each time.

If that is not a possibility, you need to show how you declare & initialize parcelID. As of your current code, parcelID looks to be a class level field that gets initialized to zero for each run so you always get the same value - 1. You need to initialize with last value from data base.

Also, implement suggestion as mentioned in comment to your question regarding PreparedStatement

Upvotes: 1

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

You shouldn't use Id generation, just create auto_increment column in database table

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

I'd let the database do the heavy lifting for you - Just define the parcelID column as serial instead of trying to set its value yourself.

Upvotes: 1

Related Questions