How to Connect to SQLite with Java

We are often developing applications where the complexity of several other databases can influence practicality and performance.

Not to mention that saving data to simple files can be insecure and/or more complicated.

The good thing about this is that there is SQLite, which is simple, fast and secure. Here on the blog there is almost a series (if we can call it that) that shows similar procedures to this one in other programming languages.

Today we are going to see how to do this with Java, one of the most powerful languages when it comes to portability.

Let’s go step by step on how to connect Java to SQLite!

1. Create a Java file, example `vim Base.java`

public class Base {
  public static void main(String[] args) {
  }
}

2. Add the necessary packages:

The names are very intuitive so there’s no need to explain. When in doubt use: import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

3. Create an instance of the `Connection` class and set it to `null`:

Connection connection = null;

4. Create an exception to connect to the database and run the commands you want:

If it doesn’t already exist, it will create the base.db file

try {
  // Create the connection to the database
  connection = DriverManager.getConnection("jdbc:sqlite:base.db");
  Statement statement = connection.createStatement();
  statement.setQueryTimeout(30); // Wait only 30 seconds to connect

  // Run commands for SQLite
  statement.executeUpdate("DROP TABLE IF EXISTS terminalroot");
  statement.executeUpdate("CREATE TABLE terminalroot (id INTEGER, name STRING)");
  statement.executeUpdate("INSERT INTO terminalroot VALUES(1, 'Marcos Oliveira')");
  statement.executeUpdate("INSERT INTO terminalroot VALUES(2, 'James Gosling')");
  ResultSet rs = statement.executeQuery("SELECT * FROM terminalroot");
  while(rs.next()) {
    // Read the entered data
    System.out.println("NAME : " + rs.getString("name"));
    System.out.println("ID : " + rs.getInt("id"));
  }
} catch(SQLException e) {
  // If the error message is: "out of memory",
  // Probably error creating(permission) or database path
  System.err.println(e.getMessage());
}

5. Use `finally` to perform an action even if there is an error after the exception

To close the file or detail the previous error

finally {
  try {
    if(connection != null){
      connection.close();
    }
  } catch(SQLException e) {
    // Also failed to close the file
    System.err.println(e.getMessage());
  }
}

The complete Base.java file is this one:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Base {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Create the connection to the database
      connection = DriverManager.getConnection("jdbc:sqlite:base.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30); // Wait only 30 seconds to connect

      // Run commands for SQLite
      statement.executeUpdate("DROP TABLE IF EXISTS terminalroot");
      statement.executeUpdate("CREATE TABLE terminalroot (id INTEGER, name STRING)");
      statement.executeUpdate("INSERT INTO terminalroot VALUES(1, 'Marcos Oliveira')");
      statement.executeUpdate("INSERT INTO terminalroot VALUES(2, 'James Gosling')");
      ResultSet rs = statement.executeQuery("SELECT * FROM terminalroot");
      while(rs.next()) {
        // Read the entered data
        System.out.println("NAME : " + rs.getString("name"));
        System.out.println("ID : " + rs.getInt("id"));
      }
    } catch(SQLException e) {
      // If the error message is: "out of memory",
      // Probably error creating(permission) or database path
      System.err.println(e.getMessage());
    }

    finally {
      try {
        if(connection != null){
          connection.close();
        }
      } catch(SQLException e) {
        // Also failed to close the file
        System.err.println(e.getMessage());
      }
    }

  }
}

6. Now download SQLite JDBC here

Example with wget for GNU/Linux:

wget https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar

Example with PowerShell for MS/Windows:

1
Invoke-WebRequest -Uri "https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar"

Example with cURL, for [BSD] (https://terminalroot.com/tags#bsd)/macOS:

curl -LO https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar

Use the -LO parameter, GitHub has redirection.

7. Compile your code and after compiling, run it with the `-classpath` parameter and enter the sqlite-jdbc-[version].jar and the class name:

If it’s GNU/Linux/BSD/macOS, use .:

javac Base.java
java -classpath ".:sqlite-jdbc-3.36.0.3.jar" Base

If it’s Microsoft Windows, use .;

javac Base.java
java -classpath ".;sqlite-jdbc-3.36.0.3.jar" Base

Possible and probable exit:

NAME: Marcos Oliveira
ID: 1
NAME: James Gosling
ID: 2

For more information visit: https://github.com/xerial/sqlite-jdbc .

java sqlite

YouTube channel