| Datetime mapping on SQLite data source [message #1758579] |
Thu, 30 March 2017 05:27  |
Eclipse User |
|
|
|
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 #1758657 is a reply to message #1758642] |
Fri, 31 March 2017 06:21  |
| Eclipse User |
|
|
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.06283 seconds
Home