英文:
Check if java Timestamp is null in jpql query
问题
我有以下代码:
@Query("select t from Training t join t.skills s join t.trainers tr join t.discipline d where " +
"(t.name in :names or :names is null) and (s.name in :skills or :skills is null) and" +
" (t.location = :location or :location is null) and " +
" (d.name = :discipline or :discipline is null) and " +
"(tr.firstName in :trainers or :trainers is null) and " +
" (((:endDate > t.endDate) and (:startDate < t.startDate)) or (:startDate is empty))")
public List<Training> filterTrainings(List<String> names, List<String> skills, String location, String discipline, List<String> trainers, Timestamp endDate, Timestamp startDate);
并且我需要检查 :startDate
和 :endDate
是否为空。有方法可以做到这一点吗?
我得到的错误是 nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
,当尝试检查 start date 是否为空,而 start date 是一个 Timestamp。
英文:
I have the following code:
@Query("select t from Training t join t.skills s join t.trainers tr join t.discipline d where " +
"(t.name in :names or :names is null) and (s.name in :skills or :skills is null) and" +
" (t.location = :location or :location is null) and " +
" (d.name = :discipline or :discipline is null) and " +
"(tr.firstName in :trainers or :trainers is null) and " +
" (((:endDate > t.endDate) and (:startDate < t.startDate)) or (:startDate is empty))")
public List<Training> filterTrainings(List<String> names, List<String> skills, String location,String discipline,List<String> trainers,Timestamp endDate,Timestamp startDate);
and i need to check if :startDate
and :endDate
are null. Is there a way to do that?
The error i get is nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
when trying to check :startDate is null
where start date is a Timestamp.
答案1
得分: 1
请尝试将LocalDateTime作为参数传递,而不是Timestamp。java.sql.Timestamp 在这里可能会引起问题。您可以通过调用timestamp.toLocalDateTime()来转换为LocalDateTime。
或者您可以尝试将时间戳作为字符串传递给filterTrainings方法。如果在调用filterTrainings方法之前时间戳为null,在赋值给一个空字符串。String _timestamp = timestamp == null ? "" : timestamp.toString()
然后,在您的SQL语句中检查字符串是否为空... ""=:timestamp OR function("to_timestamp", :timestamp, "yyyy-mm-dd hh:mm:ss.fffffffff")
。这里的问题是我们正在使用function来访问本地数据库命令。
英文:
Could you try passing a LocalDateTime as a parameter instead of a Timestamp? java.sql.Timestamp might be causing you issues here. You can convert to a LocalDateTime by calling timestamp.toLocalDateTime()
Alternatively you could try passing the timestamp as a string into filterTrainings. If the timestamp is null before calling the filterTrainings method, assign an empty string. String _timestamp = timestamp == null ? "" : timestamp.toString()
Then, in your sql statement check if the string is empty .. ""=:timestamp OR function("to_timestamp", :timestamp, "yyyy-mm-dd hh:mm:ss.fffffffff")
. The problem here is that we are using function to access native db commands.
答案2
得分: 0
也许您可以使用and COALESCE(field, default_value_for_datetime_field)
来构建您的SQL查询。这样您就不会得到NULL值。
您可以构建一个默认值,例如本月初,本周初,或者任何对于您的模型领域来说都可以作为合理替代的日期时间值。
英文:
Maybe you can build your SQL query with a and COALESCE(field, default_value_for_datetime_field)
. So you will not get NULL values.
You can build a default value as the start of the current month, of the current week, or any datetime value which could be a fair substitution for your model domain.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论