JDBC Internals

This document covers the internal mechanics of the cursor, type system, connection pool, and query cancellation. It's mainly useful if you're debugging an edge case or contributing to jdbc4py.

Cursor

Cursor in cursor.py wraps a JDBC PreparedStatement. The execution flow for execute():

prepareStatement(sql)
  → set _statement + _cancel_requested atomically under _lock
  → bind_parameters(statement, params)
  → set _executing = True under _lock   ← only after binding succeeds
  → statement.execute()
  → set _executing = False in finally
→ check _cancel_requested outside try/except
→ getResultSet() + getMetaData() → description

A few invariants:

• _executing is True only during the actual JDBC call, not during parameter binding. This means cancel() won't try to cancel a statement that hasn't started yet.
• _statement, _cancel_requested, and _executing are all updated atomically inside _lock.
• QueryCancelledError is raised outside the try/except block to avoid raise exc from exc in tracebacks.

executemany()

Streams parameters in chunks of 1,000, calling addBatch() / executeBatch() per chunk. Cancellation is checked between chunks.

Fetch paths

There are two fetch paths, depending on whether jdbc4py-helper.jar is present:

Optimized path (with helper JAR): JdbcBatchReader.readOptimizedBatch(resultSet, size) returns column-major arrays. Python assembles row tuples from those arrays with null masks applied per column. This minimizes JNI crossings for bulk fetches.

Fallback path (without helper JAR): resultSet.next() → read_current_row() → one tuple per row. Used when the helper JAR is missing or returns something unexpected.

PreparedStatement Cache

Each Connection holds a cache of prepared statements, capped at 128 queries × 4 idle statements per query; when the query limit is reached it evicts the oldest-inserted bucket (FIFO). When execute() is called, it first tries _borrow_statement() from the cache. If the cached statement is stale (e.g. connection was recycled), it falls back to a fresh prepareStatement().

The cache is shared across PooledConnection checkout cycles — prepared statements survive returning the connection to the pool.

Type System

types.py handles both directions: Python values going into JDBC parameters, and JDBC result values coming back as Python objects.

Parameter binding

bind_parameters() inspects each value and calls the appropriate JDBC setter:

Python type	JDBC setter
None	setNull
bool	setBoolean
int	setInt / setLong / setBigDecimal (by magnitude)
float	setDouble
Decimal	setBigDecimal
str	setString
bytes	setBytes
date / datetime / time	setObject with java.time types
timedelta	interval string, or PGInterval for PostgreSQL
UUID	setObject(java.util.UUID)
dict	JSON string (or PGobject for PostgreSQL jsonb)
list / tuple	setArray (PostgreSQL, incl. multi-dim) or Java list (ClickHouse); a '[..]' literal for a VECTOR parameter
ipaddress / Range / YearMonthInterval	their text form (also as array elements)

For engines that need it (PostgreSQL, Oracle), binding uses engine-specific paths. For example, PostgreSQL interval parameters go through PGInterval because the PostgreSQL driver is strict about interval format.

Parameter type hints from getParameterMetaData() are cached in an LRU OrderedDict keyed by (sql_text, param_count, engine). For long SQL (over 256 chars), the key uses an 8-byte SHA-256 digest.

Result conversion

build_column_info() does one pass over ResultSetMetaData and returns per-column converter callables. No second metadata scan during fetch. Engine-specific type names (ClickHouse uint64, PostgreSQL hstore, Oracle intervals) are checked before falling back to JDBC type codes.

Notable conversions:

