1.课程介绍
1. 配置查询对象的模型;(掌握)
2. JPQL;(掌握)
3. 原生SQL查询;(掌握)
4. 分页查询;(掌握)
5. 乐观锁;(掌握)
一,JPQL;(掌握)
select o
from 类名 o
where o.属性名
order by
group by
having
jpql原则属性的规则
(1) jpql 不能出现*号,必须出现对象的别名和属性名
(2)jpql的关键字和sql一样,不分区大小写
(3)类名何属性名区别大小写
基本查询
@Test
public void test1() throws Exception{
String jpql = "select o from Employee o";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询所有员工的姓名和所属部门名称【查询特定属性】
@Test
public void test2() throws Exception{
String jpql = "select o.name,o.department.name from Employee o";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
@Test
public void test3() throws Exception{
String jpql = "select new Employee(e.name,e.department.name) from Employee e";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o.getName()+"----"+o.getDepartment().getName());
}
}
// 查询出所有在成都和广州工作的员工【查询结果过滤】
@Test
public void test4() throws Exception{
String jpql = "select e from Employee e where e.department.city like ? or e.department.city like ?";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setParameter(1,"%成都%").setParameter(2,"%广州%");
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出所有员工信息,按照月薪排序【查询排序】order by
@Test
public void test5() throws Exception{
String jpql = "select e from Employee e order by e.salary desc";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出所有员工信息,按照部门编号排序【使用关联对象属性排序】
@Test
public void test6() throws Exception{
String jpql = "select e from Employee e order by e.department.sn asc ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出在恩宁路和八宝街上班的员工信息【使用IN】
@Test
public void test7() throws Exception{
String jpql = "select e from Employee e where e.department.street in(?0,?1)";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setParameter(0,"%恩宁路%").setParameter(1,"%八宝街%");
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出工资在5000-6000的员工【使用BETWEEN..AND..】 区间【5000,6000】 左开(5000,6000]右闭
@Test
public void test8() throws Exception{
String jpql = "select e from Employee e where e.salary between :min and :max ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setParameter("min",new BigDecimal("5000")).setParameter("max",new BigDecimal("6000"));
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出姓名包含er或者en的员工【使用LIKE】
@Test
public void test9() throws Exception{
String jpql = "select e from Employee e where e.name like ? or e.name like ? ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setParameter(1,"%er%").setParameter(1,"%en%");
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出有员工的部门【distinct】
@Test
public void test10() throws Exception{
String jpql = "select distinct e.department from Employee e ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Department> list = query.getResultList();
for (Department o : list) {
System.out.println(o);
}
}
//查询出有员工的部门【size】
@Test
public void test11() throws Exception{
String jpql = "select d from Department d where d.employee.size>0";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Department> list = query.getResultList();
for (Department o : list) {
System.out.println(o);
}
}
//查询出部门信息,按照部门的员工人数排序
@Test
public void test12() throws Exception{
String jpql = "select d from Department d order by d.employees.size desc ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Department> list = query.getResultList();
for (Department o : list) {
System.out.println(o);
}
}
//查询出没有员工参与的项目【对集合使用size】
@Test
public void test13() throws Exception{
String jpql = "select p from Project p where p.employees.size =0 ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Project> list = query.getResultList();
for (Project o : list) {
System.out.println(o);
}
}
//查询出所有员工及部门名称
@Test
public void test14() throws Exception{
String jpql = "select e.name,d.name from Employee e left join e.department d ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出市场部员工信息及电话
@Test
public void test15() throws Exception{
String jpql = "select e,p from Phone p join p.employee e where e.department.name like ?";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setParameter(1,"%市场部%");
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出各个部门员工的平均工资和最高工资
@Test
public void test16() throws Exception{
String jpql = "select e.department.name,avg(e.salary) ,max(e.salary) from Employee e group by e.department.name ";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出各个项目参与人数报表
@Test
public void test17() throws Exception{
String jpql = "select p.id,p.name,p.employee.size from Project p where p.employee.size>0";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
子查询
@Test
public void test18() throws Exception{
String jpql = "select o from Employee o where o.salary > (select avg(e.salary) from Employee e )";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
分页查询
@Test
public void test19() throws Exception{
String jpql = "select o from Employee o";
EntityManager entityManager = JpaUtils.getEntityManager();
Query query = entityManager.createQuery(jpql);
query.setFirstResult(1).setMaxResults(5);//前者是起始页面,后者是查询条数
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
乐观锁,悲观锁
事物并发
事物
一组操作,要么都成功,要么都失败
特性
原子性(atomic): 事务这个单元它已经是最小单元,不能在去分割
一致性 (consistent): 数据一致性,操作之前,是什么样,操作之后就是什么样
隔离性(insulation) : 事务和事务之间,他们是相互隔离的,你的事务,不能操作我的事务,不然出现事务并发
持久性 (Duration):当前事务commit的时候,就把数据永久存储下来;
@Test
public void test2() throws Exception{
try {
//用户1
EntityManager entityManager1 = JpaUtils.getEntityManager();
//用户2
EntityManager entityManager2 = JpaUtils.getEntityManager();
entityManager1.getTransaction().begin();
entityManager2.getTransaction().begin();
//用户1查询的时候,还有一个娃娃
Product product1 = entityManager1.find(Product.class, 1L);
//用户2查询的时候,还有一个娃娃
Product product2 = entityManager2.find(Product.class, 1L);
product1.setNum(product1.getNum()-1);
product2.setNum(product2.getNum()-1);
entityManager1.getTransaction().commit();
entityManager2.getTransaction().commit();
entityManager1.close();
entityManager2.close();
}catch (Exception e){
e.printStackTrace();
System.out.println("下次快点,已经卖完了");
}
}
乐观锁:
版本号跟新