Tree Data Structure with JPA and H2 Databse-CodePudding

I have an entity class representing a tree data structure.

When using H2 Database Engine v1.4.200, I can persist entities without a problem.

However, when migrating to H2's latest version (v2.1.214), it throws an exception because of a primary key violation. It seems that is persisting the parent and the root with the entity (see exception below).

Any idea what could have changed in H2 from v1.4.200 to v2.1.214 that may be affecting the entity's persistence?

Node.java

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Transient;

@Entity
public class Node {

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

    private String name;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "parent_id")
    @JsonIgnore
    private Node parent;
    
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "root_id")
    @JsonIgnore
    public Node root;

    @Transient
    public List<Node> children = new ArrayList<>();
}

NodeController.java

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/api/node")
public class NodeController {

    private NodeService nodeService;

    public NodeController(NodeService nodeService) {
        this.nodeService = nodeService;
    }

    @PostMapping("/save")
    public ResponseEntity<?> create(@RequestBody Node node) {
        Node entity = nodeService.save(node);
        return new ResponseEntity<>(entity, HttpStatus.CREATED);
    }

}

Exception

o.h.engine.jdbc.spi.SqlExceptionHelper:
    Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.NODE(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'Node 1', NULL, NULL)"; SQL statement:
    insert into node (id, name, parent_id, root_id) values (default, ?, ?, ?) [23505-214]

.m.m.a.ExceptionHandlerExceptionResolver:
    Resolved [org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON PUBLIC.NODE(ID) ( /* key:1 */ CAST(1 AS BIGINT), 'Node 1', NULL, NULL)"; SQL statement:<EOL>insert into node (id, name, parent_id, root_id) values (default, ?, ?, ?) [23505-214]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement]

CodePudding user response：

Identity columns have a generator that produces values for each new row. If an identity column was declared as generated by default, it is possible to specify an own value for it, in this case generator isn't used. If the specified value is from the range of generated values it may conflict with values produced by this generator. For example,

CREATE TABLE TEST(
    ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    VAL INTEGER
);
INSERT INTO TEST(ID, VAL) VALUES (1, 10);
INSERT INTO TEST(ID, VAL) VALUES (DEFAULT, 20); // <- generator also returns 1

To avoid this, you should avoid insertion of custom values into these columns, always specify DEFAULT or don't include these columns into lists of columns in INSERT commands. If this is not an option, you need to adjust generator by yourself after such insertions:

INSERT INTO TEST(ID, VAL) VALUES (1, 10);
INSERT INTO TEST(ID, VAL) VALUES (2, 11);
// ...
ALTER TABLE TEST ALTER COLUMN ID RESTART WITH (SELECT MAX(ID) FROM TEST)   1;

You can also specify a custom interval for identity column (with GENERATED BY DEFAULT AS IDENTITY(START WITH 1000), for example) and use values outside of this interval for insertions with custom values.

This issue doesn't appear with historic unsupported version of H2 database, because they incorrectly perform automatic adjustment of generator after insertions of custom values, this behavior was copied from auto-increment columns from MySQL.

H2 2.*.* implements identity columns from the SQL Standard and doesn't adjust their generators by itself (excluding some compatibility modes where such adjustment is performed for compatibility with other database systems).

Automatic adjustment is actually insecure, because a user with limited access permissions can exhaust generator of identity column with only one insertion of special value and break following insertions with generated values.