Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Datetime mapping on SQLite data source
Datetime mapping on SQLite data source [message #1758579] Thu, 30 March 2017 09:27 Go to next message
Roberto Palermo is currently offline Roberto PalermoFriend
Messages: 9
Registered: March 2017
Junior Member
Dear all,
I'm trying to use Eclipse Link in order to read a provided (the schema is not editable by me) SQLite data source, but I'm in trouble with date management.
In particular I have a field with "datetime" format, I tried to map in on Java entity with the type Date:

@Temporal(TemporalType.TIMESTAMP)
@Column(name="record_datetime", nullable=false)
protected Date record_datetime;


but in this way the date is not parsed correctly, for example if in the DB the value is
"2017-03-13 11:21:16.606005" 

I receive instead the value:
"Mon Mar 13 11:31:22 CET 2017"


In addition, when I try to create a TypedQuery based on this field, for example:

DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSS");
Date dateFrom = df.parse("2017-03-13 11:21:16.847");
List< TableRecord > values = new ArrayList< TableRecord >();
TypedQuery<JPATableEntity> typedQuery = entityManager.createQuery("SELECT t FROM table t WHERE t.record_datetime >= :tsFrom", JPATableEntity.class);
typedQuery.setParameter("tsFrom", dateFrom);
for (TableRecord t : typedQuery.getResultList())
    values.add(t);


it returns all records of table without filtering.

I think that the problem may be related to the fact that the "datetime" in the table is in the format "yyyy-MM-dd hh:mm:ss.SSSSSS" and it is wrongly parsed as "yyyy-MM-dd hh:mm:ss.SSS", could it be?

In any case what is the correct way to map the SQLite type "datetime" in Java using EclipseLink?

Many thanks in advance for your help.
Re: Datetime mapping on SQLite data source [message #1758642 is a reply to message #1758579] Fri, 31 March 2017 00:01 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1275
Registered: July 2009
Senior Member
3 questions:
1) What database platform class are you using? (specified with the target-database property)
2) what is the SQL EclipseLink is issuing?
3) what JDBC driver and version are you using? Have you tried a different one?

Best Regards,
Chris
Re: Datetime mapping on SQLite data source [message #1758657 is a reply to message #1758642] Fri, 31 March 2017 10:21 Go to previous message
Roberto Palermo is currently offline Roberto PalermoFriend
Messages: 9
Registered: March 2017
Junior Member
Hi Chris and many thanks for your response.
I have resolved in the way explained here:
http://www.thoughts-on-java.org/persist-localdate-localdatetime-jpa/

In particular I declared a convertor:
@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDateTime, String> {
 
    private static final DateTimeFormatter formatter = new DateTimeFormatterBuilder().appendPattern("yyyy-MM-dd HH:mm:ss").appendFraction(ChronoField.NANO_OF_SECOND, 6, 6, true).toFormatter();
 
    @Override
    public String convertToDatabaseColumn(LocalDateTime locDate) {
        return (locDate == null ? null : locDate.format(formatter));
    }
 
    @Override
    public LocalDateTime convertToEntityAttribute(String sqlDate) {
        return (sqlDate == null ? null : LocalDateTime.parse(sqlDate, formatter));
    }
}


then in the JPA Entity class I used the annotation:
@Convert(converter = LocalDateAttributeConverter.class)
@Column
protected LocalDateTime creationTimestamp;


In this way I managed to use the field as a LocalDateTime and use all JPA's features in a transparent way.

Many thanks for the supports!
Best regards
Previous Topic:BatchFetch examples?
Next Topic:Make normal code building for EclipseLink
Goto Forum:
  


Current Time: Fri Dec 15 00:32:06 GMT 2017

Powered by FUDForum. Page generated in 0.01849 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software