DB2 via JPA on Spring

In this tutorial, learn how to use Spring to connect to a DB2 instance with JPA.

Prerequisites

• Maven
• A DB2 instance with access credentials:
  • IBM Cloud
  • Local
• Java JDK
• IBM Cloud developer tools (Optional)

Create the project

First decide if you plan to create your project using IBM Cloud Developer Tools or Spring Initializr, then follow the respective instructions.

IBM Cloud Developer Tools

If using IBM Cloud developer tools to create the project, then use the dev plugin to create a new Spring Microservice.

ibmcloud dev create

1. Select Backend Service / Web App.
2. Select Java – Spring.
3. Select Java Microservice with Spring (Microservice).
4. Give the project a name (for example MyDb2JPAProject).
5. Decline adding services to the application (n).
6. Select an appropriate toolchain or None (‘No Devops’), if unsure.
7. Change into the application directory.

8. Add the Spring JPA starter to the pom.xml in the dependencies element:

   <dependency>
      <groupId>org.springframework.boot</groupId>>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>
   

   Show more

Spring Initializr

If using Spring Initializr to create the project, then use a browser to visit https://start.spring.io.

1. Select your Spring Boot level (default is 2.1.8 currently).
2. Name your project artifact (for example, mydb2jpaproject).
3. Add Spring Web dependency.
4. Add Spring Data JPA dependency.
5. Select Generate Project, and download the application archive.
6. Unpack the archive.
7. Change into the unpacked directory.

Add the Db2 JDBC Driver (JCC) dependency to the pom.xml

Add the Db2 driver dependency declaration from Maven Central, for example:

<dependency>
  <groupId>com.ibm.db2</groupId>
  <artifactId>jcc</artifactId>
  <version>11.5.0.0</version>
</dependency>

Create the example table

For this quick guide, we’ll only use a simple database with a single table.

Use the db2 command prompt to connect to your DB2 instance, and issue the following SQL statements to create the things table and populate it with some data:

create table things (id int, name varchar(255))
insert into things (id,name) values (1,'fish'),(2,'wibble'),(3,'stiletto')

If you chose to use a different name for the table, you’ll need to remember it for when you create the JPA classes later.

Configure Spring Data for the DB2 Instance

Spring needs to be told how to talk to the database, and, like other Spring configurations, this lives in the application.properties (or application.yaml) file that lives at src/main/resources/application.properties.

Add the following properties to the application.properties file:

spring.datasource.url=jdbc:db2://mydb2host:50000/mydb2databasename
spring.datasource.username=mydb2username
spring.datasource.password=mydb2password

Remember to alter the values to match the location and credentials for your DB2 instance.

Create the JPA classes

1. Within the project, locate the main Spring Boot Application class.

   • For projects created with Spring Initializr, the main application class is named after the artifact name you supplied when the project was created. For example, if you named your artifact demo with a package of com.example, you will find your main class at src/main/java/com/example/DemoApplication.java.
   • For projects created with the IBM Cloud Developer Tools, the main application class is always at src/main/java/application/SBApplication.java.

2. In the same directory as the application class, create a directory for the JPA classes jpa.

3. In the jpa directory, create the class that will represent a table row. This class should have the same name as the table you created earlier.

   import javax.persistence.Entity;
   import javax.persistence.Id;
   
   @Entity
   public class Things {
     @Id
     private Long id;
     private String name;
     public Things(){
     }
     @Override
     public String toString() {
       return String.format("Things[id=%d, name='%s']", id, name);
     }
   }
   

   Show more

4. In the same directory as that class, create a Repository class that will allow you to access the data. The interface is typed by the class you just created.

   import java.util.List;
   import org.springframework.data.repository.CrudRepository;
   
   public interface Repository extends CrudRepository<Things, Long>
   {
     List<Things> findByName(String name);
     Iterable<Things> findAll();
   }
   

   Show more

Add a simple invocation of the JPA classes to a RestController

For projects created with Spring Initializr, you must create your own RestController class. Make a controller directory alongside the jpa directory, and create the RestController class inside.

For projects created with the IBM Cloud Developer Tools, an example RestController has been provided for you at src/main/java/application/rest/v1/Example.java.

The RestController provides REST endpoints for your application. Inject the repository into the RestController using @Autowired, and add a simple endpoint that returns the data in the table:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.ArrayList;
import java.util.List;

@RestController
public class Example {

    @Autowired
    Repository repo;

    @RequestMapping("test")
    public @ResponseBody ResponseEntity<String> example() {
        List<String> list = new ArrayList<>();
        list.add("Table data...");
        for(Things things: repo.findAll()){
            list.add(things.toString());
        }
        return new ResponseEntity<String>(list.toString(), HttpStatus.OK);
    }

}

You may need to add the import for the Things and Repository classes created earlier.

Run the example

You can run the example like any other Spring Boot application.

mvn spring-boot:run

You can then access the endpoint to see it query the db and retrieve the information.

$ curl http://localhost:8080/test
[Table data..., Things[id=1, name='fish'], Things[id=2, name='wibble'], Things[id=3, name='stiletto']]

Summary

Spring Boot makes it easy to configure and use a DB2 instance in a Spring-native fashion using Spring auto configuration and Spring Boot properties.

References

• IBM DB2
• Spring Data JPA
• Spring home page
• Spring Guides
• Spring Boot Reference