Repository: xerial/sqlite-jdbc
Pull request #856 (2023-03-14 15:36:43)

feat: remove length/ dimension of column data types in the `TYPE_NAME` field of `getColumns()`
If columns are defined with `intvalue integer(5)`, `realvalue real(8,3)`, or `charvalue varchar(21)` for example, return `INTEGER`, `REAL` or `VARCHAR` respectively. This is consistent with how other JDBC drivers return the `TYPE_NAME` field in [`DatabaseMetaData.getColumns()`](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-).

Tests are in https://github.com/xerial/sqlite-jdbc/blob/master/src/test/java/org/sqlite/DBMetaDataTest.java#L271

gotson: > This is consistent with how other JDBC drivers return the `TYPE_NAME` field in [`DatabaseMetaData.getColumns()`](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-).

Can you clarify? 
sualeh: > This is consistent with how other JDBC drivers return the TYPE_NAME field 

> Can you clarify?

@gotson The `TYPE_NAME` field in [`DatabaseMetaData.getColumns()`](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-) should return the type name only, without the length/ dimension of the column data types. This information is returned in other fields such as `COLUMN_SIZE` and `DECIMAL_DIGITS`. The Xerial SQLite JDBC Driver returns `COLUMN_SIZE` and `DECIMAL_DIGITS` correctly, but the `TYPE_NAME` field additionally includes these values in parentheses, which is not required.


gotson: > > This is consistent with how other JDBC drivers return the TYPE_NAME field
> 
> > Can you clarify?
> 
> @gotson The `TYPE_NAME` field in [`DatabaseMetaData.getColumns()`](https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-) should return the type name only, without the length/ dimension of the column data types. This information is returned in other fields such as `COLUMN_SIZE` and `DECIMAL_DIGITS`. The Xerial SQLite JDBC Driver returns `COLUMN_SIZE` and `DECIMAL_DIGITS` correctly, but the `TYPE_NAME` field additionally includes these values in parentheses, which is not required.

Can you supplement this with some documentation ? The Javadoc does not explain that, and does not provide any example.

Patch:
diff --git a/src/main/java/org/sqlite/SQLiteConnection.java b/src/main/java/org/sqlite/SQLiteConnection.java
index 76cbefff..75b67f2d 100644
--- a/src/main/java/org/sqlite/SQLiteConnection.java
+++ b/src/main/java/org/sqlite/SQLiteConnection.java
@@ -6,6 +6,7 @@ import java.io.InputStream;
 import java.net.MalformedURLException;
 import java.net.URISyntaxException;
 import java.net.URL;
+import java.net.URLConnection;
 import java.nio.file.Files;
 import java.nio.file.StandardCopyOption;
 import java.sql.Connection;
@@ -330,7 +331,10 @@ public abstract class SQLiteConnection implements Connection {
             //            }
         }
 
-        try (InputStream reader = resourceAddr.openStream()) {
+        URLConnection conn = resourceAddr.openConnection();
+        // Disable caches to avoid keeping unnecessary file references after the single-use copy
+        conn.setUseCaches(false);
+        try (InputStream reader = conn.getInputStream()) {
             Files.copy(reader, dbFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
             return dbFile;
         }
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java b/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
index 844dcd02..6e31e0c6 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3DatabaseMetaData.java
@@ -1070,6 +1070,8 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
                                     // just ignore invalid dimension formats here
                                 }
                             }
+                            // "TYPE_NAME" (colType) is without the length/ dimension
+                            colType = colType.substring(0, iStartOfDimension).trim();
                         }
 
                         int colGenerated = "2".equals(colHidden) ? 1 : 0;
@@ -1079,9 +1081,8 @@ public abstract class JDBC3DatabaseMetaData extends org.sqlite.core.CoreDatabase
                                 .append(" as ordpos, ")
                                 .append(colNullable)
                                 .append(" as colnullable,")
-                                .append("'")
                                 .append(colJavaType)
-                                .append("' as ct, ")
+                                .append(" as ct, ")
                                 .append(iColumnSize)
                                 .append(" as colSize, ")
                                 .append(iDecimalDigits)


Test patch:
diff --git a/src/test/java/org/sqlite/BackupTest.java b/src/test/java/org/sqlite/BackupTest.java
index 60b7399a..09f66793 100644
--- a/src/test/java/org/sqlite/BackupTest.java
+++ b/src/test/java/org/sqlite/BackupTest.java
@@ -14,23 +14,20 @@ import static org.assertj.core.api.Assertions.assertThatCode;
 
 import java.io.File;
 import java.io.IOException;
