Spring import SQL with Hibernate

Question

I want to use Hibernate when running the initial sql file. The problem I currently have is that I get the following Exception: I want HIBERNATE to insert the ID instead of the database!

Caused by: java.sql.SQLException: Field 'id' doesn't have a default value

My application.properties looks like this:

spring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect
#Data Source Settings
spring.datasource.url=jdbc:mariadb://localhost:3306/kotlin
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.dbcp2.test-while-idle=true
spring.datasource.dbcp2.validation-query=SELECT 1
spring.datasource.continue-on-error=false
#Add Initial Date for Testing
spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.hbm2ddl.import_files=data.sql

My User Model contains:

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
var id: Long = 0

My startup sql script:

INSERT INTO user (first_name, last_name, pass_word, user_name, version, email) VALUES ('deepak', 'sp', '$2a$10$2nU5xMCyzPMeNiGQsrjzQeWNcHf9NjtGzVFgy6kVRcZlLh/ABTgZW', 'spdeepak', 1, "[email protected]");

It seems like it wants to directly execute the sql (generated ID by Hibernate is not used) instead of creating an User-object and afterwards insert the data.

I ALSO get this error when dealing with default values (It says there is no default value on database). So please don't just answer with "Use GenerationType.IDENTITY)

Dmitrii Cheremisin · Accepted Answer · 2020-04-29 08:39:55Z

1

Try this:

INSERT INTO user (id, first_name, last_name, pass_word, user_name, version, email) 
VALUES 
(null, 'deepak', 'sp', '$2a$10$2nU5xMCyzPMeNiGQsrjzQeWNcHf9NjtGzVFgy6kVRcZlLh/ABTgZW', 'spdeepak', 1, "[email protected]");

Your insert statement is missing id column, but it is required. If you provide null value, then db will assign its value automatically by usign autoincrement.

About id generation by hibernate for manual scripts - it is impossible. Manual scripts are executed as is directly to the database without any framework. It is pure jdbc statement sql script execution.

I see that your are using jpa repositories according to your properties files. If you want id generation to be made by hibernate then you should create your user through jpa repository.

edited Apr 29, 2020 at 8:39

answered Apr 28, 2020 at 21:04

Dmitrii Cheremisin

1,57810 silver badges11 bronze badges

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

3 Comments

mleister Over a year ago

Hibernate should do that for me instead of the database... Thats the actual question. But anyway, thank you.

Dmitrii Cheremisin Over a year ago

Hibernate can do nothing with manual scripts. you should either insert your user with jpa repository or provide valid script for the database. Because manual scripts are executed as is.

mleister Over a year ago

This is the answer i wanted to hear. Add that to your answer and I will accept it

Collectives™ on Stack Overflow

Spring import SQL with Hibernate

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related