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

org.springframework.web.HttpMediaTypeNotAcceptableException: Could not find acceptable representation

 the exception happens as Java bean serialization process due to missing getter methods on the bean. 

Init Database in Spring using Hibernate

Instructing JPA creating schema

Spring Application property spring.jpa.hibernate.ddl-auto instructs how JPA creating a database schema.

You can set spring.jpa.hibernate.ddl-auto explicitly; these values  are none, validate, update, create-drop. 

Database categories

Databases are categorized into embedded or real databases.

An embedded database is detected by looking at the Connection type: hsqldb, h2 and derby are embedded, the rest are considered as real databases.

Ddl-auto default value

Spring Boot chooses a default ddl-auto value  according to databae catogories. 

for an embedded database: 

spring.jpa.hibernate.ddl-auto = create-drop

for an real database: 

spring.jpa.hibernate.ddl-auto = none

Be careful when switching from in-memory to a “real” database that you don’t make assumptions about the existence of the tables and data in the new platform. You either have to set ddl-auto explicitly or use one of the other mechanisms to initialize the database.

when spring.jpa.hibernate.ddl-auto = create-drop 

a file named import.sql in the root of the classpath is executed on startup if Hibernate creates the schema from scratch (that is, if the ddl-auto property is set to create or create-drop).

when spring.jpa.hibernate.ddl-auto = none

schema.sql instructing creating schema from scratch.

data.sql populating data into the database. 

Optimistic Lock, Concurrent timestamp

Optimistic concurrency control (OCC) is a concurrency control method applied to transactional systems such as relational database management systems and software transactional memory. OCC assumes that multiple transactions can frequently complete without interfering with each other. While running, transactions use data resources without acquiring locks on those resources. Before committing, each transaction verifies that no other transaction has modified the data it has read. If the check reveals conflicting modifications, the committing transaction rolls back and can be restarted.[1] Optimistic concurrency control was first proposed by H. T. Kung and John T. Robinson.[2]

Setting a time-stamp in a table as a column to store the entry recorded moment; in a transaction, the values are read along with the timestamp, and after operations on these values, at the moment write back newly modified values, the transaction needs to compare the previous timestamp with the current ones recorded at the Current-timestamp cell, if they are the same, then the data is consistent until now; otherwise, the transaction needs to roll back and repeating the previous the process. 

On Cascade Delete

ON DELETE CASCADE

It specifies that the child data is deleted when the parent data is deleted.

When two entities(tables) are associated, one entity pointing to another using an FK. 

A Client has many Orders, then normally FK is kept in Order, and FK references the Client PK. 

The Client is called referenced, or father table; and Order references Client and is also called Children. 

If the referenced Client is deleted, On Delete Cascade means its children are removed by cascaded operations.  

NO ACTION

It is used in conjunction with ON DELETE or ON UPDATE. It means that no action is performed with the child data when the parent data is deleted or updated.

SET NULL

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to NULL when the parent data is deleted or updated.

SET DEFAULT

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to their default values when the parent data is deleted or updated.

Data Integrity Issue: 

when removing the father, Children will complain because of the violation of the data integrity. The Children depend on the father, it needs to remove the Children first then removing the father. 

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.

 

JDBC Connection Thread Safety

 

 The problem with many JDBC drivers is that only one thread can use a Connection at any one time --- otherwise, a thread could send a query while another one is receiving results, and this could cause severe confusion.

The PostgreSQL™ JDBC driver is thread-safe. Consequently, if your application uses multiple threads then you do not have to worry about complex algorithms to ensure that only one thread uses the database at a time.

If a thread attempts to use the connection while another one is using it, it will wait until the other thread has finished its current operation. If the operation is a regular SQL statement, then the operation consists of sending the statement and retrieving any ResultSet (in full). If it is a fast-path call (e.g., reading a block from a large object) then it consists of sending and retrieving the respective data.

This is fine for applications and applets but can cause a performance problem with servlets. If you have several threads performing queries then each but one will pause. To solve this, you are advised to create a pool of connections. Whenever a thread needs to use the database, it asks a manager class for an object. The manager hands a free connection to the thread and marks it as busy. If a free connection is not available, it opens one. Once the thread has finished using the connection, it returns it to the manager which can then either close it or adds it to the pool. The manager would also check that the connection is still alive and remove it from the pool if it is dead. The downside of a connection pool is that it increases the load on the server because a new session is created for each object. It is up to you and your applications' requirements.

SMTP Sever Listening Port

As sending an email in Java or C sharp, it needs to connect to an SMTP server to send out the email message. 

The  Modern secured SMTP server is listening on port 587.