Database Access with Spring Data

Source: Spring Boot in Practice — Chapter 3 by Somnath Musib (Manning). Concepts rewritten for this course; companion samples: spring-boot-in-practice/repo ch03.

Most apps need a database. This lesson covers how Spring Data gives you a consistent way to configure stores, map entities, and query data from a Spring Boot app — relational (JPA/H2) and a look at MongoDB.

Pattern-check challenges

These editors do not compile or run Spring Boot / JPA. Check validates patterns in your code (sourceChecks) only. Annotated snippets above each exercise show the usual shape — inline comments explain what each part does; you still type it into the editor yourself. Paste is disabled (keyboard, right-click, drag-drop) so you practice writing the code.

What this lesson covers

• Spring Data as an umbrella project and why it exists
• Configuring a relational DB (H2 + JPA) and MongoDB
• Schema init with schema.sql / data.sql
• CrudRepository, custom repositories, and JPA entities
• Query methods, @NamedQuery, @Query, Criteria API, Querydsl (overview)
• Pagination/sorting and many-to-many relationships (overview)

---

1. Introducing Spring Data

Spring Data is an umbrella of modules for relational DBs, MongoDB, and other stores. The goal: a familiar Spring programming model — annotations on domain types, repository interfaces, and optional templates (JdbcTemplate, MongoTemplate) that hide connection / exception boilerplate.

Why Spring Data?

Without an ORM or repository layer you write a lot of JDBC: open a connection, prepare statements, bind params, map rows, close resources. JPA (Java Persistence API) is a specification for mapping objects ↔ tables (ORM). Hibernate and EclipseLink implement it. Spring Data JPA sits on top and gives you:

• Metadata-driven persistence (@Entity, @Id, …)
• Repository interfaces that generate CRUD implementations at runtime
• Query derivation from method names, plus explicit JPQL/SQL when you need it

Core commons interfaces

Spring Data Commons defines store-agnostic building blocks:

Interface	Role
Repository<T, ID>	Marker — captures entity type + id type
CrudRepository<T, ID>	save, findById, findAll, count, deleteById, …
PagingAndSortingRepository<T, ID>	Extends CRUD with Pageable / Sort

Store modules (JPA, MongoDB, …) extend these. Example: JpaRepository extends paging/sorting and adds JPA-specific helpers.

Repository
    └── CrudRepository
            └── PagingAndSortingRepository
                    └── JpaRepository (Spring Data JPA)

Quiz

Spring Data basics

1 / 2

JPA is best described as…

Pick an answer to see if you are right.

---

2. Configuring databases

2.1 Relational database (H2 + Spring Data JPA)

Add the JPA starter and a driver. For demos, H2 is an embedded in-memory DB (data is lost on restart). MySQL/PostgreSQL work the same way — swap the driver and URL.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId> <!-- JPA + Hibernate + HikariCP -->
</dependency>
<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope> <!-- driver needed at runtime only -->
</dependency>

spring.datasource.url=jdbc:h2:mem:sbipdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.h2.console.enabled=true # H2 web console for ad-hoc SQL

Boot auto-configures a DataSource. By default that is HikariCP (connection pool), pulled transitively through spring-boot-starter-jdbc → HikariCP. To use Tomcat JDBC instead, exclude HikariCP from the JPA starter and add tomcat-jdbc.

Pool detection order if Hikari is missing: Tomcat pool → Commons DBCP2 → JDK DataSource.

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

spring.datasource.url=jdbc:h2:mem:sbipdb # in-memory schema name sbipdb
spring.datasource.username=sa
spring.datasource.password=password
spring.h2.console.enabled=true # enable /h2-console UI

2.2 MongoDB

Use spring-boot-starter-data-mongodb. For tests/demos, Flapdoodle’s embedded Mongo is common; for a real server, configure spring.data.mongodb.* (host, port, uri, credentials) and drop the embed dependency. MongoTemplate (or Mongo repositories) perform document operations.

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

2.3 Initialize schema with schema.sql / data.sql

