remko de knikker
Mar 26, 2018 · 5 min read

In this tutorial, you will create a Java application to retrieve bank information from the FDIC Institutions database. You will use Spring Boot to bootstrap your application, Spring Data JPA for ORM mapping and map the Institution POJO to the Institution table in a Db2 RDBMS, and Maven to build and run the application. You will also load the FDIC Institutions data into the Db2 database and manipulate the Db2 tables.

In the next upcoming tutorials, I will add an API Server and an API Management Framework to the FDIC data application. Follow https://medium.com/nycdev or https://medium.com/@remkohdev for updates.

Requirements

I am going to rely on your skills to make sure you have the following:

In the Cloud:

On your localhost:

Load FDIC Institutions into Db2

  • Download the FDIC Institutions data,
  • Go to the Banks Data and Statistics page at the FDIC website,
  • Go to the Institution Directory,
  • In the top menu, click Data Download > Institutions and Locations ID,
  • Download the ‘current list of all institutions’, a 3.77Mb zip file,
  • Load the institutions into Db2,
  • Open the ‘Manage’ page of the IBM Cloud Db2 instance,
  • Click ‘Service credentials’ > ‘New credential’, enter a name and click ‘Add’,
  • Click ‘View credential’, and copy-paste to save the Db2 credentials for the next step,
  • Go to ‘Manage’ and click ‘Open Console’,
  • Go to Menu > Load,
  • From ‘My Computer, A single delimited text file (CSV).’, select the file ‘INSTITUTIONS2.CSV’, into your default schema, e.g. ‘NGW09164’, and into a new table ‘INSTITUTION’,
  • Data can be inconsistent and the loader might fail on very large data sets, so to decrease the chances of failure, you can change the ‘DATE’ data type to ‘VARCHAR(24)’, change DECIMALs to INTEGERs when there’s no real decimal in the column, and increase the size of VARCHARs,
  • Click ‘Next’ and ‘Begin Load’,
  • Go to ‘Explore’ > ‘NGW09164’ > ‘INSTITUTION’ to view your data,

Add an Autoincrement Primary Key ID to Table

alter table INSTITUTION add column id integer not null default 0
alter table INSTITUTION alter column id drop default
alter table INSTITUTION alter column id set generated always as identity
call sysproc.admin_cmd('reorg table INSTITUTION')
update INSTITUTION set id = default
alter table INSTITUTION add constraint pkey primary key(id)

Go to the ‘RUN SQL’ menu item and run the following SQL query:

select institutio0_.id as id1_0_, institutio0_.name as name2_0_ from institution institutio0_ where institutio0_.name='The Southington Bank and Trust Company'ID1_0_     NAME2_0_1     The Southington Bank and Trust Company

Create the Spring Application

  • Generate a QuickStart Maven project:
$ mvn archetype:generate -DgroupId=com.remkohde.hello -DartifactId=spring-hibernate-db-server -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
$ cd spring-hibernate-db-server
  • Edit the class ‘com/remkohde/hello/App.java’
package com.remkohde.hello;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import java.util.List;
import java.util.ArrayList;
@SpringBootApplication
public class App
{
private static final Logger log = LoggerFactory.getLogger(App.class);
public static void main( String[] args )
{
SpringApplication.run(App.class, args);
}
@Bean
public CommandLineRunner demo(InstitutionRepository repository) {
return (args) -> {
List<String> names = new ArrayList<String>();
for(String arg : args){
if(arg.startsWith("-name")) {
String[] parts = arg.split("=");
names.add(parts[1]);
}
}
for(String name : names){
repository.findByName(name).forEach(inst -> {
log.info(inst.toString());
});
}
};
}
}
  • Create a new Entity class ‘com/remkohde/hello/Institution.java’
package com.remkohde.hello;import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Institution {
@Id
private Long id;
private String name;
public Institution(){
}
@Override
public String toString() {
return String.format("Institution[id=%d, name='%s']", id, name);
}
}
  • Create a new Spring Data Repository class that extends the Abstract CrudRepository class called ‘com/remkohde/hello/InstitutionRepository.java’. This class manages the actual ORM (Object Relation Mapping) between our ‘INSTITUTION’ table in Db2 and the Institution class in our Spring application.
