SQLite CRUD Tutorials in Java: A Step-by-Step Guide

SQLite is a lightweight and embedded relational database management system (RDBMS) that is widely used in mobile and desktop applications due to its simplicity and portability. In this tutorial, we will guide you through the process of performing CRUD (Create, Read, Update, Delete) operations in SQLite using Java. We’ll provide step-by-step instructions and practical examples with detailed explanations.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

Java Development Kit (JDK): Ensure you have Java installed on your system. You can download it from the official Oracle website or use an open-source alternative like OpenJDK.
Integrated Development Environment (IDE): Choose an IDE like Eclipse, IntelliJ IDEA, or NetBeans to write and manage your Java code.
SQLite: SQLite is a serverless database, and you can typically use the SQLite library that comes with Java. No additional installation is required.

Step 1: Create a SQLite Database

Let’s start by creating a SQLite database where we will perform CRUD operations. In SQLite, a database is just a file. You can create a database by connecting to it, and if it doesn’t exist, SQLite will create it for you.

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

public class SQLiteCRUDExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydb.db"; // URL to the SQLite database file

        try {
            Connection connection = DriverManager.getConnection(url);

            if (connection != null) {
                Statement statement = connection.createStatement();

                // Create a table called "employees"
                String createTableSQL = "CREATE TABLE IF NOT EXISTS employees (id INTEGER PRIMARY KEY, " +
                        "first_name TEXT, last_name TEXT, age INTEGER)";
                statement.execute(createTableSQL);

                statement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code connects to an SQLite database file named mydb.db and creates a table called employees if it doesn’t already exist.

Step 2: Set Up a Java Project

Open your preferred IDE and create a new Java project.
Since SQLite is a part of the Java standard library, you don’t need to add any additional dependencies.

Step 3: Perform CRUD Operations

Create Operation (Insert)

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

public class SQLiteCRUDExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydb.db";

        try {
            Connection connection = DriverManager.getConnection(url);

            if (connection != null) {
                String insertSQL = "INSERT INTO employees (first_name, last_name, age) VALUES (?, ?, ?)";
                PreparedStatement preparedStatement = connection.prepareStatement(insertSQL);
                preparedStatement.setString(1, "John");
                preparedStatement.setString(2, "Doe");
                preparedStatement.setInt(3, 30);
                preparedStatement.executeUpdate();

                preparedStatement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code inserts a new employee record into the employees table in the SQLite database.

Read Operation (Select)

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

public class SQLiteCRUDExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydb.db";

        try {
            Connection connection = DriverManager.getConnection(url);

            if (connection != null) {
                String selectSQL = "SELECT * FROM employees";
                PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
                ResultSet resultSet = preparedStatement.executeQuery();

                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String firstName = resultSet.getString("first_name");
                    String lastName = resultSet.getString("last_name");
                    int age = resultSet.getInt("age");

                    System.out.println("ID: " + id + ", Name: " + firstName + " " + lastName + ", Age: " + age);
                }

                resultSet.close();
                preparedStatement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code retrieves and prints all employee records from the employees table.

Update Operation (Update)

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

public class SQLiteCRUDExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydb.db";

        try {
            Connection connection = DriverManager.getConnection(url);

            if (connection != null) {
                String updateSQL = "UPDATE employees SET age = ? WHERE first_name = ? AND last_name = ?";
                PreparedStatement preparedStatement = connection.prepareStatement(updateSQL);
                preparedStatement.setInt(1, 31);
                preparedStatement.setString(2, "John");
                preparedStatement.setString(3, "Doe");
                preparedStatement.executeUpdate();

                preparedStatement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code updates the age of the employee named “John Doe” in the employees table.

Delete Operation (Delete)

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

public class SQLiteCRUDExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydb.db";

        try {
            Connection connection = DriverManager.getConnection(url);

            if (connection != null) {
                String deleteSQL = "DELETE FROM employees WHERE first_name = ? AND last_name = ?";
                PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL);
                preparedStatement.setString(1, "John");
                preparedStatement.setString(2, "Doe");
                preparedStatement.executeUpdate();

                preparedStatement.close();
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code deletes the employee named “John Doe” from the employees table.

Conclusion

In this tutorial, you’ve learned how to perform CRUD operations in SQLite using Java. You created an SQLite database, set up a Java project, and wrote code for Create, Read, Update, and Delete operations with detailed explanations. SQLite’s simplicity and portability make it a popular choice for embedded databases in mobile and desktop applications. Mastering these fundamental database operations will serve as a solid foundation for building data-driven applications in the Java ecosystem. As you continue your journey in Java development and SQLite, you can expand on these concepts and build more complex database-driven applications. Happy coding!