• Unsigned 32-bit (ClickHouse uint32, MySQL/MariaDB INT UNSIGNED) — fetched as getLong() because values above 2^31-1 overflow a Java int
• Unsigned 64-bit (ClickHouse uint64, MySQL/MariaDB BIGINT UNSIGNED) — fetched as getString() then parsed: values above 2^63-1 can't be decoded as a Java long (ClickHouse wraps to a negative; MySQL throws), so the unsigned decimal string is parsed directly
• VECTOR / embeddings (pgvector, Oracle 23ai, …) → list[float]; PostgreSQL inet/cidr → ipaddress objects
• Oracle NUMBER without precision — returns Decimal in the row path; the Arrow helper infers integer vs decimal from actual values
• BLOB/CLOB — read fully on access; the Java CLOB cap (default 64M chars, configurable via -Djdbc4py.clob.max_chars) prevents OOM on large LOBs
• Oracle INTERVALYM → YearMonthInterval; INTERVALDS → timedelta
• PostgreSQL hstore → dict[str, str | None]

Arrow Integration

The Arrow path is what makes jdbc4py fast for analytical workloads. Arrow IPC moves a large result set from JVM heap to Python in a single buffer handoff, rather than one JNI call per cell.

JVM side (JdbcArrowReader.java):

• Iterates the ResultSet in configurable row groups (controlled by batch_size)
• Each column gets a typed vector writer (int, double, utf8, timestamp, etc.)
• CLOB columns are capped at jdbc4py.clob.max_chars chars (default 64M) to bound heap usage
• Writes an Arrow IPC stream to a byte buffer, then hands the bytes to Python

Python side (connection.py):

• Deserializes with pa.ipc.open_stream() — near-zero-copy
• Applies post-processing for columns that need Python-side conversion (Oracle intervals, money columns, etc.)
• Returns a pyarrow.Table or yields RecordBatch objects for streaming

The JdbcArrowStreamer runs a Java daemon thread that prefetches Arrow batches into a ArrayBlockingQueue(2), so the database can keep streaming while Python is processing the previous batch.

Connection Pool Internals

ConnectionPool in pool.py is a bounded, thread-safe pool backed by queue.Queue.

State:

• _idle: Queue[_Slot] — connections waiting to be checked out
• _count: int — total connections in existence (idle + checked out), protected by _count_lock
• _closed: bool — checked on every iteration of the checkout loop
• _eviction_stop: threading.Event — signals the eviction thread to stop

Checkout flow:

while True:
  if closed: raise InterfaceError
  try to pull an idle slot → validate it (age, idle time, isValid ping)
  if count < max_connections: open a new connection
  else: block on _idle.get(timeout=min(remaining, 5s))
    ↑ cap on poll interval so _closed is re-checked periodically

Eviction: A daemon thread wakes every max(1, min(max_idle/2, max_age/2, 30)) seconds, drains the idle queue, closes expired slots, re-queues survivors, and opens new connections if the pool has dropped below min_size.

PooledConnection: Wraps a physical connection. close() returns the slot to the idle queue. _discard() (called when a with block exits via exception) closes the connection without returning it to the pool.

A circuit breaker short-circuits connection attempts when the database is unreachable — instead of all max_connections threads hammering a dead DB simultaneously, only one probe attempt is allowed through per cooldown window. The cooldown doubles on each consecutive failure, up to 30 s.

Cancellation Mechanics

cursor.cancel() is designed to be safe from any thread:

1. Acquires _lock; checks that _statement is set and _executing is True
2. Sets _cancel_requested = True
3. Calls statement.cancel() — best-effort; failures are swallowed
4. The executing thread calls _consume_cancel_request() after statement.execute() returns. For a result-producing statement it raises QueryCancelledError. For a non-result statement (INSERT/UPDATE/DDL) that already completed, it does not raise — the write was applied, and reporting it as cancelled would be a lie; the cancel simply lost the race.

A cancel that arrives between parameter binding and statement.execute() (before _executing = True) is detected by _consume_cancel_request() after execute returns. The window is short but the edge case is handled.

AsyncQuery.close() cancels the query, waits for it to finish, then closes the cursor. If the query doesn't finish within the timeout, a daemon thread is scheduled to close the cursor once it eventually completes. At most one deferred-close thread is spawned per AsyncQuery instance, regardless of how many threads call close() concurrently.