如何使用转换后的值执行查询

huangapple
go评论94阅读模式
英文:

How to perform a query with a converted value

问题

我有一个简单的转换器，用于将DayOfWeek映射为整数，以匹配使用Calendar星期几值的传统数据代码。然而，在执行条件查询时，似乎是使用了DayOfWeek的序数值，而不是我的转换后的值。

  1.     default Optional<ProgramSchedule> findByProgramWeekAndDay(final Program program, int week, final DayOfWeek dayOfWeek) {
  3.         return findOne(((root, query, cb) ->
  4.             cb.and(
  5.                 cb.equal(root.get(ProgramSchedule_.program), program),
  6.                 cb.equal(root.get(ProgramSchedule_.week), week),
  7.                 cb.equal(root.get(ProgramSchedule_.dayOfWeek), dayOfWeek))
  8.         ));
  10.     }

并使用以下方式调用它：

  1. programSchedules.findByProgramWeekAndDay(psFdProgram, 1, DayOfWeek.TUESDAY)

这给我带来了以下问题，其中参数2应该是星期几，但我期望它是3，这是Calendar.TUESDAY的星期几值。

  1. ype.descriptor.sql.BasicBinder TRACE binding parameter [1] as [VARCHAR] - [PS-FD]
  2. ype.descriptor.sql.BasicBinder TRACE binding parameter [2] as [INTEGER] - [1]

我正在使用Spring JPA。

转换器如下所示：

  1. @Component
  2. @Converter(autoApply = true)
  3. public class DayOfWeekConverter implements AttributeConverter<DayOfWeek, Integer> {
  5.     @Override
  6.     public Integer convertToDatabaseColumn(final DayOfWeek dayOfWeek) {
  8.         if (dayOfWeek == null) {
  9.             return null;
  10.         } else if (DayOfWeek.SUNDAY == dayOfWeek) {
  11.             return Calendar.SUNDAY;
  12.         } else if (DayOfWeek.MONDAY == dayOfWeek) {
  13.             return Calendar.MONDAY;
  14.         } else if (DayOfWeek.TUESDAY == dayOfWeek) {
  15.             return Calendar.TUESDAY;
  16.         } else if (DayOfWeek.WEDNESDAY == dayOfWeek) {
  17.             return Calendar.WEDNESDAY;
  18.         } else if (DayOfWeek.THURSDAY == dayOfWeek) {
  19.             return Calendar.THURSDAY;
  20.         } else if (DayOfWeek.FRIDAY == dayOfWeek) {
  21.             return Calendar.FRIDAY;
  22.         } else if (DayOfWeek.SATURDAY == dayOfWeek) {
  23.             return Calendar.SATURDAY;
  24.         } else {
  25.             throw new IllegalStateException();
  26.         }
  27.     }
  29.     @Override
  30.     public DayOfWeek convertToEntityAttribute(final Integer dbValue) {
  32.         if (dbValue == null) {
  33.             return null;
  34.         } else if (Calendar.SUNDAY == dbValue) {
  35.             return DayOfWeek.SUNDAY;
  36.         } else if (Calendar.MONDAY == dbValue) {
  37.             return DayOfWeek.MONDAY;
  38.         } else if (Calendar.TUESDAY == dbValue) {
  39.             return DayOfWeek.TUESDAY;
  40.         } else if (Calendar.WEDNESDAY == dbValue) {
  41.             return DayOfWeek.WEDNESDAY;
  42.         } else if (Calendar.THURSDAY == dbValue) {
  43.             return DayOfWeek.THURSDAY;
  44.         } else if (Calendar.FRIDAY == dbValue) {
  45.             return DayOfWeek.FRIDAY;
  46.         } else if (Calendar.SATURDAY == dbValue) {
  47.             return DayOfWeek.SATURDAY;
  48.         } else {
  49.             throw new IllegalArgumentException(String.format("dbValue=%d is not valid", dbValue));
  50.         }
  51.     }
  52. }

