Difference between using Sqliteconnection and Java Drivermanager

Question

I am connecting to the Sqlite database with the following code:

 import org.sqlite.SQLiteConnection;


 public SQLiteConnection conn() throws SQLException{
    String path = System.getProperty("user.dir");
  try {
   return new SQLiteConnection(path, "DadosPastas.s3db");
 } catch (Exception e) {
    return null;
 }
}

When I debug the IDE the result is that the connection has been successfully established.

But I have observed several examples as follows:

import java.sql.*;

public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;

try {
 Class.forName("org.sqlite.JDBC");
 c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
 System.err.println( e.getClass().getName() + ": " + e.getMessage() );
 System.exit(0);
}
 System.out.println("Opened database successfully");
 }
}

My question is what is the difference in technical and performance aspects between using Sqliteconnection directly or using Drivermanager.

Link to the Sqlite library.

Answer 1

I don’t know what you mean by "technical aspects", but in relation to performance, it is notorious that JDBC is faster. I made an algorithm for testing:

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.sqlite.SQLiteConnection;

public class SQLiteConnTest {


    public static void main(String[] args) {

        long startTime, endTime;
        double duration;

        File path = new File(System.getProperty("user.dir"));
        System.out.println("Conexao com SQLiteConnection...");

        startTime = System.nanoTime();
        sqliteConnection(path);
        endTime = System.nanoTime();

        duration = (endTime - startTime) / 1000000d;

        System.out.println("Tempo(ms): " + String.format("%.3f", duration));

        System.out.println("Conexao com JDBC...");

        startTime = System.nanoTime();
        jdbcConnection(path);
        endTime = System.nanoTime();

        duration = (endTime - startTime) / 1000000d;

        System.out.println("Tempo(ms): " + String.format("%.3f", duration));

    }

    public static void sqliteConnection(File path) {

        try {
            SQLiteConnection conn = new SQLiteConnection(path.getAbsolutePath(), "DadosPastas.s3db");
            System.out.println("SQLiteConnection has opened");
            conn.close();
            System.out.println("SQLiteConnection has closed");

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public static void jdbcConnection(File path) {

        try {

            Class.forName("org.sqlite.JDBC");
            Connection c = DriverManager.getConnection("jdbc:sqlite:" + path + "/test.db");
            System.out.println("JDBC Connection has opened");
            c.close();
            System.out.println("JDBC Connection has closed");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

In the test, I calculate the time it takes both ways to open and close the connection then.

In the first test, I executed without any of the banks being created, to see if the fact of having to create it influences the time. The exit was:

Conexao com SQLiteConnection...
SQLiteConnection has opened
SQLiteConnection has closed
Tempo(ms): 227,829
Conexao com JDBC...
JDBC Connection has opened
JDBC Connection has closed
Tempo(ms): 3,559

Once the banks were created, I executed again:

Conexao com SQLiteConnection...
SQLiteConnection has opened
SQLiteConnection has closed
Tempo(ms): 249,077
Conexao com JDBC...
JDBC Connection has opened
JDBC Connection has closed
Tempo(ms): 3,039

It can be seen that the time difference between the two is great, and the JDBC is much more performatic than the class SQLiteConnection. This is because jdbc is native, which will make it almost always faster.

I don’t know where you found this way to connect with this class, but even the driver documentation itself suggests use Drivermanager, and doesn’t say anything about the way you tested it.