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:
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!
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
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).
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!
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
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!,
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.
Enregistrer un commentaire