Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Retrieve LocalDate and LocalDateTime from DB  RSS feed

 
Sev madi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hay there,

This is my first post in here. This forum has so much I was a little confused in which section I had to post. So please bear with me.


The problem I'am dealing with is the new feature of Java 8 LocalDate. I am building an application to save time and load them.

I've got the save method working.





But can't get the load. Having some difficulties with SQL

Here is what I am trying.


Someone has any idea how to solve this. Thank you.
 
Darryl Burke
Bartender
Posts: 5167
11
Java Netbeans IDE Opera
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Sev and welcome to the Ranch! You should always store dates as dates, never as Stirings or ints. java.sql.Date has been retrofitted with a couple of methods for conversion to and from Localdate; java.sql.TimeStamp has similar methods for LocalDateTime.

If null values are permitted, you will need to code around that.
 
Sev madi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply.

In the attachments I have an img how my sql structure looks like.

Is there any way that I could retrieve the data which is now stored in DB? Or I have to modify the database structure and the code In order to be able to retrieve?

Thanks.
Screenshot-from-2015-08-18-14-28-38.png
[Thumbnail for Screenshot-from-2015-08-18-14-28-38.png]
SQL structure
 
Tapas Chand
Ranch Hand
Posts: 614
9
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would like to define the fields like
weeknr: int(2) //I presume "weeknr" means week number in year which cannot exceed 53.
dayofweek: varchar(9) //I presume you are storing values like SUNDAY, MONDAY etc. So length of 9 would suffice.
starttime: datetime or timestamp as per your requirement
stoptime: datetime or timestamp as per your requirement

I am not sure what "hrs" and "overtime" will store. May be you can explain.

NOTE: Column names in MySql are case-insensitive. So dayofweek and dayOfWeek are same.
So it is recommended to put underscore("_") between words so that column names will be more readable.
Like WEEK_NUMBER, DAY_OF_WEEK, START_TIME,STOP_TIME etc.
 
Sev madi
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for explanation.

'hrs' and 'overtime' are what the hours you've worked in day and perhaps overtime (to which you get paid double).


Would you please explain how would I retrieve data if already got some stored.


Thank you.
 
Tapas Chand
Ranch Hand
Posts: 614
9
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well as far as "weeknr" is concerned, you can get the value using getInt() method of ResultSet and store it in an "int". So no need of "LocalDate".
"starttime" and "stoptime" can be retrieved using getTimestamp() method and can be stored in LocalDateTime.

You can check the docs for methods
  • ResultSet.getTimestamp()
  • Timestamp.toLocalDateTime()
  •  
    Sev madi
    Greenhorn
    Posts: 4
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I can't change weeknr by int, because at the end I am calling timemodel which includes parameters of the all above declared variables, to which I get an error saying my timemodel has a weeknr as LocalDate and not int.




    This is actually the whole point which I am trying to figure out. I must be able to retrieve the 'int' stored data in db by using:

    LocalDate weeknr = ??
     
    Tapas Chand
    Ranch Hand
    Posts: 614
    9
    BSD Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    huhh...I am totally unsure how to convert week number to localdate.
     
    Rob Spoor
    Sheriff
    Posts: 21092
    85
    Chrome Eclipse IDE Java Windows
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    That's because a week number does not have 1 but 7 matching LocalDate objects. You can get one of these (I don't really know which though) using localDate.with(ChronoField.ALIGNED_WEEK_OF_YEAR, weekNr).
     
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!