1. Home
  2. Tutorials
  3. Java
  4. JPA Hibernate
Yolinux.com Tutorial

Java JPA: ORM database persistence framework tutorial

Java Persistence API (JPA) to access relational dabases in an object oriented fashion.

This is a tutorial with examples of JPA 2.0, the Java persistence API to access dabases in an object oriented fashion. This tutorial covers the Hibernate implementation of JPA 2.0 for Object Relational Mapping (ORM).



Free Information Technology Magazines and Document Downloads
TradePub link image

JPA Persistence and Hibernate:

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.

JPA Installation:

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.

Simple JPA one table, one Java class example:

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

SQL file: corporation.sql
create database corpinfo;
use corpinfo;

CREATE TABLE corporation (id        integer NOT NULL,
                          name      varchar(255),
                          description varchar(255),
                          address   varchar(255),
                          zipcode   varchar(16),
                          phone     varchar(32),
                          PRIMARY KEY (id))ENGINE=INNODB;


INSERT INTO corporation (id,name,description,address,zipcode,phone) 
       VALUES (1,'Mega Corp','Global industrial multinational corporation','555 Mega Way, Acropolis CA','90266','1-800-555-1211');
INSERT INTO corporation (id,name,description,address,zipcode,phone) 
       VALUES (2,'Super Corp','National industrial corporation','555 Super Way, Acropolis CA','90266','1-877-555-1212');
INSERT INTO corporation (id,name,description,address,zipcode,phone) 
       VALUES (3,'Mini Corp','State industrial corporation','555 Mini Way, Acropolis CA','90266','1-888-555-1213');
INSERT INTO corporation (id,name,description,address,zipcode,phone) 
       VALUES (4,'Stone Corp','Rock Quarry','111 Rock Way, Acropolis CA','90210','1-899-555-1214');
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
import javax.persistence.*;

@Entity
@Table(name="corporation")
public class Corporation {
    @Id                // Tell JPA that id is the primary key
    private Integer id;
    private String  name;
    private String  description;
    private String  address;
    private String  zipcode;
    private String  phone;

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

    public String getName() {
        return name;
    }
    public void setName(String _Name) {
        this.name = _Name;
    }

    public String getDescription() {
        return description;
    }
    public void setDescription(String _Description) {
        this.description = _Description;
    }

    public String getAddress() {
        return address;
    }
    public void setAddress(String _Address) {
        this.address = _Address;
    }

    public String getZipcode() {
        return zipcode;
    }
    public void setZipcode(String _Zipcode) {
        this.zipcode = _Zipcode;
    }

    public String getPhone() {
        return phone;
    }
    public void setPhone(String _Phone) {
        this.phone = _Phone;
    }
};

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

File: TestDb.java
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.spi.RootLogger;

import java.util.List;

public class TestDb {
    static{                // Static initializer
        BasicConfigurator.configure();
        RootLogger.getRootLogger().setLevel(Level.WARN);
    }

    public static void main(String[] args) throws Exception {

        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("com.corpinfo.model");
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        // Add a new record to corporation
        Corporation corporation = new Corporation();
        corporation.setId(5);
        corporation.setName("Extra Corp");
        corporation.setDescription("Extra industrial multinational corporation");
        corporation.setAddress("555 Extra Way, Acropolis CA");
        corporation.setZipcode("90267");
        corporation.setPhone("1-800-555-1213");
        
        try {
            entityManager.getTransaction().begin();
            entityManager.persist(corporation);
            entityManager.getTransaction().commit();
        } catch (RuntimeException e) {
             e.printStackTrace();
             entityManager.getTransaction().rollback();
        }

        try {
            Corporation corp3 = entityManager.find(Corporation.class, 3);
            System.out.println("Corp 3 name: " + corp3.getName() + " Zipcode: " + corp3.getZipcode());
            Corporation corp5 = entityManager.find(Corporation.class, 5);
            System.out.println("Corp 5 name: " + corp5.getName() + " Zipcode: " + corp5.getZipcode());
        } catch (RuntimeException e) {
             e.printStackTrace();
             entityManager.getTransaction().rollback();
        } finally {
             entityManager.close();
        }
    }
}