-import java.nio.file.Files;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.*;
 import java.util.Properties;
 import java.util.concurrent.atomic.AtomicInteger;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 import org.sqlite.core.DB;
 
 public class BackupTest {
+    @TempDir File tempDir;
 
     @Test
     public void backupAndRestore() throws SQLException, IOException {
         // create a memory database
-        File tmpFile = File.createTempFile("backup-test", ".sqlite");
+        File tmpFile = File.createTempFile("backup-test", ".sqlite", tempDir);
 
         try (Connection conn = DriverManager.getConnection("jdbc:sqlite:")) {
             // memory DB to file
@@ -54,8 +51,6 @@ public class BackupTest {
                     }
                 }
             }
-        } finally {
-            Files.deleteIfExists(tmpFile.toPath());
         }
     }
 
@@ -67,7 +62,7 @@ public class BackupTest {
 
     @Test
     void testFailedBackupAndRestore() throws Exception {
-        File tmpFile = File.createTempFile("backup-test", ".sqlite");
+        File tmpFile = File.createTempFile("backup-test", ".sqlite", tempDir);
 
         try (Connection conn = DriverManager.getConnection("jdbc:sqlite:")) {
             // memory DB to file
@@ -92,37 +87,30 @@ public class BackupTest {
                                                         + "doesnotexist.sqlite"))
                         .isOfAnyClassIn(SQLiteException.class);
             }
-        } finally {
-            Files.deleteIfExists(tmpFile.toPath());
         }
     }
 
     @Test
     public void memoryToDisk() throws Exception {
-
         if (!SQLiteJDBCLoader.isNativeMode()) {
             return; // skip this test in pure-java mode
         }
 
-        Connection conn = DriverManager.getConnection("jdbc:sqlite:");
-        Statement stmt = conn.createStatement();
-        stmt.executeUpdate("create table sample(id integer primary key autoincrement, name)");
-        for (int i = 0; i < 10000; i++) {
-            stmt.executeUpdate("insert into sample(name) values(\"leo\")");
-        }
+        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:");
+                Statement stmt = conn.createStatement()) {
+            stmt.executeUpdate("create table sample(id integer primary key autoincrement, name)");
+            for (int i = 0; i < 10000; i++) {
+                stmt.executeUpdate("insert into sample(name) values(\"leo\")");
+            }
 
-        File tmpFile = File.createTempFile("backup-test2", ".sqlite");
-        try {
+            File tmpFile = File.createTempFile("backup-test2", ".sqlite", tempDir);
             stmt.executeUpdate("backup to " + tmpFile.getAbsolutePath());
-            stmt.close();
-        } finally {
-            Files.deleteIfExists(tmpFile.toPath());
         }
     }
 
     @Test
     void testProgress() throws Exception {
-        File tmpFile = File.createTempFile("backup-test", ".sqlite");
+        File tmpFile = File.createTempFile("backup-test", ".sqlite", tempDir);
 
         try (SQLiteConnection conn = JDBC.createConnection("jdbc:sqlite:", new Properties())) {
             // memory DB to file
@@ -145,8 +133,6 @@ public class BackupTest {
             assertThat(rc).isEqualTo(SQLiteErrorCode.SQLITE_OK.code);
             assertThat(remainingStore.get()).isEqualTo(0);
             assertThat(pageCountStore.get()).isGreaterThan(0);
-        } finally {
-            Files.deleteIfExists(tmpFile.toPath());
         }
     }
 }
diff --git a/src/test/java/org/sqlite/BusyHandlerTest.java b/src/test/java/org/sqlite/BusyHandlerTest.java
index c1e6724c..bfd5c09f 100644
--- a/src/test/java/org/sqlite/BusyHandlerTest.java
+++ b/src/test/java/org/sqlite/BusyHandlerTest.java
@@ -3,6 +3,7 @@ package org.sqlite;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.catchThrowable;
 
