Shulou(Shulou.com)06/02 Report--

This article mainly introduces the condition of Mybatis query sentence for enumeration type Times error how to do, the article is very detailed, has a certain reference value, interested friends must read it!

Mybatis query statement condition reports an error for an enumerated type

Usually we use the tinyInt type for some enumerated fields in the database, and the corresponding fields of the java object are often defined as short or int for convenience. But this is obviously not beautiful and convenient, let the later maintainers scratch their heads to find out where your constant is defined, without comments, it is simply collapsing. After a long time, no one knows the value of this. You can only read the source code line by line.

Elegant programmers certainly think of elegant enumerations, and mybatis's "powerful" enumeration type processor EnumOrdinalTypeHandler is no stranger.

However, it took a lot of effort to configure the enumeration processor to improve the original mapper running test cases. However, the insert and part of the query did not report wrong. At this point, halfway through the process, it makes people want to give up.

Usually this mistake is

"failed to invoke constructor for handler class org.apache.ibatis.type.EnumOrdinalTypeHandler"

The reason is that the damned query condition uses enumerated objects as conditions, whether you use selectExample or other select, an error will be reported when the condition where enum = # {enum}. Do not doubt that you are not paired with EnumOrdinalTypeHandler, if not, then all the query interfaces will report errors.

There is only one related question on stackoverflow. Why are there so few? Isn't that a common mistake? Jpa or hibernate can use enumerations gracefully. The reason is that foreigners seldom use semi-automatic mybatis framework. It is only the standard in China, and the reason, of course, is that I heard that Ali uses mybatis, so it must be good. It doesn't matter whether your business has really reached the point where you want to improve sql performance.

Then again, the answer so far seems to be mybatis's bug, but for a semi-automated framework like mybatis, it doesn't have to be bug.

The solution is simple and rough, replacing the where enum = # {enum} condition with where enum in (*) everything is fine. But familiar students have found out. This performance is obviously not as good as =. The students who use short and int must be happy again. See, I said that the type of database is what type, enumeration is rubbish. The students who say this are obviously not used to encapsulation and standardization, and prefer the feeling of doing whatever they want.

That's all for today's lesson.