JPA configuration file: This defines the database connection parameters.

File: META-INF/persistence.xml
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             version="2.0">
    <persistence-unit name="com.corpinfo.model" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>Corporation</class>
        <properties>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/corpinfo"/>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.connection.password" value="supersecretpassword"/>
            <property name="hibernate.show_sql"     value="false"/>
            <property name="hibernate.format_sql"   value="true"/>
            <property name="hibernate.flushMode"    value="FLUSH_AUTO"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
        </properties>
    </persistence-unit>
</persistence>
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
<?xml version="1.0" encoding="utf-8" ?>
<project name="TestHibernate" default="compile" basedir=".">
    <description>Test hibernate JPA</description>
    <property name="build.dir" value="./" />
    <property name="dest.dir" value="./" />
    <property environment="env" />
    <path id="classpath">
         <pathelement location="/usr/java/latest/lib/tools.jar"/>
         <pathelement location="${build.dir}"/>
         <pathelement location="${build.dir}/META-INF/persistence.xml"/>
         <pathelement location="/opt/mysql-connector-java-5.1.32/mysql-connector-java-5.1.32-bin.jar"/>
         <pathelement location="/opt/mysql-connector-java-5.1.32/lib/slf4j-api-1.6.1.jar"/>
         <pathelement location="/opt/apache-log4j-1.2.17/log4j-1.2.17.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/jpa/hibernate-entitymanager-4.2.15.Final.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/antlr-2.7.7.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/dom4j-1.6.1.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/hibernate-commons-annotations-4.0.2.Final.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/hibernate-core-4.2.15.Final.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/hibernate-jpa-2.0-api-1.0.1.Final.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/javassist-3.18.1-GA.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/jboss-logging-3.1.0.GA.jar"/>
         <pathelement location="/opt/hibernate-release-4.2.15.Final/lib/required/jboss-transaction-api_1.1_spec-1.0.1.Final.jar"/>
    </path>
    <target name="clean" description="Remove .class files">
        <delete includeEmptyDirs="true" failonerror="false">
            <fileset dir="${dest.dir}">
                <include name="**/*.class"/>
                <include name="test-hibernate.jar"/>
            </fileset>
        </delete>
    </target>
    <target name="compile">
       <javac srcdir="./" destdir="./" debug="true" includeAntRuntime="false">
          <classpath refid="classpath"/>
          <include name="**/*.java"/>
       </javac>
    </target>
    <target name="jar" depends="compile" description="test example">
      <jar jarfile="${dest.dir}/test-hibernate.jar">
        <fileset dir="${dest.dir}" includes="**/*.class"/>
        <fileset dir="${dest.dir}" includes="META-INF/persistence.xml"/>
        <manifest>
            <attribute name="Main-Class" value="TestDb"/>
            <attribute name="Class-Path" value="classpath"/>
            <attribute name="Implementation-Title" value="Example"/>
            <attribute name="Implementation-Version" value="1.0"/>
            <attribute name="Implementation-Vendor" value="corpinfo.com"/>
        </manifest>
      </jar>
    </target>
    <target name="run" depends="jar">
        <java classname="TestDb" failonerror="true" fork="true">
            <classpath>
                <path refid="classpath"/>
                <path location="./"/>
            </classpath>
        </java>
    </target>
</project>

Compile: ant compile

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

JPQL and Named Queries:

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)

Database configurations for META-INF/persistence.xml:

Specific Database Property Options:

MySQL:

<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/dbName"/>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />

PostgreSQL:

<property name="hibernate.connection.url" value="jdbc:postgresql://localhost/dbName"/>
<property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

Derby:

<property name="hibernate.connection.url" value="jdbc:derby:memory/derbydb;create=true" />
<property name="hibernate.connection.driver_class" value="org.apache.derby.jdbc.EmbeddedDriver" />
<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect" />

JBoss HsqlDb:

<property name="hibernate.connection.url" value="jdbc:hsqldb:."/>
<property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>

Oracle:

