Inserting current date and time to Oracle database using Java

Question

I want to insert current date and time in the format "yyyy-mm-dd hh:mm:ss" using Java. Which datatype is suitable for this, and which method of PreparedStatement do I need to use?

Answer 1

I assume the datatype of the column is DATE in the database. If you want to insert the current date and time, you can either use oracle's built in SYSDATE:

ps = conn.prepareStatement('insert into your_table(date_col, ...) values (sysdate, ...)');

Or parameterize it and use setTimestamp() on PreparedStatement:

ps = conn.prepareStatement('insert into your_table (date_col, ...) values (?, ...)');
ps.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));

As suggested by @Basil in the comments below, You can also use java.time.Instant class:

ps.setObject(1 , Instant.now());

and while retrieving , use ResultSet#getObject(int/String, Class<T>):

Instant i = rs.getObject(1, Instant.class);

Answer 2

tl;dr

which method of PreparedStatement do I need to use

Storing:

PreparedStatement::setObject( … , java.time.Instant myInstant )

Retrieving:

ResultSet::getObject( … , Instant.class )

java.time

The modern approach uses java.time classes. Specifically: PreparedStatement::setObject and ResultSet::getObject

Get the current moment in UTC by calling Instant.now().

myPreparedStatement.setObject( … , Instant.now() ) ;

Retrieve that value.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Store that in a column of type akin to SQL-standard type TIMESTAMP WITH TIME ZONE.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 3

You can get your expected date time format using java.util.Date class . If you want to store only date then you have to convert java.util.date to java.sql.date..

If you want to store all that is time and date then go for string ( i.e. varchar data type in Oracle table) ..