Database linkage with doma2 (Spring boot)

What is Doma2

Used for database access. The features are as follows. According to Official (https://doma.readthedocs.io/en/stable/)

Doma 2 is a database access framework for Java 8 and above. Doma has many advantages.

-Use annotation processing to validate and generate source code at compile time. · Map database columns to user-defined Java objects. -Use a SQL template called "2-way SQL". · Supports Java 8 introduced classes such as java.time.LocalDate, java.util.Optional and java.util.stream.Stream ・ Independent of other libraries

Most of it is still translated, so Japanese may be a little strange. .. ..

The major advantages are the following two points -No dependency on libraries other than JRE (sensitive to dependencies ww) -All SQL can be managed in a file (is it loosely coupled?) This is an opinion I have used, but I thought that managing it with a SQL file has the advantage of preventing ** excessive commonality **. Too much commonality can be confusing.

Implementation

There was an official sample project, so I think this is easy to understand. Official sample The main required configuration is -Dao (Dao is an interface for database access) -Entity (a class that represents (but not only) a table) -SQL file (query) Will be.

Define a method for CRUD processing in Dao.

MstEmployeeDao.java



package com.example.dao;

import com.example.entity.MstEmployee;
import com.example.entity.UserEntity;
import org.seasar.doma.Dao;
import org.seasar.doma.Insert;
import org.seasar.doma.Select;
import org.seasar.doma.boot.ConfigAutowireable;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**Employee master Dao interface. */
@ConfigAutowireable
@Dao
public interface MstEmployeeDao {

  @Select
  List<MstEmployee> selectAll();

  @Select
  MstEmployee selectOne(String id);

  @Select
  UserEntity selectUser(String id);

  @Insert
  @Transactional
  int insert(MstEmployee mstEmployee);
}

Let's make the query you want to make with this method into a SQL file. It is how to use. Place the SQL file in META-INF by creating a folder hierarchy in the same way aspackage com.example.dao;in the above class and ** make the file name the same as the method name. ** (extension is sql)

You can easily understand it by looking at the tree of the sample project.

tree


├── pom.xml ...Maven config file
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── example
    │   │           ├── App.java  ...Application executable
    │   │           ├── SecurityConfig.java  ...Security configuration file
    │   │           ├── dao  ...Doma2 DAO class storage package
    │   │           │   ├── MstEmployeeDao.java
    │   │           │   ├── MstNewsDao.java
    │   │           │   └── MstRoleDao.java
    │   │           ├── dto
    │   │           │   └── NewsDto.java
    │   │           ├── entity  ...Doma2 Entity class storage package
    │   │           │   ├── AuditEntity.java
    │   │           │   ├── MstEmployee.java
    │   │           │   ├── MstNews.java
    │   │           │   ├── MstRole.java
    │   │           │   ├── UserEntity.java
    │   │           │   └── listener
    │   │           │       └── AuditListener.java
    │   │           ├── security  ...Spring Security authentication function
    │   │           │   ├── LoginUserDetails.java
    │   │           │   ├── LoginUserDetailsService.java
    │   │           │   └── UserInfo.java
    │   │           ├── service  ...Service class storage package
    │   │           │   ├── NewsService.java
    │   │           │   └── NewsServiceImpl.java
    │   │           └── web ...Spring MVC controller class storage package
    │   │               ├── LoginController.java
    │   │               ├── NewsController.java
    │   │               ├── TopController.java
    │   │               └── manager
    │   │                   ├── NewsForm.java
    │   │                   ├── NewsManagerEditController.java
    │   │                   ├── NewsManagerListController.java
    │   │                   └── NewsManagerRegisterController.java
    │   └── resources
    │       ├── META-INF
    │       │   └── com
    │       │       └── example
    │       │           └── dao ...Doma2 SQL file storage package
    │       │               ├── MstEmployeeDao
    │       │               │   ├── selectAll.sql
    │       │               │   ├── selectOne.sql
    │       │               │   └── selectUser.sql
    │       │               ├── MstNewsDao
    │       │               │   ├── selectAll.sql
    │       │               │   ├── selectNewsDtoByCond.sql
    │       │               │   └── selectOneNewsDto.sql
    │       │               └── MstRoleDao
    │       │                   └── selectAll.sql
    │       ├── ValidationMessages.properties ...Validation check message configuration file
    │       ├── application.yml ...Application configuration file
    │       ├── data.sql ...DDL statement to execute at startup
    │       ├── messages.properties ...Form property replacement configuration file for validation check message
    │       ├── schema.sql ...DML statement to execute at startup
    │       ├── static ...Static file storage folder directly under the context root at runtime
    │       │   ├── css
    │       │   │   └── lib ...CSS library storage folder
    │       │   │       ├── bootstrap-theme.min.css
    │       │   │       └── bootstrap.min.css
    │       │   ├── fonts ...Font storage folder(Bootstrap)
    │       │   │   ├── glyphicons-halflings-regular.eot
    │       │   │   ├── glyphicons-halflings-regular.svg
    │       │   │   ├── glyphicons-halflings-regular.ttf
    │       │   │   ├── glyphicons-halflings-regular.woff
    │       │   │   └── glyphicons-halflings-regular.woff2
    │       │   ├── js
    │       │   │   └── lib ...JavaScript library storage folder
    │       │   │       └── bootstrap.min.js
    │       │   └── movie
    │       │       ├── sample
    │       │       │   └── nc144421.mp4
    │       │       └── thumbnail
    │       │           ├── nc144421.jpg
    │       │           └── nc144555.jpg
    │       └── templates ...Thymeleaf template storage folder
    │           ├── learning
    │           │   └── learning.html
    │           ├── loginForm.html
    │           ├── manager
    │           │   ├── managerLayout.html
    │           │   └── news
    │           │       ├── edit
    │           │       │   ├── newsEditComplete.html
    │           │       │   ├── newsEditConfirm.html
    │           │       │   └── newsEditInput.html
    │           │       ├── list
    │           │       │   └── newsList.html
    │           │       └── register
    │           │           ├── newsRegisterComplete.html
    │           │           ├── newsRegisterConfirm.html
    │           │           └── newsRegisterInput.html
    │           ├── news
    │           │   └── news.html
    │           └── top
    │               └── top.html
    └── test ...Test code storage folder
        └── java
            └── com
                └── example
                    └── service
                        └── NewsServiceImplTest.java

How to write parameters

You can bind the variables of Dao class and the variables of the sql file by writing the sql file as follows.

selectOne



SELECT
	employee_id,
	employee_last_name,
	employee_first_name,
	role_id
FROM mst_employee
WHERE
  employee_id = /* id */'employee_id';

Furthermore, the dynamic condition can be expressed by writing as follows. You can use if statements and annotations.

selectNewsDtoByCond.sql



select
  n.mst_news_id id,
  n.role_id role_id,
  r.role_name role_nm,
  n.subject subject,
  n.url url,
  n.version version
FROM
  mst_news n
INNER JOIN
  mst_role r
ON
  n.role_id = r.role_id
WHERE
/*%if @isNotEmpty(url) */
    n.url LIKE /* @prefix(url) */'http'
/*%end*/
/*%if @isNotEmpty(subject) */
AND
    n.subject LIKE /* @prefix(subject) */'today'
/*%end*/
/*%if @isNotEmpty(roleId) */
AND
    n.role_id = /* roleId */'01'
/*%end*/
ORDER BY
  n.mst_news_id

I used JPA for database access in Spring boot, but this one seems to be the standard.

Recommended Posts

Database linkage with doma2 (Spring boot)
Download with Spring Boot
Generate barcode with Spring Boot
Hello World with Spring Boot
Get started with Spring boot
Hello World with Spring Boot!
Run LIFF with Spring Boot
SNS login with Spring Boot
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Create microservices with Spring Boot
Send email with spring boot
Database environment construction with Docker in Spring boot (IntellJ)
Use Basic Authentication with Spring Boot
gRPC on Spring Boot with grpc-spring-boot-starter
Hot deploy with Spring Boot development
Spring Boot programming with VS Code
Until "Hello World" with Spring Boot
Inquiry application creation with Spring Boot
Get validation results with Spring Boot
(Intellij) Hello World with Spring Boot
Create an app with Spring Boot
Spring with Kotorin --9 Database migration --Flyway
Connect to database with spring boot + spring jpa and CRUD operation
Google Cloud Platform with Spring Boot 2.0.0
Check date correlation with Spring Boot
I tried GraphQL with Spring Boot
[Java] LINE integration with Spring Boot
Beginning with Spring Boot 0. Use Spring CLI
I tried Flyway with Spring Boot
Message cooperation started with Spring Boot
Spring Boot gradle build with Docker
Processing at application startup with Spring Boot
Hello World with Eclipse + Spring Boot + Maven
Send regular notifications with LineNotify + Spring Boot
Perform transaction confirmation test with Spring Boot
HTTPS with Spring Boot and Let's Encrypt
Try using Spring Boot with VS Code
Start web application development with Spring Boot
Launch Nginx + Spring Boot application with docker-compose
I tried Lazy Initialization with Spring Boot 2.2.0
Implement CRUD with Spring Boot + Thymeleaf + MySQL
Asynchronous processing with Spring Boot using @Async
Spring Boot, Doma2, Gradle initial setting summary
Implement paging function with Spring Boot + Thymeleaf
(IntelliJ + gradle) Hello World with Spring Boot
Use cache with EhCashe 2.x with Spring Boot
Form class validation test with Spring Boot
Run WEB application with Spring Boot + Thymeleaf
Achieve BASIC authentication with Spring Boot + Spring Security
Challenge Spring Boot
Spring Boot Form
Spring Boot Memorandum
gae + spring boot
Spring Boot environment construction with Docker (January 2021 version)