Spring Boot + SQLite from Ground Level — Beginne | AI/ML with Champak Roy, Varanasi

1. Start here

We are going to build this in the simplest clean way:

Spring Boot starts the application.
A SQLite database file is opened or created.
schema.sql creates the table.
data.sql inserts sample rows.
A DAO uses JdbcTemplate to run SQL.
A controller exposes HTTP endpoints.

Important: this lesson focuses on learning the real flow clearly. That is why we are using JdbcTemplate first instead of jumping directly to JPA.

Goal of this lesson

By the end, you should be able to create a Spring Boot app that uses a local SQLite file and responds to API requests.

What you must check at the end

The app should start without errors, a school.db file should appear, and your API should return student data.

2. What is a database?

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

Basic words

Table = like a sheet
Row = one record
Column = one field of that record

Example

A students table may have columns like id, name, age, and course.

id	name	age	course
1	Aman	21	BCA
2	Riya	20	MCA

You will perform four common operations again and again: create, read, update, delete. Together they are called CRUD.

3. What is SQLite?

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

Good for learning

Very easy to start with. No separate server setup.

Good for small apps

Excellent for prototypes, local tools, small systems, and demos.

File-based

Your data lives in a file such as school.db.

Very important beginner point: if you use jdbc:sqlite:school.db, the database file is created relative to the working directory. That usually means your project root when you run from the IDE or terminal, but you should still check where it appears.

4. Why use JdbcTemplate first?

For your first real database lesson, JdbcTemplate is a very good choice because it keeps the flow easy to understand.

You write the SQL yourself.
You clearly see inserts, selects, updates, and deletes.
You understand what your app is actually doing.
You build a strong foundation before learning JPA and repositories.

Start with real SQL. Later, move to JPA. This order makes understanding much stronger.

5. Dependencies you need

We need Spring Web, Spring JDBC, and the SQLite JDBC driver.

Dependency	Why it is needed
`spring-boot-starter-web`	Creates HTTP endpoints such as GET, POST, PUT, DELETE.
`spring-boot-starter-jdbc`	Uses `DataSource` and `JdbcTemplate`.
`org.xerial:sqlite-jdbc`	Connects Java and Spring Boot to SQLite files.

5.1 Maven

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>

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

5.2 Gradle

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'
}

Add dependencies inside the correct dependencies section. Do not put them in the plugins section.

Check now: after saving dependencies, refresh Maven or Gradle in your IDE and make sure there are no red import errors.

6. Project structure

Create this exact beginner-friendly structure.

Project structure

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

Check now: make sure package names and folder names match exactly. Many beginner errors happen because package names do not match the actual folders.

7. Create all files

Below is the complete working beginner version.

7.1 application.properties

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

application.properties

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

app.sqlite.url=jdbc:sqlite:school.db

7.2 schema.sql

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

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
);

Because this file drops and recreates the table, sample data gets reset whenever initialization runs on startup.

7.3 data.sql

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

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');

7.4 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);
    }
}

7.5 SQLiteConfig.java

SQLiteConfig.java

package live.learnwithchampak.demo.config;

import org.sqlite.SQLiteDataSource;
import org.springframework.beans.factory.annotation.Value;
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 {

    @Value("${app.sqlite.url}")
    private String sqliteUrl;

    @Bean
    public DataSource dataSource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        dataSource.setUrl(sqliteUrl);
        return dataSource;
    }

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

7.6 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;
    }
}

7.7 StudentRequest.java

StudentRequest.java

package live.learnwithchampak.demo.dto;

public class StudentRequest {
    private String name;
    private Integer age;
    private String course;

    public StudentRequest() {
    }

    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;
    }
}

7.8 StudentDao.java

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(String name, Integer age, String course) {
        String sql = "INSERT INTO students(name, age, course) VALUES (?, ?, ?)";
        return jdbcTemplate.update(sql, name, age, course);
    }

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

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

7.9 StudentService.java

StudentService.java

package live.learnwithchampak.demo.service;

