How to correctly set the current time stamp into this Java CRUD method that insert a value into a MySql table?

Question

I have the following table into a MySql database:

CREATE TABLE log(
    timestamp TIMESTAMP NOT NULL,
    action ENUM('addPerson', 'getPerson', 'getPeople', 'updatePerson', 'deletePerson', 'deleteAll') NOT NULL,
    PRIMARY KEY (timestamp)
);

Now I am implementing a JAVA insert CRUD method into a DAO but I am finding some problem setting the timestamp value (initialized with the current date time) into the related prepared statment.

So I am doing something like this:

public void addEntry(String message) throws SQLException {
    // The Connection object is a singleton and the used connection is the same for all the CRUD methods:
    Connection conn = Database.getInstance().getConnection();

    PreparedStatement p = conn.prepareStatement("insert into log (timestamp, action) values (?,?)");

    p.setTimestamp(1, new Timestamp(time));
}

but it gime men error on the creation of the Timestamp object because it need a long value. So I think that this is not what I would !!!

How can I set it to the current timestamp (the current date-time)?

Tnx

Answer 1

Try this: p.setTimestamp(1, new Timestamp(System.currentTimeMillis()));

Note: I'd recommend you to check Javadoc before posting on SO. It really helps.

Answer 2

java.time

The modern approach uses the java.time classes that years ago supplanted the terrible java.sql.Timestamp class. Specifically, JDBC 4.2 and later supports OffsetDateTime for this.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Write to database.

myPreparedStatement.setObject( … , odt ) ;

Retrieve.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

DEFAULT CURRENT_TIMESTAMP

Even better, automate. Use DEFAULT on the definition of the table to automatically capture the current moment while inserting the row. For details on both standard SQL as well as MySQL syntax, see this Answer.