Overview

In Mybatis of O / R mapping flake work, the automatic type conversion mechanism provided in Mybatis: Enum is handled using TypeHandler. We will also introduce how to write dynamic SQL and its application using Spring MVC.

Reasons for summarizing

There is a column [^ 1] that handles "flags" and "division values" that are likely to exist in the database used by newly created systems and applications that have already been created, and the range of those values is almost unchanged [^ 2]. ]is. There are many cases where these values are handled as character strings (CHAR or VARCHAR) or numeric types, so at least in the Java code world, strings and numbers are not treated as they are, and the range of values is wide. If it is decided, can it be handled by enum ...? Is the beginning.

Article premise

In this article, we have created and confirmed the operation with the following versions.

Java 8u152 (1.8.0_152)
Spring-Boot 1.5.9.RELEASE
mybatis-spring-boot-starter 1.3.1
- mybatis 3.4.5
- mybatis-spring 1.3.1
Lombok 1.16.18

Set of source files

It is published on GitHub.

https://github.com/A-pZ/mybatis-spring-enum-sample

Implementation of search function

Introduced this time is a function that returns the result of searching a table that handles a certain flag or division value.

Contents to be implemented

To briefly explain what to implement,

Create enums that represent database flags and partition values
Create an automatic type conversion class (TypeHandler) for the created enum
Register TypeHandler

is.

Table to reference

This is the definition [^ 3] of the table to be referenced this time.

Product table (table name: item)

Column name	Mold	The role of the column	Constraints etc.
id	Numerical value	Primary key that uniquely defines the product	Value to be automatically numbered
name	String	Product name	Product nameの表示に使う
status	String	Product display classification	0:No status 1:Open to the public 2:Members only
display	String	Show / hide products	0:Do not display 1:indicate

This time, we will focus on the three-value division value defined by status and the flag handled by display.

Enums that define flags and partition values

Create enums on the Java side for the two columns status and display that appear this time. Since status is "the one that determines the scope of product disclosure", rename it to ItemPublish. Since display is a binary value that determines whether to display or not, it is generally set to TrueOrFalse so that it can be used in other columns.

`ItemPublish.java`


import java.util.Arrays;

import lombok.AllArgsConstructor;
import lombok.Getter;

/**
 *Product display category.
 */
@Getter
@AllArgsConstructor
public enum ItemPublish {
	NONE("0"), PUBLISH("1"), MEMBER_ONLY("2"), NO_VALUE("");

	private String value;

	public static ItemPublish getDisplayStatus(String value) {
		return Arrays.stream(values())
				.filter(v -> v.getValue().equals(value))
				.findFirst()
				.orElse(NO_VALUE);
	}
}

NO_VALUE is defined [^ 4] in case a value outside the range that the division value can take is specified.

`TrueOrFalse.java`


@Getter
@AllArgsConstructor
public enum TrueOrFalse {
  TRUE("1"), FALSE("0"), NONE("");

  private String value;

  TrueOrFalse(String value) {
    this.value = value;
  }

  public static TrueOrFalse getTrueOrFalse(String input) {
    return Arrays.stream(values())
        .filter(v -> v.getValue().equals(input))
          .findFirst().orElse(NONE);
  }
}

An enum that defines a boolean value (1 for true, 0 for false). Values other than 0 and 1 are unexpected, but unexpected values are defined as NONE.

Spring and Mybatis implementation

An implementation that queries the database. Spring MVC Repository and Service. The search condition is defined in the ItemCondition class, and one record of the search result is defined in the Item class.

`ItemRepository.java`


mport java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import lombok.RequiredArgsConstructor;
import serendip.spring.sample.mybatis.model.Item;
import serendip.spring.sample.mybatis.model.ItemCondition;

/**
 *Product search repository.
 */
@Repository
@RequiredArgsConstructor
public class ItemRepository {
	private final SqlSession sqlSession;

	public List<Item> selectItems(ItemCondition condition) {
		return sqlSession.selectList("selectItems", condition);
	}
}

`ItemService.java`


import java.util.List;

import org.springframework.stereotype.Service;

import lombok.RequiredArgsConstructor;
import lombok.extern.log4j.Log4j2;
import serendip.spring.sample.mybatis.model.Item;
import serendip.spring.sample.mybatis.model.ItemCondition;
import serendip.spring.sample.mybatis.repository.ItemRepository;

/**
 *Product search Service.
 */
@Service
@RequiredArgsConstructor
public class ItemService {
	private final ItemRepository repository;

	public List<Item> selectItems(ItemCondition condition) {
		return repository.selectItems(condition);
	}
}

Item Condition of the search condition. The enum defined earlier is specified as it is as a condition.

`ItemCondition.java`


import lombok.Builder;
import lombok.Getter;
import lombok.ToString;

/**
 *Product search conditions.
 */
@Builder
@ToString
@Getter
public class ItemCondition {
	private int id;
	private ItemPublish status;
	private TrueOrFalse display;
}

Item that represents one record of the search result. Handle the values of columns status and display in the defined enum respectively.

