Some examples of the criteria API (JPA 2.0)

When I wrote my examples on how to use JavaFX and JPA 2.0 (part II and III), I made some other tries using Criteria API.

Of course, these examples use the table and the entity described in "Examples using JPA 2.0"

Example using like and a JPA parameter

In JPQL

String param = "Arc%";

        Query query = em.createQuery(
             "select m from Music m where m.artisteName like :param");
        query.setParameter("param", param);
        List<Music> musics = query.getResultList();

        for (Music music : musics){
            System.out.println(music);
        }

Equivalent with Criteria API

String param = "Arc%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Music> query = cb.createQuery(Music.class);

        Root<Music> music3 = query.from(Music.class);
        query.where(cb.like(music3.<String>get("artisteName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<Music> tq = em.createQuery(query);
        tq.setParameter("param", param);
        List<Music> musics = tq.getResultList();

        for (Music music : musics){
            System.out.println(music);
        }

Example of criteria API using count

In JPQL

String param = "Archive";

        Query query = em.createQuery(
             "select count(m) from Music m where m.artisteName = :artisteNameParam");
        query.setParameter("artisteNameParam", param);
        Long count1 = (Long)query.getSingleResult();

        System.out.println("count : "+count1);

Equivalent with Criteria API

String param = "Archive";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> query = cb.createQuery(Long.class);

        Root<Music> music = query.from(Music.class);
        query.select(cb.count(music));
        query.where(cb.equal(music.<String>get("artisteName"),
                             cb.parameter(String.class,"artisteNameParam")));

        TypedQuery<Long> tq = em.createQuery(query);
        tq.setParameter("artisteNameParam", param);

        Long count = tq.getSingleResult();

        System.out.println("count : "+count);

Example of criteria using distinct

In JPQL

String param = "Arc%";

        Query query = em.createQuery(
             "select distinct m.artisteName from Music m where m.artisteName like :param");
        query.setParameter("param", param);
        List<String> artistes= query.getResultList();

        for (String artiste : artistes){
            System.out.println(artiste);
        }

Equivalent with Criteria API

String param = "Arc%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<String> query = cb.createQuery(String.class);

        Root<Music> music = query.from(Music.class);
        query.select(music.<String>get("artisteName"));
        query.distinct(true);

        query.where(cb.like(music.<String>get("artisteName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<String> tq = em.createQuery(query);
        tq.setParameter("param", param);

        List<String> artistes = tq.getResultList();


        for (String artiste : artistes){
            System.out.println(artiste);
        }

Examples using JPA 2.0

This is the first part of 3 that shows you how to get data from database with JPA 2.0 and display them with JavaFX.

In this first part we are going:

• to create the database and one table
• to create the entity and the persistence.xml file
• to use a namedQuery
• to use criteria query API

Create the database and the table

• Create a derby database with :
• Database Name : javafxDB
• UserName : APP
• Password : paddy

• Create the music table and fill it.

Execute this script to create the music table and fill it

The music table has 3 fields: an auto generated id, the name of the artist (artist_name) and the name of the album (album_title) …

And of course, it’s just for an example ;)

The Entity and the persistence unit

• The Entity which is mapped to music table

Music.java

package paddy.domain;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedQuery;

/**
 *
 * @author Patrick
 */
@Entity
@NamedQuery(name = "findAllAlbum", query= "select m from Music m")

public class Music implements Serializable {

    @Id
    @GeneratedValue
    private Long id;
    @Column(name = "artist_name")
    private String artisteName;
    @Column(name = "album_title")
    private String albumTitle;

    public Music() {
    }
    
    ...
    Getter and setter
    ...
    
    @Override
    public String toString(){

        StringBuffer sb = new StringBuffer();
        sb.append("id : ");sb.append(id);sb.append(" ; ");
        sb.append("artisteName : ");sb.append(artisteName);sb.append(" ; ");
        sb.append("albumTitle : ");sb.append(albumTitle);
        sb.append(" \n");

        return sb.toString();
    }

}

Full source here

The entity is just a plain old Java object (pojo) with some annotations.

@Entity:  designate my pojo as an entity so I can use it with JPA services.

@Id: designate the property as the entity's primary key

@GeneratedValue: used with @Id, it defines that this value is generated automatically

@Column: is used, in my example, to mapped the property of the entity with the field of the table

@NamedQuery(name = "findAllAlbum", query= "select m from Music m")  is used  to create pre-defined queries which get all record from music table.

• The persistence.xml file

META-INF\persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence">
  <persistence-unit name="MusicAndLight" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>paddy.domain.Music</class>
    <properties>
      <property name="eclipselink.target-database" value="DERBY"/>
      <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.ClientDriver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:derby://localhost:1527/javafxDB"/>
      <property name="javax.persistence.jdbc.user" value="APP"/>
      <property name="javax.persistence.jdbc.password" value="paddy"/>
    </properties>
  </persistence-unit>
</persistence>

The persistence.xml file defined:

• the persistence unit named MusicAndLight
• the entity (paddy.domain.Music) managed by the persistence unit
• and how the persistence unit connects to the database

Execute query by using a namedQuery

namedQuery

EntityManagerFactory emf = Persistence.createEntityManagerFactory("MusicAndLight");
        EntityManager em = emf.createEntityManager();

        //get and create the namedQuery findAllBum
        Query  query = em.createNamedQuery("findAllAlbum");
        //execute the query
        List<Music> musics = query.getResultList();

        for (Music music : musics){
            System.out.println(music);
        }

        em.close();
        emf.close();
    

Execute query by using a criteria API

Criteria Api

EntityManagerFactory emf = Persistence.createEntityManagerFactory("MusicAndLight");
        EntityManager em = emf.createEntityManager();

        String param = "Arc%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Music> query2 = cb.createQuery(Music.class);
        //select * from Music
        Root<Music> music2 = query2.from(Music.class);
        //where artisteName like param
        //in my example where artisteName like Arc% (begining by Arc)
        query2.where(cb.like(music2.<String>get("artisteName"), param));
        //execute the query
        List<Music> musics2 = em.createQuery(query2).getResultList();

        for (Music music : musics2){
            System.out.println(music);
        }

        em.close();
        emf.close();
    

Get the NetBeans project

Note: don’t forget to create (in NetBeans) a library containing the jar DerbyClient.jar and being called DerbyClient.