SpringBoot Output JPA Queries

Output SQL queries

The simplest way to output SQL queries, but it is not recommended. it directly uploads to standard output without any optimizations of a logging framework. Moreover, it doesn't log the parameters of prepared statements.

spring.jpa.show-sql=true

spring.jpa.properties.hibernate.format_sql=true

Output SQL queries via loggers

The hibernate logs will be sent to the configured appender. By default, Spring Boot uses Logback with a standard out appender. 

logging.level.org.hibernate.SQL=DEBUG 

logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Spring JPA Query

in construction

Spring Data JPA  Queries 

• Derived query, a method signature query
• @Query including JPQL or Native SQL 
• DSL query: type-safed, compiling time error checking 
• Projections: interface-based(closed projections or open projections); class(DTO)-based

Derived Query (Simple Query Method Signature )

This method is applied on the entity level. 

a. return type; b. friendly; c. Entity AttributeName(camelCase) d. query parameters matching entity attribute type

The query method can span multiple nested entities, chaining the attribute names and following the camelCase rules. 

List findByPersonAddressCity(String city); 

filters and, LessThan, GreatThan, Contains, Like, containing(string) IgnoreCase; between(a value and b value);  Sort the attributes: OrderBy;

Error reporting: Spring Data JPA facilitates fast failure; it avoids the errors that are thrown in the runtime. 

If the property is misspelled, getting a PropertyReferenceException: no property X found for type Y.  

@Query method

When the query method signature becomes over-complicated, it may be using @Query decorated on the top of a repository method; it could be a JPQL query or native SQL query.  Queries declared by @Query on the top of repository interface methods, take precedence over the named queries.

By default, JPA using position-based parameter binding.  Using named parameter in the query, rather than parameter positioning, ': name' ref. by '@Param("name")', may make the query less error-prone. 

DSL(dynamic query)

sort can be done providing sort or pageable.

Sort sort = new Sort(Sort.Direction.Asc, "title);
bookRepository.findByTitleContains("Hibernate",sort)

Limit the number of results.
findFirst5By

Pagination

Pageable

PageRequestOf.

Projection Types

Instead of using an Entity projection, returning the whole of aggregate root and/or its dependencies; projections may limiting the amount of returned attributes from a database query to what we need; it may optimize the underlying generated SQL query, reducing the query operations on the database and therefore improve the DB performance. 

Interface-based projection

Spring relies on the interface to create a proxy to wrap an entity so as to modify the entity behaviors.

closed projection

a closed projection means a method name exactly matches an attribute name.

a constrain: the closed projection interface is only used as an element type of a collection.

a closed project may carry out a nested projection, but it must root on the owner side, otherwise, on the inverse side, it doesn't have a reference to the nested entity.

open projection

An open projection decorated with SpEL enables us to define interface methods with unmatched names and with return values re-computed at a runtime.

drawback: its query is created during the runtime, so Spring cannot optimize the query in the advance.

Class-based projection

instead of defining interfaces and allowing Spring to create proxy around them, we may create our own classes to project from the root entity via the repository.

a constraint: the class overrides hashcode and equal(the class may be handled in a collection); constructor parameter name must be the same as the counterparts declared in the root entity.

Dynamic projection

a root entity is queried through a repository, it may have different views; the dynamic projection offers a genric way to combine the root entity and its views in one query method.

<T>  List<T> findByLastName(String lastName, Class<T> class); 

Reference

Spring Data JPA Tutorial: Creating Database Queries From Method Names

@Query Annotation in Spring Data JPA

Java 8 OffsetDateTime and JPA 4.2 suported

OffsetDateTime consists of DateTime and the offset from the UTC, for instance

 Offset DateTime: 2021-06-23T11:27:41.622253200+02:00

It includes the current local date and time, 9-digit nanosecond, and then followed by a time zone

Java 8 has introduced java.time.packages and the JDBC 4.2 API added support for the additional SQL types

  TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE.

 

  @Column(name = "offset_time", columnDefinition = "TIME WITH TIME ZONE")

  private OffsetTime offsetTime;

  @Column(name = "offset_date_time", columnDefinition = "TIMESTAMP WITH TIME ZONE")

  private OffsetDateTime offsetDateTime;

  

  Before Java 8 and JPA 2.2, developers usually had to convert date/time types to UTC before persisting them.   JPA 2.2 now supports this feature out of the box by supporting the offset to UTC and by leveraging JDBC 4.2  support for the timezone.

JPA @Column attribute column definition  (Optional) The SQL fragment that is used when generating the DDL for the column.

 

could not initialize proxy - no Session;

{"id":1,"firstName":"string","lastName":"string","email":"ynz@hotmail.com","orders"}{"message":"Could not write JSON: failed to lazily initialize a collection of role: com.ynz.demo.springjpatransaction.entities.Customer.orders, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: com.ynz.demo.springjpatransaction.entities.Customer.orders, could not initialize proxy - no Session (through reference chain: com.ynz.demo.springjpatransaction.entities.Customer[\"orders\"])"}

Cause: On the surface, the exception comes from JSON parsing and meeting unexpected char; the fundamental is due to the orders are lazily fetched, but accessed outside a live persistence session. After the entity-manager loading the customer into the persistence context, and then it closed the session; afterwards accessing entity Order become impossible, and cause this exception.

Solution: keeping the persistence session open until the moment accessing the lazily loaded Entities. The optimal solution: using the Fetch join clause to fetch entity root and its aggregated entities by one query. The worst solution: making the aggregate entities become Eager-fetched, this will cause an N+1 problem, and therefore slowing down the system performance.

How to create Many to Many relationship in JPA

Two entities within Many to many relationships are associated with a junk table. There is no side that must be an owner side or inverse side, meaning that any sider can be an owner side. 

@JoinTable defines an owner side; MappedBy defines the inverse side. By this way, both entities are acknowledged to be associated in a many-to-many relationship.

Owner side: 

Inverse side: 

And it generates a junk table in the database