jsp - How to obtain a timestamp in Java and store in MySQL database?

Question

I'm developing a basic CMS uses JSP/Servlet Technology using the MVC padagim. When a new post is added I want to get the date and time the post was created and store it as TIMESTAMP or DATATIME data type. What is the best way to do it? And how do I Do it?

The algorithm should be like this

In a java web program (such as servlet)

1. Get Time stamp (current time)
2. Store in time stamp in a variable
3. Pass sql query to store the timestamp in mysql db

Please help!

I have seen many answers, but I Don't understand those as I'm quite new to Java and jsp technology!

Answer 1

A much better way to handle this is at your database side. While creating the table specify the default value for the TIMESTAMP column as

CREATE TABLE posts (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    creation_date TIMESTAMP DEFAULT NOW()
);

The example shows a CREATE TABLE for MySQL but the concept is the same. While inserting your row, just don't specify any value for the column creation_date and the database will auto populate it for you.

---

Given the same table, if you want to insert the date from Java, your code should look like

// Get current time
Timestamp now = new Timestamp(new Date().getTime());

try {
    // Prepare INSERT through Connection
    PreparedStatement stmt = conn.prepareStatement(
        "INSERT INTO posts (title, creation_date) VALUES (?, ?)");

    // Bind values
    stmt.setString(1, title);
    stmt.setTimestamp(2, now);

    // Insert
    stmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

Note, that you would also need to open a DB Connection (the conn object above) and close it when you're done with it. If you're new to JDBC API, take a look at JDBC Basics first.