[JAVA] I tried to implement flexible OR mapping with MyBatis Dynamic SQL

Let's use MyBatis Dynamic SQL!

This time I tried an implementation that uses MyBatis Dynamic SQL to issue SQL and retrieve data!

I will use it for business, so I will leave it as a memorandum. The implementation format is similar to Seasar2's S2JDBC, and SQL is created by performing multiple method chains.

Ready to run

I prepared a simple table like this. Register several data in Texto.

CREATE TABLE "MEMBER" ( 
  "ID" NUMBER(8,0)
  , "BLOOD" VARCHAR2(20 BYTE)
  , "NAME" VARCHAR2(20 BYTE)
  , "CORP" VARCHAR2(20 BYTE)
);

Entity is also created according to the table structure.

public class Member {
	
	private Integer id;
	
	private String name;
	
	private String corp;
	
	private String blood;
	
    //Getter / setter omitted

}

Create the mapper interface as follows. This time, it is implemented on the assumption that multiple data will be returned to List.

public interface MemberMapper {

	@SelectProvider(type=SqlProviderAdapter.class, method="select")
	@Results(id="memberResult", value={
		@Result(column="ID", property="id"),
		@Result(column="NAME", property="name"),
		@Result(column="CORP", property="corp"),
		@Result(column="BLOOD", property="blood"),
	})
	List<Member> selectMany(SelectStatementProvider selectStatement);

}

Create a support class to work with MyBatis Dynamic SQL. It is used when specifying the column to be acquired or the WHERE clause.

public final class MemberDynamicSqlSupport {

	public static final Member Member = new Member();

	public static final SqlColumn <Integer> id = Member.id;
	public static final SqlColumn <String> name = Member.name;
	public static final SqlColumn <String> corp = Member.corp;
	public static final SqlColumn <String> blood = Member.blood;


	public static final class Member extends SqlTable {
		public final SqlColumn <Integer> id = column("ID", JDBCType.INTEGER);
		public final SqlColumn <String> name = column("NAME", JDBCType.VARCHAR);
		public final SqlColumn <String> corp = column("CORP", JDBCType.VARCHAR);
		public final SqlColumn <String> blood = column("BLOOD", JDBCType.VARCHAR);

		public Member() {
			super("Member");
		}
	}

}

SQL execution

Let's implement a class that actually issues SQL using the class created above. This time I will implement the test code using junit.

import static jp.co.stylez.support.MemberDynamicSqlSupport.*;
import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
import static org.mybatis.dynamic.sql.SqlBuilder.isLessThan;
import static org.mybatis.dynamic.sql.SqlBuilder.select;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = MybatisConfig.class)
public class Main {

	@Autowired
	ApplicationContext context;

	@Test
	public void test() {

		MemberMapper mapper = context.getBean(MemberMapper.class);

		SelectStatementProvider selectStatement = select(Member.allColumns()).from(Member).where(id, isLessThan(10))
				.and(corp, isEqualTo("stylez")).orderBy(id).build().render(RenderingStrategies.MYBATIS3);

		List<Member> members = mapper.selectMany(selectStatement);

		for (Member member : members) {
			System.out.println("********************");
			System.out.println("id:" + member.getId());
			System.out.println("name:" + member.getName());
			System.out.println("corp:" + member.getCorp());
			System.out.println("blood:" + member.getBlood());
			System.out.println("********************");
		}
	}

}

Implement the Config class separately and set the DataSource and MapperScan targets. It can be set in XML format, but this time it was set in Java. (I will omit the explanation related to the setting)

The Mapper registered with MapperScan is taken out and the SQL is actually executed.

The execution result is as follows.

********************
id:1
name:hogehoge
corp:stylez
blood:B
********************
********************
id:2
name:test.taro
corp:stylez
blood:O
********************
********************
id:3
name:hiroya.endo
corp:stylez
blood:A
********************

It was confirmed that multiple data of the specified conditions could be acquired!

Try it out ...

I felt that it was possible to implement it very flexibly, mainly regarding the condition specification of the WHERE clause. The readability of the code was very high, and it was easy to coat, so I had the impression that it was easy to use.

I think that is also one of the attractions because it can handle table joins and subqueries. It is also possible to use it properly with SQL using Mapper.xml, so I think that you can manage the configuration as needed.

I hope it will be a good reference for those who will use it from now on!

reference

https://github.com/mybatis/mybatis-dynamic-sql

https://mybatis.org/mybatis-dynamic-sql/docs/conditions.html

Recommended Posts

I tried to implement flexible OR mapping with MyBatis Dynamic SQL
I tried to implement ModanShogi with Kinx
I tried to implement TCP / IP + BIO with JAVA
I tried to implement Stalin sort with Java Collector
[Rails] I tried to implement batch processing with Rake task
I tried to interact with Java
I tried to implement the image preview function with Rails / jQuery
I tried to get started with WebAssembly
I tried to implement the Iterator pattern
01. I tried to build an environment with SpringBoot + IntelliJ + MySQL (MyBatis) (Windows10)
I tried to verify AdoptOpenJDK 11 (11.0.2) with Docker image
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 to implement deep learning in Java
I tried to implement a server using Netty
I tried to break a block with java (1)
I tried to implement a function equivalent to Felica Lite with HCE-F of Android
I tried to read and output CSV with Outsystems
I tried to implement Firebase push notification in Java
[Java 11] I tried to execute Java without compiling with javac
I started MySQL 5.7 with docker-compose and tried to connect
I tried to solve AOJ's Small, Large, or Equal
I tried to get started with Spring Data JPA
I tried to make FizzBuzz that is uselessly flexible
I tried to draw animation with Blazor + canvas API
[Java] I tried to implement Yahoo API product search
[Swift] I tried to implement Instagram profile-like UI with UICollectionView only with code without storyboard
I tried to implement the Euclidean algorithm in Java
roman numerals (I tried to simplify it with hash)
[Swift] I tried to implement exception handling for vending machines
I tried to implement the like function by asynchronous communication
I tried to make an introduction to PHP + MySQL with Docker
I tried to create a java8 development environment with Chocolatey
I tried to modernize a Java EE application with OpenShift.
[Rails] I tried to create a mini app with FullCalendar
[Swift] I tried to implement the function of the vending machine
I tried to link chat with Minecraft server with Discord API
I tried to automate LibreOffice Calc with Ruby + PyCall.rb (Ubuntu 18.04)
Mapping to a class with a value object in How to MyBatis
I tried to implement a buggy web application in Kotlin
I want to implement various functions with kotlin and java!
[Rails] I tried to implement "Like function" using rails and js
I tried to create a padrino development environment with Docker
I tried to get started with Swagger using Spring Boot
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
I tried to be able to pass multiple objects with Ractor
I tried DI with Ruby
I tried node-jt400 (SQL Update)
I tried node-jt400 (SQL query)
I tried node-jt400 (SQL stream)
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to verify yum-cron
I tried to solve the problem of "multi-stage selection" with Ruby
I tried to implement Ajax processing of like function in Rails
I tried to build the environment of PlantUML Server with Docker
I tried connecting to MySQL using JDBC Template with Spring MVC
I tried to build an http2 development environment with Eclipse + Tomcat
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
I tried to reimplement Ruby Float (arg, exception: true) with builtin