Postgres overlapping symbol not running in java

Question

I have a query to test two dates against two timestamp columns in the table if they overlap or not. Query is working fine in the database client but when i added it in my java code it fails with an exception error. I need to know how to format the && symbols in the query to be able to work.

SELECT count(*) 
FROM attendance_jobs 
WHERE tsrange( start_date, end_date) && tsrange(TIMESTAMP '2019-04-22', TIMESTAMP '2019-03-22 ')

Here is my java code:

long count = jdbi.withHandle(handle -> {
            return handle.createQuery("select count(*) from attendance_jobs where tsrange(start_date, end_date) && tsrange(timestamp :start_date, timestamp :end_date)")
                .bind("start_date", start_date)
                .bind("end_date", end_date)
                .mapTo(Long.class)
                    .findOnly();
        });

The start_date and end_date data type is Timestamp.

org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

Answer 1

This is just guesswork, but I think you should have a look at the usage of :start_date and :end_date again:

If start_date and end_date (java variables) are of type Timestamp you should remove the timestamp prefix to :start_date and :end_date in the query. As the documentation says, the java type Timestamp is supported by jdbi:

Out of the box, Jdbi supports the following types as SQL statement arguments:
* ...
* java.sql: Blob, Clob, Date, Time, and Timestamp
* ...

So my guess is that you have to use the query like this:

long count = jdbi.withHandle(handle -> {
        return handle.createQuery("select count(*) from attendance_jobs where tsrange(start_date, end_date) && tsrange(:start_date, :end_date)")
            .bind("start_date", start_date)
            .bind("end_date", end_date)
            .mapTo(Long.class)
                .findOnly();
    });

Also, but this may be personal taste, I recommend to use different spelling of bind variables and database columns. The latter with underscores (as you did), the other in camel case so it is less confusing if you use similar names. Also, it is uncommon to use underscores in java variables, so the code would look similar to this in my spelling:

Timestamp startDate = ...;
Timestamp endDate = ...;
String queryString = "select count(*) from attendance_jobs "
        + "where tsrange(start_date, end_date) && tsrange(:startDate, :endDate)";
long count = jdbi.withHandle(handle -> {
        return handle.createQuery(queryString)
            .bind("startDate", startDate)
            .bind("endDate", endDate)
            .mapTo(Long.class)
            .findOnly();
    });