JPA高级查询：SQL结果集映射与存储过程使用

### JPA高级查询：SQL结果集映射与存储过程使用 #### 1. SQL结果集映射基础 在SQL查询中，结果映射并非总是简单直接的。通常情况下，SQL字符串中的列别名需与单个实体的对象关系列映射直接匹配，但实际情况并非总是如此，可能存在列名不匹配或返回多个实体类型的情况。JPA提供了SQL结果集映射来处理这些场景。 使用`@SqlResultSetMapping`注解可定义SQL结果集映射，它可放置在实体类上，包含一个名称（在持久化单元内唯一）以及一个或多个实体和列映射。例如： ```java @SqlResultSetMapping( name="EmployeeResult", entities=@EntityResult(entityClass=Employee.class) ) ``` 这里定义了一个名为“EmployeeResult”的SQL结果集映射，任何返回`Employee`实体实例的查询都可引用它。该映射由单个实体结果组成，通过`@EntityResult`注解指定，引用了`Employee`实体类。查询必须为实体映射的所有列（包括外键）提供值，若缺少任何必需的实体状态，实体是部分构造还是报错取决于具体的数据库供应商。 #### 2. 外键映射 外键无需在SQL结果集映射中显式映射。当查询引擎尝试将查询结果映射到实体时，也会考虑单值关联的外键列。例如以下查询： ```sql SELECT emp_id, name, salary, manager_id, dept_id, address_id FROM emp START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id ``` `MANAGER_ID`、`DEPT_ID`和`ADDRESS_ID`列都映射到`Employee`实体关联的连接列。此查询返回的`Employee`实例可使用`getManager()`、`getDepartment()`和`getAddress()`方法，持久化提供程序会根据查询中读取的外键值检索关联实体。不过，无法通过SQL查询填充集合关联。 #### 3. 多结果映射 一个查询可能同时返回多个实体，这在两个实体存在一对一关系时最有用，否则查询可能会产生重复的实体实例。例如以下查询： ```sql SELECT emp_id, name, salary, manager_id, dept_id, address_id, id, street, city, state, zip FROM emp, address WHERE address_id = id ``` 返回`Employee`和`Address`实体的SQL结果集映射定义如下： ```java @SqlResultSetMapping( name="EmployeeWithAddress", entities={@EntityResult(entityClass=Employee.class), @EntityResult(entityClass=Address.class)} ) ``` 实体列出的顺序不重要，查询引擎使用查询的列名与实体映射数据进行匹配，而非列的位置。 #### 4. 列别名映射 若SQL语句中的列别名与实体列映射中指定的名称不直接匹配，则需要使用字段结果映射，让查询引擎进行正确关联。例如，若`EMP`和`ADDRESS`表都使用`ID`作为主键列，查询需对`ID`列进行别名处理： ```sql SELECT emp.id AS emp_id, name, salary, manager_id, dept_id, address_id, address.id, street, city, state, zip FROM emp, address WHERE address_id = address.id ``` 使用`@FieldResult`注解将列别名映射到实体属性，以下是将`EMP_ID`别名转换为实体`id`属性的映射： ```java @SqlResultSetMapping( name="EmployeeWithAddress", entities={@EntityResult(entityClass=Employee.class, fields=@FieldResult( name="id", column="EMP_ID")), @EntityResult(entityClass=Address.class)} ) ``` 可以指定多个`@FieldResult`，但只需指定不同的映射，这可以是实体属性的部分列表。 #### 5. 标量结果列映射 SQL查询不仅可返回实体结果，还能返回非实体结果类型（即标量结果类型），使用`@ColumnResult`注解进行映射。例如以下查询： ```sql SELECT e.name AS emp_name, m.name AS manager_name FROM emp e, emp m WHERE e.manager_id = m.emp_id (+) START WITH e.manager_id IS NULL CONNECT BY PRIOR e.emp_id = e.manager_id ``` 其SQL映射如下： ```java @SqlResultSetMapping( name="EmployeeAndManager", columns={@ColumnResult(name="EMP_NAME"), @ColumnResult(name="MANAGER_NAME")} ) ``` 标量结果也可与实体混合，通常用于提供有关实体的额外信息。 以下是一个更复杂的示例，应用程序的报告需要显示每个部门的信息，包括经理、员工数量和平均工资。JP QL查询如下： ```sql SELECT d, m, COUNT(e), AVG(e.salary) FROM Department d LEFT JOIN d.employees e LEFT JOIN d.employees m WHERE m IS NULL OR m IN (SELECT de.manager FROM Employee de WHERE de.department = d) GROUP BY d, m ``` 由于`Department`与部门经理`Employee`之间没有直接关系，此查询较为复杂，`employees`关系需连接两次。在生产环境中，若提供商生成的SQL查询性能不佳，可使用以下替代查询： ```sql SELECT d.id, d.name AS dept_name, e.emp_id, e.name, e.salary, e.manager_id, e.dept_id, e.address_id, s.tot_emp, s.avg_sal FROM dept d, (SELECT * FROM emp e WHERE EXISTS(SELECT 1 FROM emp WHERE manager_id = e.emp_id)) e, (SELECT d.id, COUNT(*) AS tot_emp, AVG(e.salary) AS avg_sal FROM dept d, emp e WHERE d.id = e.dept_id (+) GROUP BY d.id) s WHERE d.id = e.dept_id (+) AND d.id = s.id ``` 该查询结果包含`Department`实体、`Employee`实体以及两个标量结果（员工数量和平均工资），映射如下： ```java @SqlResultSetMapping( name="DepartmentSummary", entities={ @EntityResult(entityClass=Department.class, fields=@FieldResult(name="name", column="DEPT_NAME")), @EntityResult(entityClass=Employee.class) }, columns={@Colu ```