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();

Sybase: How to BCP data in and out of databases

To quickly copy data from a table in one database to another database, for example, from production to a development environment, use the Sybase bcp utility as follows:

Step 1: bcp out to a file
First run bcp to copy data out of your database table and into a flat file. Just hit [Return] when prompted for lengths of columns, but remember to save the table format information to a file. An example is shown below:

$ bcp  Customers out /tmp/bcp.out -S server1 -t, -U username -P password
Enter the file storage type of field firstName [char]:
Enter prefix-length of field firstName [0]:
Enter length of field firstName [32]:
Enter field terminator [,]:

Enter the file storage type of field lastName [char]:
Enter prefix-length of field lastName [0]:
Enter length of field lastName [10]:
Enter field terminator [,]:

Enter the file storage type of field accessTime [smalldatetime]:
Enter prefix-length of field accessTime [0]:
Enter field terminator [,]:

Do you want to save this format information in a file? [Y/n] Y

Host filename [bcp.fmt]: /tmp/bcp.fmt

Starting copy...

14 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (14000.00 rows per sec.)

Step 2: bcp in to the target database
Next run bcp to copy data from the flat file to your target database using the format file you saved in Step 1.

$ bcp  Customers in /tmp/bcp.out -S server2 -f /tmp/bcp.fmt -U username -P password
Starting copy...

14 rows copied.
Clock Time (ms.): total = 9  Avg = 0 (1555.56 rows per sec.)

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();