CODEWITHSUNDEEP
databasespring-bootintellij-ideaspring-data-jpah2
likejudo
likejudo

Reputation: 3736

Can H2 database exist outside of the IDE - or does the data have to be manually inserted each time?

I understand that H2 is an in-memory database. I was watching videos from a (somewhat older) course on Mockito which uses the H2 DB for illustration. In the videos I saw the H2 sql statement traces to create the table, and insert rows running by in their Eclipse IDE console. However, I do not find any traces like it in my IntelliJ IDE. Is there a setting to auto-insert data?

Or does the data have to be manually inserted each time.

Furthermore, when they are running JUnits on their repository, they do not seem to be running the SpringBoot server. I am puzzled - does the DB exist outside of the IDE?

I read up more on the features and tried to change the url to

spring.datasource.url=jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM '~/Documents/Projects/regular-encourager/encourage/src/main/resources/data.sql'

But I get error

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 #2 of URL [file:/C:/Users/Me/Documents/Projects/regular-encourager/encourage/target/classes/data.sql]: CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Domain "CATEGORY" already exists; SQL statement:
CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default') [90119-214]

When I comment out the CREATE Enum statements then I get error that it is an unknown type.

CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default');
CREATE TYPE Tone AS ENUM('Default', 'Uplifting', 'Urging', 'Warning', 'Soothing', 'Comforting', 'Inspiring', 'Centering', 'Balanced');
CREATE TYPE Topic AS ENUM('Default', 'AcceptedInChrist', 'SignificantInChrist', 'SecureInChrist', 'NoAnxietyInChrist');

data.sql is:

drop table ENCOURAGEMENT if exists;
CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default');
CREATE TYPE Tone AS ENUM('Default', 'Uplifting', 'Urging', 'Warning', 'Soothing', 'Comforting', 'Inspiring', 'Centering', 'Balanced');
CREATE TYPE Topic AS ENUM('Default', 'AcceptedInChrist', 'SignificantInChrist', 'SecureInChrist', 'NoAnxietyInChrist');
CREATE TABLE Encouragement(ID INT PRIMARY KEY, CATEGORY Category, TOPIC Topic, TONE Tone, MESSAGE VARCHAR(512));
INSERT INTO Encouragement VALUES(-1, 'Default', 'Default', 'Default', 'We walk by Faith, not by Sight');
INSERT INTO Encouragement VALUES(0,'WhoIAmInChrist','AcceptedInChrist','Uplifting','John 1:12 I am God''s child.');
INSERT INTO Encouragement VALUES(1,'WhoIAmInChrist','AcceptedInChrist','Uplifting','John 15:15 As a disciple, I am a friend of Jesus Christ.');

Upvotes: 1

Views: 189

Answers (1)

The Impaler
The Impaler

Reputation: 48850

H2 mode Connected apps Persistent
In-Memory 1 ❌
File-persistent 1 ✅
Server-mode multiple ✅

H2 can work in multiples modes: in memory, file persistent, and server. See H2 Cheat Sheet:

  • The in-memory option does not have any persistence and all data is lost once the database stops.
  • The file-persistent option is still a single-user database but the data is saved on disk. Subsequent connections will see changes from previous connections.
  • The server mode (client-server) is a separate process suitable for multi-user, multi-connections. This mode works more or less in the same way as a traditional database.

Finally, it's also possible to provide a SQL script to be run when starting H2, so tables and data is already there when your app starts.

Upvotes: 3

Related Questions