Category Archives: Data Access

This section will teach you how to add database access to the Spring Boot applications using the following elements:

  • A running database, whether initiated by/embedded within your application or
    simply accessible to your application
  • Database drivers enabling programmatic access, usually provided by the database vendor
  • A Spring Data module for accessing the target database.

How to debug SQL statements in Spring Boot applications

Spring boot auto-configuration does not require a dedicated logging configuration file. So, in order to debug your SQL Statements in Spring Boot applications, by default can use the following settings in your application.properties file:

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

When this configuration is added, you will be able to see:

1) Each SQL Statement executed

2) The timing information so that you can find slow SQL queries

3) The single parameters used to bind your Prepared Statements

On the other hand, if you are using a YAML file to configure Spring Boot, the following configuration will do:

spring:
  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: true
logging:
  level:
    org:
      hibernate:
        type: trace

The default logging is good enough for most scenarios. But sometimes in enterprise applications, we need more fine control over logging with other complex requirements. In that case, having a dedicated logging configuration is suitable.

Spring boot by default uses logback, so to customize it’s behavior, all we need to add only logback.xml in classpath and define customization over the file.

Within the XML configuration file, you can add the following appender to debug SQL Statements:

<logger name="org.hibernate.SQL" level="trace" additivity="false">
    <appender-ref ref="file" />
</logger>

To get bind variables as well:

<logger name="org.hibernate.type.descriptor.sql" level="trace">
    <appender-ref ref="file" />
</logger>

In conclusion, we have covered several ways to debug / log SQL statements in Spring Boot applications.

How to add initial data in Spring Boot JPA applications

In order to have some data inserted at application start up, all you have to do is creating a data.sql file in your src/main/resources folder and it will be automatically executed on startup. Within this file, place your SQL INSERT Statements:

INSERT into CUSTOMER(id,name,surname) VALUES (1,'aaaa','bbbbbb') INSERT into CUSTOMER(id,name,surname) VALUES (2,'cccc','dddddd')

On the other hand, if you want to execute DDL, such as CREATE TABLE statements, you have to place them into a src/main/resources/schema.sql file

CREATE TABLE task (   id          INTEGER PRIMARY KEY,   description VARCHAR(64) NOT NULL,   completed   BIT NOT NULL);

Consider, however, that Tables are by default created by Spring Boot if you include Entity objects for an in memory database like H2. If you still want to use schema.sql you’ll have to disable this feature by adding this to your application.properties:

spring.jpa.hibernate.ddl-auto=none

Finally, it is worth mentioning that if you’re using Spring boot 2, database initialization only works for embedded databases (H2, HSQLDB, …). If you want to use it for other databases as well, you need to change the spring.datasource.initialization-mode property:

spring.datasource.initialization-mode=always

That’s all! We have demonstrated in this tutorial how to add initial data in Spring Boot JPA applications, by adding SQL scripts and DDL statements in your resources folder.