本文介绍了JPA中的JPQL查询语言，强调其面向对象的特点和书写规则，并通过代码案例展示了如何使用。此外，还探讨了事务并发控制，特别是乐观锁的概念和在JPA中的实现，包括其在保证数据一致性和事务四大特性（ACID）中的作用。
JPA-day4 jpql和乐观锁
一、JPQL的特点
1.JPQL和SQL很像,查询关键字都是一样的
2.唯一的区别是:JPQL是面向对象的
二、jpql书写规则
JPA的查询语言,类似于sql
1.里面不能出现表名,列名,只能出现java的类名,属性名，区分大小写
2.出现的sql关键字是一样的意思,不区分大小写
3.不能写select * 要写select 别名
三、jpql书写的代码案例（配置号domain类）
3.1生成mysql表的sql语句(直接拖入Navicat for MySQL 即可生成练习)
ET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `department`
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`provice` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`street` varchar(255) DEFAULT NULL,
`manager_id` bigint(20) DEFAULT NULL,
`sn` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKA9601F7262086B81` (`manager_id`),
CONSTRAINT `FKA9601F7262086B81` FOREIGN KEY (`manager_id`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '市场部', '四川', '成都', '八宝街', '1', '002');
INSERT INTO `department` VALUES ('2', '技术部', '四川', '成都', '宁夏街', '5', '001');
INSERT INTO `department` VALUES ('3', '市场二部', '广东', '广州', '恩宁路', '9', '004');
INSERT INTO `department` VALUES ('4', '技术二部', '广东', '广州', '恩宁路', '13', '003');
INSERT INTO `department` VALUES ('5', '市场三部', '四川', '成都', '西大街', null, '005');
-- ----------------------------
-- Table structure for `employee`
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`department_id` bigint(20) DEFAULT NULL,
`salary` decimal(8,2) DEFAULT NULL,
`hireDate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK4AFD4ACE3DA4510D` (`department_id`),
CONSTRAINT `FK4AFD4ACE3DA4510D` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', 'Tom', '1', '4500.00', '2010-09-04');
INSERT INTO `employee` VALUES ('2', 'Aaron', '1', '4000.00', '2012-03-06');
INSERT INTO `employee` VALUES ('3', 'Abel', '1', '4200.00', '2012-06-08');
INSERT INTO `employee` VALUES ('4', 'Abraham', '1', '6000.00', '2009-11-18');
INSERT INTO `employee` VALUES ('5', 'Ben', '2', '2600.00', '2013-03-20');
INSERT INTO `employee` VALUES ('6', 'Benjamin', '2', '8000.00', '2008-07-09');
INSERT INTO `employee` VALUES ('7', 'Bert', null, '5100.00', '2012-11-15');
INSERT INTO `employee` VALUES ('8', 'Benson', '2', '3800.00', '2013-03-20');
INSERT INTO `employee` VALUES ('9', 'Cameron', '3', '5200.00', '2012-08-25');
INSERT INTO `employee` VALUES ('10', 'Carl', '3', '5500.00', '2012-07-15');
INSERT INTO `employee` VALUES ('11', 'Carlos', '3', '5000.00', '2012-09-05');
INSERT INTO `employee` VALUES ('12', 'Christian', '3', '5600.00', '2012-11-16');
INSERT INTO `employee` VALUES ('13', 'Daniel', '4', '5800.00', '2012-10-19');
INSERT INTO `employee` VALUES ('14', 'Denny', '4', '5900.00', '2012-02-25');
INSERT INTO `employee` VALUES ('15', 'Darwin', '4', '7000.00', '2012-03-21');
INSERT INTO `employee` VALUES ('16', 'Dennis', '4', '5200.00', '2012-05-18');
INSERT INTO `employee` VALUES ('17', 'Derek', '4', '5600.00', '2012-07-25');
-- ----------------------------
-- Table structure for `phone`
-- ----------------------------
DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`types` varchar(255) DEFAULT NULL,
`number` varchar(255) DEFAULT NULL,
`employee_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK4984D4E3CBA8E0` (`employee_id`),
CONSTRAINT `FK4984D4E3CBA8E0` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of phone
-- ----------------------------
INSERT INTO `phone` VALUES ('1', 'CELL', '000001', '1');
INSERT INTO `phone` VALUES ('2', 'WORK', '000002', '1');
INSERT INTO `phone` VALUES ('3', 'CELL', '000003', '2');
INSERT INTO `phone` VALUES ('4', 'CELL', '000004', '3');
INSERT INTO `phone` VALUES ('5', 'CELL', '000005', '4');
INSERT INTO `phone` VALUES ('6', 'CELL', '000006', '5');
INSERT INTO `phone` VALUES ('7', 'CELL', '000007', '6');
INSERT INTO `phone` VALUES ('8', 'WORK', '000008', '7');
INSERT INTO `phone` VALUES ('9', 'WORK', '000009', '8');
INSERT INTO `phone` VALUES ('10', 'CELL', '000010', '9');
INSERT INTO `phone` VALUES ('11', 'CELL', '000011', '10');
INSERT INTO `phone` VALUES ('12', 'CELL', '000012', '11');
INSERT INTO `phone` VALUES ('13', 'WORK', '000013', '12');
INSERT INTO `phone` VALUES ('14', 'CELL', '000014', '12');
INSERT INTO `phone` VALUES ('15', 'WORK', '000015', '13');
INSERT INTO `phone` VALUES ('16', 'WORK', '000016', '14');
INSERT INTO `phone` VALUES ('17', 'CELL', '000017', '15');
INSERT INTO `phone` VALUES ('18', 'WORK', '000018', '15');
INSERT INTO `phone` VALUES ('19', 'CELL', '000019', '16');
INSERT INTO `phone` VALUES ('20', 'CELL', '000020', '17');
INSERT INTO `phone` VALUES ('21', 'WORK', '000021', '17');
-- ----------------------------
-- Table structure for `project`
-- ----------------------------
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`manager_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK50C8E2F962086B81` (`manager_id`),
CONSTRAINT `FK50C8E2F962086B81` FOREIGN KEY (`manager_id`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of project
-- ----------------------------
INSERT INTO `project` VALUES ('1', '装饰ERP', '5');
INSERT INTO `project` VALUES ('2', '服装ERP', '5');
INSERT INTO `project` VALUES ('3', '工业制造ERP', '5');
INSERT INTO `project` VALUES ('4', '通用CRM', '13');
INSERT INTO `project` VALUES ('5', '蓝源OA', '14');
INSERT INTO `project` VALUES ('6', '蓝源SAAS平台', '13');
INSERT INTO `project` VALUES ('7', '蓝源EDP', '13');
-- ----------------------------
-- Table structure for `project_employee`
-- ----------------------------
DROP TABLE IF EXISTS `project_employee`;
CREATE TABLE `project_employee` (
`EMPLOYEE_ID` bigint(20) NOT NULL,
`PROJECT_ID` bigint(20) NOT NULL,
PRIMARY KEY (`PROJECT_ID`,`EMPLOYEE_ID`),
KEY `FK9931F934DF91BC74` (`PROJECT_ID`),
KEY `FK9931F9343CBA8E0` (`EMPLOYEE_ID`),
CONSTRAINT `FK9931F9343CBA8E0` FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES `employee` (`id`),
CONSTRAINT `FK9931F934DF91BC74` FOREIGN KEY (`PROJECT_ID`) REFERENCES `project` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of project_employee
-- ----------------------------
INSERT INTO `project_employee` VALUES ('5', '1');
INSERT INTO `project_employee` VALUES ('6', '1');
INSERT INTO `project_employee` VALUES ('8', '1');
INSERT INTO `project_employee` VALUES ('5', '2');
INSERT INTO `project_employee` VALUES ('6', '2');
INSERT INTO `project_employee` VALUES ('8', '2');
INSERT INTO `project_employee` VALUES ('13', '3');
INSERT INTO `project_employee` VALUES ('14', '3');
INSERT INTO `project_employee` VALUES ('13', '4');
INSERT INTO `project_employee` VALUES ('15', '4');
INSERT INTO `project_employee` VALUES ('16', '4');
INSERT INTO `project_employee` VALUES ('17', '4');
INSERT INTO `project_employee` VALUES ('13', '5');
INSERT INTO `project_employee` VALUES ('14', '5');
四、查询的代码案例
//查询所有员工【查询实体类型】
@Test
public void testJpql1() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e";
Query query = entityManager.createQuery(jpql);
for (Object o : query.getResultList()) {
System.out.println(o);
}
}
//查询所有员工的姓名和所属部门名称
@Test
public void testJpql2() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e.id,e.name,e.department.name from Employee e";
Query query = entityManager.createQuery(jpql);
List<Object[]> resultList = query.getResultList();
for (Object[] objects : resultList) {
System.out.println(Arrays.toString(objects));
}
}
//查询出所有在成都和广州工作的员工
@Test
public void testJpql3() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e.id,e.name,e.department.city from Employee e where e.department.city like ? or e.department.city like ?";
Query query = entityManager.createQuery(jpql);
query.setParameter(1, "%广州%").setParameter(2, "%成都%");
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出所有员工信息，按照月薪排序
@Test
public void testJpql4() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e order by e.salary asc ";
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出所有员工信息，按照部门编号排序
@Test
public void testJpql5() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e order by e.department.id asc ";
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出在恩宁路和八宝街上班的员工信息(用in)
@Test
public void testJpql6() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e where e.department.street in (?0,?1)";
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..】
@Test
public void testJpql7() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e where e.salary between :min and :max";
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 testJpql8() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e where e.name like ? or e.name like ?";
Query query = entityManager.createQuery(jpql);
query.setParameter(1, "%er%").setParameter(2, "%en%");
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出有员工的部门【distinct】
@Test
public void testJpql9() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select distinct e.department.name from Employee e where e. ?";
Query query = entityManager.createQuery(jpql);
query.setParameter(1, "%er%").setParameter(2, "%en%");
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//查询出有员工的部门
@Test
public void testJpql10() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select o from Department o where o.employees.size>0";
Query query = entityManager.createQuery(jpql);
List<Department> list = query.getResultList();
for (Department o : list) {
System.out.println(o);
}
}
//查询出部门信息，按照部门的员工人数排序
@Test
public void testJpql11() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select o,o.employees.size from Department o order by o.employees.size asc ";
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出没有员工参与的项目【对集合使用size】
@Test
public void testJpql12() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select p from Project p where p.employees.size=0";
Query query = entityManager.createQuery(jpql);
List<Project> list = query.getResultList();
for (Project o : list) {
System.out.println(o);
}
}
//.查询出所有员工及部门名称【JOIN/LEFT JOIN】
@Test
public void testJpql13() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e,d.name from Employee e left join e.department d";
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//.查询出市场部员工信息及电话
@Test
public void testJpql14() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e,p.number from Phone p left join p.employee e where e.department.name like ? ";
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 testJpql15() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e.department.name,avg(e.salary),max(e.salary) from Employee e group by e.department.name ";
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出各个项目参与人数报表
@Test
public void testJpql16() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select p.name,p.employees.size from Project p group by p.name";
Query query = entityManager.createQuery(jpql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//查询出大于平均工资的员工信息(用子查询)
@Test
public void testJpql17() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e where e.salary>(select avg(e.salary) from Employee e )";
Query query = entityManager.createQuery(jpql);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//分页查询(limit)
@Test
public void testJpql18() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String jpql = "select e from Employee e ";
Query query = entityManager.createQuery(jpql);
query.setFirstResult(0).setMaxResults(12);
List<Employee> list = query.getResultList();
for (Employee o : list) {
System.out.println(o);
}
}
//原生SQL查询
@Test
public void testJpql19() throws Exception{
EntityManager entityManager = JpaUtil.getEntityManager();
String sql = "select * from employee";
Query query = entityManager.createNativeQuery(sql);
List<Object[]> list = query.getResultList();
for (Object[] o : list) {
System.out.println(Arrays.toString(o));
}
}
//用原生sql返回对象而不是object数组
@Test
public void testJpql20() throws Exception {
EntityManager entityManager = JpaUtil.getEntityManager();
String sql = "select * from employee";
// 告诉hibernate把employee表转换为Employee对象
Query query = entityManager.createNativeQuery(sql, Employee.class);
//在sql中也不用写limit
query.setFirstResult(0).setMaxResults(15);
List<Employee> list = query.getResultList();
for (Employee employee : list) {
System.out.println(employee);
}
}
//用原生sql使用条件查询
@Test
public void testJpql21() throws Exception {
EntityManager entityManager = JpaUtil.getEntityManager();
String sql = "select * from employee where name like ? order by salary asc";
Query query = entityManager.createNativeQuery(sql, Employee.class);
query.setParameter(1, "%en%");
List<Employee> list = query.getResultList();
for (Employee employee : list) {
System.out.println(employee);
}
}
四、事务并发（乐观锁）
4.1事务的四大特性（ACID）
原子性(atomic)，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
一致性(consistent)，事务在完成时，必须使所有的数据都保持一致状态。
隔离性(insulation)，由事务并发所作的修改必须与任何其它并发事务所作的修改隔离。
持久性(Duration)，事务完成之后，它对于系统的影响是永久性的。
悲观锁
相当于同步效果, 如果一个人操作数据的时候，先把数据锁住，另外一个人必须等待我操作完，释放锁之后，才能进行操作
使用entityManager.find(class,id,LockModeType);加悲观锁，相当于发送SELECT ... FOR UPDATE(加了一个行锁)
乐观锁
.Version方式(整数,存储空间小)
// 添加一个私有字段Integer version,不由程序员维护,由JPA自己维护
@Version
private Integer version;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
