Java JPA: ORM database persistence framework tutorial

JPA 2.0 is the standardization of various Object Relational Mapping (ORM) APIs and their query languages. JPA uses the "Java Persistence Query Language" (JPQL), a SQL like query language customized for use with the Java language.

JPA uses Java classes to represent relational database tables which can contain data read from a database or have data which will be written to a database. The mapping of the Java class and its member variables to a database table and fields can be fulfilled by Java annotations to the class or by an XML configuration file. In this tutorial we will take the annotation approach. An XML configuration file will be used to define the database connection.

Java:
As a prerequisite Java and JAXB need to be installed. See our tutorial on installing Java on Linux.

Java Log4j: (version 1)
Apache Log4j version 1

cd /opt
sudo tar xzf ~/Downloads/log4j-1.2.17.tar.gz

We will be using JAR file located in:

• /opt/apache-log4j-1.2.17/log4j-1.2.17.jar

JPA/Hibernate:

The Hibernate open source implementation of JPA will be used for this tutorial and the latest version can be downloaded from: Hibernate ORM downloads page
Note that early versions of Hibernate provided an ORM Java API but may predate the JPA 2.0 standard (released Dec 2009). Red Hat offers commercial support.

cd /opt
sudo unzip ~/Downloads/hibernate-release-4.2.15.Final.zip

If using JBoss, your CLASSPATH can reference JAXB from the JBoss library: /opt/jboss-X.X.X.XXXXX/lib/

We will be using JAR files located in:

• /opt/hibernate-release-4.2.15.Final/lib/jpa/
• /opt/hibernate-release-4.2.15.Final/lib/required/

Database:
In this tutorial we will be working with a MySQL database.

Installation and configuration of MySQL on Linux tutorial

Database Connector:
In this example we will be using a MySQL relational database and will require the MySQL connector.
Download MySQL Connector/J

cd /opt
sudo tar xzf ~/Downloads/mysql-connector-java-5.1.32.tar.gz

We will be using:

• /opt/mysql-connector-java-5.1.32/mysql-connector-java-5.1.32-bin.jar
• /opt/mysql-connector-java-5.1.32/lib/slf4j-api-1.6.1.jar

Alternative source for JAR files: FindJar.com

Note that Java application servers like JBoss will come will most of the required JAR files.

Our first JPA example is for the exchange of data between a single database table and a single Java class:

SQL file: corporation.sql Load database: mysql -h localhost -u root -psupersecretpassword < corporation.sql

This will generate the following table in MySQL:

mysql -h localhost -u root -psupersecretpassword

mysql> desc corporation;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| name        | varchar(255) | YES  |     | NULL    |       |
| description | varchar(255) | YES  |     | NULL    |       |
| address     | varchar(255) | YES  |     | NULL    |       |
| zipcode     | varchar(16)  | YES  |     | NULL    |       |
| phone       | varchar(32)  | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Generate an anntotated Java class to represent the database table schema:

File: Corporation.java

Main program to insert a record to the database and to read two records from the database:

File: TestDb.java

JPA configuration file: This defines the database connection parameters.

File: META-INF/persistence.xml Note:

• This database connection is to "localhost using the default MySQL port 3306 and the database name is "corpinfo".
• The "class" definition: <class>Corporation</class> refers to the Java class marked with @Entity. Typically this will have the full namespace reference is used for example: com.megacorp.accounting.Corporation. There will also be an XML class definition for each Java class annotated with @Entity.

Ant build script:

File: build.xml

Compile: ant compile

Run: ant run

     [java] Corp 3 name: Mini Corp Zipcode: 90266
     [java] Corp 5 name: Extra Corp Zipcode: 90267

The Java Persistence Query Language (JPQL) is similar to the SQL SELECT, UPDATE and DELETE statements and was developed to be portable for any database supported by JPA. JPQL is designed to operate on Java objects, attributes and relationships rather than tables and columns. JPQL implemented as a Query, NamedQuery or TypedQuery object constructed using the EntityManager createQuery() API call. Queries can be either static or dynamically generated at runtime.

JPQL uses "identification variables" declared in the FROM clause and is named uniquely and evaluates to a value of the type of the expression used in declaring the variable. Example:

Query query = entityManager.createQuery("SELECT e FROM Corporation e");
List<Corporation> result = query.getResultList();

or for a specific field:

Query query = entityManager.createQuery("SELECT e.name FROM Corporation e WHERE e.zipcode = :zipcode");
String sZipcode="90266";
query.setParameter("zipcode", sZipcode);
List<Corporation> result = query.getResultList();

