Spring Data JDBC

 
 

Press S for speaker notes

Talk Motto

If you are not embarrassed by the first version of your product, you’ve launched too late.

— Reid Hoffman

Default Answer

  • No that feature doesn’t exist yet.

  • We accept pull requests.

Spring Data

JPA vs Spring Data Jdbc

JPAs complexity

Lazy Loading (Exception)

Dirty Checking

Session / 1st Level Cache

Proxies for Entities

Map almost anything to anything

Spring Data JDBCs Design Choices

No Lazy Loading

No Caching

No Proxies

No deferred flushing

Very simple & limited & opinionated ORM

Simplicity is King

Complexity is tempting

Consider Spring Data JDBC

if you …​

want to custom code most SQL anyway

find yourself fighting JPA

want to have DDD Aggregates

(but still want to use a RDBMS)

CrudRepository

Dependencies

<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jdbc</artifactId>
<version>1.0.0.M3</version>

The usual suspects

  • hsqldb

  • lombok

  • junit

  • spring-test

  • assertj

Configuration

@EnableJdbcRepositories
class SpringleticsConfiguration {

  @Bean
  DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.HSQL)
        .addScript("create-schema.sql")
        .generateUniqueName(true)
        .build();
  }

Configuration (Template)

@Bean
NamedParameterJdbcTemplate template(
    DataSource db
) {
  return new NamedParameterJdbcTemplate(db);
}

Entity

public class Workout {

  @Id
  Long id;
  String name;
  Focus focus;
}

Entity

All you need is an @Id

  • getters/setters are optional

  • default constructor is optional

Repository

public interface WorkoutRepository
    extends CrudRepository<Workout, Long>

Schema

CREATE TABLE Workout (
  id IDENTITY,
  name VARCHAR(200),
  focus VARCHAR(20)
);
INSERT INTO workout (name) VALUES ('Starbuxman');

Create, Read

Workout workout = new Workout();
workout.name = "Juergen Hoeller";
workout.focus = Focus.ENDURANCE;

Workout saved = repository.save(workout);

assertThat(repository.findById(saved.id)
    .isPresent()).isTrue();

Update, Delete

saved.name = "Jürgen Höller";
repository.save(saved);

repository.deleteById(saved.id);

Log Output

INSERT INTO Workout (name, focus) VALUES (?, ?)
SELECT Workout.id AS id, Workout.name AS name, Workout.focus AS focus
       FROM Workout WHERE Workout.id = ?
UPDATE Workout set id = ?, name = ?, focus = ? where id = ?
DELETE FROM Workout where id = ?

CrudRepository Save Methods

<S extends T> S           save(S entity);
<S extends T> Iterable<S> saveAll(
                    Iterable<S> entities);

CrudRepository Read Methods

Optional<T> findById(ID id);
boolean     existsById(ID id);
Iterable<T> findAll();
Iterable<T> findAllById(Iterable<ID> ids);
long        count();

CrudRepository Delete Methods

void        deleteById(ID id);
void        delete(T entity);
void        deleteAll(Iterable<? extends T>
                    entities);
void        deleteAll();

NamingStrategy

public interface NamingStrategy {
    String getSchema();
    String getTableName(Class<?> type);
    String getQualifiedTableName(Class<?> type);

    String getColumnName(
        JdbcPersistentProperty property);
    String getReverseColumnName(
        JdbcPersistentProperty property);
    String getKeyColumn(
        JdbcPersistentProperty property);
}

Non trivial Aggregates

Design Challenge

CRUD Operations for entities with references

aka ORM

Springletics Workout

class Workout {
    // ...
    List<Exercise> exercises
    // ...
}

Pseudo Code

wo = createWorkout();
wo = repository.save(wo);
wo = changeWorkout(wo);
repository.save(wo);

SQL Statements

// first save
INSERT INTO Workout (name, focus) VALUES (?, ?)
INSERT INTO Exercise (name, description, Workout, Workout_key) VALUES (?, ?, ?, ?)

// second save
DELETE FROM Exercise WHERE Workout = ?
UPDATE Workout set id = ?, name = ?, focus = ? WHERE id = ?
INSERT INTO Exercise (name, description, Workout, Workout_key) VALUES (?, ?, ?, ?)

WAT?

  • DELETE ALL referenced Entities!

  • UPDATE Aggregate Root

  • REINSERT ALL referenced Entities

What will improve

  • DELETE ALL formerly referenced Entities

  • UPDATE Aggregate Root

  • UPDATE still referenced Entities

  • INSERT new Entities

⇒ No Many-to-X relations!

Repositories work on Aggregates

Aggregates are the basic element of transfer of data storage - you request to load or save whole aggregates.

https://martinfowler.com/bliki/DDD_Aggregate.html
— Martin Fowler

Aggregate

Aggregate is a pattern in Domain-Driven Design. A DDD aggregate is a cluster of domain objects that can be treated as a single unit.

https://martinfowler.com/bliki/DDD_Aggregate.html
— Martin Fowler

Aggregate Root

An aggregate will have one of its component objects be the aggregate root. Any references from outside the aggregate should only go to the aggregate root. The root can thus ensure the integrity of the aggregate as a whole.

https://martinfowler.com/bliki/DDD_Aggregate.html
— Martin Fowler

