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 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.

Create a JPA Application using Groovy and Spring Boot

You can create a Spring Boot application using Groovy either from the IDE or using the online Spring Boot application generator http://start.spring.io and selecting Groovy as the language.

You’ll now see how to develop a simple Spring Boot web application using Groovy, Spring Data JPA, and Thymeleaf. Add the Web, Thymeleaf, JPA, and H2 starters dependencies to your application.

spring init --language=groovy -dweb,thymeleaf,jpa,h2 demogroovy 

Create a JPA entity called Customer.groovy:

package com.example.demogroovy;
import javax.persistence.*@Entity @Table(name = "CUSTOMER")
class Customer {
  @Id @GeneratedValue(strategy = GenerationType.AUTO)
  Long id
  String name
  String email
}

As you are using Groovy, you don’t need to create setters and getters for your entity properties.

Create a Spring Data JPA repository for the Entity:

package com.example.demogroovy;

import org.springframework.data.jpa.repository.JpaRepository;

interface CustomerRepository extends JpaRepository<Customer, Long> {
  Customer findByName(String name);
}

Next, create a SpringMVC controller to show the list of customers, as shown here:

package com.example.demogroovy;
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Controller
import org.springframework.ui.Model
import org.springframework.web.bind.annotation.GetMapping @Controller class HomeController {
  @Autowired CustomerRepository repo;
  @GetMapping("/") String home(Model model) {
    model.addAttribute("customers", repo.findAll())
    "home"
  }
}

Create the Thymeleaf view src/main/resources/templates/home.html to render customers:

<html xmlns:th="http://www.thymeleaf.org">
     
    <head>
        <META http-equiv="Content-Type" content="text/html; charset=UTF-8">
             
        <title>Customers List</title>
             
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
         
    </head>
     
    <body>
         
        <table>
                 
            <thead>
                     
                <tr>
                             <th>Id</th>         <th>Name</th>     
                </tr>
                     
            </thead>
                 
            <tbody>
                     
                <tr th:each="Customer : ${customers}">
                             <td th:text="${customer.id}">Id</td>         <td th:text="${customer.name}">Name</td>     
                </tr>
                     
            </tbody>
             
        </table>
         
    </body>
     
</html>

When you generate the application, the main entry point class is created, as shown here:

package com.example.demogroovy;
import org.springframework.boot.SpringApplication
import org.springframework.boot.autoconfigure.SpringBootApplication @SpringBootApplication class DemoApplication {
  static void main(String[] args) {
    SpringApplication.run(DemoApplication, args)
  }
}

You can initialize the database with sample data using a SQL script in src/main/resources/data.sql

insert into customer(id, name, email) values (1,'john','john@gmail.com'), (2,'frank','frank@gmail.com'), (3,'test','test@gmail.com'); 

Finally, include in your src/main/resources/application.properties the following property that will auto-generate the customer table at start-up:

spring.jpa.hibernate.ddl-auto = update 

Now you can run the application by executing:

 ./mvnw spring-boot:run

If you point your browser to http://localhost:8080/, you should be able to see customer details.

How to connect to H2 Console in Spring Boot

In order to enable H2 In-Memory Database in Spring Boot applications you have to include the “h2” dependency in your initializer. For example:

spring init -dweb,jpa,h2 demo-h2

The H2 Database provides a web interface called H2 Console to see the data. You can enable the h2 console in the src/main/resources/application.properties

# Enabling H2 Console spring.h2.console.enabled=true 

You can launch up H2 Console at the following address: http://localhost:8080/h2-console

As far as H2 is concerned, as soon as Spring Boot sees H2 in the class path, it auto configures a data source similar to what you see below:

spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password= spring.jpa.database-platform=org.hibernate.dialect.H2Dialect