Spring Boot Connecting with SQLITE

Lesson 2: Using a database with SQLite in Spring Boot

In this lesson, we will start from absolute ground level. You will learn what a database is, what SQLite is, how Spring Boot connects to it, which dependency goes in which file, where to place configuration files, where to write SQL files, and how to build a small working CRUD application using Spring JDBC + JdbcTemplate + SQLite.

This lesson also includes complete examples, SVG diagrams, copy buttons, speak tags, and a reusable narration script link.

Welcome to lesson two. In this lesson, you will learn how to use a real database in Spring Boot with SQLite, starting from the absolute beginning.

SQLite Spring Boot JdbcTemplate schema.sql data.sql CRUD Copy Buttons Speak Tags

Ground Level Dependencies Project Files CRUD Example Testing

1. What is a database?

A database is a place where your program stores structured data. Instead of keeping everything only in Java variables, you can save it permanently.

• A table is like a sheet.
• A row is one record.
• A column is one field.

A database stores information in an organized way. Tables contain rows and columns. Rows are records, and columns describe each part of the record.

Example table:
students

Columns:
id, name, age, course

One row:
1, Aman, 21, BCA

Use:
store, read, update, delete

This is the core database idea: one table, many rows, and fixed columns.

2. What is SQLite?

SQLite is a lightweight SQL database. It stores data in a local file instead of needing a separate database server.

This makes SQLite very good for:

• learning
• small projects
• desktop apps
• prototype Spring Boot apps

SQLite is a simple database that stores your data inside a file. It is very useful when you want an easy starting point without setting up a database server.

Spring Boot talks to SQLite through a JDBC driver, and SQLite stores data in a database file.

3. Why use JdbcTemplate first?

For the first real database lesson, JdbcTemplate is a very good choice because it keeps things simple.

• You write clear SQL yourself.
• You learn what the database is actually doing.
• You understand tables, inserts, selects, updates, and deletes.

Later, you can learn JPA and repositories. But at ground level, JDBC is easier for understanding the real flow.

We are starting with JdbcTemplate because it is easier to understand what is happening. You write SQL directly and connect Java code to database operations clearly.

4. Which dependencies do you need?

For this lesson, you need:

• spring-boot-starter-web for HTTP endpoints
• spring-boot-starter-jdbc for JDBC support
• org.xerial:sqlite-jdbc for SQLite itself

For this lesson, you need Spring Web, Spring JDBC, and the SQLite JDBC driver. Each dependency has a clear role.

Dependency	Why it is needed
spring-boot-starter-web	Create REST endpoints like GET, POST, PUT, DELETE
spring-boot-starter-jdbc	Use DataSource and JdbcTemplate
sqlite-jdbc	Connect Spring Boot to SQLite files

4.1 If your project uses Maven

Which file?

Add these inside the <dependencies> section of your pom.xml file.

pom.xml — dependencies section

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

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

4.2 If your project uses Gradle

Which file?

Add these inside the dependencies { } block of your build.gradle file.

build.gradle — dependencies block

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    implementation 'org.xerial:sqlite-jdbc:3.51.3.0'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

Important: add the dependency exactly inside the dependencies section, not outside the file and not inside the plugins section.

5. Which files do you need in the project?

A clean beginner project can use the following structure:

Project structure

src/
  main/
    java/
      live/
        learnwithchampak/
          demo/
            DemoApplication.java
            config/
              SQLiteConfig.java
            controller/
              StudentController.java
            dao/
              StudentDao.java
            model/
              Student.java
    resources/
      application.properties
      schema.sql
      data.sql

A clean beginner project needs Java files for the application, model, data access, and controller, plus resource files for application properties and SQL scripts.

What each file does

File	Purpose
DemoApplication.java	Starts the Spring Boot app
SQLiteConfig.java	Creates the SQLite DataSource and enables foreign keys
Student.java	Java model class
StudentDao.java	Runs SQL with JdbcTemplate
StudentController.java	Exposes HTTP endpoints
application.properties	Spring Boot configuration file
schema.sql	Creates tables
data.sql	Inserts sample data

6. application.properties — where and what?

Which file and where?

Create this file at: src/main/resources/application.properties

For this SQLite lesson, we will add a few simple properties.

application.properties

spring.application.name=demo
server.port=8080
spring.sql.init.mode=always

Why these?
spring.application.name gives your app a name.
server.port=8080 keeps the default learning port.
spring.sql.init.mode=always makes Spring run SQL initialization scripts.

The application properties file belongs in src main resources. In this lesson, it gives the app a name, sets the port, and tells Spring Boot to run SQL initialization scripts.

