At the beginning

I had never touched an OR Mapper other than Doma, so I suddenly thought about it and touched it with half interest.

How to install in Spring Boot application-I would like to write about the comparison with Doma that I felt by creating a simple API.

environment

IDE VSCode
Java 11.0.6
Spring Boot 2.3.1
PostgreSQL 11.6

Premise

I would like to create a simple API and touch it in various ways. The API to be created is as follows.

end point	Http Method	Overview	Remarks
`/api/employee/{employeeId}`	GET	Get employee information that matches the employee ID.
`/api/employee`	GET	Get employee information.	We will also narrow down by search conditions.
`/api/employee`	POST	Register employee information.
`/api/employee/{employeeId}`	PUT	Update employee information.
`/api/employee/{employeeId}`	DELETE	Delete employee information.

API creation

(For the time being) Creating a template for the application

Create a template for your application using the VSCode plugin called Spring Initializer Java Support.

This plugin itself is included in the Spring Boot Extension Pack, so the [Spring Boot Extension Pack]( It is enough to have https://marketplace.visualstudio.com/items?itemName=Pivotal.vscode-boot-dev-pack) installed.

Create a template interactively. From the command palette, select "Spring Initializr: Generate a Gradle Project".

Select Java.

Enter the package name. This time, leave it as com.example by default.

Enter the project name. Please give us your favorite name. (I chose employee-api.)

Select the Spring Boot version. (Select 2.3.1)

Select a dependent library. I just wanted to create a simple API, so I chose the following library.

Spring Boot DevTools / Lombok / Spring Web / Spring Data JPA / PostgreSQL Driver

Define connection information in ʻapplication.properties`

spring.jpa.database=postgresql
spring.datasource.platform=postgres
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/sample
spring.datasource.username=postgres
spring.datasource.password=postgres

Define Entity class

ʻInsert_date and ʻupdate_date are defined as common items in the table.

`CommonEntity.java`


/**
 *This class defines common items in the table.</br>
 *All Entity classes are created by inheriting this class.
 */
@MappedSuperclass
@Getter
@Setter
public class CommonEntity {

  /**Data registration date and time*/
  @Column(name = "insert_date")
  @Temporal(TemporalType.DATE)
  private Date insertdate;

  /**Data update date and time*/
  @Column(name = "update_date")
  @Temporal(TemporalType.DATE)
  private Date updateDate;

  /**
   *Methods commonly executed before data registration
   */
  @PrePersist
  public void preInsert() {
    Date date = new Date();
    setInsertdate(date);
    setUpdateDate(date);
  }

  /**
   *Commonly executed methods before updating data
   */
  @PreUpdate
  public void preUpdate() {
    setUpdateDate(new Date());
  }

}

Inherit CommonEntity that defines table common items and create Entity class for business.

`EmployeeEntity.java`


@Entity
@Table(name = "employee")
@Getter
@Setter
public class EmployeeEntity extends CommonEntity {

  /**Employee ID*/
  @Id
  @Column(name = "id")
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer employeeId;
  
  /**Employee name*/
  @Column(name = "name")
  private String employeeName;

  /**age*/
  @Column(name = "age")
  private Integer age;

  /**Job title ID*/
  @Column(name = "position_id")
  private String positionId;

  /**Department ID*/
  @Column(name = "department_id")
  private String departmentId;

}

Define Repository interface

ʻDefine an interface that inherits from org.springframework.data.jpa.repository.JpaRepository`.

`EmployeeRepository.java`


package com.example.employeeapi.employee;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends JpaRepository<EmployeeEntity, Integer> {

}

(Reference) `JpaRepository`

There are methods that can handle basic CRUD operations. In the interface that inherits this interface (in this example, ʻEmployeeRepository`), you can define your own method when the method prepared in advance in the business specifications etc. is not enough. (Join to get records, etc.)

`JpaRepository.java`


/*
 * Copyright 2008-2020 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.jpa.repository;

import java.util.List;

import javax.persistence.EntityManager;

import org.springframework.data.domain.Example;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.QueryByExampleExecutor;

/**
 * JPA specific extension of {@link org.springframework.data.repository.Repository}.
 *
 * @author Oliver Gierke
 * @author Christoph Strobl
 * @author Mark Paluch
 */
@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#findAll()
	 */
	@Override
	List<T> findAll();

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.PagingAndSortingRepository#findAll(org.springframework.data.domain.Sort)
	 */
	@Override
	List<T> findAll(Sort sort);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#findAll(java.lang.Iterable)
	 */
	@Override
	List<T> findAllById(Iterable<ID> ids);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.CrudRepository#save(java.lang.Iterable)
	 */
	@Override
	<S extends T> List<S> saveAll(Iterable<S> entities);

	/**
	 * Flushes all pending changes to the database.
	 */
	void flush();

	/**
	 * Saves an entity and flushes changes instantly.
	 *
	 * @param entity
	 * @return the saved entity
	 */
	<S extends T> S saveAndFlush(S entity);

	/**
	 * Deletes the given entities in a batch which means it will create a single {@link Query}. Assume that we will clear
	 * the {@link javax.persistence.EntityManager} after the call.
	 *
	 * @param entities
	 */
	void deleteInBatch(Iterable<T> entities);

	/**
	 * Deletes all entities in a batch call.
	 */
	void deleteAllInBatch();

	/**
	 * Returns a reference to the entity with the given identifier. Depending on how the JPA persistence provider is
	 * implemented this is very likely to always return an instance and throw an
	 * {@link javax.persistence.EntityNotFoundException} on first access. Some of them will reject invalid identifiers
	 * immediately.
	 *
	 * @param id must not be {@literal null}.
	 * @return a reference to the entity with the given identifier.
	 * @see EntityManager#getReference(Class, Object) for details on when an exception is thrown.
	 */
	T getOne(ID id);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.query.QueryByExampleExecutor#findAll(org.springframework.data.domain.Example)
	 */
	@Override
	<S extends T> List<S> findAll(Example<S> example);

	/*
	 * (non-Javadoc)
	 * @see org.springframework.data.repository.query.QueryByExampleExecutor#findAll(org.springframework.data.domain.Example, org.springframework.data.domain.Sort)
	 */
	@Override
	<S extends T> List<S> findAll(Example<S> example, Sort sort);
}

Define Service and Controller classes

Define a Service class that uses the ʻEmployee Repository` defined earlier and a Controller class that calls it.

`EmployeeService.java`


package com.example.employeeapi.employee;

import java.util.ArrayList;
import java.util.List;

import javax.transaction.Transactional;

import com.example.employeeapi.employee.dto.Employee;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
@Transactional
public class EmployeeService {

  @Autowired
  private EmployeeRepository employeeRepository;

  public Employee getEmployeeById(String employeeId) {
    EmployeeEntity entity = employeeRepository.findById(Integer.parseInt(employeeId)).get();
    Employee employee = new Employee();
    copyEntityToBean(entity, employee);
    return employee;
  }

  public List<Employee> getEmployeeList() {
    List<Employee> employees = new ArrayList<>();
    List<EmployeeEntity> employeeEntityList = employeeRepository.findAll();
    employeeEntityList.forEach(entity -> {
      Employee employee = new Employee();
      copyEntityToBean(entity, employee);
      employees.add(employee);
    });
    return employees;
  }

  public Employee createEmployee(Employee employee) {
    EmployeeEntity entity = new EmployeeEntity();
    copyBeanToEntityForInsert(employee, entity);
    EmployeeEntity createdEntity = employeeRepository.save(entity);
    Employee newEmployee = new Employee();
    copyEntityToBean(createdEntity, newEmployee);
    return newEmployee;
  }

  public Employee updateEmployee(Employee employee) {
    EmployeeEntity entity = new EmployeeEntity();
    copyBeanToEntityForUpdate(employee, entity);
    EmployeeEntity updatedEntity = employeeRepository.save(entity);
    Employee updatedEmployee = new Employee();
    copyEntityToBean(updatedEntity, updatedEmployee);
    return updatedEmployee;
  }

  public boolean deleteEmployeeById(String employeeId) {
    employeeRepository.deleteById(Integer.parseInt(employeeId));
    return true;
  }

  private void copyEntityToBean(EmployeeEntity entity, Employee employee) {
    //For the sample, make a simple copy.
    //If you want to do it cleanly, BeanUtils#Use copyProperties etc.
    employee.setId(String.valueOf(entity.getEmployeeId()));
    employee.setName(entity.getEmployeeName());
    employee.setAge(String.valueOf(entity.getAge()));
    employee.setPositionId(entity.getPositionId());
    employee.setDepartmentId(entity.getDepartmentId());
    employee.setInsertDate(String.valueOf(entity.getInsertdate()));
    employee.setUpdateDate(String.valueOf(entity.getUpdateDate()));
  }

  private void copyBeanToEntityForInsert(Employee employee, EmployeeEntity entity) {
    //For the sample, make a simple copy.
    //If you want to do it cleanly, BeanUtils#Use copyProperties etc.
    if (!"".equals(employee.getName())) {
      entity.setEmployeeName(employee.getName());
    }
    if (!"".equals(employee.getAge())) {
      entity.setAge(Integer.parseInt(employee.getAge()));
    }
    if (!"".equals(employee.getPositionId())) {
      entity.setPositionId(employee.getPositionId());
    }
    if (!"".equals(employee.getDepartmentId())) {
      entity.setDepartmentId(employee.getDepartmentId());
    }
  }

  private void copyBeanToEntityForUpdate(Employee employee, EmployeeEntity entity) {
    //For the sample, make a simple copy.
    //If you want to do it cleanly, BeanUtils#Use copyProperties etc.
    entity.setEmployeeId(Integer.parseInt(employee.getId()));
    copyBeanToEntityForInsert(employee, entity);
  }

}

`EmployeeController.java`


package com.example.employeeapi.employee;

import java.util.List;

import com.example.employeeapi.common.dto.HttpResponseDto;
import com.example.employeeapi.employee.dto.Employee;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmployeeController {

  @Autowired
  private EmployeeService employeeService;

  @GetMapping(path = "/api/employee/{employeeId}")
  public HttpResponseDto getEmployeeById(@PathVariable("employeeId") String employeeId) {
    HttpResponseDto httpResponseDto = new HttpResponseDto();
    Employee employee = employeeService.getEmployeeById(employeeId);
    httpResponseDto.setHttpStatus(HttpStatus.OK);
    httpResponseDto.setResponseData(employee);
    return httpResponseDto;
  }

  @GetMapping(path = "/api/employee")
  public HttpResponseDto getEmployeeList() {
    HttpResponseDto httpResponseDto = new HttpResponseDto();
    List<Employee> employees = employeeService.getEmployeeList();
    httpResponseDto.setHttpStatus(HttpStatus.OK);
    httpResponseDto.setResponseData(employees);
    return httpResponseDto;
  }

  @PostMapping(path = "/api/employee")
  public HttpResponseDto createEmployee(@RequestBody Employee employee) {
    HttpResponseDto httpResponseDto = new HttpResponseDto();
    Employee newEmployee = employeeService.createEmployee(employee);
    httpResponseDto.setHttpStatus(HttpStatus.CREATED);
    httpResponseDto.setResponseData(newEmployee);
    return httpResponseDto;
  }

  @PutMapping(path = "/api/employee/{employeeId}")
  public HttpResponseDto updateEmployee(@PathVariable("employeeId") String emplyeeId, @RequestBody Employee employee) {
    HttpResponseDto httpResponseDto = new HttpResponseDto();
    employee.setId(emplyeeId);
    Employee updatedEmployee = employeeService.updateEmployee(employee);
    httpResponseDto.setHttpStatus(HttpStatus.CREATED);
    httpResponseDto.setResponseData(updatedEmployee);
    return httpResponseDto;
  }

  @DeleteMapping(path = "/api/employee/{employeeId}")
  public HttpResponseDto deleteEmployee(@PathVariable("employeeId") String employeeId) {
    HttpResponseDto httpResponseDto = new HttpResponseDto();
    if (employeeService.deleteEmployeeById(employeeId)) {
      httpResponseDto.setHttpStatus(HttpStatus.OK);
      httpResponseDto.setMessage("delete success.");
    } else {
      // do something
    }
    return httpResponseDto;
  }
}

Comparison with other OR Mapper (subjective)

I wrote it as a comparison with other OR Mappers, but it is a comparison with Doma.

――What I felt was good --Simple CRUD operations can be achieved simply by using the provided API. --Doma also provides APIs other than search (SELECT), but in the case of search processing, it is necessary to create an SQL file even for a simple query. --If it's about the API provided by JpaRepository, Doma Gen seems to be quite so, but , --The feeling of use is similar to the OR Mapper for TypeScript called TypeORM that I often use. (I wonder if TypeORM was made with JPA in mind, please let me know if you are familiar with it.) ――What I felt was not good ――Since I made only simple CRUD operations, I have no particular complaints at the moment. ――However, since it was originally JPA, I have a feeling that it will hurt if I do not adopt it after studying well.

(Supplement) Database construction

The DB for verification is built based on Docker. It's a pain to make a DB because it works with copy and paste! Please use it.

$ tree
.
├── docker-compose.yml
└── init-script
    ├── 01_create_table.sql
    └── 02_insert_data.sql

`docker-compose.yml`


version: '3'
volumes:
  db_data:
services:
  database:
    image: postgres:11.6
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - db_data:/var/lib/postgresql/data
      - ./init-script:/docker-entrypoint-initdb.d
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: sample

`01_create_table.sql`


create table department (
  --Department code
  id varchar(3) primary key,
  --Department name
  name varchar(50),
  --Data input date
  insert_date date,
  --Data update date
  update_date date
);

create table "position" (
  --Job title ID
  id varchar(2) primary key,
  --Job title
  name varchar(20),
  --Data input date
  insert_date date,
  --Data update date
  update_date date
);

--table generation
create table "employee" (
  --employee number
  id serial primary key,
  --Employee name
  name varchar(50),
  --age
  age integer,
  --Position
  position_id varchar(2) references position(id),
  --Affiliation department id
  department_id varchar(3) references department(id),
  --Data input date
  insert_date date,
  --Data update date
  update_date date
);

`02_insert_data.sql`


insert into department (id, name, insert_date, update_date)
values ('001', 'Human Resources Department', '2020-06-17', '2020-06-17');
insert into department (id, name, insert_date, update_date)
values ('002', 'General Affairs Department', '2020-06-17', '2020-06-17');
insert into department (id, name, insert_date, update_date)
values ('003', 'Development department', '2020-06-17', '2020-06-17');
insert into department (id, name, insert_date, update_date)
values ('004', 'Public relations department', '2020-06-17', '2020-06-17');
insert into position (id, name, insert_date, update_date)
values ('01', 'Director', '2020-06-17', '2020-06-17');
insert into position (id, name, insert_date, update_date)
values ('02', 'Manager', '2020-06-17', '2020-06-17');
insert into position (id, name, insert_date, update_date)
values ('03', 'General', '2020-06-17', '2020-06-17');
insert into employee (
    name,
    age,
    position_id,
    department_id,
    insert_date,
    update_date
  )
values (
    'Shacho-san',
    50,
    '01',
    '001',
    '2020-06-17',
    '2020-06-17'
  );
insert into employee (
    name,
    age,
    position_id,
    department_id,
    insert_date,
    update_date
  )
values (
    'Butcho-san',
    46,
    '02',
    '001',
    '2020-06-17',
    '2020-06-17'
  );
insert into employee (
    name,
    age,
    position_id,
    department_id,
    insert_date,
    update_date
  )
values (
    'Kaccho',
    30,
    '03',
    '001',
    '2020-06-17',
    '2020-06-17'
  );
insert into employee (
    name,
    age,
    position_id,
    department_id,
    insert_date,
    update_date
  )
values (
    'Mr. Pampee',
    30,
    '03',
    '002',
    '2020-06-17',
    '2020-06-17'
  );

At the end

I would like to imagine an actual use case and create a more practical API.

reference

-Connect to database with SpringBoot + Spring JPA

-Connect to database with spring boot + spring jpa and CRUD operation

-Points for selecting Java OR mapper

I tried to get started with Spring Data JPA

At the beginning

environment

Premise

API creation

(For the time being) Creating a template for the application

Define connection information in ʻapplication.properties`

Define Entity class

CommonEntity.java

EmployeeEntity.java

Define Repository interface

EmployeeRepository.java

(Reference) JpaRepository

JpaRepository.java