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.