我用以下方式注释了dayOfWeek：

  1.     @Id
  2.     @Column(name = "Day", nullable = false)
  3.     @Convert(converter = DayOfWeekConverter.class) // I had it without this one as well
  4.     private DayOfWeek dayOfWeek;

这不仅限于条件查询，它也在JPQL查询中发生：

  1.     @Query("from ProgramSchedule p where p.program = :program and week = :week and dayOfWeek = :dayOfWeek")
  2.     Optional<ProgramSchedule> findByProgramWeekAndDay(final Program program, int week, final DayOfWeek dayOfWeek);

产生了以下结果：

  1.     where
  2. programsch0_.Program=? 
  3. and programsch0_.Week=? 
  4. and programsch0_.Day=?
  5. ype.descriptor.sql.BasicBinder TRACE binding parameter [1] as [VARCHAR] - [PS-FD]
  6. ype.descriptor.sql.BasicBinder TRACE binding parameter [2] as [INTEGER] - [1]
  7. ype.descriptor.sql.BasicBinder TRACE binding parameter [3] as [INTEGER] - [1]
英文:

I have a simple converter to map DayOfWeek to an Integer to match the legacy data code which used Calendar day of week values. However, when I am doing criteria queries, it appears to be using the ordinal value of DayOfWeek rather than my converted value.

  1.     default Optional&lt;ProgramSchedule&gt; findByProgramWeekAndDay(final Program program, int week, final DayOfWeek dayOfWeek) {
  3.         return findOne(((root, query, cb) -&gt;
  4.             cb.and(
  5.                 cb.equal(root.get(ProgramSchedule_.program), program),
  6.                 cb.equal(root.get(ProgramSchedule_.week), week),
  7.                 cb.equal(root.get(ProgramSchedule_.dayOfWeek), dayOfWeek))
  8.         ));
  10.     }

And calling it with

  1. programSchedules.findByProgramWeekAndDay(psFdProgram, 1, DayOfWeek.TUESDAY)

Gives me the following where parameter 2 was supposed be the day of the week, however, I was expecting it to be 3 which is the day of week value for Calendar.TUESDAY.

  1. ype.descriptor.sql.BasicBinder TRACE binding parameter [1] as [VARCHAR] - [PS-FD]
  2. ype.descriptor.sql.BasicBinder TRACE binding parameter [2] as [INTEGER] - [1]

I'm using Spring JPA

The converter looks like this

  1. @Component
  2. @Converter(autoApply = true)
  3. public class DayOfWeekConverter implements AttributeConverter&lt;DayOfWeek, Integer&gt; {
  5.     @Override
  6.     public Integer convertToDatabaseColumn(final DayOfWeek dayOfWeek) {
  8.         if (dayOfWeek == null) {
  9.             return null;
  10.         } else if (DayOfWeek.SUNDAY == dayOfWeek) {
  11.             return Calendar.SUNDAY;
  12.         } else if (DayOfWeek.MONDAY == dayOfWeek) {
  13.             return Calendar.MONDAY;
  14.         } else if (DayOfWeek.TUESDAY == dayOfWeek) {
  15.             return Calendar.TUESDAY;
  16.         } else if (DayOfWeek.WEDNESDAY == dayOfWeek) {
  17.             return Calendar.WEDNESDAY;
  18.         } else if (DayOfWeek.THURSDAY == dayOfWeek) {
  19.             return Calendar.THURSDAY;
  20.         } else if (DayOfWeek.FRIDAY == dayOfWeek) {
  21.             return Calendar.FRIDAY;
  22.         } else if (DayOfWeek.SATURDAY == dayOfWeek) {
  23.             return Calendar.SATURDAY;
  24.         } else {
  25.             throw new IllegalStateException();
  26.         }
  27.     }
  29.     @Override
  30.     public DayOfWeek convertToEntityAttribute(final Integer dbValue) {
  32.         if (dbValue == null) {
  33.             return null;
  34.         } else if (Calendar.SUNDAY == dbValue) {
  35.             return DayOfWeek.SUNDAY;
  36.         } else if (Calendar.MONDAY == dbValue) {
  37.             return DayOfWeek.MONDAY;
  38.         } else if (Calendar.TUESDAY == dbValue) {
  39.             return DayOfWeek.TUESDAY;
  40.         } else if (Calendar.WEDNESDAY == dbValue) {
  41.             return DayOfWeek.WEDNESDAY;
  42.         } else if (Calendar.THURSDAY == dbValue) {
  43.             return DayOfWeek.THURSDAY;
  44.         } else if (Calendar.FRIDAY == dbValue) {
  45.             return DayOfWeek.FRIDAY;
  46.         } else if (Calendar.SATURDAY == dbValue) {
  47.             return DayOfWeek.SATURDAY;
  48.         } else {
  49.             throw new IllegalArgumentException(String.format(&quot;dbValue=%d is not valid&quot;, dbValue));
  50.         }
  51.     }
  52. }

