Documentation

Ce document en français.

version 3.50.2.1-SNAPSHOT

Introduction:

The SQLite-JDBC driver is part of a Series of applications allowing us to offer you innovative services in free software.

It was born from a fork of xerial sqlite-jdbc following a refusal to merge a pull request to make the SQLite driver compatible with JDBC 4.1.

This driver allows you to use software compatible with JDBC 4.1 and SQLite database.

Being free software I encourage you:

• To duplicate its source code.
• To make changes, corrections, improvements.
• To open issue if needed.

In short, to participate in the development of this extension.
Because it is together that we can make Free Software smarter.

---

Requirement:

The SQLite-JDBC archive requires Java 11 or higher to run.

---

Installation:

Download the jar archive and do what you like.

• jar file sqlite-jdbc-3.50.2.1-SNAPSHOT.jar

---

How does it work:

Its operation does not really differ from the original driver and here are the modifications that were necessary:

• A result set (java.sql.ResultSet) is cached when executing SQL INSERT or REPLACE queries using the methods:
  • java.sql.Statement.executeUpdate(String sql, int autoGeneratedKeys)
  • java.sql.Statement.executeUpdate(String sql, String[] columnNames)
  • java.sql.PreparedStatement.executeUpdate() if the necessary options during its creation have been provided.
• SQL commands starting with INSERT or REPLACE will be supplemented with the RETURNING clause to which are added the necessary options to obtain the cached result set.
• The result set cached, when executing one of the executeUpdate() methods, is returned when the java.sql.Statement.getGeneratedKeys() method is called.

Having to complete the INSERT and REPLACE SQL commands in order to be able to follow the JDBC 4.1 API may seem at first incongruous. But in fact this is the solution implemented in the IBM DB2 v11.5 driver (ie: it suffixes SQL commands INSERT with SELECT FROM). I think the same may be true for all JDBC 4.1 drivers whose database is not written in Java and in any case this is only an implementation detail.

These modifications only concern the internal functioning of the driver and are not visible. On the other hand, to make them functional it was necessary to modify certain access methods to the JDBC SQLite driver.
You will find the description of this new API in the following section.

---

The new SQLite JDBC 4.1 API:

Here is the list of Java methods modified to support JDBC 4.1:

• java.sql.DatabaseMetaData.supportsGetGeneratedKeys() method answers true.
• java.sql.DatabaseMetaData.generatedKeyAlwaysReturned() method answers false.
• java.sql.Statement.executeUpdate(String sql, int autoGeneratedKeys) method works as follows:
  • If autoGeneratedKeys is equal to java.sql.Statement.RETURN_GENERATED_KEYS and the SQL command begins with INSERT or REPLACE and it only includes one SQL command then the records affected by the SQL command will be accessible by the java.sql.Statement.getGeneratedKeys() method (all columns in the table will be returned).
  • Otherwise the SQL command will be executed without influencing the operation of getGenerateKeys() which allows you to interweave queries before having to retrieve the records using getGeneratedKeys()...
• java.sql.Statement.executeLargeUpdate(String sql, int autoGeneratedKeys) method works like the previous one...
• java.sql.Statement.executeUpdate(String sql, String[] columnNames) method allows you to get only the requested columnNames in getGeneratedKeys().
• java.sql.Statement.executeLargeUpdate(String sql, String[] columnNames) method works like the previous one...
• java.sql.Statement.executeUpdate(String sql, int[] columnIndex) method is not supported.
• java.sql.Statement.executeLargeUpdate(String sql, int[] columnIndex) method is not supported.
• java.sql.Statement.execute(String sql, int autoGeneratedKeys) method is a no ops, it clear any cached result set and calls execute(String sql). You need to use executeUpdate(String sql, int autoGeneratedKeys) then getGeneratedKeys() methods.
• java.sql.Statement.execute(String sql, String[] columnNames) method is a no ops, it clear any cached result set and calls execute(String sql). You need to use executeUpdate(String sql, String[] columnNames) then getGeneratedKeys() methods.
• java.sql.Statement.execute(String sql, int[] columnIndex) method is not supported.
• java.sql.Connection.prepareStatement(String sql, int autoGeneratedKeys) method work same as executeUpdate(String sql, int autoGeneratedKeys) concerning the use of getGeneratedKeys() which must however be accessed after each call to the executeUpdate() or executeLargeUpdate() method.
• java.sql.Connection.prepareStatement(String sql, String[] columnNames) method work same as executeUpdate(String sql, String[] columnNames) concerning the use of getGeneratedKeys() which must however be accessed after each call to the executeUpdate() or executeLargeUpdate() method.
• java.sql.Connection.prepareStatement(String sql, int[] columnIndex) method is not supported.
• java.sql.PreparedStatement.executeUpdate() method allows access to the getGeneratedKeys() method if the prepared statement was created with the necessary options. The getGeneratedKeys() method must however be accessed after each call to the executeUpdate() or executeLargeUpdate() method.
• java.sql.PreparedStatement.executeLargeUpdate() method works like the previous one...
• java.sql.PreparedStatement.execute() method is a no ops, it clear any cached result set and calls execute(). You need to use executeUpdate() then getGeneratedKeys() methods.
• java.sql.PreparedStatement.executeQuery() method is a no ops, it clear any cached result set and calls executeQuery(). You need to use executeUpdate() then getGeneratedKeys() methods.

I would like to point out that this new mode of operation of the driver is not quite the same as that used in the xerial driver. This may require changing some SQL queries in your programs.
But it assures you that its use complies with JDBC 4.1 standards.

This new driver will bring you many advantages:

• If you use multi-row SQL INSERT commands, this allows massive and fast inserts into the database with just two JDBC methods (producing a single SQL query) executeUpdate() then getGeneratedKeys(). And these are the methods recommended by JDBC to execute the SQL commands INSERT, UPDATE, DELETE...
• Your program using SQLite will be compatible with JDBC and therefore guarantees interoperability at the level of JDBC databases.

---

Historical:

What has been done for version 3.50.2.1:

• The implementation of the java.sql.Statement.getGeneratedKeys() method now uses a javax.sql.rowset.CachedRowSet object to return a ResultSet. This eliminates the need to keep the ResultSet open.
• The dependency on SFL4J has been removed and replaced with the java.lang.System.Logger facade.
• The archive is now compiled with Java 11 and as a module.

What remains to be done for version 3.50.2.1:

• Getting to compile by passing the tests...

• Anything welcome...