hibernate:如何读取作为自定义代码存储在数组中的一组枚举

Question

主题是问题。让我邀请你深入了解一些背景。我创建了一个包含单个表的全新DB，并用单行填充该表:

create table schedule (
id int generated by default as identity (start with 100) primary key,
days_of_week char(3)[] not null check (true/*
here I unsure that days_of_week is a unidimensional
and it's not empty
and its length is equal to the length of an array
filled with unique values of days_of_week values only
*/)
);
insert into schedule (id, days_of_week) values
(0, array['mon', 'tue', 'wed', 'thu', 'fri', 'sat', 'sun']);

…生成实体类(混合龙目表)

@Entity @Data
public class Schedule {
@Id @GeneratedValue
private Integer id;
}
public interface ScheduleRepository
extends CrudRepository<Schedule, Integer> {}

. .并使用:

执行此设置

@Bean
CommandLineRunner printAllSchedules(ScheduleRepository repository) {
return args -> {
for (Schedule schedule : repository.findAll()) {
log.info(schedule.toString());
}
};
}

最后得到:

213347.962 I main/Application : Schedule(id=0)

瞧!现在我将缺失的属性添加到我的实体:

@Entity @Data
public class Schedule {
@Id @GeneratedValue
private Integer id;
private Set<DayOfWeek> daysOfWeek;
}

失败(预期):

java.lang.IllegalStateException: Failed to execute CommandLineRunner
Caused by: org.springframework.orm.jpa.JpaSystemException: Unable to
extract JDBC value for position `2`
Caused by: org.hibernate.HibernateException: Unable to extract JDBC
value for position `2`
Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast
to class java.lang.Number (java.lang.String and java.lang.Number are in module
java.base of loader 'bootstrap')

啊哈，等一下:

@Enumerated(EnumType.STRING)
private Set<DayOfWeek> daysOfWeek;

运行并得到不同的结果:

java.lang.IllegalStateException: Failed to execute CommandLineRunner
Caused by: org.springframework.orm.jpa.JpaSystemException: Unable to
extract JDBC value for position `2`
Caused by: org.hibernate.HibernateException: Unable to extract JDBC
value for position `2`
Caused by: java.lang.IllegalArgumentException: No enum constant
java.time.DayOfWeek.mon

显然，它从days_of_week列读取数组，并试图将mon转换为任意DayOfWeek，并且预期会失败。让我们试着在表中输入"正确"字符串，表示一周中的天数:

alter table schedule
alter column days_of_week
set data type varchar(9)[];
update schedule
set days_of_week = array['SUNDAY', 'MONDAY']
where id = 0;

…和运行:

215032.322 I main/Application : Schedule(id=0, daysOfWeek=[MONDAY, SUNDAY])

现在，让我们添加这一列:

alter table schedule
add column having_sex_day char(3)
not null default 'fri';
-- today, January 13, 23 is Friday, not what you thought,
-- and sex is what I'm doing right now here, not what you thought

…也触摸实体:

@Entity @Data
public class Schedule {
// ...
@Convert(converter = DayOfWeekConverter.class)
private DayOfWeek havingSexDay;
}
@Converter
public class DayOfWeekConverter
implements AttributeConverter<DayOfWeek, String> {
private static final Map<DayOfWeek, String> ENUM_ABBR = new HashMap<>(7, 1F);
private static final Map<String, DayOfWeek> ABBR_ENUM = new HashMap<>(7, 1F);
static {
for (DayOfWeek i : DayOfWeek.values()) {
String abbr = i.name().substring(0, 3).toLowerCase(Locale.ROOT);
ENUM_ABBR.put(i, abbr);
ABBR_ENUM.put(abbr, i);
}
}
@Override
public String convertToDatabaseColumn(DayOfWeek attribute) {
return ENUM_ABBR.get(attribute);
}
@Override
public DayOfWeek convertToEntityAttribute(String dbData) {
return ABBR_ENUM.get(dbData);
}
}

…和运行:

215956.497 I main/Application : Schedule(id=0, daysOfWeek=[MONDAY, SUNDAY], havingSexDay=FRIDAY)

好了，伙计们，最后一笔:现在我想让Hibernate读取daysOfWeek写成三个字母的缩写，使用相同的转换器:

@Entity @Data
public class Schedule {
@Id @GeneratedValue
private Integer id;
@Convert(converter = DayOfWeekConverter.class) // <-- IDE warns me here
private Set<DayOfWeek> daysOfWeek;
@Convert(converter = DayOfWeekConverter.class)
private DayOfWeek havingSexDay;
}
// The warning says: Converter's type DayOfWeek doesn't match
// with the attribute type Set<DayOfWeek>. I know, bro, I know

…更新表:

update schedule
set days_of_week = array['sun', 'mon']
where id = 0;

…结果:

java.lang.IllegalStateException: Failed to execute CommandLineRunner
Caused by: org.springframework.orm.jpa.JpaSystemException: Unable to
extract JDBC value for position `2`
Caused by: org.hibernate.HibernateException: Unable to extract JDBC
value for position `2`
Caused by: java.lang.IllegalArgumentException: No enum constant
java.time.DayOfWeek.mon

。我做了这样的改变:

@Converter(autoApply = true)
public class DayOfWeekConverter
implements AttributeConverter<DayOfWeek, String> {
// ...
}

…结果相同。

请帮我来回移动，来回几天的星期存储在一个数组缩写。

谢谢!

---

乌利希期刊指南:@EricGeorge，建议试试@ElementCollection。@ElementCollection用于存储在另一个表(关系)中的值，而不是存储在数组列中的值。但我还是试了:

java.lang.IllegalStateException: Failed to execute CommandLineRunner
Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException:
JDBC exception executing SQL [select d1_0.schedule_id,d1_0.days_of_week
from schedule_days_of_week d1_0 where d1_0.schedule_id=?]; SQL [n/a]
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing
SQL [select d1_0.schedule_id,d1_0.days_of_week from schedule_days_of_week d1_0
where d1_0.schedule_id=?]
Caused by: org.postgresql.util.PSQLException:
ERROR: relation "schedule_days_of_week" does not exist

的预期。对不起，Eric🤷🏼‍♂️

Answer 1

尝试添加@ElementCollection注释:

@Entity @Data
public class Schedule {
@Id @GeneratedValue
private Integer id;
@ElementCollection(targetClass=java.time.DayOfWeek, fetch = FetchType.EAGER)
@Convert(converter = DayOfWeekConverter.class) 
private Set<DayOfWeek> daysOfWeek;
@Convert(converter = DayOfWeekConverter.class)
private DayOfWeek havingSexDay;
}