Lesson 23 of 25

JDBC and SQLite

Connect Java to SQLite, create tables, insert records safely with prepared statements, and read query results.

Learning objectives

• Explain the roles of JDBC, a JDBC driver, Connection, PreparedStatement, and ResultSet.
• Add the SQLite JDBC driver to a project.
• Open a connection to a local SQLite database.
• Create a table if it does not already exist.
• Insert parameters safely with PreparedStatement.
• Read records with ResultSet.
• Close database resources automatically with try-with-resources.

1. What is JDBC?

JDBC (Java Database Connectivity) is the standard Java API for relational database access. JDBC defines interfaces such as Connection, Statement, PreparedStatement, and ResultSet. A database-specific driver implements those interfaces.

2. Add the SQLite JDBC driver

The Xerial SQLite JDBC driver packages SQLite support for Java. The current release used in this tutorial is 3.53.1.0.

Maven dependency

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.53.1.0</version>
</dependency>

If you are not using Maven or Gradle, download the normal sqlite-jdbc-3.53.1.0.jar file—not the Javadoc or sources JAR—and add it to the project's libraries/classpath.

3. Opening a database connection

A SQLite JDBC URL begins with jdbc:sqlite:. If the database file does not exist, SQLite can create it when the connection is opened.

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

public class DatabaseHelper {

    private static final String URL =
        "jdbc:sqlite:student_management.db";

    public static Connection getConnection()
            throws SQLException {
        return DriverManager.getConnection(URL);
    }
}

Keeping the connection string in one helper class avoids repeating it throughout the application.

4. Create a table

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

public class DatabaseSetup {

    public static void createStudentsTable() {
        String sql = """
            CREATE TABLE IF NOT EXISTS students (
                student_id TEXT PRIMARY KEY,
                name       TEXT NOT NULL,
                course     TEXT NOT NULL,
                mark       INTEGER NOT NULL
                    CHECK(mark BETWEEN 0 AND 100)
            )
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             Statement statement =
                 connection.createStatement()) {

            statement.execute(sql);
            System.out.println("Students table is ready.");

        } catch (SQLException e) {
            System.out.println(
                "Database setup error: " + e.getMessage()
            );
        }
    }

    public static void main(String[] args) {
        createStudentsTable();
    }
}

5. Insert data with PreparedStatement

Do not build SQL by concatenating user input. A PreparedStatement separates SQL structure from values and handles data types correctly.

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

public class StudentInsert {

    public static void addStudent(
            String id,
            String name,
            String course,
            int mark) {

        String sql = """
            INSERT INTO students
                (student_id, name, course, mark)
            VALUES (?, ?, ?, ?)
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql)) {

            statement.setString(1, id);
            statement.setString(2, name);
            statement.setString(3, course);
            statement.setInt(4, mark);

            int rows = statement.executeUpdate();

            System.out.println(
                rows + " student record inserted."
            );

        } catch (SQLException e) {
            System.out.println(
                "Insert error: " + e.getMessage()
            );
        }
    }
}

6. Read records with ResultSet

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

public class StudentList {

    public static void displayStudents() {
        String sql = """
            SELECT student_id, name, course, mark
            FROM students
            ORDER BY name
            """;

        try (Connection connection =
                 DatabaseHelper.getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql);
             ResultSet resultSet =
                 statement.executeQuery()) {

            while (resultSet.next()) {
                System.out.printf(
                    "%s | %s | %s | %d%n",
                    resultSet.getString("student_id"),
                    resultSet.getString("name"),
                    resultSet.getString("course"),
                    resultSet.getInt("mark")
                );
            }

        } catch (SQLException e) {
            System.out.println(
                "Query error: " + e.getMessage()
            );
        }
    }
}

7. Why try-with-resources matters

Connection, PreparedStatement, and ResultSet are resources that should be closed. Try-with-resources closes them automatically, even when an exception occurs.

Mini project: database connection checker

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

public class DatabaseCheck {
    public static void main(String[] args) {
        try (Connection connection =
                 DatabaseHelper.getConnection()) {

            DatabaseMetaData meta =
                connection.getMetaData();

            System.out.println("Connected successfully.");
            System.out.println(
                "Driver: " + meta.getDriverName()
            );
            System.out.println(
                "Database URL: " + meta.getURL()
            );

        } catch (SQLException e) {
            System.out.println(
                "Connection failed: " + e.getMessage()
            );
        }
    }
}

Common mistakes

Summary

You now understand the JDBC workflow: open a connection, prepare SQL, supply values, execute it, process results, and close resources. Lesson 24 applies this foundation to a complete student CRUD database mini project.