how to send LocalDateTime.now() in java to the database (without sending a date from the frontend)?

Question

the table contains this:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Embeddable
public class SourceContentMappingPK implements Serializable {
    @Column(name = "hr_code")
    private String hrCode;

    @Column(name = "muse_id")
    private String museId;

    @Column(name = "source_type")
    private String sourceType;

    @Column(name = "cts")
    private LocalDateTime cts;
}

This is the model:

@Getter
@Setter
@NoArgsConstructor
public class SourceContentMappingDTO {
    private String hrCode;
    private String museId;
    private String sourceType;
    private String masterHotelId;
    private LocalDateTime cts;

    public SourceContentMappingDTO(String hrCode, String museId, String sourceType, String masterHotelId, LocalDateTime cts) {
        this.hrCode = hrCode;
        this.museId = museId;
        this.sourceType = sourceType;
        this.masterHotelId = masterHotelId;
        this.cts = cts;
    }
}

When I send a request through postman, the current date and time are written to the database. When I make the same request through the page, everything except the date is written to the database. It is necessary to enter the date without sending it from the frontend.

 @Override
    @Transactional
    public void updateImageSources(HotelMasterListSubItemDTO dto) {
        dto.setContentSources(Arrays.asList("IMAGES"));

        List<SourceContentMapping> sourceContentMappings = entityManager.createNativeQuery("select * from SOURCE_CONTENT_MAPPING where master_hotel_id = :masterHotelId", SourceContentMapping.class)
                .setParameter("masterHotelId", dto.getMasterHotelId())
                .setHint(QueryHints.READ_ONLY, true)
                .getResultList();

        for(String contentSourceType : dto.getContentSources()) {
            boolean contentSourceTypeExists = false;
            for(SourceContentMapping sourceContentMapping : sourceContentMappings) {
                if(contentSourceType.equals(sourceContentMapping.getId().getSourceType())) {
                    contentSourceTypeExists = true;

                    entityManager.createNativeQuery("update SOURCE_CONTENT_MAPPING set muse_id = :museId, hr_code = :hrCode, cts = :cts where master_hotel_id = :masterHotelId and source_type = :sourceType")
                            .setParameter("museId", dto.getMuseId())
                            .setParameter("hrCode", dto.getHrCode())
                            .setParameter("masterHotelId", dto.getMasterHotelId())
                            .setParameter("sourceType", contentSourceType)
                            .setParameter("cts", LocalDateTime.now())
                            .executeUpdate();
                }
            }

            if(!contentSourceTypeExists) {
                entityManager.createNativeQuery("insert into SOURCE_CONTENT_MAPPING (hr_code, muse_id, source_type, master_hotel_id, cts) values (?, ?, ?, ?, ?)")
                        .setParameter(1, dto.getHrCode())
                        .setParameter(2, dto.getMuseId())
                        .setParameter(3, contentSourceType)
                        .setParameter(4, dto.getMasterHotelId())
                        .setParameter(5, LocalDateTime.now())
                        .executeUpdate();
            }
        }
    }

I tried adding .setParameter ("cts", LocalDateTime.now ()) and .setParameter (5, LocalDateTime.now ()) but like I said only through postman it works but not through GUI. I need to make the parameter optional, nullable e.g.

Using MySQL. Table contains:

museId (varchar 50, notNull),
  hrCode (varchar 50, notNull),
  source_type (varchar 50, notNull),
  masterHotelId (varchar 100),
  cts (timestamp)
  …

At this link is a picture: The first record in the database was sent from the postman, and the second is through the page.

What is the database table defined like? And which brand of database engine are you using? — Ole V.V., Apr 15 '22 at 12:11
@MCEmperor It doesn't work for me through the page where I need to add the date of execution of a certain work. — Developer123, Apr 15 '22 at 12:23
@OleV.V. using MySql, and table contains: museId (varchar 50, notNull), hrCode (varchar 50, notNull), source_type (varchar 50, notNull), masterHotelId (varchar 100), cts (timestamp) — Developer123, Apr 15 '22 at 12:26
I believe that a MySQL `timestamp` is always in UTC. You may try `OffsetDateTime.now(ZoneOffset.UTC)` or even `Instant.now()`. I haven’t tried myself; they are just the next things I would try if it were me. — Ole V.V., Apr 15 '22 at 12:41
Also which version of Spring Boot are you using? Is it true that your Spring Boot uses Hibernate, and if so, do you know which version? If I recall correctly, Hibernate doesn’t support java.time types until version 5 (hope I’m wrong). — Ole V.V., Apr 15 '22 at 12:50
@OleV.V. btw. i have tried all these suggestions but for all only the date is entered when the request is sent through postman but not through the page. — Developer123, Apr 15 '22 at 14:12

