Spring Boot throws an SQL Syntax exception-CodePudding

I am testing new endpoints on my API and getting an error that confuses me a bit.

2022-08-24 11:51:54.283  WARN 9836 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2022-08-24 11:51:54.283 ERROR 9836 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE) FROM rates WHERE (date BETWEEN '2022-01-01' AND '2022-03-01') AND ((null ' at line 1
2022-08-24 11:51:54.292 ERROR 9836 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api/v1] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE) FROM rates WHERE (date BETWEEN '2022-01-01' AND '2022-03-01') AND ((null ' at line 1

The confusing part for me is that I don't understand where it gets the first part of the query WHERE) in the error message.

Repo

@Query("SELECT * FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR rate_code = :code) AND rate > 0)", nativeQuery = true)
    fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>

Controller

@GetMapping("/historic")
    fun ratesForDateRange(
        @RequestParam startDate: Optional<Date>,
        @RequestParam endDate: Optional<Date>,
        @RequestParam(required = false) code: String?,
        @RequestParam(required = false) pageNumber: Int?
    ): Page<RateEntity> {
        val lastDate = Date.valueOf(START_DATE)
        val page = pageNumber ?: 0
        println(code)
        return repo.getByDateRange(
            startDate.orElse(lastDate),
            endDate.orElse(UtilFunctions.getCurrentSQLDate()),
            code,
            PageRequest.of(page, 50)
        )
    }

When I use the wrong code in request URL /api/v1/historic?startDate=2022-1-1&endDate=2022-3-1&code=abc I get an empty page as expected.

When I use the right code in request URL /api/v1/historic?startDate=2022-1-1&endDate=2022-3-1&code=suv and there are records between specified dates, I get the page with results as expected.

However, when I don't specify the code in request URL /api/v1/historic?startDate=2022-1-1&endDate=2022-3-1 I get this exception.

I use MySQL for Database and if use this query directly in CLI it works as expected. For example this query

SELECT * FROM rates WHERE (date BETWEEN '2022-01-01' AND '2022-03-01') AND ((null IS NULL OR rate_code = null) AND rate > 0);

returns all results for specified date range as expected.

CodePudding user response：

I think it is the problem with date_and_time in spring boot.

You are comparing two dates whereas the actual values saved are dateAndTime, which spring can not compare. Therefore, you should cast the date field as follow:

CAST(date as DATE)

Complete Query:

@Query("SELECT * FROM rates WHERE (CAST(date as DATE) BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR rate_code = :code) AND rate > 0)", nativeQuery = true)

CodePudding user response：

Ok so I've already found where the problem lies and fixed it. Probably posted too soon.

After some more testing I found out that the exception occurs when amount of results is >= page size (50 in my case).

So after displaying hibernate queries with:

spring.jpa.show-sql = true

I found this:

Hibernate: SELECT count(WHERE) FROM rates WHERE (date BETWEEN ? AND ?) AND ((? IS NULL OR rate_code = ?) AND rate > 0)

That's where WHERE) comes from, which is obviously a syntax error.

The fix was to add custom count query like this:

@Query(
        value = "SELECT * FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR currency_code = :code) AND rate > 0)",
        countQuery = "SELECT count(*) FROM rates WHERE (date BETWEEN :startDate AND :endDate) AND ((:code IS NULL OR currency_code = :code) AND rate > 0)",
        nativeQuery = true
    )
    fun getByDateRange(startDate: Date, endDate: Date, code: String?, pageable: Pageable): Page<RateEntity>