<property name="hibernate.connection.url" value="jdbc:oracle:thin:@localhost:1521:dbName" />
<property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect" />

Generic JPA Property Options:

AttributeOptions
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:

JPA Annotations:

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:
MySQLJava
BITBoolean (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)
FLOATFloat (Java wrapper class)
float (Java primitive type)
DOUBLEDouble (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[]
TINYTEXTString
char(255)
Note: 255 (2^8−1) bytes
TEXTString
char(65535)
Note: 65,535 (2^16−1) bytes = 64 Kb
MEDIUMTEXTString
char(16777215)
Note: 16,777,215 (2^24−1) bytes = 16 Mb
LONGTEXTString
char(4294967295)
Note: 4,294,967,295 (2^32−1) bytes = 4 Gb
DATETIME
TIMESTAMP
TIME
DATE
Java.util.Date
DATEJava.sql.Date
TIMEJava.sql.Time
DATETIME
TIMESTAMP
Java.sql.Timestamp
ENUMString
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: 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;
    ...
            

Two Table Join with Various Data Types:

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:

Database schema of the three table used in this problem and the SQL database join of the tables Corporation and Employee

SQL file: example-join.sql
create database corpinfo;
use corpinfo;

CREATE TABLE corporation (id_pk     integer NOT NULL,
                          name      char(255),
                          description TEXT,
                          address   char(255),
                          zipcode   char(16),
                          phone     char(32),
                          PRIMARY KEY (ID_pk))ENGINE=INNODB;

CREATE TABLE employee  (id_pk       integer NOT NULL AUTO_INCREMENT,
                        corp_fk     integer NOT NULL,
                        name        char(255),
                        department  char(64),
                        title       char(64),
                        address     char(255),
                        zipcode     char(16),
                        phone       char(32),
                        PRIMARY KEY (id_pk))ENGINE=INNODB;
ALTER TABLE employee ADD FOREIGN KEY(corp_fk) REFERENCES corporation(id_pk)
ON DELETE CASCADE;

CREATE TABLE corp_award (id_pk   integer   NOT NULL AUTO_INCREMENT,
                         corp_fk integer   NOT NULL,
                         award_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         -- Define enums as upper case to match the enum in the Java class "AwardValue".
                         value   ENUM('POOR','OK','GOOD','EXCELLENT','GREAT','PERFECT') NOT NULL DEFAULT 'ok',
                         PRIMARY KEY (id_pk))ENGINE=INNODB;
ALTER TABLE corp_award ADD FOREIGN KEY(corp_fk) REFERENCES corporation(id_pk)
ON DELETE CASCADE;

INSERT INTO corporation (id_pk,name,description,address,zipcode,phone) 
       VALUES (1,'Mega Corp','Global industrial multinational corporation','555 Mega Way, Acropolis CA','90266','1-800-555-1211');
INSERT INTO corporation (id_pk,name,description,address,zipcode,phone) 
       VALUES (2,'Super Corp','National industrial corporation','555 Super Way, Acropolis CA','90266','1-877-555-1212');
INSERT INTO corporation (id_pk,name,description,address,zipcode,phone) 
       VALUES (3,'Mini Corp','State industrial corporation','555 Mini Way, Acropolis CA','90266','1-888-555-1213');
INSERT INTO corporation (id_pk,name,description,address,zipcode,phone) 
       VALUES (4,'Stone Corp','Rock Quarry','111 Rock Way, Acropolis CA','90210','1-899-555-1214');

INSERT INTO employee (corp_fk,department,title,name,address,zipcode,phone)
       VALUES (1,'George Castanza','Corporate Officer','CTO','1414 Cherry Lane, Burbsville CA','90266','1-800-555-1213');
INSERT INTO employee (corp_fk,department,title,name,address,zipcode,phone)
       VALUES (1,'Paul Stonehenge','Corporate Officer','CEO','1514 Peachtree Lane, Burbsville CA','90266','1-800-555-1215');
INSERT INTO employee (corp_fk,department,title,name,address,zipcode,phone)
       VALUES (1,'John Watermark','IT','System Admin','1814 Appleseed Drive, Burbsville CA','90266','1-800-555-1218');
INSERT INTO employee (corp_fk,department,title,name,address,zipcode,phone)
       VALUES (4,'Fred Flinstone','Quary Worker','Rock Digger','1814 Appleseed Drive, Burbsville CA','90266','1-800-555-1218');
INSERT INTO employee (corp_fk,department,title,name,address,zipcode,phone)
       VALUES (4,'Wilma Flinstone','Finance','Analyst','1814 Appleseed Drive, Burbsville CA','90266','1-800-555-1218');

INSERT INTO corp_award (corp_fk)       VALUES (1);
INSERT INTO corp_award (corp_fk,value) VALUES (1,'good');
INSERT INTO corp_award (corp_fk,value) VALUES (2,'ok');
INSERT INTO corp_award (corp_fk,value) VALUES (3,'excellent');
INSERT INTO corp_award (corp_fk,value) VALUES (4,'good');
SELECT SLEEP(2);
INSERT INTO corp_award (corp_fk,value) VALUES (4,'excellent');

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

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

@Entity
@Table(name="corporation")
public class Corporation {
    @Id                // Tell JPA that id is the primary key
    @Column(name="id_pk")
    private Integer id;

    @Column(name="name",columnDefinition="char(255)")
    private String Name;

    @Column(name="description",length = 65535,columnDefinition="Text")
    private String Description;
    
    @Column(name="address",columnDefinition="char(255)")
    private String Address;

    @Column(name="zipcode",columnDefinition="char(16)")
    private String Zipcode;

    @Column(name="phone",columnDefinition="char(32)")
    private String Phone;

    @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;
    }

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

    public String getName() {
        return Name;
    }
    public void setName(String _Name) {
        this.Name = _Name;
    }

    public String getDescription() {
        return Description;
    }
    public void setDescription(String _Description) {
        this.Description = _Description;
    }

    public String getAddress() {
        return Address;
    }
    public void setAddress(String _Address) {
        this.Address = _Address;
    }

    public String getZipcode() {
        return Zipcode;
    }
    public void setZipcode(String _Zipcode) {
        this.Zipcode = _Zipcode;
    }

    public String getPhone() {
        return Phone;
    }
    public void setPhone(String _Phone) {
        this.Phone = _Phone;
    }
};

