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.
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.
In this article, we have created and confirmed the operation with the following versions.
It is published on GitHub.
https://github.com/A-pZ/mybatis-spring-enum-sample
Introduced this time is a function that returns the result of searching a table that handles a certain flag or division value.
To briefly explain what to implement,
is.
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.
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.
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;
}
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.
TypeHandler is what Mybatis does the conversion between the Java class included in the SQLMapping definition and the database.
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.
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));
}
}
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>
In addition to search results from the database, TypeHandler can be applied to arguments (parameters).
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.
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.
Execute the contents introduced this time from RestController of Spring and create it so that it returns a response in JSON format.
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.
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.
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.