網頁

▼

2018/6/23

JPA JPQL查詢語法

JPA的JPQL(Java Persistence Query Language)語法可類似SQL，但其查詢資料源是持久化物件(Entity)而非資料庫本身。

例如有一個對映資料表EMPLOYEE，在Java程式中對映的實體為Employee如下。

@Entity
@Table(name="EMPLOYEE")
public class Employee {

    @Id
    @Column(name="ID")
    private int id;
    
    @Column(name="NAME")
    private String name;
    
    @Column(name="EMAIL", unique=true)
    private String email;
    
    // getter and setter ommitted
}

查詢全部。因為Entity的名稱預設為實體的類別名稱，所以查詢的是Employee而不是EMPLOYEE。

EntityManager em = Persistence.createEntityManagerFactory("mydb").createEntityManager();

String jpql = "SELECT e FROM Employee e";

Query query = em.createQuery(jpql, Employee.class);
List<Employee> employeeList = query.getResultList();

查詢WHERE條件。後面的:email是插入參數的位置，可以任意命名。所以下面的query.setParameter("email", email);會將email變數插入在:email的位置

EntityManager em = Persistence.createEntityManagerFactory("mydb").createEntityManager();

String jpql = "SELECT e FROM Employee e WHERE e.email = :email";

Query query = em.createQuery(jpql, Employee.class);
query.setParameter("email", email);
List<Employee> employeeList = query.getResultList();

查詢WHERE...AND...。

EntityManager em = Persistence.createEntityManagerFactory("mydb").createEntityManager();

String jpql = "SELECT e FROM Employee e WHERE e.email = :arg1 AND e.name = :arg2";

Query query = em.createQuery(jpql, Employee.class);
query.setParameter("arg1", email);
query.setParameter("arg2", name);
List<Employee> employeeList = query.getResultList();

查詢LIKE條件

EntityManager em = Persistence.createEntityManagerFactory("mydb").createEntityManager();

String jpql = "SELECT e FROM Employee e WHERE e.name LIKE :arg1";

Query query = em.createQuery(jpql, Employee.class);
query.setParameter("arg1", "%" + name + "%"); // e.g. %matt%
List<Employee> employeeList = query.getResultList();

IS NULL

EntityManager em = Persistence.createEntityManagerFactory("mydb").createEntityManager();

String jpql = "SELECT e FROM Employee e WHERE e.name IS NULL";

Query query = em.createQuery(jpql, Employee.class);
List<Employee> employeeList = query.getResultList();

參考：

沒有留言:

張貼留言