DB2 for i CRUD API with Spring Boot

This tutorial will describe the steps we need to follow in order to implement a spring boot application that will be used for applying CRUD (Create Read Update Delete) operation on a Database File that resides in a DB2 for i Schema

Tasks on IBM i

Firstly, lets write the DDS (in N4SORC source file ) for the database table

A..........T.Name++++++RLen++TDpB......Functions++++++++++++
********** Beginning of data *******************************
A                                      UNIQUE               
A          R N4USER01R                                      
 *                                                          
A            N4ID          18S 0       COLHDG('USER ID')    
A            N4NAME        50A         COLHDG('FULL NAME')  
A            N4DSBL         1A         COLHDG('IS DISABLED')
A            N4MAIL        80A         COLHDG('HOME MAIL')  
A            N4BIRTH         L         COLHDG('BITH DATE')  
 *                                                          
A          K N4ID                                           

The table will have 5 columns (N4ID – N4NAME – N4DSBL – N4MAIL -N4BIRTH) and the column with name N4ID will accept unique values.

Next, run the following command in order to create the file with name N4USER01 in NIKOLAS400 schema

CRTPF FILE(NIKOLAS400/N4USER01) SRCFILE(LIBSRC/N4SORC) 

Note: In case auto-journaling feature is not enabled for NIKOLAS400 library, start journaling the new file by running the following command

STRJRNPF FILE(NIKOLAS400/N4USER01) JRN(myJournalLib/myJournalFile)

Tasks on JAVA IDE

create a new SPRING BOOT MAVEN project and include the following dependencies in the pom.xml file

        <!--ensure jt400 driver version is compliant with jdbc4.1 (v8.5 is not)-->
        <dependency>
            <groupId>net.sf.jt400</groupId>
            <artifactId>jt400</artifactId>
            <version>9.8</version>
        </dependency>

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

We defined the following Artifacts

• jt400
  • this contains the JDBC Driver for DB2 for i
• spring-boot-starter-data-jpa
  • this includes the JPA Implementation (eg Hibernate)
• spring-boot-starter-web
  • we need it for implementing the REST Controller we’ll show later on

Properties

Add the following entries in the application.properties

spring.datasource.url=jdbc:as400://host_ibmi/NIKOLAS400
spring.datasource.username=myusername
spring.datasource.password=mypassword

JPA

Next, we will add the JAVA class which will be responsible for modeling User entities. We use friendly names since JAVA doesn’t have the 10-chars restriction.

@Entity
@Table(name = User.TABLE_NAME)
public class User {

    public static final String TABLE_NAME= "N4USER01";

    @Id
    @Column(name = "N4ID")
    private long id;

    @NotBlank(message = "Name is mandatory")
    @Column(name = "N4NAME")
    private String name;

    @NotBlank(message = "Y-Yes, N-No")
    @Column(name = "N4DSBL")
    private String isDisabled;

    @Email
    @Column(name = "N4MAIL")
    private String homeMail;

    @NotBlank(message = "Birth Date")
    @Column(name = "N4BIRTH")
    private Date birthDate;

    // standard constructors / setters / getters / toString
}

To provide our application with basic CRUD functionality on User objects, we need to extend the CrudRepository interface (that is provided by SPRING DATA JPA)

@Repository
public interface UserRepository extends CrudRepository<User, Long> {

}

WEB

Now, its time to implement our REST Controller

@Controller
@RequestMapping("/v1/user")
public class UserController {

    @Autowired
    private UserService service;

    @PostMapping("/create")
    public ResponseEntity<Long> addUser(@RequestBody @Valid User user) {
        service.createUser(user);
        return new ResponseEntity(user.getId(), HttpStatus.OK);
    }

    @GetMapping("/read/{id}")
    public ResponseEntity<User> readUser(@PathVariable("id") long id) {
        User user = service.readUser(id);
        return new ResponseEntity<User>(user, HttpStatus.OK);
    }

    @PostMapping("/update/{id}")
    public ResponseEntity<User> updateUser(@PathVariable("id") long id, @RequestBody @Valid User user) {
        service.updateUser(id,user);
        return new ResponseEntity<User>(user, HttpStatus.OK);
    }

    @GetMapping("/delete/{id}")
    public ResponseEntity<String> deleteUser(@PathVariable("id") long id) {
        service.deleteUser(id);
        return new ResponseEntity("OK", HttpStatus.OK);
    }
}

and the relevant service class

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public void createUser(User user) {
        userRepository.save(user);
    }

    public User readUser(long id) {
        User user = userRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("Invalid user Id:" + id));
        return user;
    }

    public void updateUser(long id, User updatedUser) {
        User oldUser = readUser(id);
        userRepository.save(updatedUser);
    }

    public void deleteUser(long id) {
        User user = readUser(id);
        userRepository.delete(user);
    }
}

TESTING

Now, since we finished our code its time for testing.

Start the spring boot application and use SOAPUI application to send the below JSON on the localhost:8080/v1/user/create URL using POST method

{
	"id":1
	,"name":"Nikolas400"
	,"isDisabled":"N"
	,"homeMail":"test@test.com"
	,"birthDate": "2000-12-31"
	
}

Now, the user with id 1 should have been created.

To confirm that, send the following http request using GET method

localhost:8080/v1/user/read/1