I tried UPSERT with PostgreSQL.

Introduction

When performing a DB operation, "Update if this data has been INSERTed or is caught in a UNIQUE constraint, Otherwise I want to INSERT " At that time, I used to branch with Java. But if you are using PostgreSQL, it seems that you can do that at the same time. So I tried it.

By the way, the implementation with only SQL has quite a lot of information on other sites, so this time I will take the method using MyBatis. That doesn't change much, though.

The environment is as follows IDE:Eclipse </>:Java8,SpringBoot <DB related (main line)>: PostgreSQL, MyBatis

Environment

Since Spring Boot and MyBatis itself, and cooperation with PostgreSQL are not the main points, I will omit them to some extent. This time is Gradle.

Gradle (excerpt)

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.2'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'org.postgresql:postgresql'
	annotationProcessor 'org.projectlombok:lombok'
	providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

application.properties (excerpt)

<!-- Postgres property -->
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/hrm
spring.datasource.username=*****
spring.datasource.password=*****

Prepare a table

Prepare a table in PostgreSQL. This time Schema will be public.

PostgreSQL

create table tableName (
		id serial primary key,
		user_id integer not null,
		date_id integer not null,
		is_working smallint,
		);

		alter table tableName add constraint user_date unique(user_id, date_id);

Here is an example of this application. For example, suppose you have an application that manages a user's to-do list and checks if the user has completed a task. Insert 1 in is_working if the task is done, 0 otherwise, so that you can update all at once for one month. If you want to edit the task management retroactively, or if it turns out that the task of the previous day was not completed on September 2nd, I have to update is_working on September 1st. At this time, assuming that the value you want to update like September 1st, the value you want to newly register like September 2nd, or the case where there are multiple users, the above SQL is called user_id. The constraint is set only when the date_id matches.

All you have to do is pass the values from html to Controller and Mapper and write the upsert syntax in Mapper.

Mapper (excerpt)

<insert id="upsert">
	insert into ${tableName} (
	user_id,
	date_id,
	is_working
	)
	values (
	#{user_id},
	#{date_id},
	#{is_working}
	)
	ON CONFLICT (user_id, date_id)
	do update set
	is_working = #{is_working}
</insert>

Only this.

important point

When writing syntax in Mapper, does the word UPSERT exist in MyBatis? For, You will write insert or update. Also, if you do not write the conflict name correctly, it will not work, so be careful not to make a mistake.

Any other way?

There seems to be another specification method using ** DONOTHING ** and INDEX.

If it is DONOTHING, here For other detailed usage, I think the here site will be useful.

Summary

I saw many ways to type SQL directly, but there weren't many that were actually coded. I tried to summarize it for reference. However, it may not be relevant to any method, not limited to MyBatis, because the SQL syntax does not change. As long as you can pass the parameters to Dao, the description is the same.

I think that it is more organized than writing it in an if statement, so I would like to actively utilize it in a PostgreSQL environment.

Recommended Posts

I tried UPSERT with PostgreSQL.
I tried DI with Ruby
I tried BIND with Docker
I tried Angular tutorial + SpringBoot + PostgreSQL
I tried using JOOQ with Gradle
I tried morphological analysis with MeCab
I tried UDP communication with Java
I tried GraphQL with Spring Boot
I tried Flyway with Spring Boot
I tried customizing slim with Scaffold
I tried using Realm with Swift UI
I tried to get started with WebAssembly
I tried using Scalar DL with Docker
I tried using OnlineConverter with SpringBoot + JODConverter
I tried Spring.
I tried time-saving management learning with Studyplus.
I tried playing with BottomNavigationView a little ①
I tried tomcat
I tried using OpenCV with Java + Tomcat
I tried Lazy Initialization with Spring Boot 2.2.0
I tried youtubeDataApi.
I tried refactoring ①
I tried FizzBuzz.
I tried to implement ModanShogi with Kinx
I tried JHipster 5.1
I tried to make an automatic backup with pleasanter + PostgreSQL + SSL + docker
I tried to make Basic authentication with Java
I tried to manage struts configuration with Coggle
I tried to manage login information with JMX
I tried writing CRUD with Rails + Vue + devise_token_auth
I also tried WebAssembly with Nim and C
I made blackjack with Ruby (I tried using minitest)
I tried Eclipse MicroProfile OpenAPI with WildFly Swarm
I tried to break a block with java (1)
I tried Getting Started with Gradle on Heroku
[I tried] Spring tutorial
I tried running Autoware
I tried using Gson
I tried QUARKUS immediately
I tried using TestNG
I tried Spring Batch
I tried using Galasa
I played with Refinements
I tried node-jt400 (Programs)
I tried node-jt400 (execute)
I tried node-jt400 (Transactions)
I tried what I wanted to try with Stream softly.
I tried to implement file upload with Spring MVC
I tried to implement TCP / IP + BIO with JAVA
[Rails] I tried playing with the comment send button
I started MySQL 5.7 with docker-compose and tried to connect
I tried to get started with Spring Data JPA
[Machine learning] I tried Object Detection with Create ML [Object detection]
I tried to draw animation with Blazor + canvas API
I tried OCR processing a PDF file with Java
I tried to implement Stalin sort with Java Collector
roman numerals (I tried to simplify it with hash)
I tried to make an introduction to PHP + MySQL with Docker
I tried to create a java8 development environment with Chocolatey
I made a development environment with rails6 + docker + postgreSQL + Materialize.
I tried to modernize a Java EE application with OpenShift.