Calling getdate() using Hibernate

This post shows you how to use Hibernate to call Sybase's getdate() function in order to get the current date and time on your database server.

First, you need to create an entity to represent the date object. Hibernate will then map the result of getdate() to this entity.

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Represents a date entity.
 * Used by hibernate to map the getdate() sybase function onto.
 */
@Entity
public class DBDateTime {

    @Id
    @Temporal(TemporalType.TIMESTAMP)
    private Date date;

    /**
     * @return the date
     */
    public Date getDate() {
        return date;
    }
}

Usage:
The code snippet below shows how you would call getdate() on your Sybase database and get a Date returned:

Query query = entityManager.createNativeQuery("SELECT getdate() as date", 
                                              DBDateTime.class);
DBDateTime dateEntity = (DBDateTime) query.getSingleResult();
Date now = dateEntity.getDate();

Using a NamedQuery with a Composite ID in Hibernate

Last week I wrote about how you can create a Composite ID in Hibernate if your table has multiple key columns. In this post, I will show you how you can use a NamedQuery to select entities with embedded composite ids.

The code is shown below. The entity class is the same as before except for the @NamedQueries annotation which defines two named queries. The first query searches for a user with a specific first name and last name. The second query allows you to specify multiple last names in an "in-clause".

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * The User entity which contains an embedded UserId.
 */
@Entity
@Table(name = "Users")
@NamedQueries({
  @NamedQuery(name="user.findByName",
              query = "from User where id.firstName = :firstName and id.lastName = :lastName"),
  @NamedQuery(name="user.withLastNames",
              query = "from User where id.lastName in (:lastName)")
})
public class User {

  @EmbeddedId
  private UserId id;

  private String website;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(insertable = false)
  private Date lastUpdateTime;

  /**
   * Default constructor required by Hibernate.
   */
  public User() {
  }

  /**
   * This represents a "composite primary key" for the Users table.
   * It contains all the columns that form a unique id.
   * Must implement equals() and hashcode() and be serializable.
   * https://community.jboss.org/wiki/EqualsAndHashCode
   */
  @Embeddable
  public static class UserId  implements Serializable {

    private static final long serialVersionUID = 1L;

    private String firstName;
    private String lastName;

    /**
     * Default constructor required by hibernate.
     */
    public UserId() {
    }

    /** (non-Javadoc)
     * @see java.lang.Object#hashCode()
     */
    @Override
    public int hashCode() {
      final int prime = 31;
      int result = 1;
      result = prime * result + ((firstName == null) ? 0 : firstName.hashCode());
      result = prime * result + ((lastName == null) ? 0 : lastName.hashCode());
      return result;
    }

    /** (non-Javadoc)
     * @see java.lang.Object#equals(java.lang.Object)
     */
    @Override
    public boolean equals(Object obj) {
      if (this == obj)
        return true;
      if (obj == null)
        return false;
      if (getClass() != obj.getClass())
        return false;
      UserId other = (UserId) obj;
      if (firstName == null) {
        if (other.firstName != null)
          return false;
      } else if (!firstName.equals(other.firstName))
        return false;
      if (lastName == null) {
        if (other.lastName != null)
          return false;
      } else if (!lastName.equals(other.lastName))
        return false;
      return true;
    }
  }
}

Usage:
The code snippet below shows how you would use the named queries:

// the first query
Query query1 = entityManager.createNamedQuery("user.findByName")
                            .setParameter("firstName", "Peter")
                            .setParameter("lastName", "Griffin");
List<User> resultList = query1.getResultList();

// the second query
Query query2 = entityManager.createNamedQuery("user.withLastNames")
                            .setParameter("lastNames", Arrays.asList("Dent", "Griffin"));
List<User> resultList = query2.getResultList();

Composite IDs in Hibernate

Consider the following database table which contains a list of users and the times they last accessed a certain website:

FirstName	LastName	Website	AccessTime
Arthur	Dent	Google	2012-03-30 07:09:00.0
Peter	Griffin	Yahoo!	2012-03-30 10:36:00.0

