Search code examples
spring-booth2

H2 in memory database insertions using spring boot fails

I am having an issue populating my H2 in memory database. Have this sql script in my resource directory

DROP TABLE IF EXISTS BACKPACK_DATA;
CREATE TABLE BACKPACK_DATA(id int PRIMARY KEY AUTO_INCREMENT,
                           name VARCHAR(250),
                           quantity INT,
                           kilometers INT,
                           season JSON
);

INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('backpack', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 2, 50, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('sunscreen', 1, 20, '["Summer"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('umbrella', 1, 15, '["Spring", "Autumn"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('gloves', 1, 1, '["Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 3, 15, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 6, 30, '["Spring", "Summer", "Autumn", "Winter"]');
INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('raincoat', 1, 50, '["Spring", "Autumn"]');

Spring logs even says in start up that run the scripts

Executing SQL script from file [backpack-api\build\resources\main\schema.sql]
2024-03-01T18:05:29.984+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: DROP TABLE IF EXISTS BACKPACK_DATA
2024-03-01T18:05:29.988+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 0 returned as update count for SQL: CREATE TABLE BACKPACK_DATA( id int PRIMARY KEY AUTO_INCREMENT, name VARCHAR(250), quantity INT, kilometers INT, season JSON )
2024-03-01T18:05:29.991+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('backpack', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.991+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 1, 1, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.992+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('pair of boots', 2, 50, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.992+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('sunscreen', 1, 20, '["Summer"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('umbrella', 1, 15, '["Spring", "Autumn"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('gloves', 1, 1, '["Winter"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 3, 15, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.993+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('snacks', 6, 30, '["Spring", "Summer", "Autumn", "Winter"]')
2024-03-01T18:05:29.994+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : 1 returned as update count for SQL: INSERT INTO BACKPACK_DATA( name, quantity, kilometers, season ) VALUES ('raincoat', 1, 50, '["Spring", "Autumn"]')
2024-03-01T18:05:29.994+02:00 DEBUG 18700 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [backpack-api\build\resources\main\schema.sql] in 14 ms.

Although my in memory database is empty unless I insert manually into db using this console [1]: https://i.sstatic.net/uZZJQ.png

Not sure if anything is wrong with my script or what should I check.

Solution

  • Was missing spring.jpa.defer-datasource-initialization=true line in application.properties file.