99 lines
2.8 KiB
Markdown
Vendored
99 lines
2.8 KiB
Markdown
Vendored
# H2
|
||
|
||
<web-summary>
|
||
Use Kotlin DataFrame to query H2 databases via JDBC — read tables, run SQL queries, or fetch result sets directly.
|
||
</web-summary>
|
||
|
||
<card-summary>
|
||
Connect to H2 databases in Kotlin DataFrame and load data using simple JDBC configurations.
|
||
</card-summary>
|
||
|
||
<link-summary>
|
||
Read from H2 databases in Kotlin DataFrame using built-in SQL reading methods.
|
||
</link-summary>
|
||
|
||
|
||
Kotlin DataFrame supports reading from an [H2](https://www.h2database.com/html/main.html) database using JDBC.
|
||
|
||
Requires the [`dataframe-jdbc` module](Modules.md#dataframe-jdbc),
|
||
which is included by default in the general [`dataframe` artifact](Modules.md#dataframe-general)
|
||
and in [`%use dataframe`](SetupKotlinNotebook.md#integrate-kotlin-dataframe) for Kotlin Notebook.
|
||
|
||
You’ll also need [the official H2 JDBC driver](https://www.h2database.com/html/main.html):
|
||
|
||
<tabs>
|
||
<tab title="Gradle project">
|
||
|
||
```kotlin
|
||
dependencies {
|
||
implementation("com.h2database:h2:$version")
|
||
}
|
||
```
|
||
|
||
</tab>
|
||
<tab title="Kotlin Notebook">
|
||
|
||
|
||
```kotlin
|
||
USE {
|
||
dependencies("com.h2database:h2:$version")
|
||
}
|
||
```
|
||
|
||
</tab>
|
||
</tabs>
|
||
|
||
The actual Maven Central driver version could be found
|
||
[here](https://mvnrepository.com/artifact/com.h2database/h2).
|
||
|
||
## Read
|
||
|
||
[`DataFrame`](DataFrame.md) can be loaded from a database in several ways:
|
||
a user can read data from a SQL table by given name ([`readSqlTable`](readSqlDatabases.md)),
|
||
as a result of a user-defined SQL query ([`readSqlQuery`](readSqlDatabases.md)),
|
||
or from a given `ResultSet` ([`readResultSet`](readSqlDatabases.md)).
|
||
It is also possible to load all data from non-system tables, each into a separate `DataFrame` ([`readAllSqlTables`](readSqlDatabases.md)).
|
||
|
||
See [](readSqlDatabases.md) 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`.
|
||
|
||
```kotlin
|
||
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)
|
||
```
|
||
|
||
```kotlin
|
||
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)
|
||
```
|
||
|