[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