JPQL
JPQL全称Java Persistence Query Language
基于首次在EJB2.0中引入的EJB查询语言(EJB QL),Java持久化查询语言(JPQL)是一种可移植的查询语言,旨在以面向对象表达式语言的表达式,将SQL语法和简单查询语义绑定在一起·使用这种语言编写的查询是可移植的,可以被编译成所有主流数据库服务器上的SQL。
其特征与原生SQL语句类似,并且完全面向对象,通过类名和属性访问,而不是表名和表的属性。
查询全部
@Test
public void testFindAll() {
//1、加载配置文件创建工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
//2、通过实体管理类工厂获取实体管理器
EntityManager entityManager = factory.createEntityManager();
//3、获取事务对象,开启事物
EntityTransaction entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
//4、查询全部
Query query = entityManager.createQuery("from Customer ");
List list = query.getResultList();
for (Object o : list) {
System.out.println(o);
}
//5、提交事物(回滚事务)
entityTransaction.commit();
//6、释放资源
entityManager.close();
}
/*
Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_
Customer{custId=2, custName='xx2', custSource='null', custIndustry='It教育', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=3, custName='xx02', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=4, custName='xx12', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=5, custName='xx22', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=6, custName='xx32', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=7, custName='xx42', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=8, custName='xx52', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=9, custName='xx62', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=10, custName='xx72', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=11, custName='xx82', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=12, custName='xx92', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
*/
排序查询
@Test
public void testOrderBy() {
//1、加载配置文件创建工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
//2、通过实体管理类工厂获取实体管理器
EntityManager entityManager = factory.createEntityManager();
//3、获取事务对象,开启事物
EntityTransaction entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
//4、查询全部
Query query = entityManager.createQuery("from Customer order by custId desc ");
List list = query.getResultList();
for (Object o : list) {
System.out.println(o);
}
//5、提交事物(回滚事务)
entityTransaction.commit();
//6、释放资源
entityManager.close();
/*
Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ order by customer0_.cust_id desc
Customer{custId=12, custName='xx92', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=11, custName='xx82', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=10, custName='xx72', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=9, custName='xx62', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=8, custName='xx52', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=7, custName='xx42', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=6, custName='xx32', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=5, custName='xx22', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=4, custName='xx12', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=3, custName='xx02', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=2, custName='xx2', custSource='null', custIndustry='It教育', custLevel='null', custAddress='null', custPhone='null'}
*/
}
统计查询
@Test
public void testCount () {
//1、加载配置文件创建工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
//2、通过实体管理类工厂获取实体管理器
EntityManager entityManager = factory.createEntityManager();
//3、获取事务对象,开启事物
EntityTransaction entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
//4、查询全部
Query query = entityManager.createQuery("select count(custId) from Customer");
Object result = query.getSingleResult();
System.out.println(result);
//5、提交事物(回滚事务)
entityTransaction.commit();
//6、释放资源
entityManager.close();
}
/**
* Hibernate: select count(customer0_.cust_id) as col_0_0_ from cst_customer customer0_
* 11
*/
条件查询
@Test
public void testCondition () {
//1、加载配置文件创建工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
//2、通过实体管理类工厂获取实体管理器
EntityManager entityManager = factory.createEntityManager();
//3、获取事务对象,开启事物
EntityTransaction entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
//4、查询全部
Query query = entityManager.createQuery("from Customer where custName like ?");
query.setParameter(1, "xx%");
List list = query.getResultList();
for (Object o : list) {
System.out.println(o);
}
//5、提交事物(回滚事务)
entityTransaction.commit();
//6、释放资源
entityManager.close();
}
/*
Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ where customer0_.cust_name like ?
Customer{custId=2, custName='xx2', custSource='null', custIndustry='It教育', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=3, custName='xx02', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=4, custName='xx12', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=5, custName='xx22', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=6, custName='xx32', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=7, custName='xx42', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=8, custName='xx52', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=9, custName='xx62', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=10, custName='xx72', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=11, custName='xx82', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=12, custName='xx92', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
*/
分页查询
@Test
public void testPage () {
//1、加载配置文件创建工厂
EntityManagerFactory factory = Persistence.createEntityManagerFactory("myJpa");
//2、通过实体管理类工厂获取实体管理器
EntityManager entityManager = factory.createEntityManager();
//3、获取事务对象,开启事物
EntityTransaction entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
//4、查询全部
Query query = entityManager.createQuery("from Customer");
query.setFirstResult(0);//起始索引
query.setMaxResults(2);//每页查询的条数
List list = query.getResultList();
for (Object o : list) {
System.out.println(o);
}
//5、提交事物(回滚事务)
entityTransaction.commit();
//6、释放资源
entityManager.close();
}
/*
Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ limit ?
Customer{custId=2, custName='xx2', custSource='null', custIndustry='It教育', custLevel='null', custAddress='null', custPhone='null'}
Customer{custId=3, custName='xx02', custSource='null', custIndustry='新科技', custLevel='null', custAddress='null', custPhone='null'}
*/