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

Insert OffsetDateTime into PostgreSQL

 The SQL standard differentiates timestamps with time zone and without time zone literals by the presence of a "+" or "-" symbol and time zone offset after the time. Hence, according to the standard,

Without time-zone

'1999-01-08 04:05:06'                        

With time-zone

'1999-01-08 04:05:06 -8:00'

PostgreSQL PSQL commands

PSQL is an interactive terminal to work with the PostgreSQL database. It is used to query data from the PostgreSQL database server faster and more effectively.

Connecting to PostgreSQL container via PSQL

psql -h localhost -p Port -U username -d databasename

• \l: List all databases (or \list).
• \d: Display all tables, indexes, views, and sequences.
• \dt: Display all tables.
• \di: Display all indexes.
• \dv: Display all views.
• \ds: Display all sequences.
• \dT: Display all types.
• \dS: Display all system tables.
• \du: Display all users.
• \?: show all psql commands.
• \c dbname [username]: Connect to database, with an optional username (or \connect).
• \h sql-cmd: show syntax on sql command
• SELECT version();  :  Retrieve the current version of PostgreSQL server
• \g: execute the last command again
• \s: display command history
• \s: filename: Save the command history to a file
• \i: file: execute psql commands from a file
• \e: editor
• \H: switch the output to HTML
• \a: switch from aligned to the non-aligned column output
• \q: Exit psql shell

 

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.