Using SQLite with Java

13 Oct 2023

In this blog post, we will explore how to use SQLite with Java to create, query, and manipulate databases. SQLite is a lightweight, file-based database management system that provides a simple and efficient way to store and retrieve data.

What is SQLite?
Setting up SQLite with Java
Creating a Database
Creating Tables
Inserting Data
Querying Data
Updating and Deleting Data
Conclusion
References

1. What is SQLite?

SQLite is a C library that provides a self-contained, serverless, zero-configuration, transactional SQL database engine. It is widely used as an embedded database in various applications due to its simplicity, lightness, and reliability. SQLite databases are stored as single files, making them easy to manage and transport.

2. Setting up SQLite with Java

To use SQLite with Java, you need to download the SQLite JDBC driver, which provides connectivity between Java applications and SQLite databases. You can find the driver on the SQLite JDBC website (https://github.com/xerial/sqlite-jdbc).

Once you have downloaded the SQLite JDBC JAR file, you need to add it to your Java project’s classpath. You can do this by either manually adding the JAR file to your project’s library folder or using a build tool like Maven or Gradle to manage your dependencies.

3. Creating a Database

To create a SQLite database in Java, you need to establish a connection to the database file using the JDBC driver. Here’s an example:

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

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:/path/to/database.db");
            // Database operations go here
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

In the above code, we establish a connection to a SQLite database using the DriverManager.getConnection() method and a JDBC URL in the format "jdbc:sqlite:/path/to/database.db". Replace /path/to/database.db with the actual path to your SQLite database file.

4. Creating Tables

Once you have established a connection to the SQLite database, you can create tables to store your data. Here’s an example:

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

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:/path/to/database.db");
            Statement statement = connection.createStatement();
            
            String createTableSql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)";
            statement.executeUpdate(createTableSql);
            
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

In the above code, we create a Statement object from the Connection and execute a SQL statement to create a table named “users” with three columns: “id” (INTEGER), “name” (TEXT), and “age” (INTEGER). The IF NOT EXISTS clause ensures that the table is only created if it does not exist already.

5. Inserting Data

To insert data into a SQLite table from a Java application, you can use the PreparedStatement object. Here’s an example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:/path/to/database.db");
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO users (name, age) VALUES (?, ?)");
            
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setInt(2, 30);
            preparedStatement.executeUpdate();
            
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

In the above code, we create a PreparedStatement object from the Connection and execute a parameterized SQL statement to insert a new row into the “users” table. We use the setString() and setInt() methods to set the values of the two placeholders (?). Finally, we call executeUpdate() to execute the insert statement.

6. Querying Data

To query data from a SQLite table in Java, you can use the Statement object or the PreparedStatement object. Here’s an example using the Statement object:

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

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:/path/to/database.db");
            Statement statement = connection.createStatement();
            
            String selectQuery = "SELECT * FROM users";
            ResultSet resultSet = statement.executeQuery(selectQuery);
            
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
            
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

In the above code, we execute a SQL SELECT statement using the executeQuery() method of the Statement object. We retrieve the results using the ResultSet object and iterate over them using the next() method. For each row, we retrieve the values of the “id”, “name”, and “age” columns using the respective getter methods (getInt(), getString()). Finally, we print the retrieved values.

7. Updating and Deleting Data

To update or delete data in a SQLite table from a Java application, you can use the Statement object or the PreparedStatement object with appropriate SQL statements. Here’s an example:

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

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:/path/to/database.db");
            Statement statement = connection.createStatement();
            
            // Update data
            String updateSql = "UPDATE users SET age = 35 WHERE id = 1";
            statement.executeUpdate(updateSql);
            
            // Delete data
            String deleteSql = "DELETE FROM users WHERE id = 2";
            statement.executeUpdate(deleteSql);
            
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

In the above code, we execute the SQL statements UPDATE and DELETE using the executeUpdate() method of the Statement object. The UPDATE statement modifies the age of a user with id 1, and the DELETE statement removes the user with id 2 from the table.

8. Conclusion

In this blog post, we have covered the basics of using SQLite with Java. We learned how to set up SQLite with Java, create databases, create tables, insert data, query data, and update/delete data. SQLite provides a simple and lightweight solution for managing databases in Java applications.

9. References

SQLite: https://www.sqlite.org/
SQLite JDBC Driver: https://github.com/xerial/sqlite-jdbc

Table of Contents