Boot can run DDL/DML scripts from the classpath on startup:

File	Typical content
schema.sql	CREATE TABLE …
data.sql	INSERT …

Control with spring.sql.init.mode (Boot 2.5+; older books used spring.datasource.initialization-mode): embedded (default for H2), always, or never. Customize paths with spring.sql.init.schema-locations / data-locations (or legacy spring.datasource.schema / data).

Platform-specific files: schema-h2.sql, data-mysql.sql with spring.sql.init.platform=h2.

Important: pick either script-based DDL or Hibernate ddl-auto — not both fighting each other. If you use schema.sql, set spring.jpa.hibernate.ddl-auto=none.

CREATE TABLE COURSES (
  id int NOT NULL,
  name varchar(100) NOT NULL,
  category varchar(20) NOT NULL,
  rating int NOT NULL,
  description varchar(1000) NOT NULL,
  PRIMARY KEY (id)
);

spring.jpa.hibernate.ddl-auto=none # let schema.sql own DDL
spring.sql.init.mode=always # run scripts even for non-embedded DBs

Quiz

Database config quiz

1 / 2

Default connection pool with spring-boot-starter-data-jpa?

Pick an answer to see if you are right.

---

3. CrudRepository and JPA entities

3.1 Annotate the domain type

Map a POJO to a table with JPA annotations (Jakarta Persistence in Boot 3; older books used javax.persistence):

Annotation	Purpose
@Entity	Persistable type
@Table(name=…)	Table name when it differs from the class name
@Id	Primary key
@GeneratedValue	How ids are assigned (IDENTITY, SEQUENCE, TABLE, AUTO)
@Column	Column name / constraints when defaults are wrong

@Entity
@Table(name = "COURSES") // maps to COURSES table
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // DB identity column
    private Long id;

    @Column(name = "NAME")
    private String name;
    // category, rating, description …
}

3.2 Repository interface

@Repository
public interface CourseRepository extends CrudRepository<Course, Long> {
    // empty — Spring Data supplies save/findAll/delete implementations
}

@Repository helps component scanning and exception translation (store-specific exceptions → Spring’s DataAccessException). Prefer injecting repositories through a service layer in real apps; tests often wire the repository directly for clarity.

3.3 spring.jpa.hibernate.ddl-auto

Value	Behavior
none	No DDL from Hibernate
validate	Check schema matches entities
update	Alter schema if needed
create	Create schema; wipe data
create-drop	Create then drop at end of session (default for embedded)

@Entity
@Table(name = "COURSES")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "NAME")
    private String name;
}

@Repository // scan + exception translation
public interface CourseRepository extends CrudRepository<Course, Long> {
}

3.4 Custom base repository (limit CRUD surface)

To hide delete methods, define a base interface that extends Repository (not CrudRepository), list only the methods you want, mark it @NoRepositoryBean, then have CustomizedCourseRepository extend that base.

@NoRepositoryBean // do not create a bean for this base type
public interface BaseRepository<T, ID> extends Repository<T, ID> {
    <S extends T> S save(S entity);
    Iterable<T> findAll();
}

Testing tip: @DataJpaTest loads JPA slice only; @SpringBootTest boots the full context.

---

4. Retrieving data

4.1 Derived query methods

Declare methods following Spring Data’s naming grammar. The framework parses subject (before By) and predicate (after):

Examples:

Iterable<Course> findAllByCategory(String category);
Iterable<Course> findAllByCategoryOrderByName(String category);
boolean existsByName(String name);
long countByCategory(String category);
Iterable<Course> findByNameOrCategory(String name, String category);
Iterable<Course> findByNameStartsWith(String name);
Stream<Course> streamAllByCategory(String category);

Common prefixes: find…By, count…By, exists…By, delete…By. Keywords: And, Or, OrderBy, StartsWith, Containing, …

@Repository
public interface CourseRepository extends CrudRepository<Course, Long> {
    Iterable<Course> findAllByCategory(String category); // WHERE category = ?
    boolean existsByName(String name); // existence check
    long countByCategory(String category); // COUNT(*)
}