[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
import java.util.List;
import javax.persistence.*;

public class CorporationDAO {

    public Integer persist(String name, 
                        String description,
                        String address, 
                        String zipcode,
                        String phone)
    {
        EntityManager em = EMgrUtil.getEntityManager();
	EntityTransaction transaction = em.getTransaction();
	Corporation corporation = new Corporation();
	try {
		transaction.begin();
		corporation.setDescription(description);
		corporation.setAddress(address);
		corporation.setZipcode(zipcode);
		corporation.setPhone(phone);
		em.persist(corporation);
		transaction.commit();
	} catch (RuntimeException e) {
		e.printStackTrace();
		transaction.rollback();
	} finally {
		em.close();
	}
	return corporation.getId();
        
    }

    public  void insert ( Corporation c ) {
         
        EntityManager em = EMgrUtil.getEntityManager();       
        em.getTransaction().begin();       
        em.persist(c);     
        em.getTransaction().commit();
    }
     
    public  void update ( Corporation c ) {
         
        EntityManager em = EMgrUtil.getEntityManager();       
        em.getTransaction().begin();       
        em.merge(c);       
        em.getTransaction().commit();
    }
     
    public  void deleteById ( Integer id ) {
         
        Corporation c = findById(id);
         
        EntityManager em = EMgrUtil.getEntityManager();       
        em.getTransaction().begin();       
        em.remove(c);      
        em.getTransaction().commit();
    }
 
    public  List<Corporation> findAll() {
         
        EntityManager em = EMgrUtil.getEntityManager();       
        List<Corporation>  list = em.createQuery(" from Corporation" , Corporation.class).getResultList();       
        return list;
    }
     
    public  Corporation findById(Integer id) {
         
        EntityManager em = EMgrUtil.getEntityManager();
        Corporation corporation = em.find(Corporation.class, id);      
        return corporation;
    }

}

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

File: Employee.java
import javax.persistence.*;

@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

    @Column(name="name",columnDefinition="char(255)")
    private String Name;
    @Column(name="department",columnDefinition="char(64)")
    private String Department;
    @Column(name="title",columnDefinition="char(64)")
    private String Title;
    @Column(name="address",columnDefinition="char(255)")
    private String Address;
    @Column(name="zipcode",columnDefinition="char(16)")
    private String Zipcode;
    @Column(name="phone",columnDefinition="char(32)")
    private String Phone;

    // If the @JoinColumn annotation were omitted, a default foreign key is assumed. 
    // The default foreign key name is the name of the attribute in the source entity 
    // (program in our case), followed by an underscore, followed by the primary key 
    // name in the target entity.
    @ManyToOne
    @JoinColumn(name="corp_fk",nullable=false, insertable=false, updatable=false)
    private Corporation corporation;  // Added to support joined query
    public Corporation getCorporation() {
        return corporation;
    }
    public void setCorporation(Corporation corporation) {
        this.corporation = corporation;
    }

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

    public Integer getCorp_fk() {
        return Corp_fk;
    }
    public void setCorp_fk(Integer _Corp_fk) {
        this.Corp_fk = _Corp_fk;
    }

    public String getName() {
        return Name;
    }
    public void setName(String _Name) {
        this.Name = _Name;
    }

    public String getDepartment() {
        return Department;
    }
    public void setDepartment(String _Department) {
        this.Department = _Department;
    }

    public String getTitle() {
        return Title;
    }
    public void setTitle(String _Title) {
        this.Title = _Title;
    }

    public String getAddress() {
        return Address;
    }
    public void setAddress(String _Address) {
        this.Address = _Address;
    }

    public String getZipcode() {
        return Zipcode;
    }
    public void setZipcode(String _Zipcode) {
        this.Zipcode = _Zipcode;
    }

    public String getPhone() {
        return Phone;
    }
    public void setPhone(String _Phone) {
        this.Phone = _Phone;
    }
};

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