7. schema.sql — create the table

Which file and where?

Create this file at: src/main/resources/schema.sql

This file creates the table when the app starts.

schema.sql

DROP TABLE IF EXISTS students;

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    course TEXT NOT NULL
);

The schema dot sql file creates the database table. It belongs in src main resources and runs when the application starts.

8. data.sql — insert sample data

Which file and where?

Create this file at: src/main/resources/data.sql

This file inserts sample data into the students table.

data.sql

INSERT INTO students (name, age, course) VALUES ('Aman', 21, 'BCA');
INSERT INTO students (name, age, course) VALUES ('Riya', 20, 'MCA');
INSERT INTO students (name, age, course) VALUES ('Vikash', 22, 'B.Tech');

The data dot sql file inserts sample rows into the table. This lets you test reads immediately after the app starts.

9. The main application class

Which file and where?

Create this file at: src/main/java/live/learnwithchampak/demo/DemoApplication.java

DemoApplication.java

package live.learnwithchampak.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

The main application class starts the Spring Boot program. Every Spring Boot project needs this entry point.

10. SQLite configuration class

Which file and where?

Create this file at: src/main/java/live/learnwithchampak/demo/config/SQLiteConfig.java

This class creates the SQLite connection and turns foreign-key checking on.

SQLite foreign-key enforcement is not automatically guaranteed in every connection flow. So it is a good learning habit to enable it explicitly.

SQLiteConfig.java

package live.learnwithchampak.demo.config;

import org.sqlite.SQLiteDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class SQLiteConfig {

    @Bean
    public DataSource dataSource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        dataSource.setUrl("jdbc:sqlite:school.db");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute("PRAGMA foreign_keys = ON");
        return jdbcTemplate;
    }
}

The SQLite configuration class creates the database connection and also enables foreign key support with a pragma statement.

11. The Student model class

Which file and where?

Create this file at: src/main/java/live/learnwithchampak/demo/model/Student.java

Student.java

package live.learnwithchampak.demo.model;

public class Student {
    private Integer id;
    private String name;
    private Integer age;
    private String course;

    public Student() {
    }

    public Student(Integer id, String name, Integer age, String course) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.course = course;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}

The Student model class is a normal Java class that represents one row from the students table.

12. The DAO class — real database work

Which file and where?

Create this file at: src/main/java/live/learnwithchampak/demo/dao/StudentDao.java

This is the class that actually talks to the database using SQL.

StudentDao.java

package live.learnwithchampak.demo.dao;

import live.learnwithchampak.demo.model.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentDao {

    private final JdbcTemplate jdbcTemplate;

    public StudentDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private final RowMapper<Student> studentRowMapper = (rs, rowNum) ->
        new Student(
            rs.getInt("id"),
            rs.getString("name"),
            rs.getInt("age"),
            rs.getString("course")
        );

    public List<Student> findAll() {
        String sql = "SELECT id, name, age, course FROM students ORDER BY id";
        return jdbcTemplate.query(sql, studentRowMapper);
    }

    public Student findById(Integer id) {
        String sql = "SELECT id, name, age, course FROM students WHERE id = ?";
        List<Student> result = jdbcTemplate.query(sql, studentRowMapper, id);
        return result.isEmpty() ? null : result.get(0);
    }

    public int insert(Student student) {
        String sql = "INSERT INTO students (name, age, course) VALUES (?, ?, ?)";
        return jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getCourse());
    }

    public int update(Integer id, Student student) {
        String sql = "UPDATE students SET name = ?, age = ?, course = ? WHERE id = ?";
        return jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getCourse(), id);
    }

    public int delete(Integer id) {
        String sql = "DELETE FROM students WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
}

findAll()
Reads all rows

findById()
Reads one row by id

insert()
Adds a new row

update()
Changes an existing row

The DAO class contains SQL queries. It uses JdbcTemplate to select, insert, update, and delete student records.

13. The controller class — HTTP endpoints

Which file and where?

Create this file at: src/main/java/live/learnwithchampak/demo/controller/StudentController.java

This controller exposes database operations through REST endpoints.

StudentController.java

package live.learnwithchampak.demo.controller;

import live.learnwithchampak.demo.dao.StudentDao;
import live.learnwithchampak.demo.model.Student;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/students")
public class StudentController {

    private final StudentDao studentDao;

    public StudentController(StudentDao studentDao) {
        this.studentDao = studentDao;
    }

    @GetMapping
    public List<Student> getAllStudents() {
        return studentDao.findAll();
    }

