Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Date Changes to Yesterday's date After JPA Save

I have encountered a problem with a date field/database column while using Spring's JpaRepository to save an entity to MySQL.

The entity has LocalDate fields. When testing with LocalDate.now(), encountered a problem with the returned Date field:

  1. First save the returned object's date is correct.
  2. When return the object from MySQL database the date is a day before

Example:

Expected :2019-01-29

Actual :2019-01-28

I have tried it yesterday and results were:

Expected :2019-01-28

Actual :2019-01-27

Maybe similar to the this JPA Saving wrong date in MySQL database

Code

application-mysql-test-connection.properties

spring.jpa.hibernate.ddl-auto=create

# Database url
spring.datasource.url=jdbc:mysql://localhost:3306/test_coupon_system?serverTimezone=UTC

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

# Test Database credentials
spring.datasource.username=springuser
spring.datasource.password=springuser

### showing values - for development
spring.jpa.show-sql=true

Coupon removed constructor & getter/setter for brevity

@Entity
public class Coupon {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", length = 45)
    private long id;


    @Column(name = "name", unique = true, nullable = false, length = 45)
    private String name;

    @Column(name = "description", length = 100)
    private String description;

    @Column(name = "imageLocation")
    private String imageLocation;

    @Column(name = "startDate", length = 45)
    private LocalDate startDate;

    @Column(name = "endDate", length = 45)
    private LocalDate endDate;

    @ManyToOne(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "company_id")
    private Company company;

    @ManyToMany(cascade = CascadeType.PERSIST,fetch = FetchType.EAGER)
    @JoinTable(
            name = "customer_coupon",
            joinColumns = @JoinColumn(name = "coupon_id"),
            inverseJoinColumns = @JoinColumn(name = "customer_id")
    )
    private List<Customer> customers;

CouponRepository

@Repository
public interface CouponRepository extends JpaRepository<Coupon, Long> {

    Coupon findByName(String name);
}

Test Class with printout

@SpringBootTest
@TestPropertySource(locations = {
        "classpath:application-mysql-test-connection.properties",
})
public class CouponDateIT {

    @Autowired
    private CouponRepository repository;

    @BeforeEach
    void setUp() {
        repository.deleteAll();
    }