import live.learnwithchampak.demo.dao.StudentDao;
import live.learnwithchampak.demo.dto.StudentRequest;
import live.learnwithchampak.demo.model.Student;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentService {

    private final StudentDao studentDao;

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

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

    public Student getStudentById(Integer id) {
        return studentDao.findById(id);
    }

    public void createStudent(StudentRequest request) {
        validate(request);
        studentDao.insert(request.getName().trim(), request.getAge(), request.getCourse().trim());
    }

    public boolean updateStudent(Integer id, StudentRequest request) {
        validate(request);
        return studentDao.update(id, request.getName().trim(), request.getAge(), request.getCourse().trim()) > 0;
    }

    public boolean deleteStudent(Integer id) {
        return studentDao.delete(id) > 0;
    }

    private void validate(StudentRequest request) {
        if (request.getName() == null || request.getName().trim().isEmpty()) {
            throw new IllegalArgumentException("Name is required");
        }
        if (request.getAge() == null || request.getAge() <= 0) {
            throw new IllegalArgumentException("Age must be greater than 0");
        }
        if (request.getCourse() == null || request.getCourse().trim().isEmpty()) {
            throw new IllegalArgumentException("Course is required");
        }
    }
}

7.10 StudentController.java

StudentController.java

package live.learnwithchampak.demo.controller;

import live.learnwithchampak.demo.dto.StudentRequest;
import live.learnwithchampak.demo.model.Student;
import live.learnwithchampak.demo.service.StudentService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

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

    private final StudentService studentService;

    public StudentController(StudentService studentService) {
        this.studentService = studentService;
    }

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

    @GetMapping("/{id}")
    public ResponseEntity<?> getStudentById(@PathVariable Integer id) {
        Student student = studentService.getStudentById(id);
        if (student == null) {
            return ResponseEntity.notFound().build();
        }
        return ResponseEntity.ok(student);
    }

    @PostMapping
    public ResponseEntity<?> createStudent(@RequestBody StudentRequest request) {
        try {
            studentService.createStudent(request);
            return ResponseEntity.ok("Student created successfully");
        } catch (IllegalArgumentException e) {
            return ResponseEntity.badRequest().body(e.getMessage());
        }
    }

    @PutMapping("/{id}")
    public ResponseEntity<?> updateStudent(@PathVariable Integer id, @RequestBody StudentRequest request) {
        try {
            boolean updated = studentService.updateStudent(id, request);
            if (!updated) {
                return ResponseEntity.notFound().build();
            }
            return ResponseEntity.ok("Student updated successfully");
        } catch (IllegalArgumentException e) {
            return ResponseEntity.badRequest().body(e.getMessage());
        }
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<?> deleteStudent(@PathVariable Integer id) {
        boolean deleted = studentService.deleteStudent(id);
        if (!deleted) {
            return ResponseEntity.notFound().build();
        }
        return ResponseEntity.ok("Student deleted successfully");
    }
}

Big check now: all imports should resolve. If your IDE shows red lines, first check package names, dependency refresh, and file locations.

8. Terminal commands and what they mean

8.1 Maven commands

Command	Meaning
`mvn clean`	Deletes old build output.
`mvn compile`	Compiles Java code.
`mvn test`	Runs tests.
`mvn spring-boot:run`	Starts the app.
`mvn package`	Builds the JAR file.

8.2 Gradle commands

Command	Meaning
`gradle clean`	Deletes old build output.
`gradle build`	Compiles and builds the project.
`gradle test`	Runs tests.
`gradle bootRun`	Starts the app.

8.3 Recommended first run

Maven

mvn clean spring-boot:run

Gradle

gradle clean bootRun

Run the command from the project root folder, not from inside src.

9. Run the application and do the first checks

Run the app

Use mvn spring-boot:run or gradle bootRun.

Look for startup success

The console should show that Spring Boot started and Tomcat is running on port 8080.

Check the database file

Look for a file named school.db in your project root or working directory.

Check sample data

Open your browser and go to http://localhost:8080/students. You should see the sample student rows from data.sql.

Beginner success checkpoint: if you can open http://localhost:8080/students and see JSON data, your setup is basically working.

10. Testing the API step by step

Use the browser, Postman, Insomnia, or curl. Start with simple GET requests.

10.1 Get all students

GET all

curl http://localhost:8080/students

10.2 Get one student by id

GET by id

curl http://localhost:8080/students/1

10.3 Create a new student

POST