    @GetMapping("/{id}")
    public Object getStudentById(@PathVariable Integer id) {
        Student student = studentDao.findById(id);
        if (student == null) {
            return Map.of("message", "Student not found", "id", id);
        }
        return student;
    }

    @PostMapping
    public Map<String, Object> createStudent(@RequestBody Student student) {
        int rows = studentDao.insert(student);
        return Map.of(
            "message", "Student created",
            "rowsAffected", rows
        );
    }

    @PutMapping("/{id}")
    public Map<String, Object> updateStudent(@PathVariable Integer id, @RequestBody Student student) {
        int rows = studentDao.update(id, student);
        return Map.of(
            "message", "Student updated",
            "rowsAffected", rows
        );
    }

    @DeleteMapping("/{id}")
    public Map<String, Object> deleteStudent(@PathVariable Integer id) {
        int rows = studentDao.delete(id);
        return Map.of(
            "message", "Student deleted",
            "rowsAffected", rows
        );
    }
}

The controller connects the outside world to the database layer. It exposes GET, POST, PUT, and DELETE endpoints.

14. Full flow diagram

This is the complete beginner flow for this lesson.

The complete flow is simple. The client sends an HTTP request, the controller receives it, the DAO runs SQL, and SQLite stores or returns data.

15. How to run the project

If using Maven wrapper

Run with Maven wrapper

./mvnw spring-boot:run

If using Gradle wrapper

Run with Gradle wrapper

./gradlew bootRun

You can run the project with the Maven wrapper or the Gradle wrapper. The wrappers are safer because they use the project’s own build tool version.

16. How to test the endpoints

16.1 Get all students

GET all students

GET http://localhost:8080/api/students

16.2 Get one student

GET one student

GET http://localhost:8080/api/students/1

16.3 Create a student

POST create student

POST http://localhost:8080/api/students
Content-Type: application/json

{
  "name": "Neha",
  "age": 23,
  "course": "MBA"
}

16.4 Update a student

PUT update student

PUT http://localhost:8080/api/students/1
Content-Type: application/json

{
  "name": "Aman Kumar",
  "age": 22,
  "course": "BCA"
}

16.5 Delete a student

DELETE student

DELETE http://localhost:8080/api/students/1

Once the application is running, you can test GET, POST, PUT, and DELETE endpoints from the browser, Postman, or curl.

17. Expected JSON examples

GET /api/students

JSON response for all students

[
  {
    "id": 1,
    "name": "Aman",
    "age": 21,
    "course": "BCA"
  },
  {
    "id": 2,
    "name": "Riya",
    "age": 20,
    "course": "MCA"
  },
  {
    "id": 3,
    "name": "Vikash",
    "age": 22,
    "course": "B.Tech"
  }
]

POST /api/students

JSON response after create

{
  "message": "Student created",
  "rowsAffected": 1
}

The database endpoints return JSON responses. Some return full student objects, and some return status messages with rows affected.

18. Common beginner mistakes

• Putting dependencies in the wrong part of the build file
• Creating application.properties in the wrong folder
• Putting schema.sql under Java instead of resources
• Forgetting to add the SQLite JDBC driver
• Using the wrong package names
• Using column names that do not match SQL table columns
• Sending invalid JSON in POST or PUT

If something fails, first check the file location, then package name, then dependency section, then SQL file names.

The most common beginner mistakes are wrong file locations, wrong dependency placement, and mismatched SQL or Java names.

19. What you learned

• what a database, table, row, and column are
• what SQLite is
• which dependencies to add and exactly where to add them
• where to place application.properties, schema.sql, and data.sql
• how Spring Boot, JdbcTemplate, and SQLite work together
• how to build basic CRUD endpoints

You now know how to connect Spring Boot to SQLite, create tables, insert sample data, write DAO methods, and expose CRUD endpoints.

Quick file placement guide

Thing	Where to put it
Maven dependencies	pom.xml inside <dependencies>
Gradle dependencies	build.gradle inside dependencies { }
Properties	src/main/resources/application.properties
Schema SQL	src/main/resources/schema.sql
Sample data SQL	src/main/resources/data.sql
Java classes	src/main/java/...

Official-style learning ideas

Spring relational data guide Spring Boot data initialization SQLite JDBC driver SQLite foreign key support

Simple recommendation

1. Create project with Spring Web + JDBC
2. Add SQLite JDBC dependency
3. Create schema.sql
4. Create data.sql
5. Create model, DAO, controller
6. Run the app
7. Test CRUD endpoints

Key commands

Useful commands

java -version
mvn -version
gradle -version
./mvnw spring-boot:run
./gradlew bootRun

Programmer's Picnic — Spring Boot + SQLite lesson page