Spring JDBC · End-to-End Sample Application

Introduction

This tutorial walks the learner through building a complete command-line application that persists and retrieves Employee data with Spring JDBC 6, HikariCP, and an in-memory H2 database. Copy the project structure, run mvn spring-boot:run, and you will see data inserted and queried back from the table.

1 · Project Layout

spring-jdbc-demo/
├── pom.xml
└── src
    ├── main
    │   ├── java/com/example/demo/
    │   │   ├── DemoApplication.java
    │   │   ├── Employee.java
    │   │   ├── EmployeeDao.java
    │   │   └── PayrollService.java
    │   └── resources/
    │       ├── application.properties
    │       └── schema.sql
    └── test
        └── java/... (omitted for brevity)

2 · Maven pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
                             https://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example</groupId>
  <artifactId>spring-jdbc-demo</artifactId>
  <version>1.0.0-SNAPSHOT</version>
  <properties>
      <java.version>17</java.version>
      <spring.version>6.1.4</spring.version>
  </properties>

  <dependencies>
      <dependency>              <!-- Spring JDBC + template helpers -->
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>${spring.version}</version>
      </dependency>

      <dependency>              <!-- Transaction annotations (@Transactional) -->
          <groupId>org.springframework</groupId>
          <artifactId>spring-tx</artifactId>
          <version>${spring.version}</version>
      </dependency>

      <dependency>              <!-- High-performance connection pool -->
          <groupId>com.zaxxer</groupId>
          <artifactId>HikariCP</artifactId>
          <version>5.1.0</version>
      </dependency>

      <dependency>              <!-- In-memory H2 database (no install needed) -->
          <groupId>com.h2database</groupId>
          <artifactId>h2</artifactId>
          <version>2.2.224</version>
          <scope>runtime</scope>
      </dependency>

      <dependency>              <!-- Spring Boot runner to avoid verbose XML -->
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter</artifactId>
          <version>3.3.0</version>
      </dependency>
  </dependencies>

  <build>
      <plugins>
          <plugin>                 <!-- Plugin to run the app via mvn spring-boot:run -->
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-maven-plugin</artifactId>
              <version>3.3.0</version>
          </plugin>
      </plugins>
  </build>
</project>

Explanation: All you need is Spring JDBC, Spring TX (for @Transactional), HikariCP, and a driver (H2 here). Spring Boot’s starter gives a main class launcher so you can avoid writing boilerplate DataSourceBuilder code.

3 · SQL Schema (schema.sql)

CREATE TABLE IF NOT EXISTS employee (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    salary  DECIMAL(12,2) NOT NULL
);

Spring Boot detects schema.sql on the classpath and executes it automatically before the application beans start, so the table is ready for inserts.

4 · Application Properties

# src/main/resources/application.properties
spring.datasource.url=jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true       # browse http://localhost:8080/h2-console
spring.sql.init.mode=always
logging.level.org.springframework.jdbc.core=DEBUG  # see executed SQL

5 · The POJO (Employee.java)

package com.example.demo;

import java.math.BigDecimal;

/** Immutable record maps one row in EMPLOYEE table */
public record Employee(Long id, String name, BigDecimal salary) {}

Using Java 17 record gives us equals/hashCode/toString for free and makes the entity thread-safe (fields are final).

6 · DAO Layer (EmployeeDao.java)

package com.example.demo;

import java.math.BigDecimal;
import java.sql.*;
import java.util.List;

