spring-data-jpa 如何使用data.sql在Sping Boot 中添加主键并使用Hibernate增强的序列生成器策略

sczxawaw  于 2022-11-10  发布在  Spring
关注(0)|答案(1)|浏览(121)

我有一个项目，我试图从data.sql文件中添加值，问题是我不知道如何使用Hibernate增强序列策略添加主键。
我尝试在不添加主键值的情况下执行此操作，以为它会自动执行，但出现错误，然后我尝试使用AUTO_INCREMENT主键，但结果出现错误，我该怎么办？
所以我有一个id的Hibernate生成器：

@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")

使用此生成器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文件：

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);

//我不知道该如何在此处添加主键...
//以及如何在此处使用它...
data.sql文件：

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');

编辑：
所以我的实体看起来像这样：

@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');

但我的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
spring-data-jpa

1条答案

按热度按时间
iq0todco

iq0todco1#

schema.sql正在创建一个自动递增的PK，因此未休眠的数据库将负责填充STORE_ID列
您可以将id策略设置为IDENTITY，而不是使用序列

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

MySQL中不支持序列，因此您可以选择自动递增的列和GenerationType.IDENTITY，或者MySQL中创建序列值和GenerationType.TABLE的表。由于GenerationType.TABLE的性能问题，不建议使用此方法。源代码https://thorben-janssen.com/5-things-you-need-to-know-when-using-hibernate-with-mysql/

赞(0）分享  回复(0）举报  2022-11-10
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com