Bulk data insert in H2 database

Question

I am new to H2 database and java. I have started working on these from past 1 month. I needs some assistance regarding H2 database.

Overview of my work :

I get the query from reporting engine. Query is split and only the select with where is run on the underlying database(file structure). The results needs to stored in H2 database and the actual query(with orderby and groupby ) needs to run.

My doubts:

1. Which type of connection mode to use? (in memory/embedded).

2. Schema is dependent on query. So how to load schema and create table dynamically?

3. Which the best way to insert bulk data and fetch in H2 database ?

Any help is much appreciated.

Regards, Bharath

Answer 1

Which type of connection mode to use? (in memory/embedded).

Obviously, it depends on whether you have enough memory, and whether you need to keep the data.

Schema is dependent on query. So how to load schema and create table dynamically?

You can use create table ... as select .... This statement is optimized for performance, and faster than separate create and insert statements.

Which the best way to insert bulk data and fetch in H2 database ?

See the documentation about fast database imports.