java - Spring数据和PostgreSQL : ERROR: operator does not exist: uuid = record (when UUID list size > 1)

Question

所以我有这个 Spring Data JPA 存储库。我编写了一个单元测试来验证方法。但是，当 UUID 的大小大于 1 的集合时，它不会通过(提取集合时出错)。

这是存储库:

public interface CalculationRepository extends JpaRepository<Calculation, UUID> {

  List<Calculation>
      findByCardIdAndOccurredDateLessThanEqual(
        Collection<UUID> cardId, LocalDate endDate);
}

和测试类:

public class CalculationTest {

  @Autowired
  private CalculationRepository calculationRepository;

  private final UUID facility = randomUUID();
  private final UUID program = randomUUID();

  @Override
  CrudRepository<Calculation, UUID> getRepository() {
    return calculationRepository;
  }

  @Override
  Calculation generateInstance() {
    return generateInstance(facility, program, randomUUID(), randomUUID());
  }

  private Calculation generateInstance(UUID facility, UUID program, UUID product, UUID lot) {
    // here generating instance
  }


  @Test
  public void newTest() {
    Calculation calculation1 = this.generateInstance();
    calculation1.setOccurredDate(LocalDate.of(2010, 8, 1));
    calculationRepository.save(calculation1);

    Calculation calculation2 = this.generateInstance();
    calculation2.setOccurredDate(LocalDate.of(2010, 9, 1));
    calculationRepository.save(calculation2);

    Calculation calculation3 = this.generateInstance();
    calculation3.setOccurredDate(LocalDate.of(2010, 10, 1));
    calculationRepository.save(calculation3);

    List<Calculation> resultList = calculationRepository
            .findByCardIdAndOccurredDateLessThanEqual(
                    asList(calculation1.getCard().getId(),
                    calculation2.getCard().getId(),
                    calculation3.getCard().getId()),
                    LocalDate.of(2010, 11, 1));

    assertThat(resultList, hasItems(calculation1,
            calculation2, calculation3));
  }

我运行测试，当测试失败时出现此错误:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy146.findByStockCardIdAndOccurredDateLessThanEqual(Unknown Source)
        at org.project.stock.repository.CalculationTest.newTest(CalculationTest.java:190)

        Caused by:
        org.hibernate.exception.SQLGrammarException: could not extract ResultSet
            at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
            at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
            at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
            at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
            at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
            at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
            at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
            at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
            at org.hibernate.loader.Loader.doQuery(Loader.java:919)
            at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
            at org.hibernate.loader.Loader.doList(Loader.java:2617)
            at org.hibernate.loader.Loader.doList(Loader.java:2600)
            at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
            at org.hibernate.loader.Loader.list(Loader.java:2424)
            at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
            at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
            at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
            at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
            at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
            at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
            at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
            at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:50)
            at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:121)
            at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
            at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
            at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
            at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
            at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
            at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
            at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
            at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
            at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
            at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
            ... 10 more

            Caused by:
            org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = record
              Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
              Position: 384
                at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2412)
                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2125)
                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:297)
                at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
                at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
                at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
                at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
                at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
                ... 41 more

但是，当在测试中仅将一个元素添加到列表中时(例如，calculation1.getCard().getId())，则测试会失败，因为列表中只有 1 个元素，而不是预期的 3 个元素。

编辑:添加Calculation.java

@Entity
@Getter
@Setter
@ToString
@EqualsAndHashCode(callSuper = false)
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "calculation", schema = "stock")
public class Calculation extends BaseEntity {

  @Column(nullable = false)
  private Integer stock;

  @ManyToOne
  @JoinColumn(name = "cardid", nullable = false)
  private Card card;

  @Column(nullable = false)
  private LocalDate occurredDate;

  @Column(nullable = false, columnDefinition = "timestamp")
  private ZonedDateTime processedDate;

  public void export(Exporter exporter) {
    exporter.setId(getId());
    exporter.setStock(getStock());
    exporter.setOccurredDate(getOccurredDate());
    exporter.setProcessedDate(getProcessedDate());
  }

  public interface Exporter {

    void setId(UUID id);

    void setStock(Integer stock);

    void setOccurredDate(LocalDate date);

    void setProcessedDate(ZonedDateTime processedDate);
  }

编辑:添加了 BaseEntity.java:

@MappedSuperclass
public abstract class BaseEntity {
  protected static final String TEXT_COLUMN_DEFINITION = "text";
  protected static final String PG_UUID = "pg-uuid";

  @Id
  @GeneratedValue(generator = "uuid-gen")
  @GenericGenerator(name = "uuid-gen", strategy = "strategy")
  @Type(type = PG_UUID)
  @Getter
  @Setter
  private UUID id;
}

Answer 1

我认为你必须重命名你的方法，因为你有一个 id 列表。

您可以尝试一下吗(在 CardId 之后添加In):

findByCardIdInAndOccurredDateLessThanEqual(
    Collection<UUID> cardId, LocalDate endDate);