Дима Шуть · Answer 1 · 2022-04-15T12:48:16.920

0

To get timestamp value and put it to sql you can simply use #Timestamp.valueOf() method and put your LocalDateTime to the method`s param. To get time you can use = new Timestamp(System.currentTimeMillis()) on backend side

edited Apr 15 '22 at 12:48

answered Apr 15 '22 at 12:45

Дима Шуть

21
3

2

Thanks for wanting to contribute. That’s a step backward. The `Timestamp` class is poorly designed and long outdated. We should use classes from [java.time, the modern Java date and time API,](https://docs.oracle.com/javase/tutorial/datetime/) exclusively. – Ole V.V. Apr 15 '22 at 12:47
JDBC 4.2 or higher compliant drivers should support `java.time.LocalDateTime` for the same datatypes that they support `java.sql.Timestamp`. – Mark Rotteveel Apr 15 '22 at 13:05
1

@MarkRotteveel Is that exactly true? For a standard SQL `timestamp with time zone` I believe a `java.sql.Timestamp` was accepted, but today an `OffsetDateTime` is needed, only some drivers sensibly also accept `Instant`. Asking so becuase I have understood that MySQL does its best to keep a `timestamp` in UTC, that is, it behaves pretty much like a `timestamp with time zone` in many other database brands. – Ole V.V. Apr 15 '22 at 13:23
@OleV.V. No, JDBC only defines `OffsetDateTime` for `TIMESTAMP WITH TIME ZONE`. Some drivers will offer more flexibility, sometimes for historic reasons, but such a mapping is technically not compliant (or at least, non-standard). See also appendix B of JDBC 4.2 or 4.3. JDBC doesn't even define support for `java.time.Instant`, the only occurrence is in `java.sql.Timestamp` (`toInstant()`, `from(Instant)`), and then only because `java.util.Date` already defines it. – Mark Rotteveel Apr 15 '22 at 13:25
1

@MarkRotteveel So far it agrees with my understanding. So the question is which JDBC type MySQL’s `timestamp` maps to. If to JDBC `timestamp`, then we’re on the wrong track for explaining the OP’s problem. – Ole V.V. Apr 15 '22 at 13:44

Basil Bourque · Answer 2 · 2022-04-15T15:24:59.900

Wrong data types

You are incorrectly mixing the wrong data types.

See the MySQL 8 documentation.

See also Comments on the Question by Ole V.V.

Moment

The TIMESTAMP type in MySQL 8 is akin to the SQL standard type TIMESTAMP WITH TIME ZONE. Use this type to record a moment, a specific point on the timeline. This type represents date with time-of-day as seen “in UTC”, an offset from UTC of zero hours-minutes-seconds. The matching type in Java is OffsetDateTime, as specified by the JDBC spec.

Not a moment

The DATETIME type in MySQL 8 is akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE. This type represents a date with time of day, but lacks the context of a time zone or offset from UTC. This type cannot represent a moment. The matching Java type is LocalDateTime.

So your use of Java LocalDateTime with MySQL TIMESTAMP is a mismatch. Indeed, I cannot imagine a scenario where calling LocalDateTime.now is the right thing to do.

You have not explained your business domain. So I cannot advise you on which type is appropriate. But I can tell you to not mix the wrong types together.

Avoid legacy date-time types

Never use the classes java.sql.Timestamp, Calendar, or either Date. These are terribly flawed in design.

Those classes are now legacy, years ago supplanted by the modern java.time classes defined in JSR 310.

how to send LocalDateTime.now() in java to the database (without sending a date from the frontend)?

2 Answers2

Wrong data types

Moment

Not a moment

Avoid legacy date-time types