Configure JPA to let PostgreSQL generate the primary key value

Question

So our project use PostgreSQL database and we use JPA for operating the database. We have created the entities from the database with automatic creator in Netbeans 7.1.2.

After small changes our primary key values are described as:

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Basic(optional = false)
@NotNull
@Column(name = "idwebuser", nullable = false)
private Integer idwebuser;

The problem is that now the application is not flexible, because when we modify the database directly (using SQL or another tool) instead of going thru the Java app - the Generated Value is lower than actual database ID value - and so we get error during the creation of new entities.

Is there a possibility that the JPA could just let the database generate the ID automatically and then obtain it after the creation process? Or what could be a better solution? Thanks.

EDIT More specifically: We have a table of users and my problem is that using any type of strategy generationtype, the JPA is inserting a new entity with a specified by it's generator id. Which is wrong for me, because if I make changes to the table on my own, by adding new entries, the GeneratedValue for application is lower than the current ID - what leads us to exception with duplicated ID. Can we fix it ;)?

a short note on the answer There was a little lie from my side because we've used a PG Admin -> View first 100 Rows and edited rows from there instead of using select. ANYWAY, it turns out that this editor somehow skips the process of updating the ID and so even in DB when we write a proper INSERT it is EXECUTED with improper ID! So it was basically more a problem of the editor we used than the database and application...

now it even works using @GeneratedValue(strategy=GenerationType.IDENTITY)

Answer 1

Given the table definition:

CREATE TABLE webuser(
    idwebuser SERIAL PRIMARY KEY,
    ...
)

Use the mapping:

@Entity
@Table(name="webuser")
class Webuser {

    @Id
    @SequenceGenerator(name="webuser_idwebuser_seq",
                       sequenceName="webuser_idwebuser_seq",
                       allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator="webuser_idwebuser_seq")
    @Column(name = "idwebuser", updatable=false)
    private Integer id;

    // ....

}

The naming tablename_columname_seq is the PostgreSQL default sequence naming for SERIAL and I recommend that you stick to it.

The allocationSize=1 is important if you need Hibernate to co-operate with other clients to the database.

Note that this sequence will have "gaps" in it if transactions roll back. Transactions can roll back for all sorts of reasons. Your application should be designed to cope with this.

• Never assume that for any id n there is an id n-1 or n+1
• Never assume that the id n was added or committed before an id less than n or after an id greater than n. If you're really careful with how you use sequences you can do this, but you should never try; record a timestamp in your table instead.
• Never add to or subtract from an ID. Compare them for equality and nothing else.

See the PostgreSQL documentation for sequences and the serial data types.

They explain that the table definition above is basically a shortcut for:

CREATE SEQUENCE idwebuser_id_seq;
CREATE TABLE webuser(
    idwebuser integer primary key default nextval('idwebuser_id_seq'),
    ...
)
ALTER SEQUENCE idwebuser_id_seq OWNED BY webuser.idwebuser;

... which should help explain why we have added a @SequenceGenerator annotation to describe the sequence.

---

If you really must have a gap-less sequence (for example, cheque or invoice numbering) see gapless sequences but seriously, avoid this design, and never use it for a primary key.

---

Note: If your table definition looks like this instead:

CREATE TABLE webuser(
    idwebuser integer primary key,
    ...
)

and you're inserting into it using the (unsafe, do not use):

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT max(idwebuser) FROM webuser)+1, ...
);

or (unsafe, never do this):

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT count(idwebuser) FROM webuser), ...
);

then you're doing it wrong and should switch to a sequence (as shown above) or to a correct gapless sequence implementation using a locked counter table (again, see above and see "gapless sequence postgresql" in Google). Both the above do the wrong thing if there's ever more than one connection working on the database.

Answer 2

It seems you have to use the sequence generator like:

@GeneratedValue(generator="YOUR_SEQ",strategy=GenerationType.SEQUENCE)

Answer 3

Please, try to use GenerationType.TABLE instead of GenerationType.IDENTITY. Database will create separate table which will be use to generate unique primary keys, it will also store last used id number.

Answer 4

You can also save yourself some effort by writing a script to perform a mass conversion of the generic GenerationType.IDENTITY to the solution proposed by the selected answer. The below script has some slight dependencies on how the Java source file is formatted and will make modifications without backups. Caveat emptor!

After running the script:

1. Search and replace import javax.persistence.Table; with import javax.persistence.Table; import javax.persistence.SequenceGenerator;.
2. Reformat the source code in NetBeans as follows:
   1. Select all the source files to format.
   2. Press Alt+Shift+F
   3. Confirm reformatting.

Save the following script as update-sequences.sh or similar:

#!/bin/bash

# Change this to the directory name (package name) where the entities reside.
PACKAGE=com/domain/project/entities

# Change this to the path where the Java source files are located.
cd src/main/java

for i in $(find $PACKAGE/*.java -type f); do
  # Only process classes that have an IDENTITY sequence.
  if grep "GenerationType.IDENTITY" $i > /dev/null; then
    # Extract the table name line.
    LINE_TABLE_NAME=$(grep -m 1 @Table $i | awk '{print $4;}')
    # Trim the quotes (if present).
    TABLE_NAME=${LINE_TABLE_NAME//\"}
    # Trim the comma (if present).
    TABLE_NAME=${TABLE_NAME//,}

    # Extract the column name line.
    LINE_COLUMN_NAME=$(grep -m 1 -C1 -A3 @Id $i | tail -1)
    COLUMN_NAME=$(echo $LINE_COLUMN_NAME | awk '{print $4;}')
    COLUMN_NAME=${COLUMN_NAME//\"}
    COLUMN_NAME=${COLUMN_NAME//,}

    # PostgreSQL sequence name.
    SEQUENCE_NAME="${TABLE_NAME}_${COLUMN_NAME}_seq"

    LINE_SEQ_GENERATOR="@SequenceGenerator( name = \"$SEQUENCE_NAME\", sequenceName = \"$SEQUENCE_NAME\", allocationSize = 1 )"
    LINE_GENERATED_VAL="@GeneratedValue( strategy = GenerationType.SEQUENCE, generator = \"$SEQUENCE_NAME\" )"
    LINE_COLUMN="@Column( name = \"$COLUMN_NAME\", updatable = false )\n"

    # These will depend on source code formatting.
    DELIM_BEGIN="@GeneratedValue( strategy = GenerationType.IDENTITY )"
    # @Basic( optional = false ) is also replaced.
    DELIM_ENDED="@Column( name = \"$COLUMN_NAME\" )"

    # Replace these lines...
    #
    # $DELIM_BEGIN
    # $DELIM_ENDED
    #
    # With these lines...
    #
    # $LINE_SEQ_GENERATOR
    # $LINE_GENERATED_VAL
    # $LINE_COLUMN

    sed -i -n "/$DELIM_BEGIN/{:a;N;/$DELIM_ENDED/!ba;N;s/.*\n/$LINE_SEQ_GENERATOR\n$LINE_GENERATED_VAL\n$LINE_COLUMN/};p" $i
  else
    echo "Skipping $i ..."
  fi
done

When generating the CRUD application using NetBeans, the ID attributes won't include editable input fields.

Answer 5

It work for me

1. create table like this, use SERIAL.

CREATE TABLE webuser(
    idwebuser SERIAL PRIMARY KEY,
    ...
)

2. add @GeneratedValue(strategy = GenerationType.IDENTITY) at id field.

@Entity
@Table(name="webuser")
class Webuser {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    // ....

}