curl -X POST http://localhost:8080/students ^
  -H "Content-Type: application/json" ^
  -d "{\"name\":\"Neha\",\"age\":19,\"course\":\"B.Sc\"}"

POST for Linux / Mac terminal

curl -X POST http://localhost:8080/students \
  -H "Content-Type: application/json" \
  -d '{"name":"Neha","age":19,"course":"B.Sc"}'

10.4 Update a student

PUT

curl -X PUT http://localhost:8080/students/1 ^
  -H "Content-Type: application/json" ^
  -d "{\"name\":\"Aman Kumar\",\"age\":22,\"course\":\"BCA\"}"

PUT for Linux / Mac terminal

curl -X PUT http://localhost:8080/students/1 \
  -H "Content-Type: application/json" \
  -d '{"name":"Aman Kumar","age":22,"course":"BCA"}'

10.5 Delete a student

DELETE

curl -X DELETE http://localhost:8080/students/2

Testing pattern for beginners: always do GET all, then POST, then GET all again, then PUT, then GET by id, then DELETE, then GET all again.

11. MCQs — Test yourself

Answer the questions below and then click the check button for each one.

Score: 0 / 10

After answering, try to explain each correct option in your own words. That is the fastest way to make the lesson stick.

12. Common beginner mistakes and exact fixes

Problem: import errors

Refresh Maven or Gradle.
Check dependency block placement.
Check Java file package names.

Problem: `/students` gives 404

Confirm @RestController exists.
Confirm @RequestMapping("/students") exists.
Make sure app started successfully.

Problem: database file not found

Check your working directory.
Search the project root for school.db.
Make sure the app actually started.

Problem: table does not exist

Check schema.sql file location.
Check spring.sql.init.mode=always.
Check startup logs for SQL errors.

If the app starts but data is missing, first check that schema.sql and data.sql are inside src/main/resources, not inside a Java package.

If package names do not match folder names, Spring Boot may compile badly or fail to find classes correctly.

13. What should you learn next?

How to build HTML pages that call this API
How to connect forms to POST and PUT
How to add validation annotations
How to move from JdbcTemplate to JPA
How to create tests for controller and DAO

Once this lesson works fully, you are ready for a next lesson on a small HTML + Spring Boot student management page.

1. Start here

Goal of this lesson

What you must check at the end

2. What is a database?

Basic words

Example

3. What is SQLite?

Good for learning

Good for small apps

File-based

4. Why use JdbcTemplate first?

5. Dependencies you need

5.1 Maven

5.2 Gradle

6. Project structure

7. Create all files

7.1 application.properties

7.2 schema.sql

7.3 data.sql

7.4 DemoApplication.java

7.5 SQLiteConfig.java

7.6 Student.java

7.7 StudentRequest.java

7.8 StudentDao.java

7.9 StudentService.java

7.10 StudentController.java

8. Terminal commands and what they mean

8.1 Maven commands

8.2 Gradle commands

8.3 Recommended first run

9. Run the application and do the first checks

Run the app

Look for startup success

Check the database file

Check sample data

10. Testing the API step by step

10.1 Get all students

10.2 Get one student by id

10.3 Create a new student

10.4 Update a student

10.5 Delete a student

11. MCQs — Test yourself

Q1. What is the main advantage of SQLite for beginners?

Q2. Which Spring dependency is used to create HTTP endpoints?

Q3. Which class is used in this lesson to run SQL queries?

Q4. Where should schema.sql and data.sql usually be placed?

Q5. Which HTTP method is usually used to create a new record?

Q6. Which file stores the property app.sqlite.url in this lesson?

Q7. Which annotation makes a class handle REST API requests?

Q8. What is the likely purpose of data.sql here?

Q9. If /students returns 404, which is a likely thing to check first?

Q10. Why is JdbcTemplate a good first step before JPA?

12. Common beginner mistakes and exact fixes

Problem: import errors

Problem: /students gives 404

Problem: database file not found

Problem: table does not exist

13. What should you learn next?

Q4. Where should `schema.sql` and `data.sql` usually be placed?

Q6. Which file stores the property `app.sqlite.url` in this lesson?

Q8. What is the likely purpose of `data.sql` here?

Q9. If `/students` returns 404, which is a likely thing to check first?

Q10. Why is `JdbcTemplate` a good first step before JPA?

Problem: `/students` gives 404