JAVA JDBC

Overview

Java Database Connectivity (JDBC) is an application programming interface (API) for the Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity, which supports PostgreSQL protocol implemented in Oxla and provides consistent interface for accessing databases in Java or Kotlin. This page and its sections describe how to use Kotlin JDBC with Oxla and also lists unsupported functions and structures.

Establishing connection

    /**
     * @brief Establishes connection to a database at a given address and port.
     * @param address Address at which database is located (Can be in URL, IPv4 or IPv6 format).
     * @param port Port at which database is located (in [0, 65535] range).
     * @param databaseName Name of the database to connect to.
     * @param user (optional) Name of a user to connect as.
     * @param password (optional) Password of the given user.
     * @return Result containing a Connection object if connection was established successfully, otherwise Result(Failure) with an error message.
     */
    fun connect(
        address: String,
        port: Int,
        databaseName: String,
        user: String? = null,
        password: String? = null
    ): Result<Connection> {
        if (port !in 0..65535) {
            return Result.failure(IllegalArgumentException("Port must be in 0 - 65535 range."))
        }
        try {
            return Result.success(DriverManager.getConnection("jdbc:postgresql://$address:$port/$databaseName", user, password))
        } catch (e: SQLException) {
            return Result.failure(SQLException("Failed to establish connection to a database, because: $e"))
        } catch (_: SQLTimeoutException) {
            return Result.failure(SQLTimeoutException("Failed to establish connection to a database. Request timed out."))
        }
    }

Support for SSL/TLS is not mandated in the JDBC specification. So you cannot expect it in every driver.

Example usage

This example shows basic query execution, once the connection has been established:

val statement: Statement = connection.createStatement()
statement.queryTimeout = QUERY_TIMEOUT
val query: String = "SELECT $columnName FROM $table"
try {
    // Execute the query and...
    val result: ResultSet = statement.executeQuery(query). also {
        // ... print the results.
        while (it.next()) {
            println(it.getString(1))
        }
    }     
} catch (e: SQLException) {
    System.err.println("Failed to execute the following query: $query, error: $e")
}

Unsupported Functions & Structures

Here you can find a list of functions and potentially related structures, that we currently do not support when working with Oxla and Kotlin JDBC:

• JDBC.Connection, createArrayOf
• JDBC.Connection, getTransactionIsolation
• JDBC.Connection, prepareStatement with intArray (JDBC does not support)
• JDBC.Connection, setSavepoint
• JDBC.Connection, setTransactionIsolation
• JDBC.ResultSet, deleteRow
• JDBC.ResultSet, insertRow
• JDBC.ResultSet, refreshRow
• JDBC.ResultSet, updateRow
• JDBC.ResultSet, moveToCurrentRow
• JDBC.ResultSet, moveToInsertRow
• JDBC.Statement, RETURN_GENERATED_KEYS
• JDBC.Statement, invalid autoGeneratedKeys (JDBC does not throw)
• JDBC.Statement, execute witch intArray (JDBC does not support)
• JDBC.Statement, cancel (issues after cancel)
• JDBC.PreparedStatement, setDate
• JDBC.PreparedStatement, setObject
• JDBC.PreparedStatement, setString(1, PGInterval(“1 day”).toString())