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

HIbernate cannot execute "call next value for hibernate_sequence". What database are you using? And does the hibernate_sequence exists? And do you really want to use GenerationType.AUTO? — Simon Martinelli
– Simon Martinelli, Commented Jun 26, 2020 at 6:08
Hi I am using the H2 databases I have updated my code with my application.properties configutarions — Kire Programista
– Kire Programista, Commented Jun 26, 2020 at 12:34
Ok. And again my question why are you using GenerationType.AUTO — Simon Martinelli
– Simon Martinelli, Commented Jun 26, 2020 at 13:02
Not particular reason. Just follow an example, but don't see how that could be the problem? Maybe I am wrong. — Kire Programista
– Kire Programista, Commented Jun 26, 2020 at 13:13
Try chnage it to GenerationType.IDENTITY and try again. II think its the problem becuase the error says "HIBERNATE_SEQUENCE" not found — Simon Martinelli
– Simon Martinelli, Commented Jun 26, 2020 at 13:26

jumping_monkey · Accepted Answer · 2020-06-27 00:42:27Z

1

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

Added cascade=CascadeType.ALL as follows:

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

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;

edited Jun 27, 2020 at 0:42

answered Jun 26, 2020 at 7:15

jumping_monkey

8,1152 gold badges60 silver badges75 bronze badges

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

2 Comments

Kire Programista Over a year ago

Hi, I added both the cascade=CascadeType.ALL and removed nullable = false everywhere but I still get the same error

jumping_monkey Over a year ago

Brother, i updated my answer. Check your grammar/typo errors.

shevalda · Accepted Answer · 2022-11-22 03:11:19Z

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.

Collectives™ on Stack Overflow

InvalidDataAccessResourceUsageException: org.hibernate.exception.SQLGrammarException:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related