How to manipulate date and time in java JDBC and sql-CodePudding

I am making an app that has a database accessed with JDBC. I am using mysql but later I am going to host my DB on a platform that impose Oracle.

Let's say I want to make a table employee that maps the employee, the date he was employed and the time he ends his shift.

What I decided to do is use Date and Time as strings. So to create a table I would use :

CREATE TABLE employee
(
   ID VARCHAR(255),
   dateEmployed VARCHAR(255),
   endShift VARCHAR(255),
   Primary key (ID)
);

Then in java with jdbc I just use the executeQuery to search for all the employee and filter the result in pure java. This method seems naive, as I cannot compare the strings for date and time in sql through a Java jdbc. How can I do this the correct way ?

CodePudding user response：

What I decided to do is use Date and Time as strings.

DON'T

If you want to store a date and time then use a DATE data type (which, in Oracle, stores both a date and a time).

CREATE TABLE employee
(
   ID           VARCHAR(255),
   dateEmployed DATE,
   endShift     DATE, -- or maybe INTERVAL DAY(0) TO SECOND if you want time
                      -- without a date.
   PRIMARY KEY (ID)
);

Then in java with jdbc I just use the executeQuery to search for all the employee and filter the result in pure java. This method seems naive, as I cannot compare the strings for date and time in sql through a Java jdbc. How can I do this the correct way?

If you want to filter on dates and, for example, get all the employees who started in January 2022 then:

SELECT *
FROM   employee
WHERE  dateEmployed >= DATE '2022-01-01'
AND    dateEmployed <  DATE '2022-02-01'

If you want to use bind parameters from JDBC then your query should be:

SELECT *
FROM   employee
WHERE  dateEmployed >= ?
AND    dateEmployed <  ?

and then you set either date (if you do not have a time component to your bind value) or timestamp (if you do have a time component) bind parameters on your prepared statement.