检查Java中的Timestamp在JPQL查询中是否为null。

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

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(&quot;select t from Training t join t.skills s join t.trainers tr join t.discipline d  where &quot; +
            &quot;(t.name in :names or :names is null) and (s.name in :skills or :skills is null) and&quot; +
            &quot; (t.location = :location or :location is null) and &quot; +
            &quot; (d.name = :discipline or :discipline is null) and &quot; +
            &quot;(tr.firstName in :trainers or :trainers is null) and &quot; +
            &quot; (((:endDate &gt; t.endDate) and (:startDate &lt; t.startDate)) or (:startDate is empty))&quot;)
    public List&lt;Training&gt; filterTrainings(List&lt;String&gt; names, List&lt;String&gt; skills, String location,String discipline,List&lt;String&gt; 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 ? &quot;&quot; : timestamp.toString()

Then, in your sql statement check if the string is empty .. &quot;&quot;=:timestamp OR function(&quot;to_timestamp&quot;, :timestamp, &quot;yyyy-mm-dd hh:mm:ss.fffffffff&quot;). 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.

huangapple
  • 本文由 发表于 2020年9月2日 19:03:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/63704162.html
匿名

发表评论

匿名网友

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

确定