Map data like Mysql TIME to JAVA above 24 hours Example (37:26:30)

Question

I have a system that I need to bring Mysql database time information above 24 hours to Java.

We use Ibernate and it’s like this;

In the bank the data is as TIME (37:13:36) example:

TEMPO_REPOUSO                     time          (NULL)             YES             (NULL)                   select,insert,update,references         

Table Mapping in Java is like TIME example:

@Column(name="TEMPO_REPOUSO")
private Time tempoRepouso;

The query we made through a query using springframework.

And the object that will receive data from the database the variables corresponding to the data we declare also as TIME java.sql.Time. Example:

private Time tempoRepouso;

We are Mapping in DTO in Java; I can not in any way traffic this data, the only way that came information was declaring the object that will receive the database data as Date, but is barred when in the Bank has information of times greater than 24 hours.

Answer 1

First we need to understand two important concepts: times and durations. Consider the sentences below:

• The meeting was at two o'clock in the afternoon
• The film is two hours long

In the first case, "two hours" is a time: a specific time of the day.

In the second case, "two hours" is a duration: an amount of time, not necessarily related to a specific time (it is not said that hours begin or end, nor is it said whether he was actually assisted).

What can be confusing is that both use the same words (hours, minutes, seconds) and even the display form can be the same (a clock shows "02:00" when it is 2 hours, a stopwatch shows "02:00" when it has been 2 hours). But they are different concepts.

---

That said, the guy TIME mysql serves for both: it can either contain a timetable or a duration (hence its crease values range from '-838:59:59' to '838:59:59').

Already the class java.sql.Time represents only one time, and it’s not for durations. So much so that it only accepts values between 0 and 23 for hours, and values above that are truncated:

System.out.println(Time.valueOf("25:59:59")); // 01:59:59

Therefore, it is not possible to map durations above 24 hours to a java.sql.Time.

---

In Java 8, there is a specific class for durations: java.time.Duration. From JDBC 4.2 and Hibernate 5, there is support for Duration, that is mapped to BIGINT, then it would be necessary to change the type of the column of the bank, to contain only one number, corresponding to the total in nanoseconds (which is the accuracy of this API).

---

Or, you could create a AttributeConverter to convert the values to a Duration:

@Converter
public class DurationConverter implements AttributeConverter<Duration, Long> {
    @Override
    public Long convertToDatabaseColumn(Duration duration) {
        return duration == null ? null : duration.getSeconds();
    }

    @Override
    public Duration convertToEntityAttribute(Long dbData) {
        return dbData == null ? null : Duration.ofSeconds(dbData);
    }
}

...
@Column(name="TEMPO_REPOUSO")
@Convert(DurationConverter.class)
private Duration tempoRepouso;

Only then the query would have to return the amount of seconds corresponding to the duration, something like SELECT TIME_TO_SEC(TEMPO_REPOUSO) etc. And as it stands, I suspect it only serves for queries (upgrades and insertions in this field would need another conversion, as SEC_TO_TIME to convert the amount of seconds to the TIME mysql).