Spring Persistence with JDBC

Spring's JDBC support eliminates boilerplate Connection, Statement, and ResultSet lifecycle management while keeping SQL explicit and developer-controlled — no ORM magic, no entity state tracking, no lazy-loading surprises. JdbcTemplate / JdbcOperations are the primary entry points: they handle connection acquisition from the DataSource, statement execution, exception translation to Spring's DataAccessException hierarchy, and resource cleanup.

• spring-boot-starter-jdbc auto-configures a DataSource and registers a JdbcTemplate bean — both injectable directly.
• JdbcTemplate implements JdbcOperations; injecting the interface decouples your repository from the concrete template class.
• Spring Boot detects schema.sql and data.sql on the classpath and executes them at startup automatically when using an embedded datasource.

JdbcTemplate vs JdbcOperations

• JdbcTemplate is the concrete class; JdbcOperations is its interface. Both are auto-configured as beans by Boot's JdbcTemplateAutoConfiguration.
• AppProfileJdbcRepository injects both: JdbcTemplate (for its query() overloads with row mappers) and JdbcOperations (for update() calls). AppUserJdbcRepository injects only JdbcOperations, since it only needs updates and generated-key inserts.
• Prefer injecting JdbcOperations when you only need DML; it keeps the dependency surface minimal and test-friendly.

Transaction Management

• spring-boot-starter-jdbc registers a DataSourceTransactionManager automatically. @Transactional works without any additional configuration.
• Only AppUserJdbcRepository.save() is annotated @Transactional — the AppProfileRepository operations are single-statement and do not need explicit transaction demarcation (each executes in an implicit auto-commit transaction).
• @Transactional on a @Repository bean works because Spring wraps the bean in a proxy. The method must be called through the proxy (i.e., from another bean), not via an internal this. call, to trigger the transaction boundary.

H2 Console

• The in-memory database exists only within the running JVM — connecting from a separate process (e.g., a standalone H2 client) to jdbc:h2:mem: creates a different, empty database. The H2 console servlet runs inside the same JVM and therefore sees the live data.
• The spring-boot-h2console starter registers the H2 console servlet at /h2-console and sets spring.h2.console.enabled=true by default when the H2 dependency is on the classpath in a web application.

JDBC URL: jdbc:h2:mem:mtitek-spring-jdbc
Username: sa
Password: (empty)

Dependencies and Auto-configuration

• spring-boot-starter-jdbc pulls in HikariCP (default connection pool since Spring Boot 2), spring-jdbc, and Spring transaction support.
• spring-boot-h2console is a dedicated starter that enables the H2 browser console servlet — it is separate from spring-boot-starter-jdbc and must be declared explicitly.
• With an embedded H2 datasource and no explicit spring.datasource.url, Boot generates a random DB name unless overridden.
• spring.datasource.name sets the logical name of the datasource used in the JDBC URL: jdbc:h2:mem:mtitek-spring-jdbc.
• spring.datasource.generate-unique-name=false is required; without it, Boot ignores spring.datasource.name and generates a random UUID-based DB name, making the H2 console URL unpredictable across restarts.

<!-- pom.xml -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-h2console</artifactId>
</dependency>

# application.properties
spring.application.name=mtitek-spring-jdbc

spring.datasource.name=mtitek-spring-jdbc
spring.datasource.generate-unique-name=false

Schema and Seed Data Initialization

• identity is H2's shorthand for an auto-increment bigint primary key — equivalent to bigint generated by default as identity primary key. This matters when retrieving generated keys via GeneratedKeyHolder.
• AppUserProfileRef is a pure join table with no surrogate key, managed entirely by manual JDBC inserts — no cascade, no JPA lifecycle.
• data.sql runs after schema.sql and opens with explicit DELETE statements before inserts, ensuring idempotent re-runs on application restart (H2 in-memory DB is recreated per JVM, but the pattern matters for non-embedded DBs).

// src/main/resources/schema.sql
create table if not exists AppUser (
  id identity,
  name varchar(10) not null
);

create table if not exists AppProfile (
  id varchar(1) not null PRIMARY KEY,
  name varchar(20) not null,
  role varchar(10) not null
);

create table if not exists AppUserProfileRef (
  appUserId bigint not null,
  appProfileId varchar(1) not null
);

alter table AppUserProfileRef add foreign key (appUserId) references AppUser(id);
alter table AppUserProfileRef add foreign key (appProfileId) references AppProfile(id);

