Hibernate jumping id sequence

Question

I managed a project by JHipster who uses Java, Spring and Hibernate in backend, I created a class as follows:

Liquibase:

 <changeSet id="20160504131602" author="jhipster">
        <createTable tableName="grupo">
            <column name="id" type="bigint" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="nm_grupo" type="varchar(35)">
                <constraints unique="true" nullable="false" />
            </column>
 </createTable>

Domain:

@Entity
@Table(name = "grupo")
@Document(indexName = "grupo")
public class Grupo implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @NotNull
    @Size(min = 1, max = 35)
    @Column(name = "nm_grupo", length = 35, unique = true, nullable = false)
    private String nmGrupo;

The point is that when I have a record saved (Rest class):

@RequestMapping(value = "/grupos",
        method = RequestMethod.POST,
        produces = MediaType.APPLICATION_JSON_VALUE)
    @Timed
    public ResponseEntity<Grupo> createGrupo(@Valid @RequestBody Grupo grupo) throws URISyntaxException {
        log.debug("REST request to save Grupo : {}", grupo);
        if (grupo.getId() != null) {
            return ResponseEntity.badRequest().headers(HeaderUtil.createFailureAlert("grupo", "idexists", "A new grupo cannot already have an ID")).body(null);
        }
        Grupo result = grupoService.save(grupo);
        return ResponseEntity.created(new URI("/api/grupos/" + result.getId()))
            .headers(HeaderUtil.createEntityCreationAlert("grupo", result.getId().toString()))
            .body(result);
    }

Which in turn calls the function save of the implementation class:

public Grupo save(Grupo grupo) {
        log.debug("Request to save Grupo : {}", grupo);
        Grupo result = grupoRepository.save(grupo);
        grupoSearchRepository.save(result);
        return result;
    }

Saving the object physically in the bank:

public interface GrupoRepository extends JpaRepository<Grupo,Long> {

}

If I send save an object with a name already registered in the database Hibernate returns the error:

ERROR: Duplicate key value violates Unique Constraint "group_nm_group_key"**

This error I have already dealt with, but when I save a new record the generated code for the id is skipped at +1, thus getting a lost id.

Ex:

1. Save the group: To (id generated: 1)
2. Save the group: To (error because there is already value in the bank)
3. Save the group: AS (id generated: 3)

Note that I lost id 2.

Someone knows an elegant way to solve this problem, without having to create a new query before save to check if the name has already been registered?

Answer 1

Probably this Identifier is being generated by backend (Postgresql).

This phenomenon happens because the SEQUENCES in Postgresql are resistant to ROLLBACKs within transactions.

Probably the autoIncrement column id, is using an object of SEQUENCE in the database:

<column name="id" type="bigint" autoIncrement="${autoIncrement}">
    <constraints primaryKey="true" nullable="false"/>
</column>

SEQUENCES are intended to generate unique identifiers - not necessarily identifiers that are strictly sequential!

You must not use SEQUENCES when you are in need of a sequence with no intervals between the identifiers.

For example, the SEQUENCES ensure that if two customers simultaneously attempt to obtain a value from a sequence (using nextval()), each customer will receive a different sequential value. If one of these customers subsequently aborts its transaction using a ROLLBACK, the sequence value that was generated for that client will be lost and this will create a gap in the sequence.

The following example illustrates the functioning of a SEQUENCE within a transaction:

-- Criação do Objeto de SEQUENCE
CREATE SEQUENCE sq_teste;

-- Inicializa a Sequence
SELECT nextval('sq_teste');

-- Verifica o valor da sequencia antes de entrar na transação
SELECT currval('sq_teste'); -- ID=1

BEGIN;
SELECT nextval('sq_teste'); -- ID=2
SELECT nextval('sq_teste'); -- ID=3
SELECT nextval('sq_teste'); -- ID=4
ROLLBACK;

-- Verifica o valor da sequencia após ROLLBACK da transação
SELECT currval('sq_teste'); -- ID: 4!

--- fim-de-arquivo ---

Note that even apoś the ROLLBACK the value of the sequence did not return to what it was before the start of the transaction.

Now the question is mine: Why registered Ids need to respect an increasing sequence without intervals ?

I hope I’ve helped!

Answer 2

Unfortunately I did not find anywhere, nor in the documentation of Hibernate a way to do this automatically.

I had to create the query in hand before Insert to check if the name already existed in the base.