PostgreSQL Insertion in JAVA via JSP Incrementing Primary Key

Question

I am writing a JSP application where the user enters a food item and it is entered in a PostgreSQL database. I had no problems implementing this when the user manually had to enter the next primary key, but I removed this ability so that the primary key would be automatically assigned when the enter button is clicked. I would like the query to fetch the current maximum FID (Food ID) and set the new food item's FID to the previous + 1.

try {

    conn = ConnectionProvider.getCon();
    String sql = "select fid from project.food order by fid desc limit 1";
    pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    status = pst.getResultSetType();
    f.setFood_id(status + 1);

    pst = conn.prepareStatement("insert into project.food values(?,?,?,?,?,?)");
    pst.setInt(1, f.getFood_id());
    pst.setString(2, f.getFood_name()); //set name
    pst.setInt(3, f.getCount()); //set count
    pst.setInt(4, f.getPrice_per_item()); //set price
    pst.setInt(5, f.getThreshold()); //set threshold
    pst.setString(6, "false");
    status = pst.executeUpdate();
    conn.close();
    pst.close();
} catch (Exception ex) {
    System.out.println(ex);
}
return status;
}

The first food item is successfully inserted into the database in row 1006, instead of the 7th row, which is the first available in the database. Additionally, the second insert fails due to the failure of the primary key to the incremented by 1. The program again tries to insert the next tuple in the same row and thus violates the primary key constraint.

    org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "food_pkey"
    Detail: Key (fid)=(1006) already exists.

Kayaman · Accepted Answer · 2017-03-30 20:04:52Z

1

Make your primary key autoincrement in the database by declaring it SERIAL datatype (basically an auto incrementing INT) so a sequence is created to automatically assign values to it.

Then convert your update statement to specify all columns except for the primary key (i.e. insert into project.food(foo, bar, baz) values (?, ?, ?), and remove one ? placeholder and the pst.setInt(1,f.getFood_id()); line. This will insert values to all the other columns, and the primary key will be generated by the database.

This way you don't need to do a select when you want to insert (which was a really bad idea anyway), and you let the database do what it does best. You don't need to care about the value of the primary key after that.

edited Mar 30, 2017 at 20:04

answered Mar 30, 2017 at 19:58

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hailnick Over a year ago

Thank you for your response. I did initially have it set up as SERIAL exactly as you mentioned but the problem is that I want to use an SQL statement to bulk add food items into the food table (to be used to test the program). When a user adds from the JSP page, the serial increment tries to start at 1 and again I run into the same issue.

hailnick Over a year ago

I have found where I went wrong with implementing the table with SERIAL and can add things to the table manually with the same notation you suggested. By when I try it via the insert JSP page I have created I run into an error that I have not been able to identify: org.postgresql.util.PSQLException: ERROR: column "fid" is of type integer but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 33

Kayaman Over a year ago

There's no way that inserting from a JSP page would make SERIAL reset itself. It's controlled by the database, so unless you're explicitly assigning values to it instead of leaving it out of the insert statements, Java has nothing to say about it. Based on your exception, you're still trying to explicitly assign a value to the primary key instead of letting Postgres do it for you.

hailnick Over a year ago

Thank you! Solved. Had accidently messed with the value of the column.

Collectives™ on Stack Overflow

PostgreSQL Insertion in JAVA via JSP Incrementing Primary Key

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related