or for a specific field and a single result:

Query query = entityManager.createQuery("SELECT e.name FROM Corporation e WHERE e.zipcode = :zipcode");
String sZipcode="90210";
query.setParameter("zipcode", sZipcode);
Corporation result = query.getSingleResult();

JPQL Functions:

• JPQL Aggregation Functions include MIN(), MAX(), AVG(), SUM(), COUNT() and GROUP BY, ORDER BY and HAVING.
  Examples:
  • SELECT COUNT(e) FROM Corporation e
  • SELECT MAX(e.zipcode) FROM Corporation e
  • SELECT e FROM Corporation e ORDER BY e.zipcode
• JPQL Functions include "+" (addition), "-" (subtration), "*" (multiplication), "/" (division), ABS(), CASE/STATUS/WHEN/THEN/ELSE/END (case statement operation), COALESCE(arg1,arg2,arg3) (first non-null argument), CONCAT(str1,str2,str3), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LENGTH(e.field), LOCATE(), LOWER() (string case), MOD(), NULLIF(), SQRT(), SUBSTR(), TRIM(), UPPER() (string case).
• JPQL Operators: INDEX(), KEY(), SIZE(), IS EMPTY, TYPE(), FUNCTION() and TREAT()

JPQL Constructors:

JPQL provides a NEW operator which can be used with a fully qualified class name to store the returned data objects from the query.
Examples:

• SELECT NEW com.megacorp.accounting.Corporation (e.name, e.description, e.address, e.zipcode, e.phone) FROM Corporation e WHERE e.zipcode='90210'

JPQL WHERE Clause Comparison Operators:

The following standard SQL databse database operators are available in JPQL: =, <, >, <=, >=, LIKE (match with wildcards "%" or "_"), BETWEEN, IS NULL and IN (contained in provided list).

JPQL Update and Delete queries:

Examples:

• Update:
  Query query = entityManager.createQuery("UPDATE Corporation e SET e.description = 'New description goes here' WHERE e.name = 'Super Corp'");
  int rowCount = query.executeUpdate();
• Delete:
  Query query = entityManager.createQuery("DELETE Corporation e WHERE e.name = 'Super Corp'");
  int rowCount = query.executeUpdate();

JPQL Named and Positional Queries:

Both named and positional parameters are supported.
Examples:

• Named: (variation of example above)
  

  Query query = entityManager.createQuery("SELECT e.name FROM Corporation e WHERE e.zipcode = :var1 AND e.name = :var2");
  String sZipcode="90266";
  String sCorp="Mega Corp";
  query.setParameter("var1", sZipcode);
  query.setParameter("var2", sCorp);
  Corporation result = query.getSingleResult();
  

• Positional Parameters:
  

  Query query = entityManager.createQuery("SELECT e.name FROM Corporation e WHERE e.zipcode = ? AND e.name = ?");
  String sZipcode="90266";
  String sCorp="Mega Corp";
  query.setParameter("1", sZipcode);
  query.setParameter("2", sCorp);
  Corporation result = query.getSingleResult();
  

JPQL Clause Operators:

These include SELECT, FROM, JOIN, JOIN FETCH, LEFT JOIN, ON (JPA 2.1)

Specific Database Property Options:

MySQL:

PostgreSQL:

Derby:

JBoss HsqlDb:

Oracle:

---

Generic JPA Property Options:

Attribute	Options
hibernate.hbm2ddl.auto	validate: validate the schema, makes no changes to the database. update: update the schema. create: creates the schema, destroying previous data. create-drop: drop the schema at the end of the session.
hibernate.show_sql	true: Enable the logging of all the generated SQL statements (from HQL) to the console false: Not output
hibernate.format_sql	true: Format the generated SQL statement to make it more readable, but takes up more screen space. false: Not formatted
use_sql_comments	true: Generate SQL comments to show what the generated SQL trying to do false: No comments generated
hibernate.archive.autodetection	class: Determine which element is auto discovered by Hibernate Entity Manager while parsing the .par archive. (default to class,hbm).
hibernate.flushMode	FLUSH_AUTO:

Entity Mapping:

We must map the database and table column name to the class name and class member variable names and data types.

• Entity: The Java class is annoted with @Entity to designate it as mapped it to a database table.
  Entities require Java classes with a constructor with no-arguments. A class with no constructor by default is assigned a default constructor with no arguments.

