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();
參考：
沒有留言:
張貼留言