mysql localdate在jpa本机查询中作为字符串传递

rjee0c15 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(341)

我在jpa本地查询中传递localdate。但它把它当作字符串来执行。
当我在mysql工作台中执行同一个查询时，得到的结果与使用本机查询从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
我如何解决这个矛盾？我希望58171.00是我从java执行它时的输出。

Java mysql mysql-workbench spring-data-jpa jpa

来源：https://stackoverflow.com/questions/51835972/mysql-localdate-is-being-passed-as-string-in-jpa-native-query

1条答案

按热度按时间

bmvo0sr51#

JPA2.1是在Java8之前发布的，这就是为什么默认情况下不支持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());
  }

}

赞(0）回复(0）举报 2021-06-23

我来回答

mysql localdate在jpa本机查询中作为字符串传递

1条答案

相关问题

热门标签

最新问答