• Table Key: An additional @Id annotation is required for one member variable.
  [Potential Pitfall]: If no "@Id" is specified you will get the following error:

      [java] Caused by: org.hibernate.AnnotationException: No identifier specified for entity: Corporation
      

  If the table's primary key s auto-generated by the MySQL attribute AUTO_INCREMENT then add the Java annotation @GeneratedValue which indicates that value is automatically generated by the server. Example:

  @GeneratedValue(strategy = GenerationType.IDENTITY)
      

• Table Name: By default it is assumed that the Java class name matches the database table name. If it does not, one must identify the database table name with the annotation: @Table(name="TableName")

Example:

@Entity
@Table(name="corporation")
public class Corporation {
    @Id
    private Integer id;
    ...
    ..

    public Integer getId() {
      return id;
    }
    public void setId(Integer id) {
      this.id = id;
    }
    ...
    ..

Column Mapping:

By default it is assumed that the Java variable names matches the database field name. If it does not, one must identify the database field name with the annotation: @Column(name="Col_Name")
[Potential Pitfall]: If you mispell "Column" you will get the following error. Note that there is only one "l", NOT two in the word "Column".

    [javac] /home/user1/Test/Corporation.java:8: error: cannot find symbol
    [javac]     @Collumn(name="name")
    [javac]      ^
    [javac]   symbol:   class Collumn
    

Its @Column NOT @Collumn !!!

Mapping MySQL to Java Data Types:

The data types of the database (MySQL in this example) and of Java may not have an obvious mapping and thus must be explicitly stated. The @Column annotation can map data types in addition to the field names.
Example:

• MySQL database type: TEXT
  Java type: String
  Annotation: @Column(name="description",length = 65535,columnDefinition="Text")
• MySQL database type: varchar(255)
  Java type: String
  Annotation: none required. Default mapping works without annotation.

Type Comparisons:

MySQL	Java
BIT	Boolean (Java wrapper class) boolean (Java primitive type)
BIT(2) to BIT(8) TINYINT	Byte (Java wrapper class) byte (Java primitive type)
BIT(9) to BIT(16) SMALLINTYEAR	Short (Java wrapper class) short (Java primitive type)
BIT(17) to BIT(32) INT	Integer (Java wrapper class) int (Java primitive type)
BIT(33) to BIT(64) BIGINT UNSIGNED	Long (Java wrapper class) long (Java primitive type)
FLOAT	Float (Java wrapper class) float (Java primitive type)
DOUBLE	Double (Java wrapper class) double (Java primitive type)
NUMERIC DECIMAL	java.math.BigDecimal
NUMERIC (precision=0) DECIMAL (precision=0)	java.math.BigInteger
CHAR VARCHAR TEXT	String
BINARY VARBINARY BLOB	byte[]
TINYTEXT	String char(255) Note: 255 (2^8−1) bytes
TEXT	String char(65535) Note: 65,535 (2^16−1) bytes = 64 Kb
MEDIUMTEXT	String char(16777215) Note: 16,777,215 (2^24−1) bytes = 16 Mb
LONGTEXT	String char(4294967295) Note: 4,294,967,295 (2^32−1) bytes = 4 Gb
DATETIME TIMESTAMP TIME DATE	Java.util.Date
DATE	Java.sql.Date
TIME	Java.sql.Time
DATETIME TIMESTAMP	Java.sql.Timestamp
ENUM	String Integer

ENUM:

Two enum types supported by JPA, EnumType.STRING vs EnumType.ORDINAL (integer stored in database). When using EnumType.ORDINAL, any new enum elements must be added to the end of the list or you will accidentally change the meaning of all your records as the order represents the enum int value.

Relationship Mapping:

These annotation are used to show table relationships derived from foreign keys. See our tutorials on the use of foreign keys with the MySQL and PostreSQL databases:

• MySQL: Transactions and foreign keys
• PostgreSQL: Transactions and foreign keys

The relationships available to the databases and JPA are:

• Many-to-one: This is defined by a table index (id) being reffered to by the foreign key in many records in another table.
  

  @Entity
  @Table(name="employee")
  public class Employee {
      @Id
      @Column(name="id_pk")
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Integer   Id;
  
      @Column(name="corp_fk")
      private Integer Corp_fk; // Foreign key to table corporation
  ...
  ...
      @ManyToOne
      @JoinColumn(name="corp_fk",nullable=false, insertable=false, updatable=false)
      private Corporation corporation;  // Added to support joined query
      public Corporation getCorporation() {
          return corporation;
      }
  ...
  ...
          

• One-to-many: one Corporation is referred to by many Employee records (Collection, List, Set or Map. Set enforces unique entries.).
  

