How to pass a ZonedDateTime into native SQL query in JPA

21 февраля, 2018 rius

Sometimes you have to use native SQL instead of JPQL or JPA specification, say, when you deal with non-portable key words like WITH RECURSIVE. That means, you need an explicit type conversation. I use postgres. In this post I will elaborate on that.

We will fix this method:

@Query(value = 
        "SELECT distinct a.* FROM action a "
        + "LEFT OUTER JOIN history h "
        + "ON a.id = h.action_id "
        + "AND h.user_id = :userId "
        + "WHERE a.occurrence='DAILY' AND (h.id IS NULL OR h.entry_date < :yesterday) ", nativeQuery = true)
public List<Action> findAllAvailableActions(@Param("userId") Long userId, @Param("yesterday") ZonedDateTime yesterday);

Postgres will complain on bytea, which probably needs a conversion.

For this purpose I used an implementation of javax.persistence.AttributeConverter for types , which was already present in my project:

package ru.pochta.abon.entity;
 
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.time.ZonedDateTime;
import java.util.Calendar;
import java.util.TimeZone;
 
@Converter(autoApply = true)
public class ZonedDateTimeConverter implements AttributeConverter<ZonedDateTime, Calendar> {
    @Override
    public Calendar convertToDatabaseColumn(ZonedDateTime entityAttribute) {
        if (entityAttribute == null) {
            return null;
        }
 
        Calendar calendar = Calendar.getInstance();
        calendar.setTimeInMillis(entityAttribute.toInstant().toEpochMilli());
        calendar.setTimeZone(TimeZone.getTimeZone(entityAttribute.getZone()));
        return calendar;
    }
 
    @Override
    public ZonedDateTime convertToEntityAttribute(Calendar databaseColumn) {
        if (databaseColumn == null) {
            return null;
        }
 
        return ZonedDateTime.ofInstant(databaseColumn.toInstant(),
                databaseColumn
                        .getTimeZone()
                        .toZoneId());
    }
}

The method becomes:

@Query(value = 
        "SELECT distinct a.* FROM action a "
        + "LEFT OUTER JOIN history h "
        + "ON a.id = h.action_id "
        + "AND h.user_id = :userId "
        + "WHERE a.occurrence='DAILY' AND (h.id IS NULL OR h.entry_date < :yesterday) ", nativeQuery = true)
public List<Action> findAllAvailableActions(@Param("userId") Long userId, @Param("yesterday") Calendar yesterday);

In the class, which uses this interface’s method, I created a static converter:

private static final ZonedDateTimeConverter converter = new ZonedDateTimeConverter();
 
...
List<Action> list = repo.findAllAvailableActions(userId,
converter.convertToDatabaseColumn(ZonedDateTime.now());

The second param (yesterday) isn’t obviously yesterday, but that doesn’t matter for this particular demo.

This approach can be applied for other conversions since there are lot’s of complete converters in the internet.

Just keep in mind: your DB may not understand complex data types. Most likely you already have a necessary converter in an annotation to the corresponding entity’s field:

    @Column(name = "entry_date")
    @Convert(converter = ZonedDateTimeConverter.class)
    private ZonedDateTime entryDate;

Related Posts

Posted in Программирование
«
»
You can leave a response, or trackback from your own site.

Leave a Reply