[Java] Using SpringBoot, H2 DB and JPA REST, search the zip code using REST API-only 3 classes to create-

6 minute read

Overview

In this entry, based on Spring Boot,H2DatabaseEngineIsspecifiedandaccessismadewithSpringDataJPA.

As a subject, I imagined an API that searches a postal code. For example, if you look up the place name with “cat”, it looks like the figure below.

example_cat.png

Target reader

  • For those who want to do what the title of the entry says

Motivation for entry

  • I have to copy and paste from each site and make a model several times before starting, so to make my own boiler plate
  • Maybe it will be helpful for someone

What you can do with this entry

You can search for the zip code and address as shown in the picture at the beginning of the entry.

For the table that stores the postal code and address, create an API for searching by the following two methods.

  1. Specify the postal code and get the list of corresponding lines
  2. Specify a part of the place name to get a list of corresponding lines

usage environment

  • Windows10 Pro 64bit
  • Adopt Open JDK 1.8.0

Steps to write Java code

Spring Initializr

It looks like this.

initializr-settings.png

The default GitHub tag is here.

Make H2 console available

The H2 database comes with WEB console features that you can use as soon as you activate it. (See the figure below)

h2-console-sample.png

To enable it, specify as follows in application.yaml and application.yaml in the sense that it is enabled only during development.

application.yaml is as follows. Spring Boot JPA has a function that issues DDL based on @Entity when not doing it, but I have stopped it here because I want to specify DDL by myself.

“;Database_to_upper=false” is specified in the connection string of H2. By default of H2, the table name is aligned in upper case when issuing DDL, but this function is disabled. JPA+Hibernate expects lowercase letters as default, so I arranged them there.

sqlScriptEncoding is to prevent garbled characters of the data that is read at startup. (For example, on Windows Platform, it tries to operate with SJIS (MS932) as the platform encoding)

# properties: https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html
spring:
  datasource:
    platform: h2
    driver-class-name: org.h2.Driver
    url: jdbc:h2:./demodb;database_to_upper=false
    username: username
    password: password
    sqlScriptEncoding: UTF-8
  h2:
    console:
      enabled: false

  jpa:
    hibernate:
      ddl-auto: none

application-dev.yaml is as follows. Since it is easier to see the movement when the SQL appears in the log, jpa.show-log is set to true in the dev profile.

# properties: https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html
spring:
  h2:
    console:
      enabled: true
      path: /h2-consolea
      settings:
        web-allow-others: true
  jpa:
    show-sql: true # for debugging-purpose only.Because it shows everything, there's no ways to filter.
    properties:
      hibernate:
        format_sql: true

In this state, specify the Profile of Spring to start as follows and the WEB console is enabled.

For Power Shell: (In Spring Boot2 or later, you can pass parameters to bootRun as below)

.\gradlew bootRun --args='--spring.profiles.active=dev'

You can login to http://localhost:8080/h2-console specified in the configuration file with the set of username and password specified in the configuration file (see the figure below).

h2-console-login.png

Try loading data at startup

Spring Boot has a function to read SQL of DB schema and a function to read DB data at startup.

This can be achieved by placing the files under src/main/resources.

