Logging

• hibernate.show_sql (niet in prod!)
• hibernate.format_sql
• hibernate.use_sql_comments

params: - org.hibernate.type.descriptor.sql level trace

P6Spy

pom.xml

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version> <!-- You can use the latest version -->
</dependency>

application.properties

spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/your_database
spring.datasource.username=your_username
spring.datasource.password=your_password

databaseDialectDateFormat=yyyy-MM-dd'T'HH:mm:ss.SSSZ
customLogMessageFormat=%{currentTime}|%(executionTime)ms|%(category)|connection %(connectionId)|\n%(sqlSingleLine)

modulelist=com.p6spy.engine.outage.P6OutageFactory,com.p6spy.engine.logging.P6LogFactory
logfile=spy.log
logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
appender=com.p6spy.engine.spy.appender.Slf4JLogger
databaseDialectDateFormat=yyyy-MM-dd HH:mm:ss.SSS

•   modulelist: Specifies which P6Spy modules to enable.
•   logfile: Defines where the log file will be created.
•   appender: Determines where logs should be sent, such as a file or SLF4J for logging through a standard logging framework.

Datasource-proxy

@Bean
public DataSource dataSource() {
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    loggingListener.setQueryLogEntryCreator(new InlineQueryLogEntryCreator());

    return ProxyDataSourceBuilder.create(actualDataSource())
        .name(DATA_SOURCE_PROXY_NAME)
        .listener(loggingListener)
        .build();
}

• can have own cutom statement execution listeners

FetchSize

statement.setFetchSize(fetchSize) 10 Oracle 120 SQL PostreSQL MySQL whole resultset in single roundtrip

JPA2.2 getResultStream dan wel zetten voor PostgresQL en MySQL

Streams

MySQL streaming

One Record

statement.setFetchSize(Integer.MIN_VALUE)

Multiple Records

statement.setFetchSize(fetchSize)

Oracle

spring.jpa.properties.hibernate.jdbc.fetch_size=50

MySQL Postgres

@Query("""
    select p
    from Post p
    where date(p.createdOn) >= :sinceDate
""")
@QueryHints(
    @QueryHint(name = AvailableHints.HINT_FETCH_SIZE, value = "25")
)
Stream<Post> streamByCreatedOnSince(
    @Param("sinceDate") LocalDate sinceDate
);

Pagination

Data grows per page

• FETCH FIRST N ROWS ONLY
• FETCH NEXT N ROWS ONLY
• OFFSET M ROWS Oracle 12c SQL2012 PostgresQL 8.4

let op ORDER BY

Top-N

SELECT title
FROM post
ORDER BY created_on DESC, id DESC
FETCH FIRST 5 ROWS ONLY

Next-N

SELECT title
FROM post
ORDER BY created_on DESC, id DESC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY

PostgreSQL MySQL TOP-N

SELECT title
FROM post
ORDER BY created_on DESC, id DESC
LIMIT 5

PostgreSQL MySQL NEXT-N

SELECT title
FROM post
ORDER BY created_on DESC, id DESC
LIMIT 5
OFFSET 5

NB: OFFSET komt NA LIMIT!!!

JPQL Querying -Pagination

Page<Post> findAllByTitle(
    @Param("titlePattern") String titlePattern,
    Pageable pageRequest
);

@Query("""
    select p
    from Post p
    where p.title like :titlePattern
""")
Page<Post> findAllByTitle(
    @Param("titlePattern") String titlePattern,
    Pageable pageRequest
);

JPQL QUERY Pagination Top-N

Page<Post> posts = postRepository.findAllByTitle(
    "High-Performance Java Persistence %",
    PageRequest.of(0, 25, Sort.by("createdOn"))
);

SELECT p.id, p.created_on, p.title
FROM post p
WHERE p.title LIKE 'High-Performance Java Persistence %' ESCAPE ''
ORDER BY p.created_on ASC
OFFSET 0 ROWS
FETCH FIRST 25 ROWS ONLY

@Query(value = """
    SELECT p.id, p.title, p.created_on
    FROM post p
    WHERE p.title ilike :titlePattern
    ORDER BY p.created_on
""",
nativeQuery = true)
Page<Post> findAllByTitleLike(
    @Param("titlePattern") String titlePattern,
    Pageable pageRequest
);

Top-N

Page<Post> posts = postRepository.findAllByTitle(
    "High-Performance Java Persistence %",
    PageRequest.of(0, 25)
);

SELECT p.id, p.title, p.created_on
FROM post p
WHERE p.title ilike 'High-Performance Java Persistence %'
ORDER BY p.created_on
FETCH FIRST 25 ROWS ONLY

Offset pagination index scanning performance

CREATE INDEX idx_post_created_on ON post (created_on DESC, id DESC);

SELECT id
FROM post
ORDER BY created_on DESC
LIMIT 50;

Limit  (cost=0.28..2.51 rows=50 width=16)
  (actual time=0.013..0.022 rows=50 loops=1)
  -> Index Scan using idx_post_created_on on post p
     (cost=0.28..223.28 rows=5000 width=16)
     (actual time=0.013..0.019 rows=50 loops=1)
Planning time: 0.113 ms
Execution time: 0.055 ms

2e en latere scan

SELECT id
FROM post
ORDER BY created_on DESC
LIMIT 50
OFFSET 50;

Limit  (cost=2.51..4.74 rows=50 width=16)
  (actual time=0.032..0.044 rows=50 loops=1)
  -> Index Scan using idx_post_created_on on post p
     (cost=0.28..223.28 rows=5000 width=16)
     (actual time=0.022..0.040 rows=100 loops=1)
Planning time: 0.198 ms
Execution time: 0.071 ms

Nu 100rows!!! Op de laattste pagina wordt alles gescanned... 1.190ms..... OFFSET doesnt seek/traverse

Seek or keyset pagination

SELECT id, created_on
FROM post
ORDER BY created_on DESC, id DESC
LIMIT 50

created_on EN id moeten ERIN

Next-N

SELECT id, created_on
FROM post
WHERE (created_on, id) < ('2024-10-02 21:00:00.0', 4951)
ORDER BY created_on DESC, id DESC
LIMIT 50;

•   The row value expression (a, b) < (c, d) is PostgreSQL and MySQL.
•   It’s equivalent to a < c | (a = c & b < d).

Nu wel max 50 results in set

Spring Data JPA - WindowIterator

Oplossing!

WindowIterator<PostComment> commentWindowIterator = WindowIterator.of(
    position -> postCommentRepository.findByPost(
        post,
        PageRequest.of(
            0, pageSize,
            Sort.by(
                Sort.Order.desc(PostComment_.CREATED_ON),
                Sort.Order.desc(PostComment_.ID)
            )
        ),
        position
    )
).startingAt(ScrollPosition.keyset());

commentWindowIterator.forEachRemaining(this::processPostComment);

met Blaze Persistance Top-N

// Blaze Persistence – Keyset pagination
PagedList<Post> firstPage = cbf
    .create(entityManager, Post.class)
    .orderByAsc(Post_.CREATED_ON).orderByAsc(Post_.ID)
    .page(0, pageSize)
    .withKeysetExtraction(true)
    .getResultList();

SELECT p.id, p.created_on, p.title,
       (SELECT count(*) FROM post)
FROM post p
ORDER BY p.created_on, p.id
OFFSET 0
ROWS FETCH FIRST 25 ROWS ONLY;

Next N

// Blaze Persistence – Keyset pagination
PagedList<Post> nextPage = cbf
    .create(entityManager, Post.class)
    .orderByAsc(Post_.CREATED_ON).orderByAsc(Post_.ID)
    .page(postPage.getKeysetPage(),
          postPage.getPage() * postPage.getMaxResults(),
          postPage.getMaxResults())
    .getResultList();