  @Entity
  @Table(name="corporation")
  public class Corporation {
      @Id                // Tell JPA that id is the primary key
      @Column(name="id_pk")
      private Integer id;
  ...
  ...
      @OneToMany(cascade=CascadeType.PERSIST,mappedBy="corporation")
      private List<Employee> employees;
      public List<Employee> getEmployees() {return employees;}
      public void setEmployees(List<Employee> employees) {
          this.employees = employees;
      }
  ...
  ...
          

• Many-to-many: this relationship is accomplished by using a foreign key in each table
  File: Corporation.java

  @Entity
  @Table(name="corporation")
  public class Corporation {
      @Id                // Tell JPA that id is the primary key
      @Column(name="id_pk")
      private Integer id;
  ...
  ...
      @ManyToMany(mappedBy="corporations")
      private List<Employee> employees;
      public List<Employee> getEmployees() {return employees;}
      public void setEmployees(List<Employee> employees) {
          this.employees = employees;
      }
  ...
          

  
  File: Employee.java

  ...
  @Entity
  @Table(name="employee")
  public class Employee {
      @Id
      @Column(name="id_pk")
      private Integer   Id;
  
      @Column(name="corp_fk")
      private Integer Corp_fk; // Foreign key to table corporation
  ...
  ...
      @ManyToMany
      @JoinTable( name="EmployeeCorporation",
                  joinColumns="@JoinColumn(name="Id"),
                  inverseJoinColumns=@JoinColumn(name="id"))
      private List<Corporation> corporations;
      public List<Corporation> getCorporations() {
          return corporations;
      }
      public void setCorporations(List<Corporation> corporations) {
          this.corporations = corporations;
      }
  ...
          

  In this example the Corporation class "owns" the relationship and specifies the mapping. This is arbitrary as we can have the Employee class "own" the relationship as shown below. The entity owning the relationship is the class with the "mappedBy" annotation attribute.

  File: Corporation.java

  @Entity
  @Table(name="corporation")
  public class Corporation {
      @Id                // Tell JPA that id is the primary key
      @Column(name="id_pk")
      private Integer id;
  ...
  ...
      @ManyToMany
      @JoinTable(name="EmployeeCorporation",
                 joinColumns=@JoinColumn(name="corporationId"),
                 inverseJoinColumns=@JoinColumn(name="Id")) 
      private List<Employee> employees;
      public List<Employee> getEmployees() {return employees;}
      public void setEmployees(List<Employee> employees) {
          this.employees = employees;
      }
  ...
          

  
  File: Employee.java
  

  ...
  @Entity
  @Table(name="employee")
  public class Employee {
      @Id
      @Column(name="id_pk")
      private Integer   Id;
  
      @Column(name="corp_fk")
      private Integer Corp_fk; // Foreign key to table corporation
  ...
  ...
      @ManyToMany(mappedBy="employees")
      private List<Corporation> corporations;
      public List<Corporation> getCorporations() {
          return corporations;
      }
      public void setCorporations(List<Corporation> corporations) {
          this.corporations = corporations;
      }
  ...
  ...
          

  Note that if the @JoinTable mapping is omitted, a default join table is assumed and assigned a name defined by the owning class name + "_" + non-owning class name. The foreign key reference names would be named class name + "_" + table key.

• One-to-one:
  File: Employee.java

  ...
  @Entity
  public class Employee {
      @Id
      @Column(name="id_pk")
      private Integer   Id;
  
      @OneToOne
      @JoinColumn(name="contactId")
      private EmergencyContact contact;
  ...
  ...
          

  
  File: EmergencyContact.java
  

  ...
  @Entity
  public class EmergencyContact {
      @Id
      private Integer   Id;
  