`Item.java`


import lombok.Getter;
import lombok.Setter;

/**
 *Product table(Item)1 record of.
 */
@Getter @Setter
public class Item {
	private Integer id;
	private String name;
	private ItemPublish status;
	private TrueOrFalse display;
}

Define SQL Mapping

The SQL to be executed this time is as follows.

`sql-mappings.xml`


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="serendip.spring.sample.mybatis.repository.MemberRepository">
	<select id="selectItems"
		parameterType="serendip.spring.sample.mybatis.model.ItemCondition"
		resultType="serendip.spring.sample.mybatis.model.Item">
		SELECT
			id,
			name,
			status,
			display
		FROM
			item
	</select>
</mapper>

Now the implementation class is ready. However, when I execute it as it is, Mybatis automatically converts to enum, but a runtime exception is thrown because the method [^ 5] to be executed by default cannot be obtained.

Therefore, Mybatis uses TypeHandler which performs type conversion for a specific enum.

Create TypeHandler for enum

TypeHandler is what Mybatis does the conversion between the Java class included in the SQLMapping definition and the database.

Implemented TypeHandler

The implemented TypeHandler is provided in Mybatis's org.apache.ibatis.type package. Here, the conversion to Java type and the TypeHandler for the type used in the database column are defined. For example, Java type conversions for Big Decimal and database BLOB type conversions are also provided, which perform the functions found in JDBC PretaredStatements.

Extension of TypeHandler

TypeHandler of Mybatis is done by BaseTypeHandler <class to be converted>. Implement 4 methods, setNonNullParameter to set the value to be passed to SQL and getNullableResult (3 methods in total) to receive the value.

`ItemPublishTypeHandler.java`


import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import serendip.spring.sample.mybatis.model.ItemPublish;

/**
 *Type conversion class for Enum of product publication status.
 */
public class ItemPublishTypeHandler extends BaseTypeHandler<ItemPublish> {

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, ItemPublish parameter, JdbcType jdbcType)
			throws SQLException {
		ps.setString(i, parameter.getValue());
	}

	@Override
	public ItemPublish getNullableResult(ResultSet rs, String columnName) throws SQLException {
		return ItemPublish.getDisplayStatus(rs.getString(columnName));
	}

	@Override
	public ItemPublish getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		return ItemPublish.getDisplayStatus(rs.getString(columnIndex));
	}

	@Override
	public ItemPublish getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		return ItemPublish.getDisplayStatus(cs.getString(columnIndex));
	}
}

`TrueOrFalseTypeHandler.java`


import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import serendip.spring.sample.mybatis.model.TrueOrFalse;

/**
 *Type conversion class for Enum that handles flags.
 */
public class TrueOrFalseTypeHandler extends BaseTypeHandler<TrueOrFalse> {

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, TrueOrFalse parameter, JdbcType jdbcType)
			throws SQLException {
		ps.setString(i, parameter.getValue());
	}

	@Override
	public TrueOrFalse getNullableResult(ResultSet rs, String columnName) throws SQLException {
		return TrueOrFalse.getTrueOrFalse(rs.getString(columnName));
	}

	@Override
	public TrueOrFalse getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		return TrueOrFalse.getTrueOrFalse(rs.getString(columnIndex));
	}

	@Override
	public TrueOrFalse getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		return TrueOrFalse.getTrueOrFalse(cs.getString(columnIndex));
	}
}

Registration of TypeHandler

The created TypeHandler is described in the Mybatis configuration file. Describe the TypeHandler created by the <typeHandler> element one by one in the <typeHandlers> element.

`mybatis-config.xml`


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeHandlers>
		<typeHandler handler="serendip.spring.sample.mybatis.typehandler.ItemPublishTypeHandler"/>
		<typeHandler handler="serendip.spring.sample.mybatis.typehandler.TrueOrFalseTypeHandler"/>
	</typeHandlers>
...
</configuration>

Applies to search conditions

In addition to search results from the database, TypeHandler can be applied to arguments (parameters).

Description of handling enum in SQL Mapping

The search condition uses TrueOrFalse of ItemCondition mentioned above. For the most concise use, there is a way to get the value with getValue () of enum.

`sql-mappings.xml`


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="serendip.spring.sample.mybatis.repository.MemberRepository">
	<select id="selectItems"
		parameterType="serendip.spring.sample.mybatis.model.ItemCondition"
		resultType="serendip.spring.sample.mybatis.model.Item">
		SELECT
			id,
			name,
			status,
			display
		FROM
			item
		<where>
			<if test="display.getValue() != ''">
			     display = #{display.value}
			</if>
		</where>
	</select>
</mapper>

However, this method feels a little unreasonable because the contents described only in enum are compared with the character strings inside the dynamic SQL. However, Mybatis can write conditional statements of dynamic SQL in OGNL.

Let's use it.

Compare enums directly with SQL Mapping

To refer to an enum in OGNL, use @enum's full class name @ enumeration as shown below.

