How to correctly handle this Date field mapping using JdbcTemplate into a Spring Boot project?

Question

it is pretty long time that I am not using Spring and I am finding some difficulties with this JdbcTemplate row mapper mapping a date field.

I try to explain my situation in details.

On my database (MariaDB) I have this table named TREND002:

+-----------------+----------+------+-----+---------+-------+
| Field           | Type     | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| Time_Stamp      | datetime | NO   | PRI | NULL    |       |
| Time_Stamp_ms   | int(11)  | NO   | PRI | NULL    |       |
| Sensor001DV     | double   | YES  |     | NULL    |       |
| Sensor002DV     | double   | YES  |     | NULL    |       |
| Sensor003DV     | double   | YES  |     | NULL    |       |
..............................................................
..............................................................
..............................................................
| Sensor00NDV     | double   | YES  |     | NULL    |       |

NOTE-1: the Time_Stamp field contains value as 2010-10-22 16:50:12

Then I have this DTO class mapping the field of the previous table:

public class TrendDTO {

    private Date dateTime;
    private int timeStampMs;
    private String sensorValue;

    public TrendDTO() {
        super();
    }

    public TrendDTO(Date dateTime, int timeStampMs, String sensorValue) {
        super();
        this.dateTime = dateTime;
        this.timeStampMs = timeStampMs;
        this.sensorValue = sensorValue;
    }

    public Date getDateTime() {
        return dateTime;
    }

    public void setDateTime(Date dateTime) {
        this.dateTime = dateTime;
    }

    public int getTimeStampMs() {
        return timeStampMs;
    }

    public void setTimeStampMs(int timeStampMs) {
        this.timeStampMs = timeStampMs;
    }

    public String getSensorValue() {
        return sensorValue;
    }

    public void setSensorValue(String sensorValue) {
        this.sensorValue = sensorValue;
    }

    @Override
    public String toString() {
        return "TrendDTO [dateTime=" + dateTime + ", timeStampMs=" + timeStampMs + ", sensorValue=" + sensorValue + "]";
    }

}

Basially in this DTO class I have:

A Date field mapping the datetime field of my table.
An int field mapping the Time_Stamp_ms field of my table.
A String field mapping the value of a specific SensorXXXDV field of the table (I know that from a relational database point of view this is ugly as hell...but I inherited the project and at the moment I can't change).

Finnally I have a repository class in which there is this method:

@Override
public List<TrendDTO> findTrendListBySensorName(String tableName, String columnName) {

    List<TrendDTO> result = null;

    String RETRIEVE_TREND_BY_SENSOR_NAME = "SELECT Time_Stamp, Time_Stamp_ms, Sensor240DV FROM Delphys.TREND003";

    //result = jdbcTemplate.query(RETRIEVE_TREND_BY_SENSOR_NAME, BeanPropertyRowMapper.newInstance(TrendDTO.class));

    result =  jdbcTemplate.query(
            RETRIEVE_TREND_BY_SENSOR_NAME,
            (rs, rowNum) ->

                    new TrendDTO(
                            new java.util.Date(rs.getTimestamp("Time_Stamp").getTime()),
                            rs.getInt("Time_Stamp_ms"),
                            String.valueOf(rs.getDouble(columnName))
                    )
    );

    return result;
}

It works fine but doing in this way I obtain a list of TrendDTO instances containing dateTime field values like this:

dateTime=Fri Oct 22 16:50:12 PDT 2010

The date is perfectly correct but I think that this is the wrong format. I have to return this DTO object in JSON format to a front end and I need a date in the format:

2010-10-22 16:50:12

I also tried to change my mapper code using this:

result =  jdbcTemplate.query(
                RETRIEVE_TREND_BY_SENSOR_NAME,
                (rs, rowNum) ->

                        new TrendDTO(
                                //new java.util.Date(rs.getTimestamp("Time_Stamp").getTime()),
                                rs.getTimestamp("Time_Stamp"),
                                rs.getInt("Time_Stamp_ms"),
                                String.valueOf(rs.getDouble(columnName))
                        )
        );

As you can see I am simply using rs.getTimestamp("Time_Stamp") but doing in this way I am obtaining a dateTime inizialization like this: 2010-10-22 16:50:12.0

As you can see it end with a .0 that should represents milliseconds that I don't want. Can I specify the format to avoid to put also this ending millisecond section?

Another possible approach to solve my problem (maybe the best solution) is: in this pretty horrible database table the millisecond information is contained into the Time_Stamp_ms column that, at the moment, is mapped with my timeStampMs DTO field.

Can I modify my previous code to encapsulate this information directly into the dateTime field of my DTO object?

I am absolutly not sure that with this syntax (is it a lambda expression?) this is possible. Can I do it using this syntax or have I to implement a row mapper class to implement this behavior?

score 1 · Answer 1 · answered Mar 06 '20 at 14:36

I would do this:

Don't use the old "Date" API anymore, use the newer (Java8+) LocalDateTime instead. Assuming you have a relatively recent JDBC driver, you can do this to get a LocalDateTime, it saves you a few strokes trying to convert your timestamp to a date.

LocalDateTime yourLocalDateTime = rs.getObject("Time_Stamp", LocalDateTime.class) ;
You can format that LocalDateTime any way you want. To do that manually, you would use a DateTimeFormatter.

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); String formatDateTime = yourLocalDateTime.format(formatter);
Though if you are in a Spring Boot project and serializing to JSON, all you need to do is annotate your DTO with the @JsonFormat annotation:

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss") private LocalDateTime myLocalDateTime;

How to correctly handle this Date field mapping using JdbcTemplate into a Spring Boot project?

1 Answers1