    @Test
    void returnsLocalDate() {
        LocalDate testDate = LocalDate.now();

        DateTime.now();
        Coupon coupon = new Coupon();
        String couponName = "test1";
        coupon.setName(couponName);
        coupon.setStartDate(LocalDate.now());
        coupon.setEndDate(LocalDate.now());
        coupon = repository.saveAndFlush(coupon);

        System.out.println("===> test date " + testDate);
        System.out.println("===> coupon from db " + coupon);

        assertEquals(testDate, coupon.getStartDate());
        assertEquals(testDate, coupon.getEndDate());

        Coupon returned = repository.findByName(couponName);
        System.out.println("===> after save " + returned);
        assertEquals(testDate, returned.getStartDate());
        assertEquals(testDate, returned.getEndDate());
    }

Failing Test

Hibernate: select coupon0_.id as id1_1_, coupon0_.company_id as company_7_1_, coupon0_.description as descript2_1_, coupon0_.end_date as end_date3_1_, coupon0_.image_location as image_lo4_1_, coupon0_.name as name5_1_, coupon0_.start_date as start_da6_1_ from coupon coupon0_
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into coupon (company_id, description, end_date, image_location, name, start_date, id) values (?, ?, ?, ?, ?, ?, ?)
===> test date 2019-01-29
===> coupon from db Coupon{id=1, name='test1', description='null', imageLocation='null', startDate=2019-01-29, endDate=2019-01-29}
Hibernate: select coupon0_.id as id1_1_, coupon0_.company_id as company_7_1_, coupon0_.description as descript2_1_, coupon0_.end_date as end_date3_1_, coupon0_.image_location as image_lo4_1_, coupon0_.name as name5_1_, coupon0_.start_date as start_da6_1_ from coupon coupon0_ where coupon0_.name=?
Hibernate: select customers0_.coupon_id as coupon_i2_3_0_, customers0_.customer_id as customer1_3_0_, customer1_.id as id1_2_1_, customer1_.email as email2_2_1_, customer1_.name as name3_2_1_ from customer_coupon customers0_ inner join customer customer1_ on customers0_.customer_id=customer1_.id where customers0_.coupon_id=?
===> after save Coupon{id=1, name='test1', description='null', imageLocation='null', startDate=2019-01-28, endDate=2019-01-28}

java.lang.AssertionError: expected:<2019-01-29> but was:<2019-01-28>
Expected :2019-01-29
Actual   :2019-01-28

Log before test with

2019-01-29 11:26:07.986  INFO 6576 --- [           main] g.f.d.s.database.CouponDateIT            : No active profile set, falling back to default profiles: default
2019-01-29 11:26:09.235  INFO 6576 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data repositories in DEFAULT mode.
2019-01-29 11:26:09.308  INFO 6576 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 73ms. Found 3 repository interfaces.
2019-01-29 11:26:09.762  INFO 6576 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$70fe81c1] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2019-01-29 11:26:09.981  INFO 6576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-01-29 11:26:10.894  INFO 6576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-01-29 11:26:10.957  INFO 6576 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2019-01-29 11:26:11.019  INFO 6576 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.7.Final}
2019-01-29 11:26:11.019  INFO 6576 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2019-01-29 11:26:11.191  INFO 6576 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2019-01-29 11:26:11.341  INFO 6576 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2019-01-29 11:26:11.591  WARN 6576 --- [           main] org.hibernate.mapping.RootClass          : HHH000038: Composite-id class does not override equals(): .entity.Customer_Coupon
2019-01-29 11:26:11.591  WARN 6576 --- [           main] org.hibernate.mapping.RootClass          : HHH000039: Composite-id class does not override hashCode(): .entity.Customer_Coupon
Hibernate: drop table if exists company
Hibernate: drop table if exists coupon
Hibernate: drop table if exists customer
Hibernate: drop table if exists customer_coupon
Hibernate: drop table if exists hibernate_sequence
Hibernate: create table company (id bigint not null, email varchar(45) not null, name varchar(45) not null, password varchar(45) not null, primary key (id)) engine=MyISAM
Hibernate: alter table company add constraint UK_bma9lv19ba3yjwf12a34xord3 unique (email)
Hibernate: alter table company add constraint UK_niu8sfil2gxywcru9ah3r4ec5 unique (name)
Hibernate: create table coupon (id bigint not null, description varchar(100), end_date date, image_location varchar(255), name varchar(45) not null, start_date date, company_id bigint, primary key (id)) engine=MyISAM
Hibernate: create table customer (id integer not null, email varchar(45) not null, name varchar(45) not null, primary key (id)) engine=MyISAM
Hibernate: create table customer_coupon (customer_id bigint not null, coupon_id bigint not null, primary key (customer_id, coupon_id)) engine=MyISAM
Hibernate: create table hibernate_sequence (next_val bigint) engine=MyISAM
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: insert into hibernate_sequence values ( 1 )
Hibernate: alter table coupon add constraint UK_dfikvnp7dxdfishfvpnlc0xc1 unique (name)
Hibernate: alter table customer add constraint UK_dwk6cx0afu8bs9o4t536v1j5v unique (email)
Hibernate: alter table customer add constraint UK_crkjmjk1oj8gb6j6t5kt7gcxm unique (name)
Hibernate: alter table coupon add constraint FKe2v6qnb3w90rekqrae28iiqhm foreign key (company_id) references company (id)
Hibernate: alter table customer_coupon add constraint FKppndqdpydmsumc9yqslm5hss4 foreign key (coupon_id) references coupon (id)
Hibernate: alter table customer_coupon add constraint FKi755t5tde9sf6nrp4rm2rnnmn foreign key (customer_id) references customer (id)
2019-01-29 11:26:12.515  INFO 6576 --- [           main] o.h.t.schema.internal.SchemaCreatorImpl  : HHH000476: Executing import script 'ScriptSourceInputFromUrl(/import.sql)'
2019-01-29 11:26:12.515  INFO 6576 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2019-01-29 11:26:12.812  INFO 6576 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2019-01-29 11:26:13.747  INFO 6576 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2019-01-29 11:26:13.793  WARN 6576 --- [           main] aWebConfiguration$JpaWebMvcConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2019-01-29 11:26:14.592  INFO 6576 --- [           main] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 2 endpoint(s) beneath base path '/actuator'
2019-01-29 11:26:14.655  INFO 6576 --- [           main] g.f.d.s.database.CouponDateIT            : Started CouponDateIT in 6.985 seconds (JVM running for 8.059)

I am expecting the date field not to change after saving and returning it, but the date keeps changing to yesterday's AFTER saving the coupon.

like image 467
George Avatar asked Jan 29 '19 10:01

George


People also ask

Does Save update JPA?

You can choose between JPA's persist and merge and Hibernate's save and update methods. It seems like there are 2 pairs of 2 methods that do the same. You can use the methods persist and save to store a new entity and the methods merge and update to store the changes of a detached entity in the database.

How are Datetimes stored in MySQL?

MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.


1 Answers

In my country, the time is GMT+1 and I had the same issue.

What i did first is serverTimezone=GMT+1 which gives me error.

But JDBC uses a so-called "connection URL", so you can escape "+" by "%2B", that is

db.url=jdbc:mysql://localhost:3306/MyDB?useSSL=false&serverTimezone=GMT%2B1&useLegacyDatetimeCode=false

P.S: check this Why useLegacyDatetimeCode=false to unserstand more.

Hope it helps someone. If it does don't forget to Up Vote this.

like image 99
hamza saber Avatar answered Sep 24 '22 22:09

hamza saber