4.2 PagingAndSortingRepository

public interface CourseRepository extends PagingAndSortingRepository<Course, Long> {
}

Pageable pageable = PageRequest.of(0, 5); // page 0, size 5
PageRequest.of(0, 5, Sort.by(Sort.Order.asc("Name")));

findAll(Pageable) returns a Page; findAll(Sort) returns a sorted Iterable.

4.3 @NamedQuery vs @Query

@NamedQuery — JPQL on the entity (name = "Course.findAllByCategoryAndRating"). Works, but mixes persistence metadata into the domain class.

@Query — Prefer putting JPQL/SQL on the repository method. Supports named params (@Param), nativeQuery = true, and modifying queries with @Modifying + @Transactional.

@NamedQuery(
    name = "Course.findAllByCategoryAndRating",
    query = "select c from Course c where c.category=?1 and c.rating=?2"
)
@Entity
public class Course { … }

@Query("select c from Course c where c.category=?1")
Iterable<Course> findAllByCategory(String category);

@Query("select c from Course c where c.category=:category and c.rating >:rating")
Iterable<Course> findAllByCategoryAndRatingGreaterThan(
    @Param("category") String category,
    @Param("rating") int rating);

@Modifying
@Transactional
@Query("update Course c set c.rating=:rating where c.name=:name")
int updateCourseRatingByName(@Param("rating") int rating, @Param("name") String name);

You can also externalize named queries in META-INF/jpa-named-queries.properties (Entity.method=JPQL).

@Query("select c from Course c where c.category=?1") // JPQL on the method
Iterable<Course> findAllByCategory(String category);

4.4 Criteria API (type-safe, verbose)

Build queries with CriteriaBuilder → CriteriaQuery → Root → Predicate → TypedQuery. Compile-time types help; even a simple WHERE category = 'Spring' takes several lines. Prefer for dynamic predicates.

4.5 Querydsl (fluent alternative)

Add querydsl-jpa, querydsl-apt, and apt-maven-plugin to generate Q-types (QCourse). Extend QuerydslPredicateExecutor<Course> on the repository. Build with JPAQuery / predicates like course.category.eq("Spring"). Compared to Criteria: third-party, more concise, also works with other stores.

Quiz

Querying quiz

1 / 3

findAllByCategoryOrderByName is an example of…

Pick an answer to see if you are right.

---

5. Relationships and projections

Projections

• Interface projection — interface with getters for selected fields (DescriptionOnly.getDescription()); repository returns Iterable<DescriptionOnly>.
• Class-based / DTO — POJO constructor matching a JPQL select new com.example.AuthorCourseDto(...).

Association types

Mapping	Meaning
@OneToOne	Exactly one related entity
@OneToMany / @ManyToOne	Parent–children / many share one parent
@ManyToMany	e.g. Author ↔ Course via join table

For many-to-many: owning side uses @JoinTable; inverse side uses @ManyToMany(mappedBy = "…"). Join table holds foreign keys to both sides. Combine with @Query joining author / course / link tables into a DTO.

@ManyToMany
@JoinTable(
    name = "AUTHORS_COURSES",
    joinColumns = @JoinColumn(name = "author_id"),
    inverseJoinColumns = @JoinColumn(name = "course_id")
)
private Set<Course> courses;

@ManyToMany(mappedBy = "courses") // inverse side
private Set<Author> authors;

---

Chapter checklist

Topic	Takeaway
Spring Data	Umbrella; Commons repositories + store modules
Config	JPA starter + driver + spring.datasource.*; Mongo starter for documents
Init	schema.sql / data.sql or ddl-auto, not conflicting both
Entities	@Entity, @Id, @Column, @GeneratedValue
Repositories	CrudRepository / paging; optional custom base + @NoRepositoryBean
Queries	Derived names, @NamedQuery, @Query, Criteria, Querydsl
Relations	@ManyToMany + join table; projections for selected columns

Next in the book: Boot auto-configuration and the Actuator for monitoring → Lesson 3.