java - MySQL LocalDate 在 JPA Native Query 中作为 String 传递

Question

我在 JPA native 查询中传递 LocalDate。但是它是把它当做字符串来执行。

当我在 MySql Workbench 中和使用 native 查询从 Java 中执行相同的查询时，我得到了不同的结果。

• 在 MySQL Workbench 中:

  select sum(expense_amount) from budgetreferee.br_expense 
  where household_id=1 and next_due_date between 2018-08-01 and 2019-07-31 
    and category_id IN(41,51,58,70,74,83,94,2,20,10,9,22,28) OR 
      is_bill=TRUE and expense_id IS NOT NULL;
  

  结果是:58171.00

• 使用原生查询:

  @Query(value = "select sum(expense_amount) from br_expense where household_id=:householdId and next_due_date between str_to_date(:startDate,'%Y-%m-%d') and str_to_date(:endDate,'%Y-%m-%d') and category_id IN(41,51,58,70,74,83,94,2,20,10,9,22,28) OR is_bill=TRUE and expense_id IS NOT NULL;",nativeQuery = true)
  BigDecimal getTotalExpenseAmount(@Param("householdId") Long householdId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate)
  

  这导致了这个查询:

  select sum(expense_amount) from budgetreferee.br_expense 
  where household_id=1 and next_due_date between str_to_date
   ('2018-08-01','%Y- %m-%d') 
     and str_to_date('2019-07-31','%Y-%m-%d') and 
      category_id IN(41,51,58,70,74,83,94,2,20,10,9,22,28) OR is_bill=TRUE 
    and expense_id IS NOT NULL;
  

  结果为109871.00

我该如何解决这个差异？当我从 Java 执行它时，我希望输出 58171.00。

Answer 1

JPA 2.1 在 Java 8 之前发布，这就是默认不支持 LocalDate 的原因。您可以使用 Converter 为您完成这项工作。

import java.sql.Date;
import java.time.LocalDate;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

 @Converter(autoApply = true)
 public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {

  @Override
  public Date convertToDatabaseColumn(LocalDate localDate) {
      return (localDate == null ? null : Date.valueOf(localDate));
  }

  @Override
  public LocalDate convertToEntityAttribute(Date sqlDate) {
      return (sqlDate == null ? null : sqlDate.toLocalDate());
  }