Taxonomy Icon

Java

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

Prerequisites

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 MyDb2JDBCProject).
  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 JDBC starter to the pom.xml in the dependencies element:

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

Spring Initialzr

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.0.4 currently).
  2. Name your project artifact (for example, mydb2jdbcproject).
  3. Add Web dependency.
  4. Add JDBC dependency.
  5. Select Generate Project, and download the application archive.
  6. Unpack the archive.
  7. Change into the unpacked directory.

Add db2jcc4.jar to the project

The DB2 JDBC Driver (JCC) is not available in Maven, so you have to download it directly from IBM and add it to a local maven repository.

  1. Select and download the appropriate driver archive from DB2 JDBC Driver Versions and Downloads.
  2. Unpack the archive, and save the db2jcc4.jar file to a new lib directory on the root of your project.
  3. Add the Jar as a Maven package from the lib directory, and change the version accordingly with the archive you downloaded:

    mvn install:install-file -DlocalRepositoryPath=lib -DcreateChecksum=true -Dpackaging=jar -Dfile=./lib/db2jcc4.jar -DgroupId=com.ibm.db2.jcc -DartifactId=db2jcc4 -Dversion=4.24.92
    
  4. Add the lib directory as a Maven repository to your pom.xml:

    <repositories>
      <repository>
        <id>repo</id>
        <url>file://${project.basedir}/lib</url>
      </repository>
    </repositories>
    
  5. Add the dependency to the dependencies element in your pom.xml, and change the version to match accordingly:

    <dependency>
      <groupId>com.ibm.db2.jcc</groupId>
      <artifactId>db2jcc4</artifactId>
      <version>4.24.92</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 JDBC 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 JDBC classes jdbc.

  3. In the jdbc directory, create the class that will represent a table row:

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

Add a simple query of the DB via JDBC to a RestController

For projects created with Spring Initializr, you must create your own RestController class. Make a controller directory alongside the jdbc 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.jdbc.core.JdbcTemplate;
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
    JdbcTemplate jdbcTemplate;

    @RequestMapping("test")
    public @ResponseBody ResponseEntity<String> example() {
        List<String> list = new ArrayList<>();
        list.add("Table data...");
        jdbcTemplate.query(
                "SELECT * FROM things", new Object[]{},
                (rs,rowNum) -> new Things(rs.getLong("id"), rs.getString("name")))
                .forEach(thing -> list.add(thing.toString()));
        return new ResponseEntity<String>(list.toString(), HttpStatus.OK);
    }

}

You may need to add the import for the Things class 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