package com.remkohde.hello;import java.util.List;
import org.springframework.data.repository.CrudRepository;
public interface InstitutionRepository extends CrudRepository<Institution, Long>
{
List<Institution> findByName(String name);
}
  • Create a new directory ‘~/src/main/resources’,
  • Add a new file ‘~/src/main/resources/application.yml’,
  • In IBM Cloud, go to your Db2 database instance, go to ‘Service credentials’, click ‘view credentials’,
  • Use these credentials from the Db2 instance to configure the Spring Datasource and JPA connection to the database in ‘application.yml’,
spring:
datasource:
url: <ssljdbcurl>
username: <username>
password: <password>
driverClassName: com.ibm.db2.jcc.DB2Driver
testWhileIdle: true
validationQuery: SELECT 1
jpa:
show-sql: true
generate-ddl: false
database-platform: DB2Platform
hibernate:
ddl-auto: none
naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
properties:
hibernate:
dialect: org.hibernate.dialect.DB2Dialect
  • The last thing to do is to configure the ‘pom.xml’ Maven build file,
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>

<url>https://medium.com/@remkohdev</url>
<name>spring-hibernate-db-server</name>
<artifactId>spring-hibernate-db-server</artifactId>
<groupId>com.remkohde.hello</groupId>
<properties>
<java.version>1.8</java.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
<!-- https://www-304.ibm.com/support/docview.wss?uid=swg21363866 -->
<dependency>
<groupId>com.ibm.db2.jcc</groupId>
<artifactId>db2jcc4</artifactId>
<version>4.24.92</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.0.0.RELEASE</version>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
<configuration>
<executable>true</executable>
</configuration>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>repo</id>
<url>file://${project.basedir}/lib</url>
</repository>
<!--
<repository>
<id>com.ibm.db2.jcc</id>
<url> https://artifacts.alfresco.com/nexus/content/repositories/public/
</url>
</repository>
-->
</repositories>
</project>
  • The most interesting tag here is the dependency on db2jcc4. This Maven package is listed in MVNrepository, but it is not maintained there and the last update is from Nov 2012. Your Maven build will in fact fail trying to retrieve the dependency. What you need to do is download the latest release for db2jcc4 and create your own local Maven repository.
  • Download the latest version of the db2jcc4.jar driver from https://www-304.ibm.com/support/docview.wss?uid=swg21363866.
  • Unzip the file ‘v11.1.3fp3_jdbc_sqlj.tar.gz’,
  • Save the ‘db2jcc4.jar’ to the ‘~/lib’ directory in your project
  • Now create your own correctly formatted Maven package, based on the driver archive:
$ 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
  • Add the local repository to the Maven build file:
<dependencies>
<!-- https://www-304.ibm.com/support/docview.wss?uid=swg21363866 -->
<dependency>
<groupId>com.ibm.db2.jcc</groupId>
<artifactId>db2jcc4</artifactId>
<version>4.24.92</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>repo</id>
<url>file://${project.basedir}/lib</url>
</repository>
</repositories>

Done! Test your application, you should see something like this:

$ mvn install
$ java -jar target/spring-hibernate-db-server-1.0-SNAPSHOT.jar -name='The Southington Bank and Trust Company' -name='Union Trust Company'
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
(( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.0.0.RELEASE)INFO 75186 --- [ main] com.remkohde.hello.App: Starting App on Remkos-MBP with PID 75186 (/Users/remkohdev/dev/src/spring-hibernate-db-server/target/spring-hibernate-db-server-1.0-SNAPSHOT.jar started by remkohdev@us.ibm.com in /Users/remkohdev/dev/src/spring-hibernate-db-server)Hibernate: select institutio0_.id as id1_0_, institutio0_.name as name2_0_ from institution institutio0_ where institutio0_.name=?INFO 75186 --- [ main] com.remkohde.hello.App : Institution[id=1, name='The Southington Bank and Trust Company']Hibernate: select institutio0_.id as id1_0_, institutio0_.name as name2_0_ from institution institutio0_ where institutio0_.name=?INFO 75186 --- [ main] com.remkohde.hello.App :
Institution[id=4, name='Union Trust Company']
INFO 75186 --- [ main] com.remkohde.hello.App :
Institution[id=16503, name='Union Trust Company']

Make sure to add Spring Boot, Spring Data Java Persistence API (JPA), Object Relational Mapping (ORM), Db2 on Cloud, SQL, Apache Maven and FDIC Institutions data to your resume! ;-) Learn and share!

NYC⚡️DEV

NYC Developer Community

remko de knikker

Written by

Developer Advocate @IBMDeveloper @IBMCloud — stateless tech humanist, serpent in the shepherds mouth — Dutch NY-er

NYC⚡️DEV

NYC Developer Community

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade