JDBC java.sql.SQLException当将转换的枚举值插入MySql Smallint列时

Question

我正试图使用枚举来显示"；"许可"；字段(读、写等(。当我将记录存储在关联的MySQL表中时，权限枚举字段需要转换为关联的整数(1、2、4等(。当我运行SpringBoot应用程序并调用POST端点时，我会遇到一个奇怪的SQL异常。这里有一个例外：

无法执行DbAction.InsertRoot(entity=Invite(id=null，createdAt=2020-09-19 18:12:01，createdBy=1，listId=8，token=c3b95dc9-ec4d-4dde-a461-b12746afa6e，expiresAt=2020-09-26 18:12:01(，permission=2(，generatedId=null(]java.sql.SQLException：整数值不正确：第1行"permission"列的"WRITE"在com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129(~[mysql-connector-java-8.0.19.jar:8.0.19]

对我来说似乎很奇怪的是，db行动会报告"；"许可"；要插入的字段是2，但是SQL异常报告"0"；写入"；不是有效的整数值。知道我可能错过了什么吗？

以下是值得注意的代码和配置。请注意，我使用的是JDBC，而不是JPA:

这是枚举类：

public enum Permission {
READ(1), WRITE(2), CREATE(4), DELETE(8), ADMIN(16);
private Integer value;
Permission(final Integer value) {
this.value = value;
}
public Integer getValue() {
return this.value;
}
//@JsonCreator
@Override
public String toString() {
return this.getValue().toString();
}
//@JsonValue
public static Permission forValue(final String name) {
return Permission.valueOf(name);
}
}

此处为POJO/实体：

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.ToString;
import org.springframework.data.annotation.Id;
@Data
@AllArgsConstructor
@Builder
@ToString
public class Invite {
public static final int TOKEN_MINIMUM_LENGTH = 36;
public static final int TOKEN_MAXIMUM_LENGTH = 40;
@Id
private Long id;
private String createdAt;
private String createdBy;
private Long listId;
private String token;
private String expiresAt;
private Permission permission;
}

这是JdbcCustomConversions。需要注意的是，调试时ReadingConverter似乎跳闸了，但WritingConverter没有。我不确定这是否是问题的一部分。

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.convert.ReadingConverter;
import org.springframework.data.convert.WritingConverter;
import org.springframework.data.jdbc.core.convert.JdbcCustomConversions;
import org.springframework.data.jdbc.repository.config.AbstractJdbcConfiguration;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
import java.util.Arrays;
@Configuration
@EnableAutoConfiguration
public class JdbcConfiguration extends AbstractJdbcConfiguration {
/**
* JDBC Custom Conversions.
*
*/
@Override
public JdbcCustomConversions jdbcCustomConversions() {
return new JdbcCustomConversions(Arrays.asList(PermissionToInteger.INSTANCE, IntegerToPermission.INSTANCE));
}
// I have never seen this get tripped
@WritingConverter
enum PermissionToInteger implements Converter<Permission, Integer> {
INSTANCE;
@Override
public Integer convert(final Permission permission) {
return permission == null ? null : permission.getValue();
}
}
// this one trips in the debugger and appears to be working fine
@ReadingConverter
enum IntegerToPermission implements Converter<Integer, Permission> {
INSTANCE;
@Override
public Permission convert(final Integer code) {
for (final Permission perm: Permission.values()) {
if (perm.getValue().equals(code)) {
return perm;
}
}
return null;
}
}
}

这是一个简单的存储库：

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import java.util.List;
import java.util.Optional;
public interface InviteRepository extends CrudRepository<Invite, Long> {
@Query("SELECT * from invite where token = :token")
Optional<Invite> findByToken(String token);
@Query("SELECT * from invite where created_by = :userId")
List<Invite> findByUserId(Long userId);
@Query("SELECT * from invite where list_id = :wishlistId")
List<Invite> findByWishlistId(Long wishlistId);
}

任何提示都将不胜感激！

Aaron

Answer 1

修复方法是将WritingConverter更改为String，而不是Integer:

@WritingConverter
public enum PermissionToInteger implements Converter<Permission, String> {
INSTANCE;
@Override
public String convert(final Permission permission) {
return permission == null ? null : permission.getValue().toString();
}
}

Answer 2

在调试了spring-jdbc的源代码后，它可以通过JdbcValue类中指定的目标类将枚举类型转换为integer。

@Bean
override fun jdbcCustomConversions() = JdbcCustomConversions(arrayListOf(
@WritingConverter
object: Converter<LoginTypes, JdbcValue> {
override fun convert(p0: LoginTypes) = JdbcValue.of(p0.code,JDBCType.INTEGER)
},
@ReadingConverter
object:Converter<Int,LoginTypes>{
override fun convert(p0: Int): LoginTypes? = LoginTypes.values().first { it.code==p0 }
}```