SELECT p.id, p.created_on, p.title,
       (SELECT count(*) FROM post)
FROM post p
WHERE ('2024-09-09 12:10:00.0', 10) < (p.created_on, p.id)
ORDER BY p.created_on, p.id
OFFSET 0
ROWS FETCH FIRST 25 ROWS ONLY;

Projections

Fetching too many columns

Instead of fetching all columns:

SELECT *  
FROM post_comment pc  
LEFT JOIN post p ON p.id = pc.post_id  
LEFT JOIN post_details pd ON p.id = pd.id  

Fetch a custom SQL projection:

SELECT pc.id, pc.review  
FROM post_comment pc  
LEFT JOIN post p ON p.id = pc.post_id  
LEFT JOIN post_details pd ON p.id = pd.id  

(Joins zijn niet nodig... => dus tweede is dan sneller)

Tuple projection

not type save

• The JPA Tuple wraps the default Object[] projection and allows you to retrieve the column values via their aliases.

List<Tuple> commentTuples = postRepository.findAllCommentTuplesByPostTitle(titleToken);
Tuple commentTuple = commentTuples.get(0);

long id = commentTuple.get("id", Number.class).longValue();
String title = commentTuple.get("title", String.class);

Interface-based projection

• If you want a type-safe projection, Spring Data JPA provides the option of wrapping the result in a Proxy based on a given interface.

The Interface-based projection can be used like this:

@Query("""
select
p.id as id,
p.title as title,
c.review as review
from PostComment c
join c.post p
where p.title like :postTitle
order by c.id
""")
List<PostCommentSummary> findAllCommentSummariesByPostTitle(
@Param("postTitle") String postTitle
);

// nu type safe!       
Long id = commentSummary.getId();
String title = commentSummary.getTitle();

Met ingebakken (!) DTOs en Records

DTOs

properties.put(
    "hibernate.integrator_provider",  // Hibernate property for custom integrator
    (IntegratorProvider) () -> Collections.singletonList( // Lambda to provide integrator
        new ClassImportIntegrator( // Hypersistence Utils integrator
            List.of( // List of classes to register
                PostCommentDTO.class,  // First DTO class
                PostCommentRecord.class // Second DTO class
            )
        )
    )
);

String jpql = "SELECT new PostCommentDTO(pc.id, pc.comment) FROM PostComment pc WHERE pc.post.id = :postId";

Records

public record PostCommentDTO(Long id, String comment) {}
public record PostCommentRecord(Long id, String content) {}

properties.put(
    "hibernate.integrator_provider",
    (IntegratorProvider) () -> Collections.singletonList(
        new ClassImportIntegrator(
            List.of(
                PostCommentDTO.class,   // Register the record as a DTO
                PostCommentRecord.class // Register additional records as needed
            )
        )
    )
);

@Query(
    value = "SELECT pc.id, pc.comment FROM post_comment pc WHERE pc.post_id = :postId",
    nativeQuery = true
)
List<PostCommentDTO> findCommentsByPostId(@Param("postId") Long postId);

Long id = commentRecord.id();
String title = commentRecord.title();

TupleTransformer and ResultListTransformer

The SQL projection can contain data from multiple tables, like the post and post_comment, which form a one-to-many table relationship.

SELECT p.id AS p_id,
p.title AS p_title,
pc.id AS pc_id,
pc.review AS pc_review
FROM post p
JOIN post_comment pc ON p.id = pc.post_id
ORDER BY pc.id

komt uit:

SQL

Structured query language

show databases;

use dbname;

show tables;

desc users;

-- use a db
use testdb;

-- use a delimter
delimiter $$

create procedure HelloWorld()
begin
select "Hello WOlrd!";
end$$

delimiter ;

drop procedure HelloWorld;

call HelloWorld();