`sql-mappings.xml`


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="serendip.spring.sample.mybatis.repository.MemberRepository">
	<select id="selectItems"
		parameterType="serendip.spring.sample.mybatis.model.ItemCondition"
		resultType="serendip.spring.sample.mybatis.model.Item">
		SELECT
			id,
			name,
			status,
			display
		FROM
			item
		<where>
			<if test="display != @serendip.spring.sample.mybatis.model.TrueOrFalse@NONE">
			    display = #{display.value}
			</if>
		</where>
	</select>
</mapper>

You can now apply enums to dynamic SQL conditionals as well.

Via RestController of Spring MVC

Execute the contents introduced this time from RestController of Spring and create it so that it returns a response in JSON format.

Controller implementation

import java.util.List;
import java.util.Optional;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import lombok.RequiredArgsConstructor;
import serendip.spring.sample.mybatis.model.Item;
import serendip.spring.sample.mybatis.model.ItemCondition;
import serendip.spring.sample.mybatis.model.ItemPublish;
import serendip.spring.sample.mybatis.model.TrueOrFalse;
import serendip.spring.sample.mybatis.service.ItemService;

/**
 *Product search RestController.
 */
@RestController
@RequestMapping("/items")
@RequiredArgsConstructor
public class ItemController {

	private final ItemService service;

	@GetMapping("")
	public List<Item> searchItems(@RequestParam Optional<String> publish, @RequestParam Optional<String>  display) {
		ItemCondition condition = ItemCondition.builder()
				.status(ItemPublish.getDisplayStatus(publish.orElse("")))
				.display(TrueOrFalse.getTrueOrFalse(display.orElse("")))
				.build();

		return service.selectItems(condition);
	}
}

Assuming that the search condition is not set, if the parameter is undefined, it will be the value when it is undefined in each enum value.

Execution result

http://127.0.0.1:8080/items

[
    {
        "id": 1,
        "name": "Wooden chair",
        "status": "NONE",
        "display": "TRUE"
    },
    {
        "id": 2,
        "name": "Glass table",
        "status": "PUBLISH",
        "display": "TRUE"
    },
    {
        "id": 3,
        "name": "Wooden table",
        "status": "MEMBER_ONLY",
        "display": "FALSE"
    }
]

http://127.0.0.1:8080/items?display=0

[
    {
        "id": 3,
        "name": "Wooden table",
        "status": "MEMBER_ONLY",
        "display": "FALSE"
    }
]

http://127.0.0.1:8080/items?display=1

[
    {
        "id": 1,
        "name": "Wooden chair",
        "status": "NONE",
        "display": "TRUE"
    },
    {
        "id": 2,
        "name": "Glass table",
        "status": "PUBLISH",
        "display": "TRUE"
    }
]

For this parameter value, the database value is used as it is as the condition value, but other values can be used in the Controller. Also, if you want to further convert the JSON response value, implement jackson's JsonSerialize interface in the enum and it will be converted automatically.

in conclusion

We hope that you can use automatic type conversion and value conversion to help build more robust and maintainable applications (・ ω ・).

[^ 1]: There is also a "development site or vendor dialect" in the content indicated by the "flag" and "classification value". In this article, "flag" is a column that has only two values, that is, a column that shows only true / false of so-called boolean, and "partition value" is a column that has a fixed range of possible values for purposes other than boolean. [^ 2]: For example, there are cases where the delete flag and update flag, which are not so preferable even as of 2018, exist regardless of whether the system is old or new. 0: valid records, 1: deleted, etc. [^ 3]: The column name is intentionally made sloppy, which is difficult to guess from the name, which tends to be a legacy system. What are status and display! ?? Will it be? [^ 4]: This time I always return some value, but if an impossible value is specified, it may throw an application exception. [^ 5]: The java.lang.Enum.valueOf method is executed.

Handle Enum in Mybatis TypeHandler

Overview

Reasons for summarizing

Article premise

Set of source files

Implementation of search function

Contents to be implemented

Table to reference

Enums that define flags and partition values

ItemPublish.java

TrueOrFalse.java

Spring and Mybatis implementation

ItemRepository.java

ItemService.java

ItemCondition.java

Item.java

Define SQL Mapping

sql-mappings.xml

Create TypeHandler for enum

Implemented TypeHandler

Extension of TypeHandler

ItemPublishTypeHandler.java

TrueOrFalseTypeHandler.java

Registration of TypeHandler

mybatis-config.xml

Applies to search conditions

Description of handling enum in SQL Mapping

sql-mappings.xml

Compare enums directly with SQL Mapping

sql-mappings.xml

Via RestController of Spring MVC

Controller implementation

Execution result

in conclusion

`ItemPublish.java`

`TrueOrFalse.java`

`ItemRepository.java`

`ItemService.java`

`ItemCondition.java`

`Item.java`

`sql-mappings.xml`

`ItemPublishTypeHandler.java`

`TrueOrFalseTypeHandler.java`

`mybatis-config.xml`

`sql-mappings.xml`

`sql-mappings.xml`