I am using a MyBatis ORM to interact between my Java 11 application and the MySql database. When I want to insert date objects (tried java.time.LocalDate and java.sql.Date) to the table (column has jdbc type DATE), all dates are shifted backwards by 1 day.
Pseudo-Example:
Entry entry1 = new Entry("John", LocalDate.of(2019,4,29))
Entry entry2 = new Entry("John", Date.valueOf("2019-04-29"))
Both of these will be stored as follows in the sql table.
user |date |
------|-----------|
John |2019-04-28 |
The mysql server uses UTC, the application may run on different timezones (GMT+1, GMT+2) but this shouldn't consistently shift dates, should it?
The java class:
public class Entry {
public String user;
public LocalDate date;
}
The MyBatis mapper and Service:
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserEntryMapper {
void saveEntry(@Param("entry") Entry entry);
}
public class UserEntryService {
private final UserEntryMapper userEntryMapper;
@Inject
public UserEntryService(UserEntryMapper dbMapper){
this.userEntryMapper = dbMapper;
}
@Transactional
public void saveEntry(Entry entry) {
userEntryMapper.saveEntry(entry);
}
}
The MyBatis xml:
<insert id="saveEntry">
INSERT INTO `user_entry_list`
(`user`, `date`)
VALUES
(#{entry.user}, #{entry.date, jdbcType=DATE});
</insert>
EDIT:
I tried instantiating the java.util.Date differently:
Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR, 2019);
cal.set(Calendar.MONTH, Calendar.APRIL);
cal.set(Calendar.DATE, 29);
cal.set(Calendar.HOUR_OF_DAY, 0);
java.util.Date date = cal.getTime();
Indeed, this still results in 2019-04-28. However, if I put cal.set(Calendar.HOUR_OF_DAY, 2), then the correct date is saved. This means java.util.Date and LocalDate are implicitly setting a time at midnight... : /
