Spring Data JDBC Tutorial

1. What is Spring Data JDBC?

Spring Data JDBC is a persistence framework that provides a simplified approach to working with relational databases using JDBC. It offers:

• Repository abstraction - Reduces boilerplate code
• Object mapping - Maps Java objects to database tables
• Simple configuration - Minimal setup required
• No caching/lazy loading - Predictable database access
• DDL generation - Can automatically create database schema

Unlike JPA/Hibernate, Spring Data JDBC doesn't include:

• Session/caching complexity
• Lazy loading
• Dirty checking
• Complex mapping configurations

2. When to Use Spring Data JDBC?

Use Spring Data JDBC when:

• You want simple, predictable SQL access
• You prefer explicit database operations
• You need direct control over SQL queries
• Your domain model aligns well with database schema
• You want to avoid JPA complexity

Consider JPA/Hibernate when:

• You need complex object relationships
• You want automatic dirty checking
• Caching is important
• You have complex inheritance hierarchies

3. Basic Concepts

Key Components:

• Entities - Java classes mapped to database tables
• Repositories - Interfaces for CRUD operations
• Aggregate Roots - Main entities that are persisted
• Embedded Types - Value objects stored within entities

Dependencies (Maven)

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

4. CRUD Examples

Entity Definition

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;
import java.time.LocalDateTime;

@Table("users")
public class User {
    @Id
    private Long id;
    private String username;
    private String email;
    private LocalDateTime createdAt;

    // Constructors
    public User() {}

    public User(String username, String email) {
        this.username = username;
        this.email = email;
        this.createdAt = LocalDateTime.now();
    }

    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public LocalDateTime getCreatedAt() { return createdAt; }
    public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }
}

Repository Interface

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.Optional;

public interface UserRepository extends CrudRepository<User, Long> {

    // Custom query methods
    Optional<User> findByUsername(String username);
    List<User> findByEmailContaining(String emailPart);

    // Custom SQL query
    @Query("SELECT * FROM users WHERE created_at >= :since")
    List<User> findUsersCreatedAfter(@Param("since") LocalDateTime since);

    // Count method
    long countByEmailEndingWith(String domain);
}

Service Class

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Service
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    // CREATE - Save new user
    public User createUser(User user) {
        return userRepository.save(user);
    }

    // READ - Get user by ID
    public Optional<User> getUserById(Long id) {
        return userRepository.findById(id);
    }

    // READ - Get all users
    public Iterable<User> getAllUsers() {
        return userRepository.findAll();
    }

    // READ - Custom query
    public List<User> getUsersByEmailDomain(String domain) {
        return userRepository.findByEmailContaining(domain);
    }

    // UPDATE - Update user
    public User updateUser(User user) {
        if (user.getId() == null) {
            throw new IllegalArgumentException("User ID cannot be null for update");
        }
        return userRepository.save(user);
    }

    // DELETE - Delete user by ID
    public void deleteUser(Long id) {
        userRepository.deleteById(id);
    }

    // Complex operation with transaction
    @Transactional
    public void updateUserEmail(Long userId, String newEmail) {
        User user = userRepository.findById(userId)
            .orElseThrow(() -> new RuntimeException("User not found"));
        user.setEmail(newEmail);
        userRepository.save(user);
    }
}

Configuration

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;

@Configuration
@EnableJdbcRepositories
public class DataConfig {
    // Configuration is automatically handled by Spring Boot
}

application.properties

# H2 Database Configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# H2 Console (for testing)
spring.h2.console.enabled=true

# Show SQL queries
spring.data.jdbc.repositories.log-sql-queries=true

# Auto-create schema
spring.sql.init.mode=always

Schema SQL (src/main/resources/schema.sql)

CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Test Data (src/main/resources/data.sql)

INSERT INTO users (username, email) VALUES 
('john_doe', '[e-mail]'),
('jane_smith', '[e-mail]'),
('bob_wilson', '[e-mail]');

Controller Example

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @PostMapping
    public ResponseEntity<User> createUser(@RequestBody User user) {
        User savedUser = userService.createUser(user);
        return ResponseEntity.ok(savedUser);
    }

    @GetMapping("/{id}")
    public ResponseEntity<User> getUser(@PathVariable Long id) {
        Optional<User> user = userService.getUserById(id);
        return user.map(ResponseEntity::ok)
                  .orElse(ResponseEntity.notFound().build());
    }

    @GetMapping
    public List<User> getAllUsers() {
        return (List<User>) userService.getAllUsers();
    }

    @PutMapping("/{id}")
    public ResponseEntity<User> updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        User updatedUser = userService.updateUser(user);
        return ResponseEntity.ok(updatedUser);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
        return ResponseEntity.noContent().build();
    }
}

Testing the Implementation

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.Optional;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    void testCreateAndFindUser() {
        // Create
        User user = new User("testuser", "[e-mail]");
        User savedUser = userService.createUser(user);

        assertNotNull(savedUser.getId());

        // Read
        Optional<User> foundUser = userService.getUserById(savedUser.getId());
        assertTrue(foundUser.isPresent());
        assertEquals("testuser", foundUser.get().getUsername());
    }

    @Test
    void testUpdateUser() {
        User user = new User("updateuser", "[e-mail]");
        User savedUser = userService.createUser(user);

        savedUser.setEmail("[e-mail]");
        User updatedUser = userService.updateUser(savedUser);

        assertEquals("[e-mail]", updatedUser.getEmail());
    }

    @Test
    void testDeleteUser() {
        User user = new User("deleteuser", "[e-mail]");
        User savedUser = userService.createUser(user);

        userService.deleteUser(savedUser.getId());

        Optional<User> deletedUser = userService.getUserById(savedUser.getId());
        assertFalse(deletedUser.isPresent());
    }
}

Main Application Class

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

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

Key Points to Remember:

1. Simple Mapping: Fields are mapped directly to columns by name
2. No Proxies: Objects are always real instances, no lazy loading
3. Explicit Operations: Everything is explicit - no automatic dirty checking
4. Aggregate Roots: Focus on persisting complete aggregates
5. Transaction Management: Use @Transactional for multi-operation transactions