Let's say that you want to use both the FirstName and LastName fields to uniquely identify records in the table and perform searches/updates using Hibernate. There are different ways to define a "composite primary key" and, in this post, I will show you an approach I have used successfully in the past, which involves creating an EmbeddedId.

The @EmbeddedId approach:
The code is shown below and is pretty self-explanatory. It consists of a User class which contains a static nested UserId class. The latter holds the first name and the last name attributes and thus represents the composite ID.

import java.io.Serializable;

import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

import org.hibernate.annotations.Type;
import org.joda.time.DateTime;

/**
 * The User entity which contains an embedded UserId.
 */
@Entity
@Table(name = "Users")
public class User {

  @EmbeddedId
  private UserId id;

  private String website;

  @Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
  private DateTime accessTime;

  /**
   * Default constructor required by Hibernate.
   */
  public User() {
  }

  /**
   * @param id the user id
   */
  public User(UserId id) {
    this.id = id;
  }

  /**
   * @return the id
   */
  public UserId getId() {
    return id;
  }

  /**
   * @param id the id to set
   */
  public void setId(UserId id) {
    this.id = id;
  }

  /**
   * @return the website
   */
  public String getWebsite() {
    return website;
  }

  /**
   * @param website the website to set
   */
  public void setWebsite(String website) {
    this.website = website;
  }

  /**
   * @return the accessTime
   */
  public DateTime getAccessTime() {
    return accessTime;
  }

  /**
   * @param accessTime the accessTime to set
   */
  public void setAccessTime(DateTime accessTime) {
    this.accessTime = accessTime;
  }

  /**
   * This represents a "composite primary key" for the Users table.
   * It contains all the columns that form a unique id.
   * Must implement equals() and hashcode() and be serializable.
   * https://community.jboss.org/wiki/EqualsAndHashCode
   */
  @Embeddable
  public static class UserId  implements Serializable {

    private static final long serialVersionUID = 1L;

    private String firstName;
    private String lastName;

    /**
     * Default constructor required by Hibernate.
     */
    public UserId() {
    }

    /**
     * @param firstName the first name
     * @param lastName the last name
     */
    public UserId(String firstName, String lastName) {
      this.firstName = firstName;
      this.lastName = lastName;
    }

    /**
     * @return the firstName
     */
    public String getFirstName() {
      return firstName;
    }

    /**
     * @param firstName the firstName to set
     */
    public void setFirstName(String firstName) {
      this.firstName = firstName;
    }

    /**
     * @return the lastName
     */
    public String getLastName() {
      return lastName;
    }

    /**
     * @param lastName the lastName to set
     */
    public void setLastName(String lastName) {
      this.lastName = lastName;
    }

    /** (non-Javadoc)
     * @see java.lang.Object#hashCode()
     */
    @Override
    public int hashCode() {
      final int prime = 31;
      int result = 1;
      result = prime * result + ((firstName == null) ? 0 : firstName.hashCode());
      result = prime * result + ((lastName == null) ? 0 : lastName.hashCode());
      return result;
    }

    /** (non-Javadoc)
     * @see java.lang.Object#equals(java.lang.Object)
     */
    @Override
    public boolean equals(Object obj) {
      if (this == obj)
        return true;
      if (obj == null)
        return false;
      if (getClass() != obj.getClass())
        return false;
      UserId other = (UserId) obj;
      if (firstName == null) {
        if (other.firstName != null)
          return false;
      } else if (!firstName.equals(other.firstName))
        return false;
      if (lastName == null) {
        if (other.lastName != null)
          return false;
      } else if (!lastName.equals(other.lastName))
        return false;
      return true;
    }
  }
}

Usage:
The code snippet below shows how you would search for a user and update its access time:

UserId userId = new UserId("Peter", "Griffin");

// search for a user
User user = entityManager.find(User.class, userId);
if (user == null) {
    // the user doesn't exist, so create one
    user = new User(userId);
}
user.setAccessTime(new DateTime());

// update the user
entityManager.merge(user);