Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Friday, August 19, 2011

Providing Your Own MySQL @Id Column in the Play! Framework

When you create Model entities in the Play! Framework, Play will provide a default id column.  If you need to use your own id column -- say if you are given a database schema to work with -- then you should extend a different Play class.

To create a JPA entity in the Play! framework, create a class that subclasses play.db.jpa.Model.  Annotate the class with @Entity, create public fields for the data, and implement a constructor.  For example, this is a class in the app/models folder that works with a MySQL table called "BD_USER".

package models;

import java.util.*;
import javax.persistence.*;

import play.db.jpa.*;

@Entity
@Table(name="bd_user")
public class User extends Model {
  public String user_nm;
  public String password_tx;
  public String email_tx;
  public Date create_dt;
  public Date last_update_dt;
  public String user_type_code_id;
 

  public User(String _user_nm,
   String _password_tx,
   String _email_tx,
   Date _create_dt,
   Date _last_update_dt,
   String _user_type_code_id) {
   

   user_nm = _user_nm;
   password_tx = _password_tx;
   email_tx = _email_tx;
   create_dt = _create_dt;
   last_update_dt = _last_update_dt;
   user_type_code_id = _user_type_code_id;
  }


The User class works with several defaults.  An @Id will be provided by extending Model.  Each field name matches with the corresponding column name.  One default that is overridden is the table name, which is specified using the @Table annotation to be "BD_USER".  If this were left off, JPA would try to use a table called USER for persistence operations, possibly creating the table if configured so.

DB-Specific Generated Values

As mentioned in the Play! documentation, GenericModel is an alternative class to implement.  It does not provide an @Id.  The following class, Tutorial, extends GenericModel.  Note that in addition to providing an @Id, a @GeneratedValue is provided.  @GeneratedValue tends to be database-specific (though vendor neutral).  In this example, the MySQL auto number feature is used as indicated in the annotation.

package models;

import java.util.*;
import javax.persistence.*;

import play.db.jpa.*;

@Entity
@Table(name="bd_tutorial")
public class Tutorial extends GenericModel {

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
  public Long tutorial_id;
  public String tutorial_title_tx;
  public String tutorial_desc_tx;
  public Date create_dt;
  public Date last_update_dt;

  public String tech_code_id;

  public Tutorial(String _tutorial_title_tx,
   String _tutorial_desc_tx,
   Date _create_dt,
   Date _last_update_dt,
   String _tech_code_id) {
    tutorial_title_tx = _tutorial_title_tx;
    tutorial_desc_tx = _tutorial_desc_tx;
    create_dt = _create_dt;
    last_update_dt = _last_update_dt;
    tech_code_id = _tech_code_id;
  }
}


Oracle

Oracle doesn't use the same auto-generated identifier strategy as MySQL.  (However, some DBAs replicate this with triggers.)  Rather, Oracle uses sequences which are separated from the tables.  In this code snippet, a sequence video_id_seq provides the unique identifiers for a table's video_id column.  Note that an additional annotation, @Column, is used which allows a different Java field name.

@Id
@GeneratedValue(strategy=SEQUENCE, generator="videoIdSeq")
@SequenceGenerator(name="videoId", sequenceName="video_id_seq", allocationSize=1)
@Column(name="video_id")
protected Long videoId;


Not Generated

Another option with the @Id annotation is to forgo the generation.  Say you have a reference data item with a String code, use a definition like this without @GeneratedValue.  In this case, "tech_code_id" can be "TAL", "JAS", "NOT", or another 3-character code value that exists in a table.

@Id
public String tech_code_id;
 
If you've written JPA code before, you've had to configure persistence.xml and a provider like Hibernate.  This is one of the nice features of the Play! Framework.  You can immediately write simple classes like User and Tutorial above and immediately begin testing.  No DAOs required.

2 comments:

  1. nice tip, specially the oracle stuff

    ReplyDelete
  2. The great thing I like in mysql is there auto_increment feature for ID. first time read about Play framework though , wondering how many framework one could learn :)

    Javin
    10 tips on java debugging with eclipse

    ReplyDelete