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.M2</version>

The usual suspects

  • hsqldb

  • lombok

  • junit

  • spring-test

  • assertj

Configuration

@EnableJdbcRepositories
public class SpringleticsConfiguration {

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

Configuration (Template)

@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(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 = ?

Methods in CrudRepository

<S extends T> S
            save(S entity);
<S extends T> Iterable<S>
            saveAll(Iterable<S> entities);
Optional<T> findById(ID id);
boolean     existsById(ID id);
Iterable<T> findAll();
Iterable<T> findAllById(Iterable<ID> ids);
long        count();
void        deleteById(ID id);
void        delete(T entity);
void        deleteAll(Iterable<? extends T> entities);
void        deleteAll();

NamingStrategy

PrefixNamingStrategy

class PrefixNamingStrategy implements NamingStrategy {
  private Map<Class, String> columnPrefix = new HashMap<>();

  { columnPrefix.put(Workout.class, "WO"); }

  public String getColumnName(JdbcPersistentProperty property) {
    return columnPrefix.get(property.getOwner().getType())
        + "_"
        + NamingStrategy.super.getColumnName(property);
  }

  public String getTableName(Class<?> type) {
    return "T_" + NamingStrategy.super.getTableName(type);
  }
}

Dynamic NamingStrategy

private final ThreadLocal<String> schema = new ThreadLocal<>();

new NamingStrategy() {

  @Override
  public String getSchema() {
    return schema.get();
  }
};

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 Statments

// 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!

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.

Auditing

Better CRUD

upserts?

Derived Queries

Sorting and Paging

Named Queries with MyBatis

Named Queries via Properties

Spring Boot Support

TOQAEFT

The One Question Asked Every Freaking Time

Is there reactive support?

Not yet

JDBC is blocking

Might wrap it in a ThreadPool

Oracle Works on an Async SQL-API

Not Flow/Reactive Stream based

R2DBC

Reactive Relational Database Connectivity

There is a project exploring possibilities: https://github.com/nebhale/r2dbc

References

Jens Schauder

Vater, Spieler, Läufer, Boulderer

+

JUG Organizer