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