+import java.nio.file.Path;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
@@ -15,12 +16,14 @@ 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.junit.jupiter.api.io.TempDir;
 import org.sqlite.core.DB;
 import org.sqlite.core.NativeDBHelper;
 
 public class BusyHandlerTest {
     private Connection conn;
     private Statement stat;
+    @TempDir Path tempDir;
 
     @BeforeEach
     public void connect() throws Exception {
@@ -35,8 +38,9 @@ public class BusyHandlerTest {
      * @return the connection
      * @throws SQLException if the connection cannot be established
      */
-    private static Connection createConnection(int threadNum) throws SQLException {
-        return DriverManager.getConnection("jdbc:sqlite:target/test" + threadNum + ".db");
+    private Connection createConnection(int threadNum) throws SQLException {
+        return DriverManager.getConnection(
+                "jdbc:sqlite:" + tempDir.resolve("test" + threadNum + ".db"));
     }
 
     @AfterEach
@@ -46,7 +50,7 @@ public class BusyHandlerTest {
     }
 
     /** An internal helper class which tests that BusyHandlers are thread safe */
-    public static class BusyWork extends Thread {
+    public class BusyWork extends Thread {
         private final Connection busyWorkConn;
         private final Statement stat;
         private final CountDownLatch lockedLatch = new CountDownLatch(1);
@@ -115,40 +119,41 @@ public class BusyHandlerTest {
     }
 
     private void basicBusyHandler(int threadNum) throws Exception {
-        Connection localConn = createConnection(threadNum);
-        final int[] calls = {0};
-        BusyHandler.setHandler(
-                localConn,
-                new BusyHandler() {
-                    @Override
-                    protected int callback(int nbPrevInvok) {
-                        assertThat(calls[0]).isEqualTo(nbPrevInvok);
-                        calls[0]++;
+        try (Connection localConn = createConnection(threadNum)) {
+            final int[] calls = {0};
+            BusyHandler.setHandler(
+                    localConn,
+                    new BusyHandler() {
+                        @Override
+                        protected int callback(int nbPrevInvok) {
+                            assertThat(calls[0]).isEqualTo(nbPrevInvok);
+                            calls[0]++;
 
-                        if (nbPrevInvok <= 1) {
-                            return 1;
-                        } else {
-                            return 0;
+                            if (nbPrevInvok <= 1) {
+                                return 1;
+                            } else {
+                                return 0;
+                            }
                         }
-                    }
-                });
+                    });
 
-        BusyWork busyWork = new BusyWork(threadNum);
-        busyWork.start();
+            BusyWork busyWork = new BusyWork(threadNum);
+            busyWork.start();
 
-        // let busyWork block inside insert
-        busyWork.lockedLatch.await();
+            // let busyWork block inside insert
+            busyWork.lockedLatch.await();
 
-        try (Statement localStat = localConn.createStatement()) {
-            Throwable thrown = catchThrowable(() -> doWork(localStat));
-            assertThat(thrown).isInstanceOf(SQLiteException.class);
-            assertThat(((SQLiteException) thrown).getErrorCode())
-                    .isEqualTo(SQLiteErrorCode.SQLITE_BUSY.code);
-        }
+            try (Statement localStat = localConn.createStatement()) {
+                Throwable thrown = catchThrowable(() -> doWork(localStat));
+                assertThat(thrown).isInstanceOf(SQLiteException.class);
+                assertThat(((SQLiteException) thrown).getErrorCode())
+                        .isEqualTo(SQLiteErrorCode.SQLITE_BUSY.code);
+            }
 
-        busyWork.completeLatch.countDown();
-        busyWork.join();
-        assertThat(calls[0]).isEqualTo(3);
+            busyWork.completeLatch.countDown();
+            busyWork.join();
+            assertThat(calls[0]).isEqualTo(3);
+        }
     }
 
     /**
diff --git a/src/test/java/org/sqlite/ConnectionTest.java b/src/test/java/org/sqlite/ConnectionTest.java
index b96006db..1f775a77 100644
--- a/src/test/java/org/sqlite/ConnectionTest.java
+++ b/src/test/java/org/sqlite/ConnectionTest.java
@@ -1,20 +1,12 @@
 package org.sqlite;
 
-import static org.assertj.core.api.Assertions.assertThat;
-import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
-import static org.assertj.core.api.Assertions.assertThatThrownBy;
+import static org.assertj.core.api.Assertions.*;
 
 import java.io.File;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.io.InputStream;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.*;
 import java.util.ArrayList;
 import java.util.Properties;
 import java.util.concurrent.ExecutionException;
@@ -22,15 +14,18 @@ import java.util.concurrent.ExecutorService;
 import java.util.concurrent.Executors;
 import java.util.concurrent.Future;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 import org.sqlite.SQLiteConfig.JournalMode;
 import org.sqlite.SQLiteConfig.Pragma;
 import org.sqlite.SQLiteConfig.SynchronousMode;
 
 /**
- * These tests check whether access to files is woring correctly and some Connection.close() cases.
+ * These tests check whether access to files is working correctly and some Connection.close() cases.
  */
 public class ConnectionTest {
 
+    @TempDir static File tempDir;
+
     @Test
     public void isValid() throws SQLException {
         Connection conn = DriverManager.getConnection("jdbc:sqlite:");
@@ -226,12 +221,8 @@ public class ConnectionTest {
 
     public static File copyToTemp(String fileName) throws IOException {
         InputStream in = ConnectionTest.class.getResourceAsStream(fileName);
-        File dir = new File("target");
-        if (!dir.exists()) {
-            dir.mkdirs();
-        }
 
-        File tmp = File.createTempFile(fileName, "", new File("target"));
+        File tmp = File.createTempFile(fileName, "", tempDir);
         tmp.deleteOnExit();
         FileOutputStream out = new FileOutputStream(tmp);
 
@@ -280,6 +271,10 @@ public class ConnectionTest {
         assertThat(rs.getInt(1)).isEqualTo(200);
         rs.close();
         stmt4.close();
+
+        conn1.close();
+        conn2.close();
+        conn3.close();
         conn4.close();
     }
 
@@ -325,6 +320,7 @@ public class ConnectionTest {
                 .isThrownBy(() -> stat.executeUpdate("ATTACH DATABASE attach_test.db AS attachDb"));
 
         stat.close();
+        conn.close();
     }
 
     @Test
diff --git a/src/test/java/org/sqlite/DBMetaDataTest.java b/src/test/java/org/sqlite/DBMetaDataTest.java
index ecab8a94..0c8ef7c8 100644
--- a/src/test/java/org/sqlite/DBMetaDataTest.java
+++ b/src/test/java/org/sqlite/DBMetaDataTest.java
@@ -35,7 +35,7 @@ public class DBMetaDataTest {
         conn = DriverManager.getConnection("jdbc:sqlite:");
         stat = conn.createStatement();
         stat.executeUpdate(
-                "create table test (id integer primary key, fn float default 0.0, sn not null, intvalue integer(5), realvalue real(8,3));");
+                "create table test (id integer primary key, fn float default 0.0, sn not null, intvalue integer(5), realvalue real(8,3), charvalue varchar(21));");
         stat.executeUpdate("create view testView as select * from test;");
         meta = conn.getMetaData();
     }
@@ -276,15 +276,32 @@ public class DBMetaDataTest {
         assertThat(rs.getString("IS_NULLABLE")).isEqualTo("YES");
         assertThat(rs.getString("COLUMN_DEF")).isNull();
         assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.INTEGER);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("INTEGER");
         assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(2000000000);
         assertThat(rs.getInt("DECIMAL_DIGITS")).isEqualTo(0);
         assertThat(rs.getString("IS_AUTOINCREMENT")).isEqualTo("NO");
+
+        // verify type of metadata columns
+        assertThat(rs.getObject("COLUMN_NAME")).isInstanceOf(String.class);
+        assertThat(rs.getObject("DATA_TYPE")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("TYPE_NAME")).isInstanceOf(String.class);
+        assertThat(rs.getObject("COLUMN_SIZE")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("DECIMAL_DIGITS")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("NUM_PREC_RADIX")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("NULLABLE")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("CHAR_OCTET_LENGTH")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("ORDINAL_POSITION")).isInstanceOf(Integer.class);
+        assertThat(rs.getObject("IS_NULLABLE")).isInstanceOf(String.class);
+        assertThat(rs.getObject("IS_AUTOINCREMENT")).isInstanceOf(String.class);
+        assertThat(rs.getObject("IS_GENERATEDCOLUMN")).isInstanceOf(String.class);
+
         assertThat(rs.next()).isFalse();
 
         rs = meta.getColumns(null, null, "test", "fn");
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("fn");
         assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.FLOAT);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("FLOAT");
         assertThat(rs.getString("IS_NULLABLE")).isEqualTo("YES");
         assertThat(rs.getString("COLUMN_DEF")).isEqualTo("0.0");
         assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(2000000000);
@@ -296,6 +313,8 @@ public class DBMetaDataTest {
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("sn");
         assertThat(rs.getString("IS_NULLABLE")).isEqualTo("NO");
+        assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.VARCHAR);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("");
         assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(2000000000);
         assertThat(rs.getInt("DECIMAL_DIGITS")).isEqualTo(10);
         assertThat(rs.getString("COLUMN_DEF")).isNull();
@@ -305,6 +324,7 @@ public class DBMetaDataTest {
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("intvalue");
         assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.INTEGER);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("INTEGER");
         assertThat(rs.getString("IS_NULLABLE")).isEqualTo("YES");
         assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(5);
         assertThat(rs.getInt("DECIMAL_DIGITS")).isEqualTo(0);
@@ -315,12 +335,24 @@ public class DBMetaDataTest {
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
         assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.FLOAT);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("REAL");
         assertThat(rs.getString("IS_NULLABLE")).isEqualTo("YES");
         assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(11);
         assertThat(rs.getInt("DECIMAL_DIGITS")).isEqualTo(3);
         assertThat(rs.getString("COLUMN_DEF")).isNull();
         assertThat(rs.next()).isFalse();
 
+        rs = meta.getColumns(null, null, "test", "charvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
+        assertThat(rs.getInt("DATA_TYPE")).isEqualTo(Types.VARCHAR);
+        assertThat(rs.getString("TYPE_NAME")).isEqualTo("VARCHAR");
+        assertThat(rs.getString("IS_NULLABLE")).isEqualTo("YES");
+        assertThat(rs.getInt("COLUMN_SIZE")).isEqualTo(21);
+        assertThat(rs.getInt("DECIMAL_DIGITS")).isEqualTo(0);
+        assertThat(rs.getString("COLUMN_DEF")).isNull();
+        assertThat(rs.next()).isFalse();
+
         rs = meta.getColumns(null, null, "test", "%");
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("id");
@@ -332,6 +364,8 @@ public class DBMetaDataTest {
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("intvalue");
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
         assertThat(rs.next()).isFalse();
 
         rs = meta.getColumns(null, null, "test", "%n");
@@ -358,6 +392,9 @@ public class DBMetaDataTest {
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("TABLE_NAME")).isEqualTo("test");
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("TABLE_NAME")).isEqualTo("test");
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
         // VIEW "testView"
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("TABLE_NAME")).isEqualTo("testView");
@@ -374,6 +411,9 @@ public class DBMetaDataTest {
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("TABLE_NAME")).isEqualTo("testView");
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("TABLE_NAME")).isEqualTo("testView");
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
         assertThat(rs.next()).isFalse();
 
         rs = meta.getColumns(null, null, "%", "%");
@@ -401,6 +441,8 @@ public class DBMetaDataTest {
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("intvalue");
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
         // VIEW "testView"
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("TABLE_NAME")).isEqualTo("testView");
@@ -413,6 +455,8 @@ public class DBMetaDataTest {
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("intvalue");
         assertThat(rs.next()).isTrue();
         assertThat(rs.getString("COLUMN_NAME")).isEqualTo("realvalue");
+        assertThat(rs.next()).isTrue();
+        assertThat(rs.getString("COLUMN_NAME")).isEqualTo("charvalue");
         assertThat(rs.next()).isFalse();
 
         rs = meta.getColumns(null, null, "doesnotexist", "%");
diff --git a/src/test/java/org/sqlite/ErrorMessageTest.java b/src/test/java/org/sqlite/ErrorMessageTest.java
index 50a307f7..a29b33ea 100644
--- a/src/test/java/org/sqlite/ErrorMessageTest.java
+++ b/src/test/java/org/sqlite/ErrorMessageTest.java
@@ -12,62 +12,60 @@ import java.sql.SQLException;
 import java.sql.Statement;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.condition.DisabledInNativeImage;
+import org.junit.jupiter.api.io.TempDir;
 import org.sqlite.core.DB;
 
 @DisabledInNativeImage // assertj Assumptions do not work in native-image tests
 public class ErrorMessageTest {
+    @TempDir File tempDir;
+
     @Test
     public void moved() throws SQLException, IOException {
-        File from = File.createTempFile("error-message-test-moved-from", ".sqlite");
-        from.deleteOnExit();
-
-        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + from.getAbsolutePath());
-        Statement stmt = conn.createStatement();
-        stmt.executeUpdate("create table sample(id, name)");
-        stmt.executeUpdate("insert into sample values(1, 'foo')");
-
-        File to = File.createTempFile("error-message-test-moved-from", ".sqlite");
-        assumeThat(to.delete()).isTrue();
-        assumeThat(from.renameTo(to)).isTrue();
-
-        assertThatThrownBy(() -> stmt.executeUpdate("insert into sample values(2, 'bar')"))
-                .isInstanceOf(SQLException.class)
-                .hasMessageStartingWith("[SQLITE_READONLY_DBMOVED]");
-
-        stmt.close();
-        conn.close();
+        File from = File.createTempFile("error-message-test-moved-from", ".sqlite", tempDir);
+
+        try (Connection conn =
+                        DriverManager.getConnection("jdbc:sqlite:" + from.getAbsolutePath());
+                Statement stmt = conn.createStatement()) {
+            stmt.executeUpdate("create table sample(id, name)");
+            stmt.executeUpdate("insert into sample values(1, 'foo')");
+
+            File to = File.createTempFile("error-message-test-moved-from", ".sqlite", tempDir);
+            assumeThat(to.delete()).isTrue();
+            assumeThat(from.renameTo(to)).isTrue();
+
+            assertThatThrownBy(() -> stmt.executeUpdate("insert into sample values(2, 'bar')"))
+                    .isInstanceOf(SQLException.class)
+                    .hasMessageStartingWith("[SQLITE_READONLY_DBMOVED]");
+        }
     }
 
     @Test
     public void writeProtected() throws SQLException, IOException {
-        File file = File.createTempFile("error-message-test-write-protected", ".sqlite");
-        file.deleteOnExit();
+        File file = File.createTempFile("error-message-test-write-protected", ".sqlite", tempDir);
 
-        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + file.getAbsolutePath());
-        Statement stmt = conn.createStatement();
-        stmt.executeUpdate("create table sample(id, name)");
-        stmt.executeUpdate("insert into sample values(1, 'foo')");
-        stmt.close();
-        conn.close();
+        try (Connection conn =
+                        DriverManager.getConnection("jdbc:sqlite:" + file.getAbsolutePath());
+                Statement stmt = conn.createStatement()) {
+            stmt.executeUpdate("create table sample(id, name)");
+            stmt.executeUpdate("insert into sample values(1, 'foo')");
+        }
 
         assumeThat(file.setReadOnly()).isTrue();
 
-        conn = DriverManager.getConnection("jdbc:sqlite:" + file.getAbsolutePath());
-        stmt = conn.createStatement();
-        Statement finalStmt = stmt;
-        assertThatThrownBy(() -> finalStmt.executeUpdate("insert into sample values(2, 'bar')"))
-                .isInstanceOf(SQLException.class)
-                .hasMessageStartingWith("[SQLITE_READONLY]");
-        stmt.close();
-        conn.close();
+        try (Connection conn =
+                        DriverManager.getConnection("jdbc:sqlite:" + file.getAbsolutePath());
+                Statement stmt = conn.createStatement()) {
+            assertThatThrownBy(() -> stmt.executeUpdate("insert into sample values(2, 'bar')"))
+                    .isInstanceOf(SQLException.class)
+                    .hasMessageStartingWith("[SQLITE_READONLY]");
+        }
     }
 
     @Test
     public void cantOpenDir() throws IOException {
-        File dir = File.createTempFile("error-message-test-cant-open-dir", "");
+        File dir = File.createTempFile("error-message-test-cant-open-dir", "", tempDir);
         assumeThat(dir.delete()).isTrue();
         assumeThat(dir.mkdir()).isTrue();
-        dir.deleteOnExit();
 
         assertThatThrownBy(
                         () -> DriverManager.getConnection("jdbc:sqlite:" + dir.getAbsolutePath()))
@@ -78,31 +76,29 @@ public class ErrorMessageTest {
     @Test
     public void shouldUsePlainErrorCodeAsVendorCodeAndExtendedAsResultCode()
             throws SQLException, IOException {
-        File from = File.createTempFile("error-message-test-plain-1", ".sqlite");
-        from.deleteOnExit();
-
-        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + from.getAbsolutePath());
-        Statement stmt = conn.createStatement();
-        stmt.executeUpdate("create table sample(id, name)");
-        stmt.executeUpdate("insert into sample values(1, 'foo')");
-
-        File to = File.createTempFile("error-message-test-plain-2", ".sqlite");
-        assumeThat(to.delete()).isTrue();
-        assumeThat(from.renameTo(to)).isTrue();
-
-        assertThatThrownBy(() -> stmt.executeUpdate("insert into sample values(2, 'bar')"))
-                .isInstanceOfSatisfying(
-                        SQLiteException.class,
-                        (ex) -> {
-                            assertThat(SQLiteErrorCode.getErrorCode(ex.getErrorCode()))
-                                    .isEqualTo(SQLiteErrorCode.SQLITE_READONLY);
-                            assertThat(ex.getResultCode())
-                                    .isEqualTo(SQLiteErrorCode.SQLITE_READONLY_DBMOVED);
-                        })
-                .hasMessageStartingWith("[SQLITE_READONLY_DBMOVED]");
-
-        stmt.close();
-        conn.close();
+        File from = File.createTempFile("error-message-test-plain-1", ".sqlite", tempDir);
+
+        try (Connection conn =
+                        DriverManager.getConnection("jdbc:sqlite:" + from.getAbsolutePath());
+                Statement stmt = conn.createStatement()) {
+            stmt.executeUpdate("create table sample(id, name)");
+            stmt.executeUpdate("insert into sample values(1, 'foo')");
+
+            File to = File.createTempFile("error-message-test-plain-2", ".sqlite", tempDir);
+            assumeThat(to.delete()).isTrue();
+            assumeThat(from.renameTo(to)).isTrue();
+
+            assertThatThrownBy(() -> stmt.executeUpdate("insert into sample values(2, 'bar')"))
+                    .isInstanceOfSatisfying(
+                            SQLiteException.class,
+                            (ex) -> {
+                                assertThat(SQLiteErrorCode.getErrorCode(ex.getErrorCode()))
+                                        .isEqualTo(SQLiteErrorCode.SQLITE_READONLY);
+                                assertThat(ex.getResultCode())
+                                        .isEqualTo(SQLiteErrorCode.SQLITE_READONLY_DBMOVED);
+                            })
+                    .hasMessageStartingWith("[SQLITE_READONLY_DBMOVED]");
+        }
     }
 
     @Test
diff --git a/src/test/java/org/sqlite/InsertQueryTest.java b/src/test/java/org/sqlite/InsertQueryTest.java
index 14ad3c49..a4b2ade4 100644
--- a/src/test/java/org/sqlite/InsertQueryTest.java
+++ b/src/test/java/org/sqlite/InsertQueryTest.java
@@ -10,24 +10,21 @@
 package org.sqlite;
 
 import java.io.File;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
 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.junit.jupiter.api.io.TempDir;
 
 public class InsertQueryTest {
     String dbName;
 
     @BeforeEach
-    public void setUp() throws Exception {
-        File tmpFile = File.createTempFile("tmp-sqlite", ".db");
-        tmpFile.deleteOnExit();
+    public void setUp(@TempDir File tempDir) throws Exception {
+        File tmpFile = File.createTempFile("tmp-sqlite", ".db", tempDir);
         dbName = tmpFile.getAbsolutePath();
     }
 
@@ -41,11 +38,21 @@ public class InsertQueryTest {
     }
 
     class IndependentConnectionFactory implements ConnectionFactory {
+        // Internal bookkeeping so connections are still closed in the end.
+        private final List<Connection> connections = new ArrayList<>();
+
         public Connection getConnection() throws SQLException {
-            return DriverManager.getConnection("jdbc:sqlite:" + dbName);
+            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
+            connections.add(conn);
+            return conn;
         }
 
-        public void dispose() throws SQLException {}
+        public void dispose() throws SQLException {
+            for (Connection connection : connections) {
+                connection.close();
+            }
+            connections.clear();
+        }
     }
 
     class SharedConnectionFactory implements ConnectionFactory {
diff --git a/src/test/java/org/sqlite/JDBCTest.java b/src/test/java/org/sqlite/JDBCTest.java
index ef2636f6..c873728e 100644
--- a/src/test/java/org/sqlite/JDBCTest.java
+++ b/src/test/java/org/sqlite/JDBCTest.java
@@ -18,6 +18,7 @@ import java.util.List;
 import java.util.Properties;
 import java.util.concurrent.atomic.AtomicInteger;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 
 public class JDBCTest {
     @Test
@@ -182,9 +183,9 @@ public class JDBCTest {
     void name() {}
 
     @Test
-    public void jdbcHammer() throws Exception {
+    public void jdbcHammer(@TempDir File tempDir) throws Exception {
         final SQLiteDataSource dataSource = createDatasourceWithExplicitReadonly();
-        File tempFile = File.createTempFile("myTestDB", ".db");
+        File tempFile = File.createTempFile("myTestDB", ".db", tempDir);
         dataSource.setUrl("jdbc:sqlite:" + tempFile.getAbsolutePath());
         try (Connection connection = dataSource.getConnection()) {
             connection.setAutoCommit(false);
diff --git a/src/test/java/org/sqlite/ListenerTest.java b/src/test/java/org/sqlite/ListenerTest.java
index cf06ba8b..9a818f94 100644
--- a/src/test/java/org/sqlite/ListenerTest.java
+++ b/src/test/java/org/sqlite/ListenerTest.java
@@ -11,8 +11,10 @@ import java.util.List;
 import java.util.concurrent.BlockingQueue;
 import java.util.concurrent.LinkedBlockingDeque;
 import java.util.concurrent.atomic.AtomicInteger;
+import org.junit.jupiter.api.AfterEach;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 import org.sqlite.core.DB;
 import org.sqlite.core.NativeDBHelper;
 
@@ -21,9 +23,8 @@ public class ListenerTest {
     private SQLiteConnection connectionOne, connectionTwo;
 
     @BeforeEach
-    public void connect() throws Exception {
-        File tmpFile = File.createTempFile("test-listeners", ".db");
-        tmpFile.deleteOnExit();
+    public void connect(@TempDir File tempDir) throws Exception {
+        File tmpFile = File.createTempFile("test-listeners", ".db", tempDir);
 
         connectionOne =
                 (SQLiteConnection)
@@ -37,6 +38,12 @@ public class ListenerTest {
                 "CREATE TABLE IF NOT EXISTS sample (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT);");
     }
 
+    @AfterEach
+    public void close() throws Exception {
+        connectionOne.close();
+        connectionTwo.close();
+    }
+
     @Test
     public void testSetAndRemoveUpdateHook() throws Exception {
         final List<UpdateEvent> updates = new LinkedList<>();
diff --git a/src/test/java/org/sqlite/SQLiteJDBCLoaderTest.java b/src/test/java/org/sqlite/SQLiteJDBCLoaderTest.java
index bb478520..64ae3d13 100644
--- a/src/test/java/org/sqlite/SQLiteJDBCLoaderTest.java
+++ b/src/test/java/org/sqlite/SQLiteJDBCLoaderTest.java
@@ -27,11 +27,8 @@ package org.sqlite;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.assertThatNoException;
 
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.nio.file.Path;
+import java.sql.*;
 import java.util.concurrent.ExecutorService;
 import java.util.concurrent.Executors;
 import java.util.concurrent.TimeUnit;
@@ -39,6 +36,7 @@ import java.util.concurrent.atomic.AtomicInteger;
 import org.junit.jupiter.api.AfterEach;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 
 public class SQLiteJDBCLoaderTest {
 
@@ -110,11 +108,11 @@ public class SQLiteJDBCLoaderTest {
     }
 
     @Test
-    public void test() throws Throwable {
+    public void test(@TempDir Path tmpDir) throws Throwable {
         final AtomicInteger completedThreads = new AtomicInteger(0);
         ExecutorService pool = Executors.newFixedThreadPool(32);
         for (int i = 0; i < 32; i++) {
-            final String connStr = "jdbc:sqlite:target/sample-" + i + ".db";
+            final String connStr = "jdbc:sqlite:" + tmpDir.resolve("sample-" + i + ".db");
             final int sleepMillis = i;
             pool.execute(
                     () -> {
@@ -129,6 +127,7 @@ public class SQLiteJDBCLoaderTest {
                                             // works.
                                             // synchronized (TestSqlite.class) {
                                             Connection conn = DriverManager.getConnection(connStr);
+                                            conn.close();
                                             // }
                                         });
                         completedThreads.incrementAndGet();
diff --git a/src/test/java/org/sqlite/SavepointTest.java b/src/test/java/org/sqlite/SavepointTest.java
index 32e1ad57..a052b096 100644
--- a/src/test/java/org/sqlite/SavepointTest.java
+++ b/src/test/java/org/sqlite/SavepointTest.java
@@ -15,6 +15,7 @@ import org.junit.jupiter.api.AfterEach;
 import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 
 /**
  * These tests assume that Statements and PreparedStatements are working as per normal and test the
@@ -34,9 +35,8 @@ public class SavepointTest {
     }
 
     @BeforeEach
-    public void connect() throws Exception {
-        File tmpFile = File.createTempFile("test-trans", ".db");
-        // tmpFile.deleteOnExit();
+    public void connect(@TempDir File tempDir) throws Exception {
+        File tmpFile = File.createTempFile("test-trans", ".db", tempDir);
 
         Properties prop = new Properties();
         prop.setProperty("shared_cache", "false");
diff --git a/src/test/java/org/sqlite/TransactionTest.java b/src/test/java/org/sqlite/TransactionTest.java
index 84b7399c..1489b0d7 100644
--- a/src/test/java/org/sqlite/TransactionTest.java
+++ b/src/test/java/org/sqlite/TransactionTest.java
@@ -4,12 +4,7 @@ import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
 
 import java.io.File;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
+import java.sql.*;
 import java.util.HashSet;
 import java.util.Properties;
 import java.util.Set;
@@ -17,6 +12,7 @@ import org.junit.jupiter.api.AfterEach;
 import org.junit.jupiter.api.BeforeAll;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.io.TempDir;
 import org.sqlite.SQLiteConfig.TransactionMode;
 
 /**
@@ -39,9 +35,8 @@ public class TransactionTest {
     }
 
     @BeforeEach
-    public void connect() throws Exception {
-        File tmpFile = File.createTempFile("test-trans", ".db");
-        // tmpFile.deleteOnExit();
+    public void connect(@TempDir File tempDir) throws Exception {
+        File tmpFile = File.createTempFile("test-trans", ".db", tempDir);
 
         Properties prop = new Properties();
         prop.setProperty("shared_cache", "false");
@@ -339,55 +334,59 @@ public class TransactionTest {
     }
 
     @Test
-    public void transactionModes() throws Exception {
-        File tmpFile = File.createTempFile("test-trans", ".db");
+    public void transactionModes(@TempDir File tempDir) throws Exception {
+        File tmpFile = File.createTempFile("test-trans", ".db", tempDir);
 
         SQLiteDataSource ds = new SQLiteDataSource();
         ds.setUrl("jdbc:sqlite:" + tmpFile.getAbsolutePath());
 
         // deferred
-        SQLiteConnection con = (SQLiteConnection) ds.getConnection();
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.DEFERRED);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
-        runUpdates(con, "tbl1");
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.DEFERRED);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+            runUpdates(con, "tbl1");
+        }
 
         ds.setTransactionMode(TransactionMode.DEFERRED.name());
-        con = (SQLiteConnection) ds.getConnection();
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.DEFERRED);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.DEFERRED);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        }
 
         // Misspelled deferred should be accepted for backwards compatibility
         ds.setTransactionMode("DEFFERED");
-        con = (SQLiteConnection) ds.getConnection();
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.DEFERRED);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.DEFERRED);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        }
 
-        con = (SQLiteConnection) ds.getConnection();
-        con.getConnectionConfig().setTransactionMode(TransactionMode.valueOf("DEFFERED"));
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.DEFERRED);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            con.getConnectionConfig().setTransactionMode(TransactionMode.valueOf("DEFFERED"));
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.DEFERRED);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin;");
+        }
 
         // immediate
         ds.setTransactionMode(TransactionMode.IMMEDIATE.name());
-        con = (SQLiteConnection) ds.getConnection();
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.IMMEDIATE);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin immediate;");
-        runUpdates(con, "tbl2");
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.IMMEDIATE);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin immediate;");
+            runUpdates(con, "tbl2");
+        }
 
         // exclusive
         ds.setTransactionMode(TransactionMode.EXCLUSIVE.name());
-        con = (SQLiteConnection) ds.getConnection();
-        assertThat(con.getConnectionConfig().getTransactionMode())
-                .isEqualTo(TransactionMode.EXCLUSIVE);
-        assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin exclusive;");
-        runUpdates(con, "tbl3");
-
-        tmpFile.delete();
+        try (SQLiteConnection con = (SQLiteConnection) ds.getConnection()) {
+            assertThat(con.getConnectionConfig().getTransactionMode())
+                    .isEqualTo(TransactionMode.EXCLUSIVE);
+            assertThat(con.getConnectionConfig().transactionPrefix()).isEqualTo("begin exclusive;");
+            runUpdates(con, "tbl3");
+        }
     }
 
     public void runUpdates(Connection con, String table) throws SQLException {