B4J Tutorial H2 Database

This is a very quick tutorial on the H2 database - https://www.h2database.com/html/main.html

The main features of H2 are:

• Very fast, open source, JDBC API
• Embedded and server modes; in-memory databases
• Browser based Console application
• Small footprint: around 2 MB jar file size

Other advantages over SQLite are;

• Its pure Java so JDBC driver is only 2Mb vs SQLite 7mb (since SQLite has platform specific binaries)
• Encryption is out of the box
• Richer feature set - users, roles etc
• RDBMS compatability modes (eg run in Postgres mode - this means you can migrate to a full blown RDBMS with very little work in the future)

Feature comparison;

There are some speed comparisions with SQLite here https://www.b4x.com/android/forum/threads/jserver-sqlite-multiple-request-stress-test.130575 (in my tests its about 600% faster than SQLite in WAL mode).

To use you need to download the jar file and add to additional jars in your project;

#AdditionalJar: h2-1.4.200

Conenction examples...

1. Embedded Mode

Dim driver As String = "org.h2.Driver"
Dim url As String = $"jdbc:h2:C:\temp\mydatabase"$
DB.Initialize2(driver,url,"sa","sa")

2. Embedded In memory database

DBtemp.Initialize2("org.h2.Driver","jdbc:h2:mem:mydatabase","sa","sa")

3. Connect to remote server (in Potsgres Mode)

Dim driver As String = "org.h2.Driver"
Dim url As String = "jdbc:h2:tcp://db_host/~/mydatabase;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE"
DBdest.Initialize2(driver,url,"sa","sa")

To run the database in server mode there is a start up batch file called h2w.bat in the download from the H2 web site.

4. Encrypted database connection

Dim DB As SQL
  
Dim driver As String = "org.h2.Driver"
Dim url As String = $"jdbc:h2:${File.DirApp}\report.h2;CIPHER=AES"$
DB.Initialize2(driver,url,"user","cipherpassword userpassword")
  
DB.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")
  
DB.Close

 

[MichalK73]

Links on the jar files download page do not work. Neither sourceforge nor Maven. Can you somehow share your jar version?

 

[mcqueccu]

https://repo1.maven.org/maven2/com/h2database/h2/1.4.200/h2-1.4.200.jar

 

[DonManfred]

https://mvnrepository.com/artifact/com.h2database/h2/1.4.200

 

[tchart]

These links are working; https://www.h2database.com/html/download.html

 

[MichalK73]

These links are working; https://www.h2database.com/html/download.html

If they would work for me, I would not ask for links. From Download / JAR File section does not work with Maven and sourceforge. I don't have any pc lock.

 

[MichalK73]

I just turned the H2 into my reworked project. Works approx.
For now I can write everything from my finger, but I would use GUI to H2 with encryption support from time to time. A few checked it either they do not have encryption support or they are paid. I'm looking for something simple, cheap or free. Do you know anything?

 

[tchart]

I just turned the H2 into my reworked project. Works approx.
For now I can write everything from my finger, but I would use GUI to H2 with encryption support from time to time. A few checked it either they do not have encryption support or they are paid. I'm looking for something simple, cheap or free. Do you know anything?

Have you tried DBeaver? It just uses JDBC so the GUI shouldn't care if the connection is encrypted or not. Just use the same connection string you are using the B4J. DBeaver is very clever, it will even download the driver for you. And it's free...

DBeaver Community | Free Open-Source Database Management Tool

dbeaver.io

 

[MichalK73]

I didn't know this program. He downloaded the H2 driver, but I don't know how to set the ODBC connection to the encrypted database.
Ok it worked. You need to add a connection parameter.

 

[wimpie3]

I've used H2 a lot in the past for my .net development. Does anyone know if there will be a version for Android some day?

 

[asales]

Does anyone know if there will be a version for Android some day?

Documentation about H2 Database and Android:

Tutorial

 

[wimpie3]

Documentation about H2 Database and Android:

Tutorial

Thanks. No B4A libs though... So not usable for me.

 

[MichalK73]

Why do you need a library for B4A ?? You attach the jar to the project and probably use the same sql library as in B4J.

 

[wimpie3]

Sure?

 

[tchart]

Just a note that H2 has recently been updated to 2.x (December 2021).

There are lots of new things including geometry datatypes.

History

 

[MichalK73]

Hey.

I have a problem with the new v.2.x H2 version.
The problem is that it doesn't accept the syntax I use for all databases with B4X.
The old checked syntax for DBM:

Dim ww As List = DBM.SQLSelect(DBM.GetSQL, "select * from ? limit 3", Array As String(ItemId))

Generates an error.

(Exception) java.lang.Exception:  org.h2.jdbc.JdbcSQLSyntaxErrorException: Błąd składniowy w wyrażeniu SQL "SELECT * FROM ?[*] LIMIT 3"; oczekiwano "identifier"
Syntax error in SQL statement "SELECT * FROM ?[*] LIMIT 3"; expected "identifier"; SQL statement:
select * from ? limit 3 [42001-206]

As you can see there is no substitution instead of ? variable.
However, if I write it like this:

Dim ww As List = DBM.SQLSelect(DBM.GetSQL, $"select * from ${ItemId} limit 3"$, Null)

Everything is working properly.
How to revert to the old method of setting variables as it was in the old version of H2?
Maybe some parameter of database setting is responsible for that?