df-research/dataframe/docs/StardustDocs/topics/dataSources/sql/H2.md

H2

Use Kotlin DataFrame to query H2 databases via JDBC — read tables, run SQL queries, or fetch result sets directly. Connect to H2 databases in Kotlin DataFrame and load data using simple JDBC configurations. Read from H2 databases in Kotlin DataFrame using built-in SQL reading methods.

Kotlin DataFrame supports reading from an H2 database using JDBC.

Requires the dataframe-jdbc module, which is included by default in the general dataframe artifact and in %use dataframe for Kotlin Notebook.

You’ll also need the official H2 JDBC driver:

dependencies {
    implementation("com.h2database:h2:$version")
}

USE {
    dependencies("com.h2database:h2:$version")
}

The actual Maven Central driver version could be found here.

Read

DataFrame can be loaded from a database in several ways:
a user can read data from a SQL table by given name (readSqlTable),
as a result of a user-defined SQL query (readSqlQuery),
or from a given ResultSet (readResultSet).
It is also possible to load all data from non-system tables, each into a separate DataFrame (readAllSqlTables).

See for more details.

H2 Compatibility Modes

When working with H2 database, the library automatically detects the compatibility mode from the connection. If no MODE is specified in the JDBC URL, the default Regular mode is used. H2 supports the following compatibility modes: MySQL, PostgreSQL, MSSQLServer, MariaDB, and Regular.

import org.jetbrains.kotlinx.dataframe.io.DbConnectionConfig
import org.jetbrains.kotlinx.dataframe.api.*

// Basic H2 connection (uses Regular mode by default)

val url = "jdbc:h2:mem:testDatabase"
val username = "sa"
val password = ""

val dbConfig = DbConnectionConfig(url, username, password)

val tableName = "Customer"

val df = DataFrame.readSqlTable(dbConfig, tableName)

import org.jetbrains.kotlinx.dataframe.io.DbConnectionConfig
import org.jetbrains.kotlinx.dataframe.api.*

// H2 with PostgreSQL compatibility mode

val postgresUrl = "jdbc:h2:mem:testDatabase;MODE=PostgreSQL"
val username = "sa"
val password = ""

val postgresConfig = DbConnectionConfig(postgresUrl, username, password)

val tableName = "Customer"

val dfPostgres = DataFrame.readSqlTable(postgresConfig, tableName)