Overview

Java applications send queries and read result sets through the familiar Statement, PreparedStatement, and ResultSet interfaces. This page covers sending queries and reading DuckDB's nested and composite types. For opening the Connection these run on, see Define Connections.

Sending Queries

DuckDB supports the standard JDBC methods to send queries and retrieve result sets. First a Statement object has to be created from the Connection, this object can then be used to send queries using execute() and executeQuery(). execute() is meant for queries where no results are expected like CREATE TABLE or UPDATE etc. and executeQuery() is meant to be used for queries that produce results (e.g., SELECT). Below are two examples. See also the JDBC Statement and ResultSet documentations.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

Connection conn = DriverManager.getConnection("jdbc:duckdb:");

// create a table
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)");
// insert two items into the table
stmt.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)");

try (ResultSet rs = stmt.executeQuery("SELECT * FROM items")) {
    while (rs.next()) {
        System.out.println(rs.getString(1));
        System.out.println(rs.getInt(3));
    }
}
stmt.close();

jeans
1
hammer
2

DuckDB also supports prepared statements as per the JDBC API:

import java.sql.PreparedStatement;

try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO items VALUES (?, ?, ?);")) {
    stmt.setString(1, "chainsaw");
    stmt.setDouble(2, 500.0);
    stmt.setInt(3, 42);
    stmt.execute();
    // more calls to execute() possible
}

Warning Do not use prepared statements to insert large amounts of data into DuckDB. See the data import documentation for better options.

Reading Nested and Composite Types

DuckDB supports nested and composite column types such as LIST, STRUCT, MAP, UNION, and ENUM. The JDBC driver maps each of these to an idiomatic Java object. Read the values from a ResultSet with getObject, which returns the following types:

try (ResultSet rs = stmt.executeQuery("SELECT [1, 2, 3] AS l, {'a': 1, 'b': 2} AS s")) {
    while (rs.next()) {
        DuckDBArray list = (DuckDBArray) rs.getObject(1);
        Object[] values = (Object[]) list.getArray();

        DuckDBStruct struct = (DuckDBStruct) rs.getObject(2);
        Map<String, Object> fields = struct.getMap();
    }
}

The DuckDB result set also exposes typed accessors as extensions to the JDBC API, including getArray(int), getStruct(int), getUuid(int), getHugeint(int), and getJsonObject(int). DuckDBStruct.getMap() returns the struct fields keyed by name, and DuckDBArray.getResultSet() exposes the list elements as an index and value result set.

Further Reading

• Handle Results — Apache Arrow interchange, result streaming, and chunked results beyond the standard ResultSet.
• Import Data — the Appender and batch writer, the recommended alternatives to prepared statements for bulk inserts.
• Prepared Statements — DuckDB's SQL-level support for the parameterized queries used here.
• Define Connections — opening the Connection that these statements run on.