Domain Model

• AppUser.id is Long (boxed), not long — intentionally nullable before persistence; the repository sets it after reading from GeneratedKeyHolder.
• AppProfile uses @NoArgsConstructor(access = AccessLevel.PRIVATE, force = true) — Lombok generates a private no-arg constructor (required internally by Lombok's @Data/final field handling), while force = true initializes final-like fields to their defaults. The only public constructor is the @AllArgsConstructor-generated one.
• AppProfile.role is typed as the Role enum; stored as varchar in the DB using .name() on write and Role.valueOf() on read — no JPA @Enumerated, fully manual.
• Bean Validation annotations (@NotNull, @Size) on AppUser are enforced at the MVC layer via @Valid, not at the JDBC layer — the repository performs no validation.

@Data
public class AppUser {
    private Long id;

    @NotNull
    @Size(min = 3, message = "Name must be at least 3 characters long")
    private String name;

    @Size(min = 1, message = "You must choose at least 1 appProfile")
    private List<AppProfile> appProfiles = new ArrayList<>();

    public void addAppProfile(AppProfile appProfile) {
        this.appProfiles.add(appProfile);
    }
}

@Data
@AllArgsConstructor
@NoArgsConstructor(access = AccessLevel.PRIVATE, force = true)
public class AppProfile {
    private String id;
    private String name;
    private Role role;

    public enum Role {
        USER, ADMIN, SUPPORT;
    }
}

AppProfileJdbcRepository — Query and CRUD

• jdbcTemplate.query(sql, rowMapper) — the no-varargs overload used in findAll(); passes no parameters. The varargs overload used in findById() places bind parameters after the row mapper — the signature is query(String sql, RowMapper<T> rowMapper, Object... args).
• The row mapper is a method reference (this::mapRowToAppProfile) satisfying the RowMapper<AppProfile> functional interface — no anonymous class boilerplate needed.
• findById uses query() returning a List rather than queryForObject() — intentional: queryForObject() throws EmptyResultDataAccessException on no result instead of returning Optional.empty(), making Optional wrapping awkward without a try-catch.
• appProfile.getRole().name() serializes the enum to its string literal ("USER", "ADMIN", "SUPPORT") for the varchar column. Using .toString() would work here since Role doesn't override it, but .name() is the safe, unambiguous choice.

@Repository
public class AppProfileJdbcRepository implements AppProfileRepository {
    private JdbcTemplate jdbcTemplate;
    private JdbcOperations jdbcOperations;

    public AppProfileJdbcRepository(JdbcTemplate jdbcTemplate, JdbcOperations jdbcOperations) {
        this.jdbcTemplate = jdbcTemplate;
        this.jdbcOperations = jdbcOperations;
    }

    @Override
    public List<AppProfile> findAll() {
        return jdbcTemplate.query("select id, name, role from AppProfile", this::mapRowToAppProfile);
    }

    @Override
    public Optional<AppProfile> findById(String id) {
        List<AppProfile> results = jdbcTemplate.query(
            "select id, name, role from AppProfile where id=?",
            this::mapRowToAppProfile, id);
        return results.size() == 0 ? Optional.empty() : Optional.of(results.get(0));
    }

    private AppProfile mapRowToAppProfile(ResultSet row, int rowNum) throws SQLException {
        return new AppProfile(row.getString("id"), row.getString("name"),
                AppProfile.Role.valueOf(row.getString("role")));
    }

    @Override
    public void save(AppProfile appProfile) {
        jdbcOperations.update("insert into AppProfile (id, name, role) values (?, ?, ?)",
                appProfile.getId(), appProfile.getName(), appProfile.getRole().name());
    }

    @Override
    public void update(AppProfile appProfile) {
        jdbcOperations.update("update AppProfile set name=?, role=? where id=?",
                appProfile.getName(), appProfile.getRole().name(), appProfile.getId());
    }

    @Override
    public void delete(AppProfile appProfile) {
        jdbcOperations.update("delete from AppProfile where id = ?", appProfile.getId());
    }
}

AppUserJdbcRepository — Insert with Generated Keys

• Retrieving auto-generated keys requires a PreparedStatementCreatorFactory — the simple jdbcOperations.update(sql, args...) varargs overload does not support generated key retrieval.
• pscf.setReturnGeneratedKeys(true) sets Statement.RETURN_GENERATED_KEYS on the underlying PreparedStatement — without this, GeneratedKeyHolder.getKey() returns null.
• PreparedStatementCreatorFactory constructor takes the SQL and explicit SQL type constants (Types.VARCHAR) — these map to JDBC type codes and drive the PreparedStatement.setXxx() calls precisely, unlike the varargs overload which infers types via StatementCreatorUtils.
• keyHolder.getKey().longValue() — getKey() returns a Number; call longValue() explicitly. Using intValue() silently truncates IDs above Integer.MAX_VALUE.
• @Transactional on save() wraps the AppUser insert and all AppUserProfileRef inserts in a single transaction — if any profile ref insert fails, the user insert is also rolled back. Without it, a partial failure leaves an orphaned AppUser row.
• saveAppProfiles iterates and inserts one row per profile — no batch update. For large profile sets, JdbcTemplate.batchUpdate() would be more efficient.

@Repository
public class AppUserJdbcRepository implements AppUserRepository {
    private JdbcOperations jdbcOperations;

    public AppUserJdbcRepository(JdbcOperations jdbcOperations) {
        this.jdbcOperations = jdbcOperations;
    }

    @Override
    @Transactional
    public AppUser save(AppUser appUser) {
        PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(
                "insert into AppUser (name) values (?)", Types.VARCHAR);
        pscf.setReturnGeneratedKeys(true);
        PreparedStatementCreator psc = pscf.newPreparedStatementCreator(Arrays.asList(appUser.getName()));

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcOperations.update(psc, keyHolder);

        long appUserId = keyHolder.getKey().longValue();
        appUser.setId(appUserId);

        saveAppProfiles(appUserId, appUser.getAppProfiles());

        return appUser;
    }

    private void saveAppProfiles(long appUserId, List<AppProfile> appProfiles) {
        for (AppProfile appProfile : appProfiles) {
            jdbcOperations.update(
                "insert into AppUserProfileRef (appUserId, appProfileId) values (?, ?)",
                appUserId, appProfile.getId());
        }
    }
}

MVC Integration and Session-Scoped Model

• @SessionAttributes("appUser") persists the AppUser model attribute across requests in the HTTP session — this is how profile selections accumulate across the multi-step form flow before the final save in AppUserController.
• The @ModelAttribute(name = "appUser") method in AppProfileController acts as an initializer: it creates a new AppUser only when one is not already in the session. Once stored, Spring retrieves it from the session on subsequent requests.
• @ModelAttribute (without name) on addAppProfilesToModel runs before every handler in this controller — findAll() is called on every request to this controller, including GET and POST. Cache if this becomes a bottleneck.
• @ExceptionHandler(IllegalArgumentException.class) catches the exception thrown by AppProfile.Role.valueOf() in the row mapper when the DB contains an unrecognized role string — a practical guard against schema/enum drift.
• SessionStatus.setComplete() in AppUserController.processAppUser() clears the appUser session attribute after a successful save, preventing stale data on the next form visit.

@Controller
@RequestMapping("/appProfiles")
@SessionAttributes("appUser")
public class AppProfileController {
    @ModelAttribute
    public void addAppProfilesToModel(Model model) {
        List<AppProfile> appProfiles = appProfileRepository.findAll();
        model.addAttribute("profiles", appProfiles);

        Role[] roles = AppProfile.Role.values();
        for (Role role : roles) {
            model.addAttribute(role.toString().toLowerCase(), filterByRole(appProfiles, role));
        }
    }

    @ModelAttribute(name = "appUser")
    public AppUser appUser() {
        return new AppUser();
    }

    @PostMapping("/appUsers")
    public String addAppProfile(@RequestParam(required = false) String appProfileId,
            @ModelAttribute AppUser appUser, Model model) {
        Optional<AppProfile> appProfile = appProfileRepository.findById(appProfileId);
        if (appProfile.isEmpty()) {
            model.addAttribute("errorMessage", "Invalid profile id: " + appProfileId);
            return "appUserProfileForm";
        }
        appUser.addAppProfile(appProfile.get());
        return "redirect:/appUsers/user";
    }

    @ExceptionHandler(IllegalArgumentException.class)
    public String handleIllegalArgumentException(IllegalArgumentException ex, Model model) {
        model.addAttribute("errorMessage", ex.getMessage());
        return "appUserProfileForm";
    }
}