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.
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.
- Get a list of corresponding lines by specifying the zip code
- 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
- Windows10 Pro 64bit
- Adopt Open JDK 1.8.0
Steps to write Java code
Spring Initializr
It looks like this.
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)
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).
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.
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