JDBC with Jython

Jython runs on the JVM, so it can use Java's JDBC API directly: the same driver, the same connection string, the same java.sql classes a Java application uses. You import them as usual and drive the database from Python-style code. This chapter shows the standard JDBC workflow from Jython.

Getting a Connection

Put the driver JAR on the classpath (for example MYSQL_HOME or the driver JAR on PYTHONPATH), import java.sql, and obtain a connection through the driver manager:

import java.sql

url = "jdbc:mysql://localhost:3306/shop"
user = "app"
password = "secret"

conn = java.sql.DriverManager.getConnection(url, user, password)
print("connected:", conn.isValid(5))

Statements and Queries

stmt = conn.createStatement()
rs = stmt.executeQuery("SELECT sku, price FROM products ORDER BY price")

while rs.next():
    sku = rs.getString("sku")
    price = rs.getDouble("price")
    print(sku, price)

rs.close()
stmt.close()

Column accessors are typed getters (getString, getInt, getDouble, getTimestamp) and accept either the column name or its 1-based index. Always close the ResultSet and Statement when done, or use try/finally blocks.

Prepared Statements

For values that change, use a PreparedStatement with placeholders; the driver handles quoting and escaping, which also protects you from SQL injection:

ps = conn.prepareStatement(
    "INSERT INTO orders (sku, qty) VALUES (?, ?)"
)
ps.setString(1, "CU-001")
ps.setInt(2, 3)
rows = ps.executeUpdate()
print("inserted", rows, "row")
ps.close()

Transactions

JDBC supports transactions through the connection. Turn off autocommit, do the work, then commit or roll back:

conn.setAutoCommit(False)
try:
    ps = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?"
    )
    ps.setDouble(1, 50.0)
    ps.setString(2, "acc-1")
    ps.executeUpdate()
    ps.close()
    conn.commit()
except Exception:
    conn.rollback()
    raise
finally:
    conn.setAutoCommit(True)

Closing Up

Close the connection in a finally block, or centralize it in a small helper. For longer applications, consider a connection pool such as HikariCP from Java, which Jython can instantiate and use like any other Java object.

Summary

From Jython you program JDBC exactly as Java does: DriverManager.getConnection for the connection, typed statements for queries, PreparedStatement for parameterized SQL, and explicit commit/rollback for transactions. The Jython code stays readable while the JVM does the heavy lifting.