Java Tutorial/JPA/EJB Query Language — различия между версиями

Текущая версия на 05:18, 1 июня 2010

Содержание

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

Версия 17:44, 31 мая 2010 (просмотреть исходный код) (обсуждение)	Текущая версия на 05:18, 1 июня 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)

Java Tutorial/JPA/EJB Query Language — различия между версиями

Текущая версия на 05:18, 1 июня 2010

Содержание

Aggregate function: AVG

EJB QL Between With Date Type Value

EJB QL Concat Function Substring

EJB QL Count distinct

EJB QL Count Entity

EJB QL Create new Object In Select Statement

EJB QL Exist And Subquery

EJB QL Get Distinct Entity

EJB QL Get Entity From Entity

EJB QL Get Field From Entity

EJB QL Get Single result

EJB QL Get Two Properties From Entity

EJB QL Having Clause

EJB QL In Function

EJB QL In Function With One To Many

EJB QL In With Subquery

EJB QL is Field empty

EJB QL Join Entity And Its Field

EJB QL Join Many To Many

EJB QL Join One to Many Left Join Fetch

EJB QL Join One To Many Map

EJB QL Join One To One

EJB QL Join One To One Return Two Entities

EJB QL Join Three Entities

EJB QL Join Two Entities

EJB QL Left Join One To Many Map

EJB QL Max With Date Type

EJB QL Order By Two Fields

EJB QL Pass Entity As Parameter

EJB Query Language: Not Exist

EJB SQL Count Function

Like statement and Wild Card

Like statement: Wild Card Escape

Not In function

Order Descending

Query an Entity as Query a Table

Query Field Of Field

Query Flush Mode

Query One To Many Collection

Query with Parameters

Range Check With Comparison Operator

Return List Of Entities From Query

Size function

Three Level Field Reference

Two Aggregate Functions

Use Between To check Salary Range

Use Object Function

Using SubQuery in EJB Query Langauge

Where Clause With SubQuery

Навигация

Поиск