1

I am using H2 database. I want to prefill my database with data.sql. But now I get the exception , that date cannot be converted. I even get a NumberFormatException. I really do not know why. 

INSERT INTO Client
VALUES ('10000000','12.10.2011','Petra','Trump');

In my entity I also specified the date the same way.

  @DateTimeFormat(pattern = "dd.MM.yyyy")
  private LocalDate  dateOfBirth;

So I do not understand what could be wrong.

To see how the date is stored in the database I tried to connect to H2 by http://localhost:8080/h2-console but it always said JDBC URL : jdbc:h2:mem:testdb is not correct. So I wonder what I should input there instead. I am just using boot and boot did everything for me.

Josephine
  • 35
  • 4
  • I just found out it is stored in a format like that . 'yyyy-MM-dd' but why. that differs from @DateTimeFormat(pattern = "dd.MM.yyyy") – Josephine May 24 '20 at 22:21
  • 1
    Hi @Josephine, I am guessing `data.sql` is a native h2 data import format. So it is not linked at all to the `@DateTimeFormat` annotation in the java code. – Andrew NS Yeow May 24 '20 at 22:29
  • aha. Thanks. Good guess. But the table has to be created beforehand by Hibernate and there I expected the format to be influenced by @DateTimeFormat(pattern = "dd.MM.yyyy") – Josephine May 24 '20 at 23:01
  • Datetime values should not be written in SQL as strings, there is a standard date literal: `DATE '2011-10-12'`; it can be used in the most of database systems. Character string literals such as `'2011-10-12'` are supported by H2 too, but their usage is a bad idea, you may run into some trouble with them in more complex commands and they aren't portable between different database systems. Database knows nothing about your entities, their fields, and annotations. – Evgenij Ryazanov May 25 '20 at 04:28

2 Answers2

0

For right JDBC URL go to application.properties file and look for "spring.datasource.url".

And for data format for H2 you need to use SQL DATE look here: How to convert LocalDate to SQL Date Java?

GDBxNS
  • 139
  • 6
0

Your insert script will not look to your class entity, it will execute directly as natively without jpa examine.

So you need to fix your date like: DATE '2022-10-08' (year-month-date)

Or for timed like: TIMESTAMP '2022-10-08 11:17:52.924'

utrucceh
  • 1,076
  • 6
  • 11