InvalidDataAccessResourceUsageException: org.hibernate.exception.SQLGrammarException:

Question

I have the following problem I have been stuck for a while:

I get this error: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [call next value for hibernate_sequence]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

I found out that people with this error get it because they use reserve words for table names but I do not think this is my issue.

My two model classes are as follows. I am skipping the getters/setters and consturctors

@Entity
@Table(name = "GATEWAY_MODEL")
public class GetewayModel implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column(name = "serial_number", nullable = false, length = 12, updatable = true)
    private String serialNumber;
    @Column(name = "name", nullable = false, length = 12, updatable = true)
    private String name;
    @Column(name = "ipFour", nullable = false, length = 12, updatable = true)
    private String ipFour;
    @Column(name = "peripheral_devices", updatable = true)
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "gateway")
    private Set<PeripheralDevicesModel> peripheralDevices = new HashSet<PeripheralDevicesModel>();


@Entity
@Table(name = "PERIPHERAL_DIVICES_MODEL")
public class PeripheralDevicesModel {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column(name = "uID", nullable = false)
    private String uID;
    @Column(name = "vendor", nullable = false)
    private String vendor;
    @Column(name = "date_created", nullable = false)
    private Date dateCreated;
    @Enumerated(EnumType.STRING)
    @Column(name = "status")
    private Status status;
    @JsonIgnore
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "gateway")
    private GetewayModel gateway;

Then in the main class I try to put some data like this:

@Bean
CommandLineRunner initDatabase(GatewayRepository gatewayRepo, PeripheralDevicesRepository devicesRepo) {

    Set<PeripheralDevicesModel> devicesSet = new HashSet<>();

    GetewayModel gateway = new GetewayModel();
    gateway.setId(123l);
    gateway.setSerialNumber("1123");
    gateway.setName("gateway");
    gateway.setIpFour("1.160.10.240");
    
    PeripheralDevicesModel devices = new PeripheralDevicesModel();
    devices.setId(1234l);
    devices.setuID("2");
    devices.setDateCreated(new Date());
    devices.setGateway(gateway);
    devices.setStatus(Status.OFFLINE);
    devices.setVendor("vendor");
    devicesSet.add(devices);
    
    gateway.setPeripheralDevices(devicesSet);
    return args -> {
        gatewayRepo.save(gateway);
        devicesRepo.save(devices);
    };

I am guessing that are is some issue because of the OneToMany Relationship in my model data.

I bit more from the stack trace

call next value for hibernate_sequence 2020-06-26 08:34:53 - SQL Error: 90036, SQLState: 90036 2020-06-26 08:34:53 - Sequence "HIBERNATE_SEQUENCE" not found; SQL statement: call next value for hibernate_sequence [90036-200] 2020-06-26 08:34:53 -

Do you have any idea how to resolve this or why it is not working. Thanks

configuration.properties:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# Enabling H2 Console
spring.h2.console.enabled=true

# Custom H2 Console URL
spring.h2.console.path=/h2

spring.jpa.hibernate.ddl-auto=none
 
#Turn Statistics on and log SQL stmts
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=false
#logging.level.org.hibernate.type=trace
#logging.level.org.hibernate.stat=debug
 
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} - %msg%n

Update: After making GenerationType.IDENTITY I got the following error now:

Hibernate: 
    select
        getewaymod0_.id as id1_0_1_,
        getewaymod0_.ip_four as ip_four2_0_1_,
        getewaymod0_.name as name3_0_1_,
        getewaymod0_.serial_number as serial_n4_0_1_,
        peripheral1_.gateway as gateway5_1_3_,
        peripheral1_.id as id1_1_3_,
        peripheral1_.id as id1_1_0_,
        peripheral1_.date_created as date_cre2_1_0_,
        peripheral1_.gateway as gateway5_1_0_,
        peripheral1_.uid as uid3_1_0_,
        peripheral1_.vendor as vendor4_1_0_ 
    from
        gateway_model getewaymod0_ 
    left outer join
        peripheral_divices_model peripheral1_ 
            on getewaymod0_.id=peripheral1_.gateway 
    where
        getewaymod0_.id=?
2020-06-26 16:42:04 - SQL Error: 42102, SQLState: 42S02
2020-06-26 16:42:04 - Table "GATEWAY_MODEL" not found; SQL statement:
select getewaymod0_.id as id1_0_1_, getewaymod0_.ip_four as ip_four2_0_1_, getewaymod0_.name as name3_0_1_, getewaymod0_.serial_number as serial_n4_0_1_, peripheral1_.gateway as gateway5_1_3_, peripheral1_.id as id1_1_3_, peripheral1_.id as id1_1_0_, peripheral1_.date_created as date_cre2_1_0_, peripheral1_.gateway as gateway5_1_0_, peripheral1_.uid as uid3_1_0_, peripheral1_.vendor as vendor4_1_0_ from gateway_model getewaymod0_ left outer join peripheral_divices_model peripheral1_ on getewaymod0_.id=peripheral1_.gateway where getewaymod0_.id=? [42102-200]
2020-06-26 16:42:04 - HHH000327: Error performing load command
org.hibernate.exception.SQLGrammarException: could not prepare statement

Answer 1

Ran into the same problem. The difference was that I did not specify strategy in @GeneratedValues. Turns out the default strategy is GeneratedType.AUTO.

So Simon Martinelli's comment solves my issue, by explicitly specify @GeneratedValue(strategy = GenerationType.IDENTITY) for the id column/field.

This article summarises the strategies nicely.

• GenerationType.Identity does not create any additional sequence tables like GenerationType.AUTO / GenerationType.SEQUENCE and also maintain the sequences in every table starts from 0, rather than maintaining the sequence number across the tables like GenerationType.AUTO does it.
• GenerationType.AUTO generates one more table named hibernate_sequences for maintaining the sequences.
• GenerationType.SEQUENCE is purely customizable, probably every auto generation field would have configured with separate sequences.

Answer 2

I changed 2 things(besides the few grammar/typo errors in your code):

1. Added cascade=CascadeType.ALL as follows:

    @JsonIgnore
    @ManyToOne(cascade=CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "gateway")
    private GatewayModel gateway;
   

2. You can't add an entity, which has columns with nullable = false:
   devices.setGateway(new GetewayModel());

   devices.setGateway(gateway);

Otherwise, it's working fine on H2.

UPDATE:
Grammar/typo errors to look for:

• @Table(name = "PERIPHERAL_DIVICES_MODEL") needs to be @Table(name = "PERIPHERAL_DEVICES_MODEL")
• public class GetewayModel needs to be public class GatewayModel
• private GetewayModel gateway; needs to be private GatewayModel gateway;