Repository: xerial/sqlite-jdbc
Pull request #782 (2022-09-09 09:50:37)

Jdbc read only
Builds upon #432 but since i can't push updates on the original branch, i had to create a different PR


Patch:
diff --git a/USAGE.md b/USAGE.md
index ef432998..f0d1f57e 100644
--- a/USAGE.md
+++ b/USAGE.md
@@ -126,3 +126,33 @@ You set the mode at the connection string level:
 ```java
 Connection connection = DriverManager.getConnection("jdbc:sqlite:db.sqlite?hexkey_mode=sse", "", "AE...");
 ```
+
+## Explicit read only transactions (use with Hibernate)
+
+In order for the driver to be compliant with Hibernate, it needs to allow setting the read only flag after a connection has been created.
+
+SQLite has a notion of "auto-upgrading" read-only transactions to read-write transactions. This can cause `SQLITE_BUSY` exceptions which are difficult to deal with in a JPA/Hibernate/Spring scenario.
+
+For example:
+
+- open connection
+- query data <--- this uses a read-only transaction in SQLite by default
+- write data <--- this is risky as it promotes the transaction to read-write
+- commit
+
+The approach taken is:
+
+- open transactions on demand
+- allow setting `readOnly` only if no statement has been executed yet
+- if `readOnly(false)` is received, then we _quit_ out of our transaction, and open a new transaction with `BEGIN IMMEDIATE`. This forces a global lock on the database, preventing `SQLITE_BUSY`.
+
+You can activate explicit read only support in 2 ways:
+- via `SQLiteConfig#setExplicitReadOnly(true)`: 
+```java
+SQLiteConfig config = new SQLiteConfig();
+config.setExplicitReadOnly(true);
+```
+- using the pragma `jdbc.explicit_readonly`:
+```java
+DriverManager.getConnection("jdbc:sqlite::memory:?jdbc.explicit_readonly=true");
+```
\ No newline at end of file
diff --git a/src/main/java/org/sqlite/SQLiteConfig.java b/src/main/java/org/sqlite/SQLiteConfig.java
index d8acf079..df10fc29 100755
--- a/src/main/java/org/sqlite/SQLiteConfig.java
+++ b/src/main/java/org/sqlite/SQLiteConfig.java
@@ -55,6 +55,7 @@ public class SQLiteConfig {
     private int openModeFlag = 0x00;
 
     private final int busyTimeout;
+    private boolean explicitReadOnly;
 
     private final SQLiteConnectionConfig defaultConnectionConfig;
 
@@ -89,6 +90,9 @@ public class SQLiteConfig {
         this.busyTimeout =
                 Integer.parseInt(pragmaTable.getProperty(Pragma.BUSY_TIMEOUT.pragmaName, "3000"));
         this.defaultConnectionConfig = SQLiteConnectionConfig.fromPragmaTable(pragmaTable);
+        this.explicitReadOnly =
+                Boolean.parseBoolean(
+                        pragmaTable.getProperty(Pragma.JDBC_EXPLICIT_READONLY.pragmaName, "false"));
     }
 
     public SQLiteConnectionConfig newConnectionConfig() {
@@ -182,6 +186,9 @@ public class SQLiteConfig {
         pragmaParams.remove(Pragma.LIMIT_WORKER_THREADS.pragmaName);
         pragmaParams.remove(Pragma.LIMIT_PAGE_COUNT.pragmaName);
 
+        // exclude this "fake" pragma from execution
+        pragmaParams.remove(Pragma.JDBC_EXPLICIT_READONLY.pragmaName);
+
         Statement stat = conn.createStatement();
         try {
             if (pragmaTable.containsKey(Pragma.PASSWORD.pragmaName)) {
@@ -321,7 +328,8 @@ public class SQLiteConfig {
         pragmaTable.setProperty(
                 Pragma.DATE_STRING_FORMAT.pragmaName,
                 defaultConnectionConfig.getDateStringFormat());
-
+        pragmaTable.setProperty(
+                Pragma.JDBC_EXPLICIT_READONLY.pragmaName, this.explicitReadOnly ? "true" : "false");
         return pragmaTable;
     }
 
@@ -351,6 +359,20 @@ public class SQLiteConfig {
         }
     }
 
+    /** @return true if explicit read only transactions are enabled */
+    public boolean isExplicitReadOnly() {
+        return this.explicitReadOnly;
+    }
+
+    /**
+     * Enable read only transactions after connection creation if explicit read only is true.
+     *
+     * @param readOnly whether to enable explicit read only
+     */
+    public void setExplicitReadOnly(boolean readOnly) {
+        this.explicitReadOnly = readOnly;
+    }
+
     public static enum Pragma {
 
         // Parameters requiring SQLite3 API invocation
@@ -358,7 +380,7 @@ public class SQLiteConfig {
         SHARED_CACHE("shared_cache", "Enable SQLite Shared-Cache mode, native driver only", OnOff),
         LOAD_EXTENSION(
                 "enable_load_extension",
-                "Enable SQLite load_extention() function, native driver only",
+                "Enable SQLite load_extension() function, native driver only",
                 OnOff),
 
         // Pragmas that can be set after opening the database
@@ -450,7 +472,10 @@ public class SQLiteConfig {
                 null),
         BUSY_TIMEOUT("busy_timeout", null),
         HEXKEY_MODE("hexkey_mode", toStringArray(HexKeyMode.values())),
-        PASSWORD("password", null);
+        PASSWORD("password", null),
+
+        // extensions: "fake" pragmas to allow conformance with JDBC
+        JDBC_EXPLICIT_READONLY("jdbc.explicit_readonly");
 
         public final String pragmaName;
         public final String[] choices;
diff --git a/src/main/java/org/sqlite/SQLiteConnection.java b/src/main/java/org/sqlite/SQLiteConnection.java
index f757dce5..f1a806e3 100644
--- a/src/main/java/org/sqlite/SQLiteConnection.java
+++ b/src/main/java/org/sqlite/SQLiteConnection.java
@@ -14,6 +14,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Properties;
 import java.util.concurrent.Executor;
+import org.sqlite.SQLiteConfig.TransactionMode;
 import org.sqlite.core.CoreDatabaseMetaData;
 import org.sqlite.core.DB;
 import org.sqlite.core.NativeDB;
@@ -26,6 +27,9 @@ public abstract class SQLiteConnection implements Connection {
     private CoreDatabaseMetaData meta = null;
     private final SQLiteConnectionConfig connectionConfig;
 
+    private TransactionMode currentTransactionMode;
+    private boolean firstStatementExecuted = false;
+
     /**
      * Connection constructor for reusing an existing DB handle
      *
@@ -62,6 +66,9 @@ public abstract class SQLiteConnection implements Connection {
             SQLiteConfig config = this.db.getConfig();
             this.connectionConfig = this.db.getConfig().newConnectionConfig();
             config.apply(this);
+            this.currentTransactionMode = this.getDatabase().getConfig().getTransactionMode();
+            // connection starts in "clean" state (even though some PRAGMA statements were executed)
+            this.firstStatementExecuted = false;
         } catch (Throwable t) {
             try {
                 if (newDB != null) {
@@ -74,6 +81,22 @@ public abstract class SQLiteConnection implements Connection {
         }
     }
 
+    public TransactionMode getCurrentTransactionMode() {
+        return this.currentTransactionMode;
+    }
+
+    public void setCurrentTransactionMode(final TransactionMode currentTransactionMode) {
+        this.currentTransactionMode = currentTransactionMode;
+    }
+
+    public void setFirstStatementExecuted(final boolean firstStatementExecuted) {
+        this.firstStatementExecuted = firstStatementExecuted;
+    }
+
+    public boolean isFirstStatementExecuted() {
+        return firstStatementExecuted;
+    }
+
     public SQLiteConnectionConfig getConnectionConfig() {
         return connectionConfig;
     }
@@ -332,9 +355,15 @@ public abstract class SQLiteConnection implements Connection {
         if (connectionConfig.isAutoCommit() == ac) return;
 
         connectionConfig.setAutoCommit(ac);
-        db.exec(
-                connectionConfig.isAutoCommit() ? "commit;" : connectionConfig.transactionPrefix(),
-                ac);
+        // db.exec(connectionConfig.isAutoCommit() ? "commit;" : this.transactionPrefix(), ac);
+
+        if (this.getConnectionConfig().isAutoCommit()) {
+            db.exec("commit;", ac);
+            this.currentTransactionMode = null;
+        } else {
+            db.exec(this.transactionPrefix(), ac);
+            this.currentTransactionMode = this.getConnectionConfig().getTransactionMode();
+        }
     }
 
     /**
@@ -413,7 +442,9 @@ public abstract class SQLiteConnection implements Connection {
         checkOpen();
         if (connectionConfig.isAutoCommit()) throw new SQLException("database in auto-commit mode");
         db.exec("commit;", getAutoCommit());
-        db.exec(connectionConfig.transactionPrefix(), getAutoCommit());
+        db.exec(this.transactionPrefix(), getAutoCommit());
+        this.firstStatementExecuted = false;
+        this.setCurrentTransactionMode(this.getConnectionConfig().getTransactionMode());
     }
 
     /** @see java.sql.Connection#rollback() */
@@ -422,7 +453,9 @@ public abstract class SQLiteConnection implements Connection {
         checkOpen();
         if (connectionConfig.isAutoCommit()) throw new SQLException("database in auto-commit mode");
         db.exec("rollback;", getAutoCommit());
-        db.exec(connectionConfig.transactionPrefix(), getAutoCommit());
+        db.exec(this.transactionPrefix(), getAutoCommit());
+        this.firstStatementExecuted = false;
+        this.setCurrentTransactionMode(this.getConnectionConfig().getTransactionMode());
     }
 
     /**
@@ -527,4 +560,8 @@ public abstract class SQLiteConnection implements Connection {
         final String newFilename = sb.toString();
         return newFilename;
     }
+
+    protected String transactionPrefix() {
+        return this.connectionConfig.transactionPrefix();
+    }
 }
diff --git a/src/main/java/org/sqlite/core/CorePreparedStatement.java b/src/main/java/org/sqlite/core/CorePreparedStatement.java
index f40db8da..f7a85fa8 100644
--- a/src/main/java/org/sqlite/core/CorePreparedStatement.java
+++ b/src/main/java/org/sqlite/core/CorePreparedStatement.java
@@ -23,6 +23,7 @@ import java.util.Calendar;
 import org.sqlite.SQLiteConnection;
 import org.sqlite.SQLiteConnectionConfig;
 import org.sqlite.date.FastDateFormat;
+import org.sqlite.jdbc3.JDBC3Connection;
 import org.sqlite.jdbc4.JDBC4Statement;
 
 public abstract class CorePreparedStatement extends JDBC4Statement {
@@ -64,12 +65,20 @@ public abstract class CorePreparedStatement extends JDBC4Statement {
             return new long[] {};
         }
 
-        try {
-            return conn.getDatabase()
-                    .executeBatch(pointer, batchQueryCount, batch, conn.getAutoCommit());
-        } finally {
-            clearBatch();
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
         }
+
+        return this.withConnectionTimeout(
+                () -> {
+                    try {
+                        return conn.getDatabase()
+                                .executeBatch(
+                                        pointer, batchQueryCount, batch, conn.getAutoCommit());
+                    } finally {
+                        clearBatch();
+                    }
+                });
     }
 
     /** @see org.sqlite.jdbc3.JDBC3Statement#clearBatch() () */
diff --git a/src/main/java/org/sqlite/core/CoreStatement.java b/src/main/java/org/sqlite/core/CoreStatement.java
index 8e9406b4..55b97c4e 100644
--- a/src/main/java/org/sqlite/core/CoreStatement.java
+++ b/src/main/java/org/sqlite/core/CoreStatement.java
@@ -19,6 +19,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import org.sqlite.SQLiteConnection;
 import org.sqlite.SQLiteConnectionConfig;
+import org.sqlite.jdbc3.JDBC3Connection;
 import org.sqlite.jdbc4.JDBC4ResultSet;
 
 public abstract class CoreStatement implements Codes {
@@ -68,12 +69,17 @@ public abstract class CoreStatement implements Codes {
         if (sql == null) throw new SQLException("SQLiteJDBC internal error: sql==null");
         if (rs.isOpen()) throw new SQLException("SQLite JDBC internal error: rs.isOpen() on exec.");
 
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
+        }
+
         boolean success = false;
         boolean rc = false;
         try {
             rc = conn.getDatabase().execute(this, null);
             success = true;
         } finally {
+            notifyFirstStatementExecuted();
             resultsWaiting = rc;
             if (!success) {
                 this.pointer.close();
@@ -95,12 +101,17 @@ public abstract class CoreStatement implements Codes {
         if (sql == null) throw new SQLException("SQLiteJDBC internal error: sql==null");
         if (rs.isOpen()) throw new SQLException("SQLite JDBC internal error: rs.isOpen() on exec.");
 
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
+        }
+
         boolean rc = false;
         boolean success = false;
         try {
             rc = conn.getDatabase().execute(sql, conn.getAutoCommit());
             success = true;
         } finally {
+            notifyFirstStatementExecuted();
             resultsWaiting = rc;
             if (!success && pointer != null) {
                 pointer.close();
@@ -124,5 +135,9 @@ public abstract class CoreStatement implements Codes {
         }
     }
 
+    protected void notifyFirstStatementExecuted() {
+        conn.setFirstStatementExecuted(true);
+    }
+
     public abstract ResultSet executeQuery(String sql, boolean closeStmt) throws SQLException;
 }
diff --git a/src/main/java/org/sqlite/core/NativeDB.java b/src/main/java/org/sqlite/core/NativeDB.java
index e46e0fd3..9560f319 100644
--- a/src/main/java/org/sqlite/core/NativeDB.java
+++ b/src/main/java/org/sqlite/core/NativeDB.java
@@ -18,6 +18,7 @@ package org.sqlite.core;
 
 import java.nio.ByteBuffer;
 import java.nio.charset.StandardCharsets;
+import java.sql.DriverManager;
 import java.sql.SQLException;
 import org.sqlite.BusyHandler;
 import org.sqlite.Collation;
@@ -83,6 +84,8 @@ public final class NativeDB extends DB {
     /** @see org.sqlite.core.DB#_exec(java.lang.String) */
     @Override
     public synchronized int _exec(String sql) throws SQLException {
+        DriverManager.println(
+                "DriverManager [" + Thread.currentThread().getName() + "] [SQLite EXEC] " + sql);
         return _exec_utf8(stringToUtf8ByteArray(sql));
     }
 
@@ -114,6 +117,8 @@ public final class NativeDB extends DB {
     /** @see org.sqlite.core.DB#prepare(java.lang.String) */
     @Override
     protected synchronized SafeStmtPtr prepare(String sql) throws SQLException {
+        DriverManager.println(
+                "DriverManager [" + Thread.currentThread().getName() + "] [SQLite PREP] " + sql);
         return new SafeStmtPtr(this, prepare_utf8(stringToUtf8ByteArray(sql)));
     }
 
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3Connection.java b/src/main/java/org/sqlite/jdbc3/JDBC3Connection.java
index 93e54d90..1ec76e59 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3Connection.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3Connection.java
@@ -13,6 +13,8 @@ import java.util.HashMap;
 import java.util.Map;
 import java.util.Properties;
 import java.util.concurrent.atomic.AtomicInteger;
+import org.sqlite.SQLiteConfig;
+import org.sqlite.SQLiteConfig.TransactionMode;
 import org.sqlite.SQLiteConnection;
 import org.sqlite.SQLiteOpenMode;
 
@@ -20,10 +22,64 @@ public abstract class JDBC3Connection extends SQLiteConnection {
     private final AtomicInteger savePoint = new AtomicInteger(0);
     private Map<String, Class<?>> typeMap;
 
+    private boolean readOnly = false;
+
     protected JDBC3Connection(String url, String fileName, Properties prop) throws SQLException {
         super(url, fileName, prop);
     }
 
+    /**
+     * This will try to enforce the transaction mode if SQLiteConfig#isExplicitReadOnly is true and
+     * auto commit is disabled.
+     *
+     * <ul>
+     *   <li>If this connection is read only, the PRAGMA query_only will be set
+     *   <li>If this connection is not read only:
+     *       <ul>
+     *         <li>if no statement has been executed, PRAGMA query_only will be set to false, and an
+     *             IMMEDIATE transaction will be started
+     *         <li>if a statement has already been executed, an exception is thrown
+     *       </ul>
+     * </ul>
+     *
+     * @throws SQLException if a statement has already been executed on this connection, then the
+     *     transaction cannot be upgraded to write
+     */
+    @SuppressWarnings("deprecation")
+    public void tryEnforceTransactionMode() throws SQLException {
+        // important note: read-only mode is only supported when auto-commit is disabled
+        if (getDatabase().getConfig().isExplicitReadOnly()
+                && !this.getAutoCommit()
+                && this.getCurrentTransactionMode() != null) {
+            if (isReadOnly()) {
+                // this is a read-only transaction, make sure all writing operations are rejected by
+                // the DB
+                // (note: this pragma is evaluated on a per-transaction basis by SQLite)
+                getDatabase()._exec("PRAGMA query_only = true;");
+            } else {
+                if (getCurrentTransactionMode() == TransactionMode.DEFERRED
+                        || this.getCurrentTransactionMode() == TransactionMode.DEFFERED) {
+                    if (isFirstStatementExecuted()) {
+                        // first statement was already executed; cannot upgrade to write
+                        // transaction!
+                        throw new SQLException(
+                                "A statement has already been executed on this connection; cannot upgrade to write transaction");
+                    } else {
+                        // this is the first statement in the transaction; close and create an
+                        // immediate one
+                        getDatabase()._exec("commit; /* need to explicitly upgrade transaction */");
+
+                        // start the write transaction
+                        getDatabase()._exec("PRAGMA query_only = false;");
+                        getDatabase()
+                                ._exec("BEGIN IMMEDIATE; /* explicitly upgrade transaction */");
+                        setCurrentTransactionMode(TransactionMode.IMMEDIATE);
+                    }
+                }
+            }
+        }
+    }
+
     /** @see java.sql.Connection#getCatalog() */
     public String getCatalog() throws SQLException {
         checkOpen();
@@ -68,18 +124,32 @@ public abstract class JDBC3Connection extends SQLiteConnection {
     }
 
     /** @see java.sql.Connection#isReadOnly() */
-    public boolean isReadOnly() throws SQLException {
-        return (getDatabase().getConfig().getOpenModeFlags() & SQLiteOpenMode.READONLY.flag) != 0;
+    public boolean isReadOnly() {
+        SQLiteConfig config = getDatabase().getConfig();
+        return (
+        // the entire database is read-only
+        ((config.getOpenModeFlags() & SQLiteOpenMode.READONLY.flag) != 0)
+                // the flag was set explicitly by the user on this connection
+                || (config.isExplicitReadOnly() && this.readOnly));
     }
 
     /** @see java.sql.Connection#setReadOnly(boolean) */
     public void setReadOnly(boolean ro) throws SQLException {
-        // trying to change read-only flag
-        if (ro != isReadOnly()) {
-            throw new SQLException(
-                    "Cannot change read-only flag after establishing a connection."
-                            + " Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection().");
+        if (getDatabase().getConfig().isExplicitReadOnly()) {
+            if (ro != readOnly && isFirstStatementExecuted()) {
+                throw new SQLException(
+                        "Cannot change Read-Only status of this connection: the first statement was"
+                                + " already executed and the transaction is open.");
+            }
+        } else {
+            // trying to change read-only flag
+            if (ro != isReadOnly()) {
+                throw new SQLException(
+                        "Cannot change read-only flag after establishing a connection."
+                                + " Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection().");
+            }
         }
+        this.readOnly = ro;
     }
 
     /** @see java.sql.Connection#nativeSQL(java.lang.String) */
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3PreparedStatement.java b/src/main/java/org/sqlite/jdbc3/JDBC3PreparedStatement.java
index 8cf391bb..9dc29740 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3PreparedStatement.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3PreparedStatement.java
@@ -44,14 +44,22 @@ public abstract class JDBC3PreparedStatement extends CorePreparedStatement {
         rs.close();
         pointer.safeRunConsume(DB::reset);
 
-        boolean success = false;
-        try {
-            resultsWaiting = conn.getDatabase().execute(this, batch);
-            success = true;
-            return 0 != columnCount;
-        } finally {
-            if (!success && !pointer.isClosed()) pointer.safeRunConsume(DB::reset);
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
         }
+
+        return this.withConnectionTimeout(
+                () -> {
+                    boolean success = false;
+                    try {
+                        resultsWaiting =
+                                conn.getDatabase().execute(JDBC3PreparedStatement.this, batch);
+                        success = true;
+                        return 0 != columnCount;
+                    } finally {
+                        if (!success && !pointer.isClosed()) pointer.safeRunConsume(DB::reset);
+                    }
+                });
     }
 
     /** @see java.sql.PreparedStatement#executeQuery() */
@@ -65,16 +73,24 @@ public abstract class JDBC3PreparedStatement extends CorePreparedStatement {
         rs.close();
         pointer.safeRunConsume(DB::reset);
 
-        boolean success = false;
-        try {
-            resultsWaiting = conn.getDatabase().execute(this, batch);
-            success = true;
-        } finally {
-            if (!success && !pointer.isClosed()) {
-                pointer.safeRunInt(DB::reset);
-            }
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
         }
-        return getResultSet();
+
+        return this.withConnectionTimeout(
+                () -> {
+                    boolean success = false;
+                    try {
+                        resultsWaiting =
+                                conn.getDatabase().execute(JDBC3PreparedStatement.this, batch);
+                        success = true;
+                    } finally {
+                        if (!success && !pointer.isClosed()) {
+                            pointer.safeRunInt(DB::reset);
+                        }
+                    }
+                    return getResultSet();
+                });
     }
 
     /** @see java.sql.PreparedStatement#executeUpdate() */
@@ -93,7 +109,12 @@ public abstract class JDBC3PreparedStatement extends CorePreparedStatement {
         rs.close();
         pointer.safeRunConsume(DB::reset);
 
-        return conn.getDatabase().executeUpdate(this, batch);
+        if (this.conn instanceof JDBC3Connection) {
+            ((JDBC3Connection) this.conn).tryEnforceTransactionMode();
+        }
+
+        return this.withConnectionTimeout(
+                () -> conn.getDatabase().executeUpdate(JDBC3PreparedStatement.this, batch));
     }
 
     /** @see java.sql.PreparedStatement#addBatch() */
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java b/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
index 8db8e10c..274e6516 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
@@ -15,10 +15,14 @@ import org.sqlite.core.DB;
 import org.sqlite.core.DB.ProgressObserver;
 
 public abstract class JDBC3Statement extends CoreStatement {
+
+    private int queryTimeout; // in seconds, as per the JDBC spec
+
     // PUBLIC INTERFACE /////////////////////////////////////////////
 
     protected JDBC3Statement(SQLiteConnection conn) {
         super(conn);
+        this.queryTimeout = 0;
     }
 
     /** @see java.sql.Statement#close() */
@@ -27,20 +31,23 @@ public abstract class JDBC3Statement extends CoreStatement {
     }
 
     /** @see java.sql.Statement#execute(java.lang.String) */
-    public boolean execute(String sql) throws SQLException {
+    public boolean execute(final String sql) throws SQLException {
         internalClose();
 
-        SQLExtension ext = ExtendedCommand.parse(sql);
-        if (ext != null) {
-            ext.execute(conn.getDatabase());
+        return this.withConnectionTimeout(
+                () -> {
+                    SQLExtension ext = ExtendedCommand.parse(sql);
+                    if (ext != null) {
+                        ext.execute(conn.getDatabase());
 
-            return false;
-        }
+                        return false;
+                    }
 
-        this.sql = sql;
+                    JDBC3Statement.this.sql = sql;
 
-        conn.getDatabase().prepare(this);
-        return exec();
+                    conn.getDatabase().prepare(JDBC3Statement.this);
+                    return exec();
+                });
     }
 
     /** @see java.sql.Statement#execute(java.lang.String, int) */
@@ -63,14 +70,18 @@ public abstract class JDBC3Statement extends CoreStatement {
         internalClose();
         this.sql = sql;
 
-        conn.getDatabase().prepare(this);
+        return this.withConnectionTimeout(
+                () -> {
+                    conn.getDatabase().prepare(JDBC3Statement.this);
 
-        if (!exec()) {
-            internalClose();
-            throw new SQLException("query does not return ResultSet", "SQLITE_DONE", SQLITE_DONE);
-        }
+                    if (!exec()) {
+                        internalClose();
+                        throw new SQLException(
+                                "query does not return ResultSet", "SQLITE_DONE", SQLITE_DONE);
+                    }
 
-        return getResultSet();
+                    return getResultSet();
+                });
     }
 
     static class BackupObserver implements ProgressObserver {
@@ -80,7 +91,7 @@ public abstract class JDBC3Statement extends CoreStatement {
     }
 
     /** @see java.sql.Statement#executeUpdate(java.lang.String) */
-    public int executeUpdate(String sql) throws SQLException {
+    public int executeUpdate(final String sql) throws SQLException {
         return (int) executeLargeUpdate(sql);
     }
 
@@ -93,27 +104,30 @@ public abstract class JDBC3Statement extends CoreStatement {
     public long executeLargeUpdate(String sql) throws SQLException {
         internalClose();
         this.sql = sql;
-        DB db = conn.getDatabase();
-
-        long changes = 0;
-        SQLExtension ext = ExtendedCommand.parse(sql);
-        if (ext != null) {
-            // execute extended command
-            ext.execute(db);
-        } else {
-            try {
-                changes = db.total_changes();
 
-                // directly invokes the exec API to support multiple SQL statements
-                int statusCode = db._exec(sql);
-                if (statusCode != SQLITE_OK) throw DB.newSQLException(statusCode, "");
-
-                changes = db.total_changes() - changes;
-            } finally {
-                internalClose();
-            }
-        }
-        return changes;
+        return this.withConnectionTimeout(
+                () -> {
+                    DB db = conn.getDatabase();
+                    long changes = 0;
+                    SQLExtension ext = ExtendedCommand.parse(sql);
+                    if (ext != null) {
+                        // execute extended command
+                        ext.execute(db);
+                    } else {
+                        try {
+                            changes = db.total_changes();
+
+                            // directly invokes the exec API to support multiple SQL statements
+                            int statusCode = db._exec(sql);
+                            if (statusCode != SQLITE_OK) throw DB.newSQLException(statusCode, "");
+
+                            changes = db.total_changes() - changes;
+                        } finally {
+                            internalClose();
+                        }
+                    }
+                    return changes;
+                });
     }
 
     /** @see java.sql.Statement#executeLargeUpdate(java.lang.String, int) */
@@ -245,13 +259,15 @@ public abstract class JDBC3Statement extends CoreStatement {
 
     /** @see java.sql.Statement#getQueryTimeout() */
     public int getQueryTimeout() throws SQLException {
-        return conn.getBusyTimeout();
+        return queryTimeout;
     }
 
     /** @see java.sql.Statement#setQueryTimeout(int) */
     public void setQueryTimeout(int seconds) throws SQLException {
-        if (seconds < 0) throw new SQLException("query timeout must be >= 0");
-        conn.setBusyTimeout(1000 * seconds);
+        if (seconds < 0) {
+            throw new SQLException("query timeout must be >= 0");
+        }
+        this.queryTimeout = seconds;
     }
 
     // TODO: write test
@@ -402,4 +418,26 @@ public abstract class JDBC3Statement extends CoreStatement {
     public long executeLargeUpdate(String sql, String[] cols) throws SQLException {
         throw unsupported();
     }
+
+    protected <T> T withConnectionTimeout(SQLCallable<T> callable) throws SQLException {
+        int origBusyTimeout = conn.getBusyTimeout();
+        if (queryTimeout > 0) {
+            // SQLite handles busy timeout in milliseconds, JDBC in seconds
+            conn.setBusyTimeout(1000 * queryTimeout);
+        }
+        try {
+            return callable.call();
+        } finally {
+            if (queryTimeout > 0) {
+                // reset connection timeout to the original value
+                conn.setBusyTimeout(origBusyTimeout);
+            }
+        }
+    }
+
+    @FunctionalInterface
+    protected interface SQLCallable<T> {
+
+        T call() throws SQLException;
+    }
 }


Test patch:
diff --git a/src/test/java/org/sqlite/BusyHandlerTest.java b/src/test/java/org/sqlite/BusyHandlerTest.java
index 573cf1fc..c1e6724c 100644
--- a/src/test/java/org/sqlite/BusyHandlerTest.java
+++ b/src/test/java/org/sqlite/BusyHandlerTest.java
@@ -13,6 +13,7 @@ import java.util.concurrent.CompletableFuture;
 import java.util.concurrent.CountDownLatch;
 import org.junit.jupiter.api.AfterEach;
 import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Disabled;
 import org.junit.jupiter.api.Test;
 import org.sqlite.core.DB;
 import org.sqlite.core.NativeDBHelper;
@@ -108,6 +109,7 @@ public class BusyHandlerTest {
      * @throws Exception on test failure
      */
     @Test
+    @Disabled("This test is very flaky; disabling it for now")
     public void basicBusyHandler() throws Exception {
         basicBusyHandler(0);
     }
@@ -155,6 +157,7 @@ public class BusyHandlerTest {
      * @throws Exception on test failure
      */
     @Test
+    @Disabled("This test is very flaky; disabling it for now")
     public void testUnregister() throws Exception {
         final int[] calls = {0};
         BusyHandler.setHandler(
diff --git a/src/test/java/org/sqlite/JDBCTest.java b/src/test/java/org/sqlite/JDBCTest.java
index 99fb7e49..25b9606c 100644
--- a/src/test/java/org/sqlite/JDBCTest.java
+++ b/src/test/java/org/sqlite/JDBCTest.java
@@ -9,12 +9,15 @@
 // --------------------------------------
 package org.sqlite;
 
-import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.*;
 
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.Statement;
+import java.io.File;
+import java.io.PrintWriter;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
 import java.util.Properties;
+import java.util.concurrent.atomic.AtomicInteger;
 import org.junit.jupiter.api.Test;
 
 public class JDBCTest {
@@ -48,4 +51,224 @@ public class JDBCTest {
     public void shouldReturnNullIfProtocolUnhandled() throws Exception {
         assertThat(JDBC.createConnection("jdbc:anotherpopulardatabaseprotocol:", null)).isNull();
     }
+
+    @Test
+    public void pragmaReadOnly() throws SQLException {
+        SQLiteConnection connection =
+                (SQLiteConnection)
+                        DriverManager.getConnection(
+                                "jdbc:sqlite::memory:?jdbc.explicit_readonly=true");
+        assertThat(connection.getDatabase().getConfig().isExplicitReadOnly()).isTrue();
+    }
+
+    @Test
+    public void canSetJdbcConnectionToReadOnly() throws Exception {
+        SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+        try (Connection connection = dataSource.getConnection()) {
+            connection.setAutoCommit(false);
+            assertThat(connection.isReadOnly()).isFalse();
+            connection.setReadOnly(true);
+            assertThat(connection.isReadOnly()).isTrue();
+            connection.setReadOnly(false);
+            assertThat(connection.isReadOnly()).isFalse();
+            connection.setReadOnly(true);
+            assertThat(connection.isReadOnly()).isTrue();
+        }
+    }
+
+    @Test
+    public void cannotSetJdbcConnectionToReadOnlyAfterFirstStatement() throws Exception {
+        SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+
+        try (Connection connection = dataSource.getConnection()) {
+            connection.setAutoCommit(false);
+            // execute a statement
+            try (Statement statement = connection.createStatement()) {
+                boolean success = statement.execute("SELECT * FROM sqlite_master");
+                assertThat(success).isTrue();
+            }
+            // try to assign read-only
+            assertThatExceptionOfType(SQLException.class)
+                    .as("Managed to set readOnly = true on a dirty connection!")
+                    .isThrownBy(() -> connection.setReadOnly(true));
+        }
+    }
+
+    @Test
+    public void canSetJdbcConnectionToReadOnlyAfterCommit() throws Exception {
+        SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+        try (Connection connection = dataSource.getConnection()) {
+            connection.setAutoCommit(false);
+            connection.setReadOnly(true);
+            // execute a statement
+            try (Statement statement = connection.createStatement()) {
+                boolean success = statement.execute("SELECT * FROM sqlite_master");
+                assertThat(success).isTrue();
+            }
+            connection.commit();
+
+            // try to assign a new read-only value
+            connection.setReadOnly(false);
+        }
+    }
+
+    @Test
+    public void canSetJdbcConnectionToReadOnlyAfterRollback() throws Exception {
+        System.out.println("Creating JDBC Datasource");
+        SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+        System.out.println("Creating JDBC Connection");
+        try (Connection connection = dataSource.getConnection()) {
+            System.out.println("JDBC Connection created");
+            System.out.println("Disabling auto-commit");
+            connection.setAutoCommit(false);
+            System.out.println("Creating statement");
+            // execute a statement
+            try (Statement statement = connection.createStatement()) {
+                System.out.println("Executing query");
+                boolean success = statement.execute("SELECT * FROM sqlite_master");
+                assertThat(success).isTrue();
+            } finally {
+                System.out.println("Closing statement");
+            }
+            System.out.println("Performing rollback");
+            connection.rollback();
+
+            System.out.println("Setting connection to read-only");
+            // try to assign read-only
+            connection.setReadOnly(true);
+            // execute a statement
+            try (Statement statement2 = connection.createStatement()) {
+                System.out.println("Executing query 2");
+                boolean success = statement2.execute("SELECT * FROM sqlite_master");
+                assertThat(success).isTrue();
+            } finally {
+                System.out.println("Closing statement 2");
+            }
+            System.out.println("Performing rollback 2");
+            connection.rollback();
+        }
+    }
+
+    @Test
+    public void cannotExecuteUpdatesWhenConnectionIsSetToReadOnly() throws Exception {
+        SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+        try (Connection connection = dataSource.getConnection()) {
+            connection.setAutoCommit(false);
+            connection.setReadOnly(true);
+
+            // execute a statement
+            try (Statement statement = connection.createStatement()) {
+                assertThatExceptionOfType(SQLException.class)
+                        .as("Managed to modify DB contents on a read-only connection!")
+                        .isThrownBy(
+                                () ->
+                                        statement.execute(
+                                                "CREATE TABLE TestTable(ID VARCHAR(255), PRIMARY KEY(ID))"));
+            }
+            connection.rollback();
+
+            // try to assign read-only
+            connection.setReadOnly(true);
+        }
+    }
+
+    @Test
+    void name() {}
+
+    @Test
+    public void jdbcHammer() throws Exception {
+        final SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
+        File tempFile = File.createTempFile("myTestDB", ".db");
+        dataSource.setUrl("jdbc:sqlite:" + tempFile.getAbsolutePath());
+        try (Connection connection = dataSource.getConnection()) {
+            connection.setAutoCommit(false);
+            try (Statement stmt = connection.createStatement()) {
+                stmt.executeUpdate("CREATE TABLE TestTable(ID INT, testval INT, PRIMARY KEY(ID));");
+                stmt.executeUpdate("INSERT INTO TestTable (ID, testval) VALUES(1, 0);");
+            }
+            connection.commit();
+        }
+
+        final AtomicInteger count = new AtomicInteger();
+        List<Thread> threads = new ArrayList<>();
+        for (int i = 0; i < 10; i++) {
+            Thread thread =
+                    new Thread(
+                            () -> {
+                                for (int i1 = 0; i1 < 100; i1++) {
+                                    try {
+                                        try (Connection connection = dataSource.getConnection()) {
+                                            connection.setAutoCommit(false);
+                                            boolean read = Math.random() < 0.5;
+                                            if (read) {
+                                                connection.setReadOnly(true);
+                                                try (Statement statement =
+                                                        connection.createStatement()) {
+                                                    ResultSet rs =
+                                                            statement.executeQuery(
+                                                                    "SELECT * FROM TestTable");
+                                                    rs.close();
+                                                }
+                                            } else {
+                                                try (Statement statement =
+                                                        connection.createStatement()) {
+                                                    try (ResultSet rs =
+                                                            statement.executeQuery(
+                                                                    "SELECT * FROM TestTable")) {
+                                                        while (rs.next()) {
+                                                            int id = rs.getInt("ID");
+                                                            int value = rs.getInt("testval");
+                                                            count.incrementAndGet();
+                                                            statement.executeUpdate(
+                                                                    "UPDATE TestTable SET testval = "
+                                                                            + (value + 1)
+                                                                            + " WHERE ID = "
+                                                                            + id);
+                                                        }
+                                                    }
+                                                }
+                                                connection.commit();
+                                            }
+                                        }
+                                    } catch (SQLException e) {
+                                        throw new RuntimeException("Worker failed", e);
+                                    }
+                                }
+                            });
+            thread.setName("Worker #" + (i + 1));
+            threads.add(thread);
+        }
+        for (Thread thread : threads) {
+            thread.start();
+        }
+        for (Thread thread : threads) {
+            thread.join();
+        }
+        try (Connection connection2 = dataSource.getConnection()) {
+            connection2.setAutoCommit(false);
+            connection2.setReadOnly(true);
+            try (Statement stmt = connection2.createStatement()) {
+                try (ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable")) {
+                    assertThat(rs.next()).isTrue();
+                    int id = rs.getInt("ID");
+                    int val = rs.getInt("testval");
+                    assertThat(id).isEqualTo(1);
+                    assertThat(val).isEqualTo(count.get());
+                    assertThat(rs.next()).isFalse();
+                }
+            }
+            connection2.commit();
+        }
+    }
+
+    // helper methods -----------------------------------------------------------------
+
+    private SQLiteDataSource createDatasourceWithExplicitReadonly() {
+        DriverManager.setLogWriter(new PrintWriter(System.out));
+        SQLiteConfig config = new SQLiteConfig();
+        config.setExplicitReadOnly(true);
+        config.setBusyTimeout(10000);
+
+        return new SQLiteDataSource(config);
+    }
 }