Many to X relations

If multiple Aggregate roots reference X,

X must be another Aggregate Root.

⇒ Not part of this Aggregate.

What is part of the Aggregate?

Everything referenced.

What to do

  • Just reference an id.

But I don’t like that!

  • That’s ok.

Use

  • @Query

  • Custom method implementations

  • Custom base repository

Just consider for a moment

  • It is a strong constraint

  • It might improve your architecture

Events

Register a Listener

class BeforeSaveListener
    implements ApplicationListener<BeforeSaveEvent> {
  @Override
  public void onApplicationEvent(BeforeSaveEvent event) {
    // do what ever you want
  }
}

Available Events

  • BeforeSaveEvent

  • AfterSaveEvent

  • BeforeDeleteEvent

  • AfterDeleteEvent

  • AfterLoadEvent - when an Aggregate got instantiated from the database

AggregateChange

  • A model of the changes to be applied to the database

  • contains a mutable list of DbAction s.

DbAction

Represents a single SQL statement to be executed against the database.

Do whatever you want

  • Access the repository

  • change the entities

  • Change the AggregateChange!

Auditing

Enable Auditing Support

@EnableJdbcRepositories
@EnableJdbcAuditing
class AuditingConfiguration {

Add fields

@CreatedDate Instant created;
@LastModifiedDate Instant modified;

@CreatedBy String createdBy;
@LastModifiedBy String modifiedBy;

Register AuditAware

@Bean
AuditorAware<String> auditorAware() {
  return () -> Optional.of("Jens Schauder");
}

MyBatis

How?

CRUD methods get translated into DbActions

Each action gets mapped to a MyBatis query id.

If not found default implementation is used.

Example: Deleting all via a given property path referenced entities → deleteAll.<propertyPath>

Dependencies

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.4.5</version>
</dependency>
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis-spring</artifactId>
  <version>1.3.1</version>
</dependency>

Context

@Bean
SqlSessionFactoryBean sessionFactory(DataSource db) {

  org.apache.ibatis.session.Configuration config =
      new org.apache.ibatis.session.Configuration();
  config.getTypeAliasRegistry()
      .registerAlias("MyBatisContext", MyBatisContext.class);

  config.getTypeAliasRegistry().registerAlias(Workout.class);
  config.addMapper(WorkoutMapper.class);

  SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
  bean.setDataSource(db);
  bean.setConfiguration(config);

  return bean;
}

Context

@Bean
SqlSessionTemplate session(SqlSessionFactory factory) {
  return new SqlSessionTemplate(factory);
}

Context

  @Bean
  DataAccessStrategy dataAccessStrategy(
      JdbcMappingContext context,
      SqlSession sqlSession
  ) {
    return MyBatisDataAccessStrategy
        .createCombinedAccessStrategy(context, sqlSession);
  }
}

Mapping

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="de.schauderhaft.spring.data.jdbc.talk.WorkoutMapper">
  <select id="findById" resultType="Workout">
    SELECT
      id,
      'Name based on an id-' || id AS name,
      focus
    FROM Workout
    WHERE id = #{id}
  </select>
</mapper>

Thanks!

Kazuki Shimizu

kazuki43zoo

@Query

@Query

@Query("SELECT * FROM WORKOUT WHERE " +
    "NAME like '%' || :name || '%'")
List<Workout> findByName(
    @Param("name") String name
);

@Modifying

@Modifying
@Query("DELETE FROM WORKOUT WHERE " +
    "NAME like '%' || :name || '%'")
Long deleteByName(@Param("name") String name);

RowMapper

@Query(value = "VALUES ('Dummy-Workout')",
    rowMapperClass = DummyRowMapper.class)
Workout wonkyWorkout();

RowMapper

class DummyRowMapper implements RowMapper<Workout> {
  @Override
  public Workout mapRow(ResultSet rs, int rowNum)
      throws SQLException {

    Workout workout = new Workout();
    workout.name = rs.getString(1);
    return workout;
  }
}

Integration with SD-Rest

Make it a bootiful Webapplication

Spring Data JDBC specific

Almost nothing.

Entities need to be Beans for JSON serializer.

(Maybe) upcoming

Not promising anything.

Better CRUD

upserts?

Derived Queries

Sorting and Paging

Named Queries with MyBatis

Named Queries via Properties

Spring Boot Support

Is there reactive support?

The problem

JDBC is blocking

Could be wrapped in a ThreadPool which would be a compromise.

JPA won’t happen

The whole approach is opposite to reactive.

ADBA

Asynchronous DataBase Api being developed by Oracle

AoJ

ADBA over JDBC

An rudimentary implementation of ADBA based on JDBC.

R2DBC

Reactive Relational Database Connectivity

R2DBC Contributers

  • Pivotal (Spring, Spring Data, Reactor)

  • jOOQ (Lukas Eder)

  • RxJava (Dávid Karnok)

R2DBC Experimental Implementations

Planned: R2DBC over ADBA

R2DBC @ Spring Data

Database Client inspired by Webclient.

Reactive Relational Repositories.

Reactive Wrap up

Lots is happening.

Many parties involved.

Nothing that is production ready in the near future.

References

Jens Schauder

Dad, Board&Roleplaying Gamer, Runner, Freeletics Masochist

+

JUG Organizer