Try searching for a zip code with REST API using SpringBoot, H2 DB and JPA REST-Only 3 classes to create-

Overview

This entry is based on Spring Boot and has H2 Database Engine in the database. ) Is specified, and an example of accessing with Spring Data JPA is summarized.

As a subject, I imagined an API to search for a zip code. For example, if you look up a place with "cat" in it, it looks like the figure below.

example_cat.png

Assumed reader

--For the purpose of reference for those who want to do what the entry title says

Motivation for entry

--Somehow, I have to copy and paste from each site to make a template and then start multiple times, so to make my own boiler plate ――Because it may be helpful for someone

What you can do with this entry

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

Create an API to search the table that stores the zip code and address by the following two methods.

  1. Get a list of corresponding lines by specifying the zip code
  2. Specify a part of the place name to get the list of corresponding lines

--On the post office page the zip code can be downloaded, load it into the database and enable REST access.

usage environment

Steps to write Java code

Spring Initializr

It looks like this.

initializr-settings.png

The initial GitHub tag is here.

Allow the H2 console to come out

The H2 database comes with a web console feature that you can use as soon as you enable it. (See the figure below)

h2-console-sample.png

To enable it, specify the following in application.yaml and application.yaml, which means that it will be enabled only during development.

application.yaml is as follows. Spring Boot JPA has a function to issue DDL based on @Entity when it is not done in particular, but I want to specify DDL by myself, so I stopped it here.

The reason why "; database_to_upper = false" is specified in the connection string of H2 is that the table name is aligned to uppercase when issuing DDL by default in H2, but this function is disabled. JPA + Hibernate expects lowercase letters as the default, so I arranged them there.

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

# 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 if SQL is logged, jpa.show-log is specified as 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, the WEB console is enabled by specifying the Spring Profile to start as shown below.

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

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

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

h2-console-login.png

Try loading the data at startup

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

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

When using multiple data sources or switching, each DB is unique The syntax may be different, in which case you can handle it by putting a file for your writing platform. (There is an explanation around here)

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

I am creating a table with src / data / resources / schema-h2.sql. Since it is a process that runs every time, IF NOT EXISTS is attached. I decided to use the H2 function to assign the ID.

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 checking the operation during development. I'm lonely when it's empty. This is also a process that runs every time it is started, so the SQL is set so that it will not be inserted twice.

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

(Bonus) Read CSV for testing using H2's CSV READ function

Data can be read into H2 from CSV and selected. Using this, from the Postal Code Information Download Site of the post office, the file "KEN_ALL." Use CSV.

On the H2 WEB console mentioned above, execute the following. Since there was no column name in the CSV data, the column name is specified as an option of CSVREAD and selected. 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

There are only three.

ij-image.png

Main

It's the usual Spring Boot Application.

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

Entity

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

Setter / Getter is automatically generated using lombok. The "id" with @ javax.persistense.Id corresponds to the one automatically numbered by 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

The repository layer. By inheriting "PagingAndSortingRepository", Spring Data JPA, REST will provide the implementation required for DB access and REST access from the outside.

The parameters in the @RepositoryRestResource annotation are also given on the framework side without specifying them, but in this example they are explicitly specified.


@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 is named according to the Spring Data JPA naming rules. For methods that follow the naming rules, the framework implements the corresponding SQL statements and parameters. So this is the only line you need to find an address from the 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 you will be looking for a match with LIKE among them.

    @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);

Put the code at this point on GitHub put it as 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 code that needs to be written can be considerably reduced as long as the range provided by the framework can be achieved.

Supplement

I thought about what to do with the three code placement packages for a moment, but in this example I simply put them all in the same folder. Depending on the style, you can place it in any position you like.

Recommended Posts

Try searching for a zip code with REST API using SpringBoot, H2 DB and JPA REST-Only 3 classes to create-
Create a simple CRUD with SpringBoot + JPA + Thymeleaf ② ~ Screen and function creation ~
How to create a database for H2 Database anywhere
Create a private key / public key in CentOS8.2 and connect to SSH with VS Code
Implement REST API with Spring Boot and JPA (Application Layer)
Create a simple CRUD with SpringBoot + JPA + Thymeleaf ③ ~ Add Validation ~
Implement REST API with Spring Boot and JPA (Infrastructure layer)
Try hitting the zip code search API with Spring Boot
Tutorial to create a blog with Rails for beginners Part 2
Implement REST API with Spring Boot and JPA (domain layer)
8 things to insert into DB using Spring Boot and JPA
How to create and launch a Dockerfile for Payara Micro
Tutorial to create a blog with Rails for beginners Part 0