Skip to content

Latest commit

 

History

History
160 lines (112 loc) · 5.87 KB

java-db.md

File metadata and controls

160 lines (112 loc) · 5.87 KB

In-Memory Java Databases Overview

Main libraries

Logo Derby Logo HSQL Logo H2

Configuration

Maven Dependencies

Apache Derby

<dependency>
  <groupId>org.apache.derby</groupId>
  <artifactId>derby</artifactId>
   <version>10.17.1.0</version>
  <scope>test</scope>
</dependency>

🔗 https://search.maven.org/artifact/org.apache.derby/derby

Hyper SQL

<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.7.4</version>
  <scope>test</scope>
</dependency>

🔗 https://search.maven.org/artifact/org.hsqldb/hsqldb

H2

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>2.3.232</version>
  <scope>test</scope>
</dependency>

🔗 https://search.maven.org/artifact/com.h2database/h2

Drivers

Library Driver Class name
Apache Derby org.apache.derby.jdbc.EmbeddedDriver
Hyper SQL DB org.hsqldb.jdbc.JDBCDriver
H2 Database Engine org.h2.Driver

Hibernate Dialect

Library Dialect Class name
Apache Derby org.hibernate.dialect.DerbyDialect
Hyper SQL DB org.hibernate.dialect.HSQLDialect
H2 Database Engine org.hibernate.dialect.H2Dialect

Create database

File

Library JDBC String User  Password
Apache Derby jdbc:derby:target/junit/db/my_db;create=true 🚫 🚫
Hyper SQL DB jdbc:hsqldb:file:./target/junit/db/my_db;create=true SA 🚫
H2 Database Engine jdbc:h2:file:./target/junit/db/my_db sa 🚫

In-Memory

Library JDBC String User  Password
Apache Derby jdbc:derby:memory:my_db;create=true 🚫 🚫
Hyper SQL DB jdbc:hsqldb:mem:my_db SA 🚫
H2 Database Engine jdbc:h2:mem:my_db sa 🚫

Shutdown database

Library JDBC String User  Password
Apache Derby jdbc:derby:target/junit/db/my_db;shutdown=true 🚫 🚫
Hyper SQL DB jdbc:hsqldb:file:target/junit/db/my_db;shutdown=true SA 🚫
H2 Database Engine database is closed when the last connection to it is closed 🚫 🚫

Validation Queries

Library Validation Query
Apache Derby values 1
Hyper SQL DB select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
H2 Database Engine select 1

🔗 http://vondrnotes.blogspot.com/2012/05/validationquery-for-different-databases.html

Compatibility Modes

DBMS Provider Apache Derby Hyper SQL DB H2 Database Engine
Oracle 🚫 sql.syntax_ora=true MODE=Oracle
MySQL 🚫 sql.syntax_mys=true MODE=MySQL
PostgreSQL 🚫 sql.syntax_pgs=true MODE=PostgreSQL
DB2 🚫 sql.syntax_db2=true MODE=DB2
MS SQLServer 🚫 sql.syntax_mss=true MODE=MSSQLServer
Derby ⛔ 🚫 MODE=Derby
HSQLDB 🚫 ⛔ MODE=HSQLDB
H2 🚫 🚫 ⛔

Liquibase Type Name

Library Type Name
Apache Derby derby
Hyper SQL DB hsqldb
H2 Database Engine h2

🔗 https://www.liquibase.org/databases.html

Data Types

Remarks

Apache Derby

To remove an in-memory database, use the connection URL attribute drop as follows: jdbc:derby:memory:my_db;drop=true

When you drop the database, Derby issues what appears to be an error but is actually an indication of success. You need to catch error 08006, as described in "The WwdEmbedded program" in Getting Started with Derby.

Hyper SQL

If no username or password is specified, the default SA user and an empty password are used

Both the username and password are case-sensitive. (The exception is the default SA user, which is not case-sensitive). If no username or password is specified, the default SA user and an empty password are used.

A connection property ifexists=true allow connection to an existing database only and avoid creating a new database.

H2

By default, a new database is automatically created if it does not exist yet.

;DB_CLOSE_DELAY=<seconds> The parameter <seconds> specifies the number of seconds to keep a database open after the last connection to it was closed.

🔗 Database URL Overview