Java Tutorial/JPA/EJB Query Language
Содержание
- 1 Aggregate function: AVG
- 2 EJB QL Between With Date Type Value
- 3 EJB QL Concat Function Substring
- 4 EJB QL Count distinct
- 5 EJB QL Count Entity
- 6 EJB QL Create new Object In Select Statement
- 7 EJB QL Exist And Subquery
- 8 EJB QL Get Distinct Entity
- 9 EJB QL Get Entity From Entity
- 10 EJB QL Get Field From Entity
- 11 EJB QL Get Single result
- 12 EJB QL Get Two Properties From Entity
- 13 EJB QL Having Clause
- 14 EJB QL In Function
- 15 EJB QL In Function With One To Many
- 16 EJB QL In With Subquery
- 17 EJB QL is Field empty
- 18 EJB QL Join Entity And Its Field
- 19 EJB QL Join Many To Many
- 20 EJB QL Join One to Many Left Join Fetch
- 21 EJB QL Join One To Many Map
- 22 EJB QL Join One To One
- 23 EJB QL Join One To One Return Two Entities
- 24 EJB QL Join Three Entities
- 25 EJB QL Join Two Entities
- 26 EJB QL Left Join One To Many Map
- 27 EJB QL Max With Date Type
- 28 EJB QL Order By Two Fields
- 29 EJB QL Pass Entity As Parameter
- 30 EJB Query Language: Not Exist
- 31 EJB SQL Count Function
- 32 Like statement and Wild Card
- 33 Like statement: Wild Card Escape
- 34 Not In function
- 35 Order Descending
- 36 Query an Entity as Query a Table
- 37 Query Field Of Field
- 38 Query Flush Mode
- 39 Query One To Many Collection
- 40 Query with Parameters
- 41 Range Check With Comparison Operator
- 42 Return List Of Entities From Query
- 43 Size function
- 44 Three Level Field Reference
- 45 Two Aggregate Functions
- 46 Use Between To check Salary Range
- 47 Use Object Function
- 48 Using SubQuery in EJB Query Langauge
- 49 Where Clause With SubQuery
Aggregate function: AVG
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT AVG(e.salary) FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Between With Date Type Value
File: Main.java
<source lang="java">
import java.util.Date; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.TemporalType; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Date start = new Date(); Date end = new Date(); em.createQuery("SELECT e " + "FROM Student e " + "WHERE e.startDate BETWEEN :start AND :end") .setParameter("start", start, TemporalType.DATE) .setParameter("end", end, TemporalType.DATE).getResultList(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Concat Function Substring
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("UPDATE Phone p SET p.number = CONCAT("288", SUBSTRING(p.number, LOCATE(p.number, "-"), 4))"); query.executeUpdate(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Count distinct
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT COUNT(DISTINCT p.type) FROM Student e JOIN e.phones p GROUP BY e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Count Entity
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d FROM Department d WHERE (SELECT COUNT(e) FROM d.students e) = 2"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Create new Object In Select Statement
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); String projectName = "jdk"; em .createQuery( "SELECT NEW EmpMenu(e.name, e.department.name) " + "FROM Project p JOIN p.students e " + "WHERE p.name = :project " + "ORDER BY e.name").setParameter("project", projectName).getResultList(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
} class EmpMenu {
private String employeeName; private String departmentName; public EmpMenu(String employeeName, String departmentName) { this.employeeName = employeeName; this.departmentName = departmentName; } public String getEmployeeName() { return employeeName; } public String getDepartmentName() { return departmentName; }
}</source>
EJB QL Exist And Subquery
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE EXISTS (SELECT p FROM Phone p WHERE p.student = e)"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Get Distinct Entity
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT DISTINCT e.department FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Get Entity From Entity
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e.department FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Get Field From Entity
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e.name FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Get Single result
File: Student.java
<source lang="java">
import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.NamedQuery; import javax.persistence.QueryHint; @Entity @NamedQuery(name="findStudentNoCache",
query="SELECT e FROM Student e WHERE e.id = ?1" )
public class Student {
@Id private int id; private String name; private long salary; public int getId() { return id; } public String getName() { return name; } public long getSalary() { return salary; } public String toString() { return "Student " + getId() + ": name: " + getName() + ", salary: " + getSalary(); }
}</source>
EJB QL Get Two Properties From Entity
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e.name, e.salary FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Having Clause
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT COUNT(p) FROM Student e JOIN e.projects p GROUP BY e HAVING COUNT(p) >= 2"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL In Function
File: Main.java
<source lang="java">
import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); em.createQuery("DELETE FROM Department d WHERE d.name IN ("CA13", "CA19", "NY30")") .executeUpdate(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL In Function With One To Many
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT DISTINCT p FROM Student e, IN(e.phones) p"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL In With Subquery
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE e.department IN (SELECT DISTINCT d FROM Department d JOIN d.students de JOIN de.projects p WHERE p.name LIKE "CHEM%")"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL is Field empty
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query unassignedQuery = em.createQuery("SELECT e FROM Student e " + "WHERE e.projects IS EMPTY"); unassignedQuery.getResultList(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join Entity And Its Field
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT p.number FROM Student e JOIN e.phones p"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join Many To Many
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); String projectName = "jdk"; em.createQuery( "SELECT e FROM Project p JOIN p.students e WHERE p.name = :project " + "ORDER BY e.name").setParameter("project", projectName).getResultList(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join One to Many Left Join Fetch
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d FROM Department d LEFT JOIN FETCH d.students"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join One To Many Map
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d FROM Student e JOIN e.department d"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join One To One
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e JOIN FETCH e.address"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join One To One Return Two Entities
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e, a FROM Student e JOIN e.address a"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join Three Entities
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT DISTINCT p FROM Department d JOIN d.students e JOIN e.projects p"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Join Two Entities
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT DISTINCT d FROM Department d, Student e WHERE d = e.department"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Left Join One To Many Map
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d, e FROM Department d LEFT JOIN d.students e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Max With Date Type
File: Message.java
<source lang="java">
import java.util.Date; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.ManyToOne; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity public class Message {
@Id private int id; private String message; @Temporal(TemporalType.DATE) private Date postingDate; @ManyToOne Conversation conversation; public int getId() { return id; } public void setId(int empNo) { this.id = empNo; } public String getMessage() { return message; } public void setMessage(String name) { this.message = name; } public Date getPostingDate() { return postingDate; } public void setPostingDate(Date postingDate) { this.postingDate = postingDate; } public Conversation getConversation() { return conversation; } public void setConversation(Conversation conversation) { this.conversation = conversation; } public String toString() { return "Message " + getId() + ": " + getMessage() + " posted: " + getPostingDate(); }
}</source>
EJB QL Order By Two Fields
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e JOIN e.department d ORDER BY d.name, e.name DESC"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB QL Pass Entity As Parameter
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Department dept = new Department(); Student stud = (Student) em.createNamedQuery("findHighestPaidByDepartment").setParameter( "dept", dept).getSingleResult(); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB Query Language: Not Exist
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE NOT EXISTS (SELECT p FROM e.phones p WHERE p.type = "Cell")"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
EJB SQL Count Function
File: Project.java
<source lang="java">
import java.util.ArrayList; import java.util.Collection; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.ManyToMany; @Entity public class Project {
@Id protected int id; protected String name; @ManyToMany(mappedBy="projects") private Collection<Student> students; public Project() { students = new ArrayList<Student>(); } public int getId() { return id; } public String getName() { return name; } public Collection<Student> getStudents() { return students; } public String toString() { return "Project id: " + getId() + ", name: " + getName(); }
}</source>
Like statement and Wild Card
File: Department.java
<source lang="java">
import java.util.HashSet; import java.util.Set; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.OneToMany; @Entity public class Department {
@Id private int id; private String name; @OneToMany(mappedBy="department") private Set<Student> students = new HashSet<Student>(); public int getId() { return id; } public void setId(int deptNo) { this.id = deptNo; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public Set<Student> getStudents() { return students; } public String toString() { return "Department no: " + getId() + ", name: " + getName(); }
}</source>
Like statement: Wild Card Escape
File: Address.java
<source lang="java">
import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {
@Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); }
}</source>
Not In function
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT p FROM Phone p WHERE p.type NOT IN ("Cell", "Home")"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Order Descending
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e ORDER BY e.name DESC"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Query an Entity as Query a Table
File: Student.java
<source lang="java">
import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Student {
@Id private long id = 0; private String name; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String toString() { return "\n\nID:" + id + "\nName:" + name + "\n\n"; }
}</source>
Query Field Of Field
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.NoResultException; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); String query = "SELECT e.salary " + "FROM Student e " + "WHERE e.department.name = "deptName" AND " + " e.name = "empName""; try { Long l = (Long) em.createQuery(query).getSingleResult(); } catch (NoResultException e) { } em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Query Flush Mode
File: Conversation.java
<source lang="java">
import java.util.ArrayList; import java.util.Collection; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.NamedQueries; import javax.persistence.NamedQuery; import javax.persistence.OneToMany; @Entity @NamedQueries({
@NamedQuery(name="findActiveConversations", query="SELECT c " + "FROM Conversation c " + "WHERE c.status = "ACTIVE""), @NamedQuery(name="findLastMessageDate", query="SELECT MAX(m.postingDate) " + "FROM Conversation c JOIN c.messages m " + "WHERE c = :conversation")
}) public class Conversation {
public final static String ACTIVE = "ACTIVE"; public final static String INACTIVE = "INACTIVE"; @Id private int id; private String status; @OneToMany(mappedBy="conversation") private Collection<Message> messages = new ArrayList<Message>(); public int getId() { return id; } public void setId(int empNo) { this.id = empNo; }
public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Collection<Message> getMessages() { return messages; } public void addMessage(Message m) { if (!getMessages().contains(m)) { getMessages().add(m); if (m.getConversation() != null) { m.getConversation().getMessages().remove(m); } m.setConversation(this); } } public String toString() { return "Conversation " + getId() + ": status: " + getStatus(); }
}</source>
Query One To Many Collection
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d.students FROM Department d"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Query with Parameters
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.NoResultException; import javax.persistence.Persistence; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); String deptName = "chem"; String empName = "Joe"; try { Long l = (Long) em.createQuery( "SELECT e.salary FROM Student e WHERE e.department.name = :deptName AND " + " e.name = :empName ").setParameter("deptName", deptName).setParameter( "empName", empName).getSingleResult(); } catch (NoResultException e) { } em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Range Check With Comparison Operator
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE e.salary >= 40000 AND e.salary <= 45000"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Return List Of Entities From Query
File: Student.java
<source lang="java">
import java.util.Date; import javax.persistence.Basic; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Student {
@Id private long id = System.currentTimeMillis(); @Basic private String name; @Basic private Date dateOfBirth = new Date(); public Date getDateOfBirth() { return dateOfBirth; } public void setDateOfBirth(Date dateOfBirth) { this.dateOfBirth = dateOfBirth; } public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; }
}</source>
Size function
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT d FROM Department d WHERE SIZE(d.students) = 2"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Three Level Field Reference
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE e.address.state IN ("NY", "CA")"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Two Aggregate Functions
File: Main.java
<source lang="java">
import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT COUNT(e), AVG(e.salary) FROM Student e"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Use Between To check Salary Range
File: Department.java
<source lang="java">
import java.util.HashSet; import java.util.Set; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.OneToMany; @Entity public class Department {
@Id private int id; private String name; @OneToMany(mappedBy="department") private Set<Student> students = new HashSet<Student>(); public int getId() { return id; } public void setId(int deptNo) { this.id = deptNo; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public Set<Student> getStudents() { return students; } public String toString() { return "Department no: " + getId() + ", name: " + getName(); }
}</source>
Use Object Function
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT OBJECT(d) FROM Department d"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Using SubQuery in EJB Query Langauge
File: Main.java
<source lang="java">
import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.FlushModeType; import javax.persistence.Persistence; import javax.persistence.Query; public class Main {
static EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAService"); static EntityManager em = emf.createEntityManager(); public static void main(String[] a) throws Exception { em.getTransaction().begin(); Query query = em.createQuery("SELECT e FROM Student e WHERE e.salary = (SELECT MAX(e2.salary) FROM Student e2)"); System.out.println(query.getResultList()); em.getTransaction().rumit(); em.close(); emf.close(); Helper.checkData(); }
}</source>
Where Clause With SubQuery
File: Department.java
<source lang="java">
import java.util.HashSet; import java.util.Set; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.OneToMany; @Entity public class Department {
@Id private int id; private String name; @OneToMany(mappedBy="department") private Set<Student> students = new HashSet<Student>(); public int getId() { return id; } public void setId(int deptNo) { this.id = deptNo; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public Set<Student> getStudents() { return students; } public String toString() { return "Department no: " + getId() + ", name: " + getName(); }
}</source>