Java Tutorial/JPA/EJB Query Language

Aggregate function: AVG

File: Main.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();
  }
}

EJB QL Between With Date Type Value

File: Main.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();
  }
}

EJB QL Concat Function Substring

File: Main.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();
  }
}

EJB QL Count distinct

File: Main.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();
  }
}

EJB QL Count Entity

File: Main.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();
  }
}

EJB QL Create new Object In Select Statement

File: Main.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;
  }
}

EJB QL Exist And Subquery

File: Main.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();
  }
}

EJB QL Get Distinct Entity

File: Main.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();
  }
}

EJB QL Get Entity From Entity

File: Main.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();
  }
}

EJB QL Get Field From Entity

File: Main.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();
  }
}

EJB QL Get Single result

File: Student.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();
    }
}

EJB QL Get Two Properties From Entity

File: Main.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();
  }
}

EJB QL Having Clause

File: Main.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();
  }
}

EJB QL In Function

File: Main.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();
  }
}

EJB QL In Function With One To Many

File: Main.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();
  }
}

EJB QL In With Subquery

File: Main.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();
  }
}

EJB QL is Field empty

File: Main.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();
  }
}

EJB QL Join Entity And Its Field

File: Main.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();
  }
}

EJB QL Join Many To Many

File: Main.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();
  }
}

EJB QL Join One to Many Left Join Fetch

File: Main.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();
  }
}

EJB QL Join One To Many Map

File: Main.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();
  }
}

EJB QL Join One To One

File: Main.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();
  }
}

EJB QL Join One To One Return Two Entities

File: Main.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();
  }
}

EJB QL Join Three Entities

File: Main.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();
  }
}

EJB QL Join Two Entities

File: Main.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();
  }
}

EJB QL Left Join One To Many Map

File: Main.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();
  }
}

EJB QL Max With Date Type

File: Message.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();
    }
}

EJB QL Order By Two Fields

File: Main.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();
  }
}

EJB QL Pass Entity As Parameter

File: Main.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();
  }
}

EJB Query Language: Not Exist

File: Main.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();
  }
}

EJB SQL Count Function

File: Project.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();
    }
}

Like statement and Wild Card

File: Department.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();
    }
}

Like statement: Wild Card Escape

File: Address.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();
    }
}

Not In function

File: Main.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();
  }
}

Order Descending

File: Main.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();
  }
}

Query an Entity as Query a Table

File: Student.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";
  }
}

Query Field Of Field

File: Main.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();
  }
}

Query Flush Mode

File: Conversation.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();
    }
}

Query One To Many Collection

File: Main.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();
  }
}

Query with Parameters

File: Main.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();
  }
}

Range Check With Comparison Operator

File: Main.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();
  }
}

Return List Of Entities From Query

File: Student.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;
  }
  
  
}

Size function

File: Main.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();
  }
}

Three Level Field Reference

File: Main.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();
  }
}

Two Aggregate Functions

File: Main.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();
  }
}

Use Between To check Salary Range

File: Department.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();
    }
}

Use Object Function

File: Main.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();
  }
}

Using SubQuery in EJB Query Langauge

File: Main.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();
  }
}

Where Clause With SubQuery

File: Department.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();
    }
}