JDBC Timestamp - How to select a Java Timestamp field from a database timestamp column

Here's a JDBC Timestamp example, showing how to read a Java Timestamp field from a database Timestamp column (a MySQL Timestamp field) in a SQL SELECT statement. I pulled this source code out of a real-world Java application, so I'll break it into small steps, and hopefully it will make sense.

The MySQL timestamp field definition

My Java application uses a MySQL database, including one table named commands that has a MySQL Timestamp field I need to read. The SQL definition for this MySQL table is shown below:

create table commands (
    id int auto_increment not null,
    command_name varchar(128) not null,
    command_line varchar(1024) not null,
    updated_by_id int,
    updated_time timestamp not null default now(),
    foreign key (updated_by_id) references nagios_users(id) ON DELETE SET NULL,
    primary key (id),
    constraint unique index idx_commands_cmd_name_unique (command_name)
) ENGINE = InnoDB;

For our timestamp purposes, the most important part of this table is the field named updated_time, which is the MySQL timestamp field we'll be reading in our JDBC code.

The example Java bean class

Next, I created a Java class that is essentially a JavaBean that maps to this database table. The fields of this class primarily match the fields from the commands table, and the class has getter and setter methods for each field.

For the purposes of our example, I've stripped down my JavaBean class to only show the code for our Java and MySQL Timestamp purposes. Here are the lines of source code from this class that relate to this Timestamp field, with a little added documentation:

// (1) import the Java Timestamp class near the top of my class.
//     note that we use a Java Timestamp to map to the MySQL Timestamp field.
import java.sql.Timestamp;

// (2) declare this timestamp field in my class
private Timestamp updatedTime;

// (3) the setter method for this timestamp field
public void setUpdatedTime(Timestamp updatedTime)
{
  this.updatedTime = updatedTime;
}

// (4) the getter method for this timestamp field
public Timestamp getUpdatedTime()
{
  return updatedTime;
}

As you can see from this code, the updatedTime field in my class is a Java Timestamp field that maps to the MySQL Timestamp column named updated_time.

The Java Data Access Object (DAO) method

With those pieces in place, I just have to define a Java DAO object that performs the SQL SELECT statement, providing the mapping between the database and my JavaBean class.

I used the Spring Framework at this point to simplify my DAO objects, but that's not important for this example. The important part I want to show here is how I retrieve a Java Timestamp field from a database column, so I added a comment in the example code below to show where that Timestamp field is retrieved:

private static final class NagiosCommandMapper implements ParameterizedRowMapper
{
  public NagiosCommand mapRow(ResultSet rs, int rowNum)
  throws SQLException 
  {
    NagiosCommand command = new NagiosCommand();
    command.setId( rs.getInt("id") );
    command.setCommandName( rs.getString("command_name") );
    command.setCommandLine( rs.getString("command_line") );
    command.setUpdatedById( rs.getInt("updated_by_id") );

    // retrieve the timestamp field from the database table
    command.setUpdatedTime(rs.getTimestamp("updated_time"));
    return command;
  }
}  

Again, my use the of the Spring Framework here isn't very important, because even in a pure JDBC application without using Spring, your code related to the Timestamp field will still look like this:

command.setUpdatedTime(rs.getTimestamp("updated_time"));

The general pattern/formula

As you can see, when you want to read a Java Timestamp field from a database table Timestamp column, there is a short multi-step formula that you can easily follow:

1. If you're responsible for the database, design your database table that includes a Timestamp field.
2. Create a JavaBean that maps your Java fields to the database table fields, including the mapping of the Java Timestamp field to the database table Timestamp field.
3. Create one or more Java DAO methods that read from (and write to) the database table, and handle the mapping of the table fields to their related fields in your JavaBean class. When using a SELECT statement (like this example), you'll use the ResultSet getTimestamp method to perform this mapping.

I hope you find this Java/MySQL Timestamp tutorial helpful. If you have any questions or comments just leave them below, and I'll get back with you as quickly as I can.