CODEWITHSUNDEEP
javadatabase
kittu
kittu

Reputation: 7008

Best way to insert date and timestamp into database using java

Before finishing off the coding, I would like to know what would be the best way to insert date and time stamp in database using java.

Database could be oracle, mssql, db2 or mysql.

I want to insert date and time stamp in a way so that its retrieval should be easy for reconciliation of records. Lets say our DB admin might want to pull records between range Aug 2015 and Oct 2015 and some time range as well.

Option 1: using different sql query scripts for each database type

Example: 

INSERT INTO YOUR_TABLE (YOUR_DATE_COLUMN, COLUMN_2, COLUMN_3) 
VALUES (SYSDATE, 1234, 567);

Option 2: using java syntax as follows

Example: 

java.util.Date date = new java.util.Date();
long t = date.getTime();
java.sql.Date sqlDate = new java.sql.Date(t);
java.sql.Time sqlTime = new java.sql.Time(t);
java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(t);

pstmt.setDate(2, sqlDate);
pstmt.setTime(3, sqlTime);
pstmt.setTimestamp(4, sqlTimestamp);
pstmt.executeUpdate();

Would option 2 handle proper insertion of date and timestamp in different db types?

Upvotes: 2

Views: 3985

Answers (2)

janos
janos

Reputation: 124648

Would option 2 handle proper insertion of date and timestamp in different db types?

Assuming that you used the right Java types matching the underlying column types, yes that's the idea.

Note that in the example you gave, you set columns 2, 3, 4, but not 1. You will obviously need to set column 1 too, and they should match with columns in the SQL string (you didn't include), with ? placeholders for the values for the appropriate columns.

In any case, check the documentation of the jdbc drivers of the databases you want to work with, look at the implementation notes concerning the mapping of Java types to SQL types, and any potential documented gotchas.

Upvotes: 2

Lachezar Balev
Lachezar Balev

Reputation: 12021

Definitely option 2 will handle the insertion properly. At least better than option 1 :-) Of course you should create columns with proper data types in the database.

The things that you should be careful for are:

  1. The datatypes in the database;
  2. Truncation of the data depending on 1);
  3. Time zones (the default time zone of the JVM will be used) so if you need conversions you have to care about this. E.g. @see setDate(int, Date, Calendar)

Upvotes: 1

Related Questions