CODEWITHSUNDEEP
javasqlspringhibernateh2
zolv
zolv

Reputation: 1902

Spring/H2/Hibernate FORMATDATETIME "unexpected token: 'yyyy.MM.dd'"

I have Spring with Hibernate and H2. Everything works just fine, except one of my custom queries:

My Entity:

@Entity
public class Match {

    @Id
    @GeneratedValue( strategy = GenerationType.AUTO )
    private Long id;

    private Date started;

    private Long time;

    /* Getters and setters here */
}

My repository:

@Repository
public interface MatchRepository extends CrudRepository<Match, Long> {

    @Query("SELECT IFNULL(sum(m.time), 0) FROM Match m where m.time >= 180000 AND m.time <= 1200000")
    long getPlayingTimeTotal();

    @Query("select count(*) from (select FORMATDATETIME(m.started, 'yyyy.MM.dd') from Match m where m.time >= 180000 AND m.time <= 1200000 GROUP BY FORMATDATETIME(m.started, 'yyyy.MM.dd'))")
    long getNumberOfDays();
}

First query for method getPlayingTimeTotal works just fine, but for the second one I get errors:

I get errors:

line 1:22: unexpected token: (

And also later:

line 1:56: unexpected token: 'yyyy.MM.dd'

When I check the query in my DB tool (I use SQuirreL SQL) then everything works just fine.

How to fix my query?

Upvotes: 1

Views: 890

Answers (1)

Andrea Ligios
Andrea Ligios

Reputation: 50281

If you don't specify they're native queries, you should use JPQL syntax, eg. count(m) instead of count(*).

If you need native SQL, you need to do like follows: 

@Query(nativeQuery = true,
             value = "SELECT IFNULL(sum(m.time), 0) FROM Match m " + 
                     "where m.time >= 180000 AND m.time <= 1200000")
long getPlayingTimeTotal();

@Query(nativeQuery = true, 
             value = "select count(*) from (" + 
                       "select FORMATDATETIME(m.started, 'yyyy.MM.dd') " + 
                       "from Match m where m.time >= 180000 AND m.time <= 1200000 " + 
                       "GROUP BY FORMATDATETIME(m.started, 'yyyy.MM.dd'))")
long getNumberOfDays();

Upvotes: 2

Related Questions