H2 SQL Database - Insert If the Record Does Not Exist

H2 SQL database - INSERT if the record does not exist

The following works for MySQL, PostgreSQL, and the H2 database:

drop table ACCESSLEVELS;

create table ACCESSLEVELS(id int, name varchar(255));

insert into ACCESSLEVELS select * from (
select 0, 'admin' union
select 1, 'SEO' union
select 2, 'sales director' union
select 3, 'manager' union
select 4, 'REP'
) x where not exists(select * from ACCESSLEVELS);

Insert into h2 table if not exists

The merge statement should allow you to achieve what you want. I'm no expert on H2, but I've used the MERGE statement in SQL Server several times and from the looks of that website it should do the trick.

From the website:

Updates existing rows, and insert rows that don't exist. If no key column is specified, the primary key columns are used to find the row.

H2 database content is not persisting on insert and update

But the getAllRequest() assert fails as the output still contains the single row(as inserted by schemadata.sql) => which means the insertRequest change is not persisted

I would double-check that the assert(Await.result(response2, 5 seconds).size === 2) line is failing because of a size difference. Could it be failing for some other general failure?

For example, as INIT is run on each connection it could be that you are re-creating the database for each connection. Unless you're careful with the SQL, that could produce an error such as "table already exists". Adding TRACE_LEVEL_SYSTEM_OUT=2; to your H2 URL can be helpful in tracking what H2 is doing.

A couple of suggestions.

First, you could ensure your SQL only runs as needed. For example, your schema.sql could add checks to avoid trying to create the table twice:

CREATE TABLE IF NOT EXISTS my_table( my_column VARCHAR NULL );

And likewise for your schemadata.sql:

MERGE INTO my_table KEY(my_column) VALUES ('a') ;

Alternatively, you could establish schema and test data around your tests (e.g., possibly in Scala code, using Slick). Your test framework probably has a way to ensure something is run before and after a test or test suit.

Slick insert into H2, but no data inserted

My assumptions are that your JDBC url is something like jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'init.sql' and no connection pooling is used.

There are two scenarios:

1. the connection is performed with keepAliveConnection = true (or by appending DB_CLOSE_DELAY=-1 to the JDBC url) and the init.sql is something like:

DROP TABLE IF EXISTS QUESTION;
CREATE TABLE QUESTION(...);
INSERT INTO QUESTION VALUES(null, ...);
INSERT INTO QUESTION VALUES(null, ...);

2. the connection is performed with keepAliveConnection = false (default) (without appending DB_CLOSE_DELAY=-1 to the JDBC url) and the init.sql is something like:

CREATE TABLE QUESTION(...);
INSERT INTO QUESTION VALUES(null, ...);
INSERT INTO QUESTION VALUES(null, ...);

---

The call to questionDao.createQuestion will open a new connection to your H2 database and will trigger the initialization script (init.sql).

In both scenarios, right after this call, the database contains a QUESTION table with 2 rows.

In scenario (2) after this call the connection is closed and according to H2 documentation:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

The call to questionDao.questions will then open a new connection to your H2 database and will trigger again the initialization script (init.sql).

In scenario (1) the first connection is kept alive (and also the database content) but the new connection will re-execute the initialization script (init.sql) erasing the database content.

Given that (in both scenarios) questionDao.createQuestion returns 3, as expected, but then the content is lost and so the subsequent call to questionDao.questions will use a freshly initialized database.

INSERT INTO table SELECT not working with constant values in H2 DB

As mentioned in the comments,

1. Use single quotes for sting constants.
2. Do not use braces with constants in select clause.
3. Check the join condition. Without matching 2 tables based on common column, you are doing a Cartesian product

---

Related Topics