File: Award.java
import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name="corp_award")
public class Award {
    @Id
    @Column(name="id_pk")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer   Id;
    @Column(name="corp_fk")
    private Integer   Corp_fk;
    @Column(name="award_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date Award_Date;
    // @Column(name="value")
    // [java] Caused by: org.hibernate.HibernateException: Wrong column type in corpinfo.corp_award for column value. Found: enum, expected: varchar(255)
    // The "columnDefinition" fixes this error. Else String expected.
    @Column(name="value",columnDefinition = "ENUM('POOR','OK','GOOD','EXCELLENT','GREAT','PERFECT')")
    @Enumerated(EnumType.STRING)  // Other option is EnumType.ORDINAL which uses a numerical value.
    private AwardValue Value;

    @ManyToOne
    @JoinColumn(name="corp_fk",nullable=false, insertable=false, updatable=false)
    private Corporation corporation;  // Added to support joined query
    public Corporation getCorporation() {
        return corporation;
    }

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

    public Integer getCorp_fk() {
        return Corp_fk;
    }
    public void setCorp_fk(Integer _Corp_fk) {
        this.Corp_fk = _Corp_fk;
    }

    public Date getAward_Date() {
        return Award_Date;
    }
    public void setAward_Date(Date _Award_Date) {
        this.Award_Date = _Award_Date;
    }

    public AwardValue getValue() {
        return Value;
    }
    public void setValue(AwardValue _Value) {
        this.Value = _Value;
    }
};

File: AwardValue.java
public enum AwardValue {
   POOR,       // 0
   OK,         // 1
   GOOD,       // 2
   EXCELLENT,  // 3
   GREAT,      // 4
   PERFECT     // 5
}
[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
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
 
public class EMgrUtil {
 
    private static EntityManagerFactory entityManagerFactory;
    private static EntityManager        entityManager;
 
    public static EntityManager createEntityManager() {
        if (entityManager == null) {           
          try {
              // com.corpinfo.model is defined in persistence.xml
              entityManagerFactory = Persistence.createEntityManagerFactory("com.corpinfo.model");
              entityManager = entityManagerFactory.createEntityManager();
          } catch(ExceptionInInitializerError e) {
              throw e;
          }
        }
         
        return entityManager;
    }

    public static EntityManagerFactory getEntityManagerFactory() {
        return entityManagerFactory;
    }

    public static EntityManager getEntityManager() {
        return entityManager;
    }

    public static void close() {
        entityManager.close();
        entityManagerFactory.close();
    }
}

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
import javax.persistence.*;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.spi.RootLogger;

import java.util.List;

public class TestDb {
    static{                // Static initializer
        BasicConfigurator.configure();
        RootLogger.getRootLogger().setLevel(Level.WARN);
    }

    public static void main(String[] args) throws Exception {

        EMgrUtil.createEntityManager(); // Call to static method
        CorporationDAO corpDAO = new CorporationDAO();

        // Add a new record to corporation
        Corporation corporation = new Corporation();
        corporation.setId(5);
        corporation.setName("Extra Corp");
        corporation.setDescription("Extra industrial multinational corporation");
        corporation.setAddress("555 Extra Way, Acropolis CA");
        corporation.setZipcode("90267");
        corporation.setPhone("1-800-555-1213");
        corpDAO.insert(corporation);

        Corporation corp5 = corpDAO.findById(5);
        System.out.println("Corp 5 name: " + corp5.getName() + " Zipcode: " + corp5.getZipcode());

        System.out.println("\nList of Corporations:");
        List<Corporation> corpAll = corpDAO.findAll();
        for(int i = 0; i < corpAll.size(); i++)
        {
            System.out.println("Corp: " + corpAll.get(i).getId() + " " + corpAll.get(i).getName());
        }

        corpDAO.deleteById(5);

        System.out.println("\nQuery result:");
        String sql = "SELECT c FROM Corporation c WHERE name=?1";
        EntityManager entityManager = EMgrUtil.getEntityManager();
        Query query = entityManager.createQuery(sql);
        query.setParameter(1, "Stone Corp");
        Corporation corp = (Corporation)query.getSingleResult();
        System.out.println("Corp name: " + corp.getName());

        List<Employee> empAll = corp.getEmployees();
        for (Employee employee : empAll) {
           System.out.println("    Employee: " + employee.getName());
        }

        String sql2 = "SELECT a FROM Award a WHERE corp_fk=?1 ORDER BY Award_Date DESC";
        Query query2 = entityManager.createQuery(sql2);
        query2.setParameter(1, corp.getId());
        List<Award> awards = query2.getResultList();
        System.out.println("    Last corporate rating: " + awards.get(0).getValue());

        EMgrUtil.close();
    }

}

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:

...
...

        System.out.println("\nQuery result:");
        String sql = "SELECT c FROM Corporation c WHERE name=?1";
        EntityManager entityManager = EMgrUtil.getEntityManager();
        Query query = entityManager.createQuery(sql);
        query.setParameter(1, "Stone Corp");
        corpAll = query.getResultList();
        for (int i = 0; i < corpAll.size(); i++) {
            System.out.println("Corp name: " + corpAll.get(i).getName());
            List<Employee> empAll = corpAll.get(i).getEmployees();
            for (Employee employee : empAll) {
                System.out.println("    Employee: " + employee.getName());
            }

            String sql2 = "SELECT a FROM Award a WHERE corp_fk=?1 ORDER BY Award_Date DESC";
            Query query2 = entityManager.createQuery(sql2);
            query2.setParameter(1, corpAll.get(i).getId());
            List<Award> awards = query2.getResultList();
            System.out.println("    Last corporate rating: " + awards.get(0).getValue());
        }

...
...

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

Links:

Books:

"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?

Amazon.com
"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?

Amazon.com
"Core Java Server Faces"
by David Geary, Cay S. Horstmann
ISBN # 0131738860, Prentice Hall PTR 2nd edition

Amazon.com
"JSP, Servlets, and MySQL"
by David Harms
ISBN # 0764547879, Hungry Minds, Inc

Amazon.com

   

    Bookmark and Share

Advertisements