When using multiple data sources or switching, etc., The syntax may vary, but in that case you can put the file for the platform you are writing to. ([There is a description here) (https://docs.spring.io/spring-boot/docs/2.1.0.M1/reference/html/howto-database-initialization.html))

In this sample, the following files are prepared. As an example, we are using a schema that stores zip codes and addresses.

I create a table in src/data/resources/schema-h2.sql. Since it is a process that runs every time, IF NOT EXISTS is attached. I decided to attach the ID using the H2 function.

CREATE TABLE IF NOT EXISTS
postal_codes (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code7 VARCHAR(7) NOT NULL,
  address1 VARCHAR(12) NOT NULL,
  address2 VARCHAR(60),
  address3 VARCHAR(60)
  );

In src/data/resources/data-h2.sql, 2 initial data are input. Mainly for operation check during development. It’s lonely when it’s empty. This is also a process that runs every time it is started, so it is SQL that does not duplicate INSERT.

INSERT INTO postal_codes (code7, address1, address2, address3) select '0640941','Hokkaido','Chuo-ku, Sapporo','Asaigaoka' where not exists (select * from postal_codes where code7 = '0640941');
INSERT INTO postal_codes (code7, address1, address2, address3) select '0600041','Hokkaido','Chuo-ku, Sapporo','Odori Higashi' where not exists (select * from postal_codes where code7 = '0600041');

(Bonus) Read CSV for test using CSV READ function of H2

Data can be read from CSV and selected in H2. Using this, a file “KEN_ALL.” that contains data for the whole country from the Postal code information download site of the post office. CSV” is used.

On the H2 WEB console described above, execute the following. Since there is no column name in the CSV data, we specify the column name as an option of CSVREAD and select it. The result is stored in the postal_codes table.

insert into postal_codes (code7, address1, address2, address3) select "col3", "col7", "col8", "col9" from csvread('~/KEN_ALL.CSV','col1,col2,col3,col4,col5, col6,col7,col8,col9,col10,col11,col2,col3,col4,col15','charset=SJIS')

At this point, you have the data to query.

Java code

Only three.ij-image.png

Main

The usual Spring Boot Application.

@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}

Entity

Prepare a class to load data from DB. Spring Data JPA also has a function that automatically creates a corresponding table from the class name without specifying @Table, but it is explicitly specified here.

Setter/Getter is automatically generated using lombok. The “id” that is given @javax.persistense.Id corresponds to the one automatically numbered in H2.

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "`POSTAL_CODES`")
@Data
public class PostalCodeInfo {
    @Id
    private long id;
    private String code7;
    private String address1;
    private String address2;
    private String address3;
}

Repository

Repository layer. By inheriting “PagingAndSortingRepository”, Spring Data JPA, REST provides the implementation required for DB access and REST access from the outside.

The parameter in @RepositoryRestResource annotation is also assigned by the framework side without specifying it, but it is explicitly specified in this example.


@RepositoryRestResource(collectionResourceRel = "postalcode", path = "postalcodes")
public interface PostalCodeInfoRepository extends PagingAndSortingRepository<PostalCodeInfo, Long> {

    List<PostalCodeInfo> findByCode7(@Param("code") String code);

    @Query("SELECT p FROM PostalCodeInfo p "
            + "WHERE address1 LIKE CONCAT('%',:name,'%')"
            + "OR address2 LIKE CONCAT('%',:name,'%')"
            + "OR address3 LIKE CONCAT('%',:name,'%')"
            + "ORDER BY p.code7")
    List<PostalCodeInfo> anyFieldLike(@Param("name") String name);
}

There are two methods, but the first one uses method names according to Spring Data JPA naming rules. For methods that follow the naming rules, the framework will implement the corresponding SQL statements and parameters. So, this one line is the only code needed to retrieve an address from a 7-digit zip code.

    List<PostalCodeInfo> findByCode7(@Param("code") String code);

In the other method, you specify the query yourself. There are three columns related to the address, but one of them is the one that LIKE searches for a match.

    @Query("SELECT p FROM PostalCodeInfo p "
            + "WHERE address1 LIKE CONCAT('%',:name,'%')"
            + "OR address2 LIKE CONCAT('%',:name,'%')"
            + "OR address3 LIKE CONCAT('%',:name,'%')"
            + "ORDER BY p.code7")
    List<PostalCodeInfo> anyFieldLike(@Param("name") String name);

The code at this point is [put it as tag 0.1.0] on GitHub (https://github.com/hrkt/boot-h2-jpa-demo/releases/tag/0.1.0).

in conclusion

This entry covered an example using Spring Boot, H2 DB and Spring Data REST. It was confirmed that the amount of code that had to be written was quite small as long as the framework could do what it wanted within the range provided.

Supplement

For a moment I wondered what to do with a three-code deployment package, but for this example I simply put them in the same folder. I think it should be placed in any position depending on the fashion.