JPQL
- JPQL:Java Persistence Query Language
- 用于完成复杂查询,EntityManager.find 方法只能完成根据 id 查询
- 和 SQL 语法基本一致,但 JPQL 不支持 select *
- 查出来的实体对象的均处于 Managed 状态,可以用于 remove,setter,remove 之后还可以调用 persist 保存
- 注意分页查询的写法
- JPQL 还可以用于更新、删除操作
JPQL 测试
查询全部 from Student
@Test
public void test1() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
String jpql = "from Student";
Query query = em.createQuery(jpql);
List list = query.getResultList();
/*
Hibernate:
select
student0_.sid as sid1_0_,
student0_.sage as sage2_0_,
student0_.sname as sname3_0_
from
Student student0_
*/
for (Object o : list) {
System.out.println(o);
}
/*
Student{sid=1, sname='刘备', sage=24}
Student{sid=2, sname='关羽', sage=23}
Student{sid=3, sname='张飞', sage=21}
*/
tx.commit();
em.close();
factory.close();
}
逆序查询全部 from Student order by sid desc
@Test
public void test2() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
String jpql = "from Student order by sid desc";
Query query = em.createQuery(jpql);
List list = query.getResultList();
/*
Hibernate:
select
student0_.sid as sid1_0_,
student0_.sage as sage2_0_,
student0_.sname as sname3_0_
from
Student student0_
order by
student0_.sid desc
*/
for (Object o : list) {
System.out.println(o);
}
/*
Student{sid=3, sname='张飞', sage=21}
Student{sid=2, sname='关羽', sage=23}
Student{sid=1, sname='刘备', sage=24}
*/
tx.commit();
em.close();
factory.close();
}
分页查询 from Student
@Test
public void test3() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
String jpql = "from Student";
Query query = em.createQuery(jpql);
query.setFirstResult(1);
query.setMaxResults(2);
List list = query.getResultList();
/*
Hibernate:
select
student0_.sid as sid1_0_,
student0_.sage as sage2_0_,
student0_.sname as sname3_0_
from
Student student0_ limit ?,
?
*/
for (Object o : list) {
System.out.println(o);
}
/*
Student{sid=2, sname='关羽', sage=23}
Student{sid=3, sname='张飞', sage=21}
*/
tx.commit();
em.close();
factory.close();
}
统计查询 select count(*) from Student
@Test
public void test4() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
String jpql = "select count(*) from Student";
Query query = em.createQuery(jpql);
Long count = (Long) query.getSingleResult();
/*
Hibernate:
select
count(*) as col_0_0_
from
Student student0_
*/
System.out.println(count); // 3
tx.commit();
em.close();
factory.close();
}
条件查询 from Student where sname like ?1
@Test
public void test5() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
String jpql = "from Student where sname like ?1";
Query query = em.createQuery(jpql);
query.setParameter(1, "关%");
List list = query.getResultList();
/*
Hibernate:
select
student0_.sid as sid1_0_,
student0_.sage as sage2_0_,
student0_.sname as sname3_0_
from
Student student0_
where
student0_.sname like ?
*/
for (Object o : list) {
System.out.println(o);
}
/*
Student{sid=2, sname='关羽', sage=23}
*/
tx.commit();
em.close();
factory.close();
}
更新 update Student set sage = ?2 where sid = ?1
@Test
public void test6() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
Query query = em.createQuery("update Student set sage = ?2 where sid = ?1");
query.setParameter(1,1L);
query.setParameter(2,100);
query.executeUpdate();
/*
Hibernate:
update
Student
set
sage=?
where
sid=?
*/
tx.commit();
em.close();
factory.close();
}
删除 delete from Student where sage < ?1
@Test
public void test7() {
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
EntityManager em = factory.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
Query query = em.createQuery("delete from Student where sage < ?1");
query.setParameter(1, 18);
query.executeUpdate();
/*
Hibernate:
delete
from
Student
where
sage<?
*/
tx.commit();
em.close();
factory.close();
}