  ...
  ...
      @OneToOne(mappedBy="emergencyContact")
      private Employee employee;
  ...
          

This example will perform a SQL table join using two JPA annotated Java classes.

The data types do not match by default to show the required data type mapping including an example of use of an enum.

The dabase schema representation:

SQL file: example-join.sql

Load database: mysql -h localhost -u root -psupersecretpassword < example-join.sql

The "corp_award" table in MySQL:

mysql -h localhost -u root -psupersecretpassword

mysql> select * from corp_award;
+-------+---------+---------------------+-----------+
| id_pk | corp_fk | award_date          | value     |
+-------+---------+---------------------+-----------+
|     1 |       1 | 2014-09-18 18:35:21 | OK        |
|     2 |       1 | 2014-09-18 18:35:21 | GOOD      |
|     3 |       2 | 2014-09-18 18:35:21 | OK        |
|     4 |       3 | 2014-09-18 18:35:21 | EXCELLENT |
|     5 |       4 | 2014-09-18 18:35:21 | GOOD      |
|     6 |       4 | 2014-09-18 18:35:23 | EXCELLENT |
+-------+---------+---------------------+-----------+

Note that MySQL converts the lower case SQL enumerated values to upper case!

Generate an anntotated Java class to represent the database table schema:
File: Corporation.java

[Potential Pitfall]: The primary key for the table "Corporation" is assigned and not automatically generated by the MySQL database. The MySQL attribute "AUTO_INCREMENT" is NOT used.
If the following notation is applied to the primary key "id_pk" as follows:
@GeneratedValue(strategy = GenerationType.IDENTITY)
Then you will get the following "detatched entity" error:
[java] Caused by: org.hibernate.PersistentObjectException: detached entity passed to persist: Corporation

[Potential Pitfall]: Note that the database schema defines the Corporation "description" as type "TEXT". This maps to Java as a JPA column mapping annotation columnDefinition="Text" and length = 65535. If this mapping is not given by an annotation then you would get the following error:
[java] Caused by: org.hibernate.HibernateException: Wrong column type in corpinfo.corporation for column description. Found: text, expected: varchar(255)
Mapping: http://dev.mysql.com/doc/ndbapi/en/mccj-using-clusterj-mappings.html

[Potential Pitfall]: If no mapping is assigned to the Java strings then following occurs:
[java] Caused by: org.hibernate.HibernateException: Wrong column type in corpinfo.corporation for column address. Found: char, expected: varchar(255)
This is fixed with a Java annotation or by changing the MySQL field data type.

It is quite common to develop "Data Access Objects" (DAO) to provide methods to access the data. This abstraction layer is popular when the database is access many times across the application's code.

File: CorporationDAO.java

Entity relationship Many-to-one: many employees to one corporation.

File: Employee.java

The following classes are used to manage the enumerated type in the database:

File: Award.java

File: AwardValue.java [Potential Pitfall]: Case of these enumerated values must match the case of the enumerated values in MySQL.

The EntityManager in larger applications is often handled by a static helper class. This class handles the full life cycle of the EntityManager.

File: EMgrUtil.java

The goal of this JPA example is to mimic SQL joined select:
mysql> select corporation.name, employee.name from corporation, employee where corporation.id_pk=employee.corp_fk AND corporation.name='Stone Corp';

+------------+-----------------+
| name       | name            |
+------------+-----------------+
| Stone Corp | Fred Flinstone  |
| Stone Corp | Wilma Flinstone |
+------------+-----------------+

Main program to access the database (read/write) including a join between the tables corporation and employee and work with an enumerated type.

File: TestDb.java
Note the us of the Query method getSingleResult() which returns a single Java Object.

This example uses the same JPA configuration file persistence.xml and Apache ant script build.xml

compile: ant compile

run: ant run

run:
     [java] Corp 5 name: Extra Corp Zipcode: 90267
     [java] 
     [java] List of Corporations:
     [java] Corp: 1 Mega Corp
     [java] Corp: 2 Super Corp
     [java] Corp: 3 Mini Corp
     [java] Corp: 4 Stone Corp
     [java] Corp: 5 Extra Corp
     [java] 
     [java] Query result:
     [java] Corp name: Stone Corp
     [java]     Employee: Rock Digger
     [java]     Employee: Analyst
     [java]     Last corporate rating: EXCELLENT

If the results were non-unique, a list of corporations would be handled as shown in the following code snippet:

Note the use of the Query method getResultList() which returns a Java List of Objects.

• Hibernate ORM
• Hibernate Javadocs
• JEE 7 Javadoc
• JEE 6 Javadoc

	"Core Java 2, Volume 1: Fundamentals " by Cay S. Horstmann, Gary Cornell ISBN # 0132354764, Prentice Hall PTR 8th edition The industry standard. Need I say more?
	"Core Java 2: Volume 2 Advanced Features " by Cay S. Horstmann, Gary Cornell ISBN # 0132354799, Prentice Hall PTR 8th edition The industry standard. Need I say more?
	"Core Java Server Faces" by David Geary, Cay S. Horstmann ISBN # 0131738860, Prentice Hall PTR 2nd edition
	"JSP, Servlets, and MySQL" by David Harms ISBN # 0764547879, Hungry Minds, Inc