import org.springframework.jdbc.core.*;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {

    private final JdbcTemplate jdbc;

    public EmployeeDao(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /** Insert and return generated ID */
    public long save(Employee e) {
        String sql = "INSERT INTO employee(name, salary) VALUES (?, ?)";
        KeyHolder kh = new GeneratedKeyHolder();

        jdbc.update(con -> {
            PreparedStatement ps =
                con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, e.name());
            ps.setBigDecimal(2, e.salary());
            return ps;
        }, kh);

        return kh.getKey().longValue();
    }

    /** Read one row by ID */
    public Employee findById(long id) {
        String sql = "SELECT id, name, salary FROM employee WHERE id = ?";
        return jdbc.queryForObject(sql,
               RecordRowMapper.of(Employee.class),
               id);
    }

    /** List all rows */
    public List<Employee> findAll() {
        String sql = "SELECT id, name, salary FROM employee";
        return jdbc.query(sql, RecordRowMapper.of(Employee.class));
    }

    /** Give everyone a small raise (batch update) */
    public int[] batchRaise(BigDecimal bonus) {
        String sql = "UPDATE employee SET salary = salary + ?";
        return jdbc.batchUpdate(sql,
               ps -> ps.setBigDecimal(1, bonus));
    }
}

Explanation: • JdbcTemplate hides low-level connection handling.
• RecordRowMapper converts each row → Employee record.
• batchRaise shows batch processing; the single SQL is executed once, updating every row.

7 · Service Layer (PayrollService.java)

package com.example.demo;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PayrollService {

    private final EmployeeDao dao;

    public PayrollService(EmployeeDao dao) { this.dao = dao; }

    @Transactional
    public void hireSampleEmployees() {
        dao.save(new Employee(null, "Alice",  new BigDecimal("7000")));
        dao.save(new Employee(null, "Bob",    new BigDecimal("6500")));
        dao.save(new Employee(null, "Carol",  new BigDecimal("8000")));
    }

    @Transactional(readOnly = true)
    public List<Employee> listPayroll() {
        return dao.findAll();
    }

    @Transactional
    public void giveEveryoneRaise(BigDecimal amount) {
        dao.batchRaise(amount);
    }
}

Explanation: @Service marks business logic; @Transactional guarantees all writes succeed or none do. Read-only transactions hint the driver to optimize (some dialects skip locks).

8 · Boot Launcher (DemoApplication.java)

package com.example.demo;

import java.math.BigDecimal;

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

@SpringBootApplication   // pulls in @Configuration, @ComponentScan, etc.
public class DemoApplication implements CommandLineRunner {

    private final PayrollService payroll;

    public DemoApplication(PayrollService payroll) {
        this.payroll = payroll;
    }

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

    @Override
    public void run(String... args) {

        // 1. Insert sample data
        payroll.hireSampleEmployees();

        // 2. Query and print
        System.out.println("--- Payroll before raise ---");
        payroll.listPayroll().forEach(System.out::println);

        // 3. Give everyone ₹ 2 000 raise
        payroll.giveEveryoneRaise(new BigDecimal("2000"));

        // 4. Query again
        System.out.println("\n--- Payroll after raise ---");
        payroll.listPayroll().forEach(System.out::println);
    }
}

What you’ll see on the console (the numbers will vary because H2 starts IDs at 1 each run):

--- Payroll before raise ---
Employee[id=1, name=Alice, salary=7000.00]
Employee[id=2, name=Bob,   salary=6500.00]
Employee[id=3, name=Carol, salary=8000.00]

--- Payroll after raise ---
Employee[id=1, name=Alice, salary=9000.00]
Employee[id=2, name=Bob,   salary=8500.00]
Employee[id=3, name=Carol, salary=10000.00]

9 · Running the App

1 · Open a terminal inside spring-jdbc-demo/.
2 · Run mvn spring-boot:run (downloads dependencies ≈ first time only).
3 · Watch the console output above.
4 · Optionally browse http://localhost:8080/h2-console, JDBC URL = jdbc:h2:mem:demo, user = sa, no password.

10 · Key Takeaways

• Spring JDBC offers a lightweight alternative to full ORMs; you stay in SQL land.
• JdbcTemplate handles connection cleanup, placeholder binding, and error translation.
• Records + RecordRowMapper make mapping terse and immutable.
• Use @Transactional in service layer instead of sprinkling commits in DAO.
• For production, swap H2 for PostgreSQL/MySQL by changing the driver dependency and spring.datasource.url.