Java Tutorial/JPA/EJB Query Language

Материал из Java эксперт
Перейти к: навигация, поиск

Содержание

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>