Design Decisions

Rationale for the major choices in jdbc4py.

Why JPype instead of Py4J or a subprocess

JPype runs the JVM inside the Python process via JNI. The alternatives were:

• Py4J — communicates with a separate JVM process over a socket. Lower complexity, but adds network serialization overhead and process lifecycle management. For bulk data transfer (millions of rows), socket IPC is a bottleneck.
• subprocess + stdin/stdout — completely isolated, but the serialization cost is even worse and you lose type fidelity.

JPype gives direct object access at JNI speed with no serialization overhead for the control path. The tradeoff is that a JVM crash takes the Python process with it, and JNI errors (like calling into an unattached thread) can cause SIGSEGV rather than a Python exception. The codebase manages this carefully — thread attachment checks before every JNI entry point, safe __del__ implementations that abort rather than crash if the JVM isn't running.

Why Arrow IPC instead of row-by-row transfer

The cost of the JDBC → Python path is dominated by JNI crossing overhead, not by data volume. Each resultSet.getObject() call is a JNI boundary crossing, and a 1M-row × 50-column table means 50M crossings.

Arrow IPC collapses this: the JVM serializes an entire row group into a contiguous byte buffer, then Python deserializes it in one call. The number of JNI crossings drops from rows × columns to a handful of buffer handoffs. On a 50K-row PostgreSQL read this makes the Arrow→pandas path ~1.8× faster than SQLAlchemy + psycopg, and 3–5× on SQL Server/Oracle/MariaDB (see BENCHMARKS.md); the gain comes from moving less data across the language boundary, not from out-computing a C driver.

The tradeoff is that Arrow IPC adds deserialization overhead on both sides and requires the helper JAR to be built. For small results, the overhead outweighs the savings — fetchall() is faster below ~10K rows.

Why asyncio is thread-based

JDBC is a blocking API — there's no async variant of Connection, Statement, or ResultSet. So jdbc4py.aio doesn't pretend otherwise: it runs the blocking calls on a worker thread (one dedicated thread per connection, for thread affinity) via run_in_executor, which keeps the event loop free without inventing a fake-async layer.

Native reactive JDBC drivers (R2DBC) are a different API surface entirely and out of scope.

Why not bundle JDBC drivers

Bundling JARs creates version coupling — if you need Oracle 21c features but the bundled JAR is 19c, you're stuck. JDBC JARs are also sometimes licensed in ways that complicate redistribution (Oracle in particular). Leaving driver selection to the user avoids both problems.

The repository does include commonly used JARs in drivers/ as a convenience for local development. These are not published as part of the PyPI package.

Why convertStrings=False

JPype can convert JString to Python str automatically, but this creates subtle bugs: a JString that hasn't been converted yet behaves like a str for == comparisons but fails for isinstance(x, str) checks and doesn't serialize correctly with json.dumps. Explicit conversion (str(jstring)) at every boundary eliminates the entire class of problem. The codebase does this consistently.

Why prepared statements everywhere

JDBC PreparedStatement vs Statement isn't just about SQL injection prevention — it also affects execution plans. Most databases cache the execution plan for a prepared statement and reuse it on subsequent executions. A Statement may or may not get plan caching depending on the driver.

jdbc4py always uses PreparedStatement, which means parameterized queries get plan caching for free, and the API doesn't expose a path to construct SQL strings directly.

Why the connection pool uses queue.Queue

queue.Queue is the simplest bounded thread-safe queue in the standard library. The checkout loop's _idle.get(timeout=poll_interval) with a bounded poll interval handles both the timeout case and the "pool closed while waiting" case in a handful of lines without any bespoke synchronization primitives.

The pool could use asyncio or trio primitives for async support, but that would require separate sync and async codepaths for everything from checkout to eviction. The current design is straightforward and correct.

Why max_connections alongside max_size

max_connections was added as a more descriptive name for the pool's hard cap. max_size remains as an alias for backward compatibility. If both are passed, max_connections takes precedence.