I annotated dayOfWeek as follows

  1.     @Id
  2.     @Column(name = &quot;Day&quot;, nullable = false)
  3.     @Convert(converter = DayOfWeekConverter.class) // I had it without this one as well
  4.     private DayOfWeek dayOfWeek;

It's not limited to criteria queries either, it also occurs with JPQL queries:

  1.     @Query(&quot;from ProgramSchedule p where p.program = :program and week = :week and dayOfWeek = :dayOfWeek&quot;)
  2. Optional&lt;ProgramSchedule&gt; findByProgramWeekAndDay(final Program program, int week, final DayOfWeek dayOfWeek);

yields

  1.     where
  2. programsch0_.Program=? 
  3. and programsch0_.Week=? 
  4. and programsch0_.Day=?
  5. ype.descriptor.sql.BasicBinder TRACE binding parameter [1] as [VARCHAR] - [PS-FD]
  6. ype.descriptor.sql.BasicBinder TRACE binding parameter [2] as [INTEGER] - [1]
  7. ype.descriptor.sql.BasicBinder TRACE binding parameter [3] as [INTEGER] - [1]

答案1

得分: 1

@Id和@Convert不能同时使用，所以你需要使用@IdClass，像这样：

  1. @Entity
  2. @IdClass(MyEntity.class)
  3. public class MyId {
  4.   @Id
  5.   private DayOfWeek dayOfWeek;
  6.   ...
  7. }
  9. public class MyEntity implements Serializable {
  10.   @Column(name = "Day")
  11.   @Convert(converter = DayOfWeekConverter.class)
  12.   private DayOfWeek dayOfWeek;
  13. }

编辑 根据文档，上面的代码也不起作用，所以我寻找了一种解决方法，找到了这个：

  1. @Entity
  2. public class MyEntity {
  3.   @EmbeddedId
  4.   private DayOfWeek dayOfWeek;
  5.   ...
  6. }
  8. @Embeddable
  9. public static class Id {
  10.   @Column(name = "Day")
  11.   @Convert(converter = DayOfWeekConverter.class)
  12.   private DayOfWeek dayOfWeek;
  13. }
英文:

@Id and @Convert cannot be used together, so you have to use an @IdClass, like so:

  1. @Entity
  2. @IdClass(MyEntity.class)
  3. public class MyId {
  4. @Id
  5. private DayOfWeek dayOfWeek;
  6. ...
  7. }
  8. public class MyEnity implements Serializable {
  9. @Column(name = &quot;Day&quot;)
  10. @Convert(converter = DayOfWeekConverter.class)
  11. private DayOfWeek dayOfWeek;
  12. }

EDIT According to docs the above code also doesn't work, so I looked for a workaround and found this:

  1. @Entity
  2. public class MyEntity {
  3. @EmbeddedId
  4. private DayOfWeek dayOfWeek;
  5. ...
  6. }
  7. @Embeddable
  8. public static class Id {
  9. @Column(name = &quot;Day&quot;)
  10. @Convert(converter = DayOfWeekConverter.class)
  11. private DayOfWeek dayOfWeek;
  12. }
  13. </details>

huangapple
  • 本文由 发表于 2020年8月8日 18:19:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/63314274.html
  • java
  • jpa
  • spring
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定