Mybatis handles enumerated types 1. Enumerated package com.ahut.core.enums;import java.util.HashMap;import java.util.Map;/** @ ClassName: SexEnum * @ Description: gender enumeration * @ author cheng * @ date 8:32:27 * / public enum SexEnum {MAN ("1", "male"), WOMAN ("2", "female"); private String key; private String value Private static Map sexEnumMap = new HashMap (); static {for (SexEnum sexEnum: SexEnum.values ()) {sexEnumMap.put (sexEnum.getKey (), sexEnum);}} / * * privatization constructor * * @ param key * @ param value * / private SexEnum (String key, String value) {this.key = key This.value = value;} / * * @ Title: getSexEnumByKey * @ Description: get enumerations based on key * @ param key * @ return * / public static SexEnum getSexEnumByKey (String key) {return sexEnumMap.get (key);} public String getKey () {return key;} public void setKey (String key) {this.key = key } public String getValue () {return value;} public void setValue (String value) {this.value = value;}} 2, entity class package com.ahut.entity;import java.io.Serializable;import java.util.Date;import com.ahut.core.enums.SexEnum containing enumerations / * @ ClassName: Demo * @ Description: * @ author cheng * @ date 8:32:59 on November 21, 2017 * / public class Demo implements Serializable {/ * / private static final long serialVersionUID = 4122974131420281791L; private Date birthDay; private String userName; private int age; private String id; private SexEnum sex; public Demo () {super () / / TODO Auto-generated constructor stub} @ Override public String toString () {return "Demo [id=" + id + ", userName=" + userName + ", age=" + age + ", birthDay=" + birthDay + ", sex=" + sex + "]";} public String getId () {return id;} public void setId (String id) {this.id = id } public String getUserName () {return userName;} public void setUserName (String userName) {this.userName = userName;} public int getAge () {return age;} public void setAge (int age) {this.age = age;} public Date getBirthDay () {return birthDay;} public void setBirthDay (Date birthDay) {this.birthDay = birthDay } public SexEnum getSex () {return sex;} public void setSex (SexEnum sex) {this.sex = sex;}} 3, write enumeration processor package com.ahut.handler;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 com.ahut.core.enums.SexEnum / * @ ClassName: EnumHandler * @ Description: * @ author cheng * @ date 8:41:12 on November 20, 2017 * / public class SexEnumHandler extends BaseTypeHandler {/ * is used to define the setting parameters How to convert the parameter of Java type to the corresponding database type * / @ Override public void setNonNullParameter (PreparedStatement ps, int I, SexEnum parameter, JdbcType jdbcType) throws SQLException {/ / baseTypeHandler has done the null judgment of parameter / / the second parameter: the value ps.setString (I, parameter.getKey ()) stored in the database. } / * is used to define how to convert the database type to the corresponding Java type * / @ Override public SexEnum getNullableResult (ResultSet rs, String columnName) throws SQLException {System.out.println ("columnName execute me") when obtaining field data through the field name. / / the acquisition type is determined according to the storage type of the database. In this example, the database stores the String type String key = rs.getString (columnName); if (rs.wasNull ()) {return null;} else {/ / locate the SexEnum subclass return SexEnum.getSexEnumByKey (key) according to the key value in the database }} / * is used to define how to convert the database type to the corresponding Java type * / @ Override public SexEnum getNullableResult (ResultSet rs, int columnIndex) throws SQLException {System.out.println ("columnIndex execute me") when the field data is obtained through the field index. / / the acquisition type is determined according to the storage type of the database. In this example, the database stores the String type String key = rs.getString (columnIndex); if (rs.wasNull ()) {return null;} else {/ / locate the SexEnum subclass return SexEnum.getSexEnumByKey (key) according to the key value in the database }} / * how to convert the database type to the corresponding Java type * / @ Override public SexEnum getNullableResult (CallableStatement cs, int columnIndex) throws SQLException {/ / determine the acquisition type according to the database storage type after calling the stored procedure with definition. In this example, the database stores the String type String key = cs.getString (columnIndex) If (cs.wasNull ()) {return null;} else {/ / locate the SexEnum subclass return SexEnum.getSexEnumByKey (key) according to the key value in the database;} 4, configure the enumeration processor

Mybatis configuration

5. Dao layer package com.ahut.mapper;import java.util.List;import java.util.Map;import com.ahut.entity.Demo / * @ ClassName: DemoMapper * @ Description: * @ author cheng * @ date 9:10:38 * / public interface DemoMapper {/ * @ Title: saveDemo * @ Description: save * @ param map * @ throws Exception * / void saveDemo (Map map) throws Exception / * * @ Title: selectDemoList * @ Description: query * @ return * @ throws Exception * / List selectDemoList () throws Exception; / * * @ Title: selectDemoList1 * @ Description: query * @ return * @ throws Exception * / List selectDemoList1 () throws Exception 6. Mapper file INSERT INTO DEMO VALUES (replace (UUID (),'-','), # {USER_NAME}, # {AGE}, # {BIRTH_DAY}, # {SEX}) SELECT ID, USER_NAME, AGE, BIRTH_DAY, SEX FROM DEMO SELECT ID, USER_NAME USERNAME AGE, BIRTH_DAY BIRTHDAY, SEX FROM DEMO 7, Test package com.ahut.service Import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import com.ahut.core.enums.SexEnum;import com.ahut.entity.Demo / * @ ClassName: DemoServiceTest * @ Description: * @ author cheng * @ date 9:28:56 * / @ SpringBootTest@RunWith (SpringRunner.class) public class DemoServiceTest {@ Autowired private DemoService demoService; / * * @ Title: testSelectDemoList1 * @ Description: * @ throws Exception * / @ Test public void testSelectDemoList1 () throws Exception {List demoList = demoService.selectDemoList1 () For (Demo demo: demoList) {System.out.println (demo)}} / * * @ Title: testSelectDemoList * @ Description: * @ throws Exception * / @ Test public void testSelectDemoList () throws Exception {List demoList = demoService.selectDemoList () For (Map map: demoList) {for (String key: map.keySet ()) {if (key.equals (BIRTH_DAY)) {Date birthDay = (Date) map.get (key); System.out.println (key + ":" + birthDay) } else if (key.equals ("AGE")) {int age = (int) map.get (key); System.out.println (key + ":" + age);} else if (key.equals ("SEX")) {SexEnum sex = (SexEnum) map.get (key) System.out.println (key + ":" + sex);} else {String value = (String) map.get (key); System.out.println (key + ":" + value) }} / * * @ Title: testSaveDemo * @ Description: * @ throws Exception * / @ Test public void testSaveDemo () throws Exception {Map map = new HashMap (); map.put ("USER_NAME", "rick11"); map.put ("AGE", 22) Map.put ("BIRTH_DAY", new Date ()); map.put ("SEX", SexEnum.WOMAN); demoService.saveDemo (map);}

Execute the testSaveDemo method:

SexEnum.WOMAN is converted to 2 and stored in the database

Execute the testSelectDemoList1 method:

1 and 2 in the database were successfully converted to enumerations

When resultType is an entity class that contains enumerations, mybatis calls the enumeration handler

Execute the testSelectDemoList method:

Error

As you can see from the following figure, the enumeration processor is not called when resultType is map

The above is all the content of this article "what to do if the condition of Mybatis query statement is enumerated type Times error". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.