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