lundi 22 février 2010

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

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

7 commentaires:

Mandor a dit…

Hello,

I am trying to construct queries dynamically, and my next target is add JOIN clauses (the most dinamically as possible).

By now, for example, this code work for me :
...
Class baseClass;
...
CriteriaBuilder cb = JpaHandle.get().getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(this.baseClass);
Root entity_ = cq.from(this.baseClass);
Predicate restrictions = null;
restrictions = cb.conjunction();
restrictions = cb.and(restrictions, entity_.get("id").in(this.listId));
cq.where(restrictions);
...
Query qry = JpaHandle.get().createQuery(cq);

NOTE : JpaHandle.get() is the provider of wicket-RAD of JPA -an implementation that I think that uses Hibernate (I'm a newbe!)-, and I could not recive Criteria but the API let play me with CriteriaBuilder.

My problem is that doing join's doesn't work for me (at compile time :P).

Samples of codes that contains the main idea but doesn't work :

Is there a way to something like this in standard JPA ? (Note : this don't compile)
Join experimentAssays = entity_.join( entity_.get("assay_id") );

Or like that :
CriteriaQuery q = cb.createQuery(Customer.class);
Root c = q.from(Customer.class);
SetJoin o = c.join(Customer_.orders);


Of curse, I have the particular anotations in the classes (this.baseClass)
For example :
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "assay_id", nullable = false)


Some ideas? All comments are welcome!!

Thanks for your time!

Patrick Champion a dit…

Hi!

You can try something like this

CriteriaQuery q = cb.createQuery(Customer.class);
Root c = q.from(Customer.class);
Join< Customer,Order > co = c.join("orders");
...

where orders is the attribue name of the Customer entity which has the join(@JoinColumn) with the Order entity

@Entity
public class Customer ...

...
@OneToMany
@JoinColumn(name="id_order")
private List< Order > orders ...

Patrick

Mandor a dit…

Hello Patrick,

Thanks a lot for your help (and for the quick response!! -sorry for the delay, I was on holidays! ("little" ones :) )-)!

I try your idea and works perfect. At last the code remains (more or less) like this :

CriteriaBuilder cb = JpaHandle.get().getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(A.class);
Root entity_ = cq.from(A.class);
...
Join joinAB = entity_.join("b");
...
restrictionsJoinedEntity = cb.and(restrictionsJoinedEntity, joinAB.get("id").in(listJoinIds));
...
cq.select(entity_); // I see that is it required if we do a join (if I would Experiments assaysExperiment)
cq.where(restrictionsJoinedEntity);

You are a crack! (thank you a lot again, It is difficult to catch properly information about this JPA -or at least I don't get the proper page).

Mandor a dit…

Hello another time, Patrick,

As I said, your solution works perfectly, f.ex :

---
CriteriaQuery q = cb.createQuery(Customer.class);
Root c = q.from(Customer.class);
Join< Customer,Order > co = c.join("orders");
...
Predicate restrictionsJoinedEntity = cb.conjuntion();
restrictionsJoinedEntity = cb.and(restrictionsJoinedEntity, co.get("id").in(listJoinIds)); // listJoinIds is an ArrayList of Integers
...
q.select(c);
q.where(restrictionsJoinedEntity);
...


where orders is the attribue name of the Customer entity which has the join(@JoinColumn) with the Order entity

@Entity
public class Customer ...

...
@OneToMany
@JoinColumn(name="id_order")
private List< Order > orders ...

...
---

In order to avoid the concret classes, I use this alternative ones (and also this works!):

---
...
CriteriaQuery q = cb.createQuery(Customer.class);
Root c = q.from(Customer.class);
...
Predicate restrictionsJoinedEntity = cb.conjuntion();
restrictionsJoinedEntity = cb.and(restrictionsJoinedEntity, c.join("orders").get("id").in(listJoinIds));
...

---

So, If I use c.join("orders") it works perfectly. But I have to know that is the property "orders"!.

You know another way to do this but working directly with classes? (to be more generical).

To do something like this : c.join(Orders.class)

(So, we know implicity that there is one relation between Orders and Customers... maybe there is a direct way!)
...


Thanks for your time!

Patrick Champion a dit…

hi !

I was on vacation too ;)
In my knowledge there is no way to do that...
In the javadoc for 'join' you always must to pass the attribute in one way or another
http://download.oracle.com/javaee/6/api/javax/persistence/criteria/From.html#join%28javax.persistence.metamodel.CollectionAttribute%29

if some one has an idea ?

Patrick

Mandor a dit…

Hi Patrick, thanks for your reply.

Yes, I didn't found another system. It is a pity you have to invoke a particular property even though there are already all meta-information which relates different classes...

At last I made a Helper Class that exploring annotations found the precise property to join from one class to another, and gives more flexibility.

Regards!,

Brandon a dit…

Thanks for posting this. Your example using the Criteria API help me out. It was much cleaner and more straight-forward than others I have seen.