CODEWITHSUNDEEP
javamysqlspring-boothibernatespring-data-jpa
MyProblems
MyProblems

Reputation: 69

How to add primary key using data.sql in spring boot with hibernate enhanced sequence generator strategy

i have a project where i'm trying to add values from data.sql file, problem is i don't know how to add primary key with hibernate enhanced sequence strategy.

I tried to do that without adding values of primary keys, thinking it will do it automatically, but error, then i tried to use like a AUTO_INCREMENT primary key, but that results in errors tho... what can i do?

So i have a Hibernate generator for id's:

@org.hibernate.annotations.GenericGenerator(
        name = "STORE_ID_GENERATOR", strategy = "enhanced-sequence",
        parameters =
                {
                        @org.hibernate.annotations.Parameter(name = "sequence_name", value = "HIBERNATE_STORE_SEQUENCE"),
                        @org.hibernate.annotations.Parameter(name = "initial_value", value = "1")

An Entity which use this generator id:

    @Entity
    public class Store {
    
        @Id
        @GeneratedValue(generator = "STORE_ID_GENERATOR") //pre insert added values on id
        @Column(name = "STORE_ID", unique = true, updatable = false)
        private Long storeId;

Schema.sql file:

create table PROJECT_HIBERNATE_Store (STORE_ID bigint not null, STORE_LOCATION_CITY varchar(255), STORE_LOCATION_COUNTRY varchar(255), STORE_LOCATION_ZIPCODE varchar(255), storeName varchar(255), storeType varchar(255), PROMOTION_STORE_ID bigint,  PRIMARY KEY AUTO_INCREMENT (STORE_ID) bigint);

// i don't know wot to add primary key here...

// and how to use it here...

data.sql file:

INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE,
                                     storeName, storeType, STORE_ID) values ('Sudava', 'America', '12341431', 'Catena', 'MEDICAL_STORE', 'hibernate_sequence.nextval');

Edited::

So my Entity looks Like this:

@Entity
public class Store {

    @Id
    @GeneratedValue(generator = "STORE_ID_GENERATOR") //pre insert added values on id
    @Column(name = "STORE_ID", unique = true, updatable = false)
    private Long storeId;

schema.sql:

create table PROJECT_HIBERNATE_Store (STORE_ID bigint not null, STORE_LOCATION_CITY varchar(255), STORE_LOCATION_COUNTRY varchar(255), STORE_LOCATION_ZIPCODE varchar(255), storeName varchar(255), storeType varchar(255), PROMOTION_STORE_ID bigint,  PRIMARY KEY (STORE_ID));

data.sql:

INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE,
                                     storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL');

but my stackTrace:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/marius/IdeaProjects/shopping-project-with-hibernate/target/classes/data.sql]: INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE, storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL'); nested exception is org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "'STORE_ID_GENERATOR.NEXTVAL' (PROJECT_HIBERNATE_STORE: ""STORE_ID"" BIGINT NOT NULL)"; SQL statement:
INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE, storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL') [22018-200]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.19.jar:5.3.19]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.19.jar:5.3.19]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.19.jar:5.3.19]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:740) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:415) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:303) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1312) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1301) ~[spring-boot-2.6.7.jar:2.6.7]
    at com.balabasciuc.shoppingprojectwithhibernate.ShoppingProjectWithHibernateApplication.main(ShoppingProjectWithHibernateApplication.java:10) ~[classes/:na]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/marius/IdeaProjects/shopping-project-with-hibernate/target/classes/data.sql]: INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE, storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL'); nested exception is org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "'STORE_ID_GENERATOR.NEXTVAL' (PROJECT_HIBERNATE_STORE: ""STORE_ID"" BIGINT NOT NULL)"; SQL statement:
INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE, storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL') [22018-200]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282) ~[spring-jdbc-5.3.19.jar:5.3.19]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.3.19.jar:5.3.19]
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:54) ~[spring-jdbc-5.3.19.jar:5.3.19]
    at org.springframework.boot.jdbc.init.DataSourceScriptDatabaseInitializer.runScripts(DataSourceScriptDatabaseInitializer.java:90) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.runScripts(AbstractScriptDatabaseInitializer.java:145) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyScripts(AbstractScriptDatabaseInitializer.java:107) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyDataScripts(AbstractScriptDatabaseInitializer.java:101) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.initializeDatabase(AbstractScriptDatabaseInitializer.java:76) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.afterPropertiesSet(AbstractScriptDatabaseInitializer.java:65) ~[spring-boot-2.6.7.jar:2.6.7]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.19.jar:5.3.19]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.19.jar:5.3.19]
    ... 18 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "'STORE_ID_GENERATOR.NEXTVAL' (PROJECT_HIBERNATE_STORE: ""STORE_ID"" BIGINT NOT NULL)"; SQL statement:
INSERT INTO PROJECT_HIBERNATE_Store (STORE_LOCATION_CITY, STORE_LOCATION_COUNTRY, STORE_LOCATION_ZIPCODE, storeName, storeType, STORE_ID) values ('Suceava', 'Romania', '12341431', 'Catena', 'MEDICAL_STORE', 'STORE_ID_GENERATOR.NEXTVAL') [22018-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.get(DbException.java:194) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.table.Column.getDataConversionError(Column.java:409) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:381) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.table.Table.validateConvertUpdateSequence(Table.java:845) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.command.dml.Insert.insertRows(Insert.java:187) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.command.dml.Insert.update(Insert.java:151) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.command.CommandContainer.update(CommandContainer.java:198) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.command.Command.executeUpdate(Command.java:251) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:228) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201) ~[h2-1.4.200.jar:1.4.200]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261) ~[spring-jdbc-5.3.19.jar:5.3.19]
    ... 28 common frames omitted
Caused by: org.h2.message.DbException: Data conversion error converting "STORE_ID_GENERATOR.NEXTVAL" [22018-200]
    at org.h2.message.DbException.get(DbException.java:194) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.value.Value.convertTo(Value.java:875) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.value.Value.convertTo(Value.java:772) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.value.TypeInfo.cast(TypeInfo.java:515) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.table.Column.validateConvertUpdateSequence(Column.java:378) ~[h2-1.4.200.jar:1.4.200]
    ... 38 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "STORE_ID_GENERATOR.NEXTVAL" [22018-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:457) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
    ... 43 common frames omitted
Caused by: java.lang.NumberFormatException: For input string: "STORE_ID_GENERATOR.NEXTVAL"
    at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[na:na]
    at java.base/java.lang.Long.parseLong(Long.java:692) ~[na:na]
    at java.base/java.lang.Long.parseLong(Long.java:817) ~[na:na]
    at org.h2.value.Value.convertToLong(Value.java:1011) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.value.Value.convertTo(Value.java:808) ~[h2-1.4.200.jar:1.4.200]
    ... 41 common frames omitted


Process finished with exit code 1

Upvotes: -1

Views: 1271

Answers (1)

johnnyutts
johnnyutts

Reputation: 1452

Your schema.sql is creating a PK that is auto incremented so the DB not hibernate will be responsible for populating the STORE_ID column

Rather than using a sequence you can set your id strategy to IDENTIY

@Id
@GeneratedValue (strategy = GenerationType.IDENTITY)
@Column(name = "STORE_ID")
private Long storeId;

Sequences are not supported in MySQL so your options are a column with auto increment and GenerationType.IDENTITY, or a table in MySQL that creates the sequence values and GenerationType.TABLE. Due to performance issues with GenerationType.TABLE this not a recommended approach. Source https://thorben-janssen.com/5-things-you-need-to-know-when-using-hibernate-with-mysql/

Upvotes: 1

Related Questions