如何在JPA中使用原生查询与接口投影从多表获取指定列数据

需求场景 #

通过JPA执行原生SQL查询，关联多张数据表，并使用基于接口的投影获取指定字段的查询结果，避免创建冗余实体类。

实现步骤 #

1. 定义投影接口

创建一个接口，接口中的getter方法名需与原生查询的字段别名严格对应（遵循驼峰命名规则，例如别名centerCode对应getCenterCode()）：

public interface GetSlotGroupSeatAllocatedFromDB {
    String getCenterCode();
    String getCenterName();
    String getSlotName();
    Integer getSeatAllocatedCount();
    String getCenterCity();
    String getGroupName();
}

2. 在Repository中编写原生查询

在JpaRepository接口中添加带原生查询的方法，通过@Query注解指定SQL语句，并设置nativeQuery = true标识这是数据库原生SQL：

@Query(value = "select cm.center_code as centerCode, " +
               "cm.center_name as centerName, " +
               "es.exam_slot_code as slotName, " +
               "ecm.total_seat_allocated as seatAllocatedCount, " +
               "ecityM.city_name as centerCity, " +
               "afcatg.name as groupName " +
               "from center_master cm " +
               "inner join centre_examslot_mapping cesm on cm.center_id = cesm.centre_id " +
               "inner join exam_city_master ecityM on ecityM.city_id = cm.center_city_id " +
               "inner join exam_slot es on cesm.exam_slot_id = es.exam_slot_id " +
               "inner join exam_center_mapping ecm on cesm.centre_examslot_mappingid = ecm.centre_examslot_mappingid " +
               "inner join afcat_group afcatg on ecm.afcat_group_id = afcatg.afcat_group_id " +
               "where cm.center_code = ?1", 
       nativeQuery = true)
List<GetSlotGroupSeatAllocatedFromDB> getData(String centerCode);

核心要点说明 #

• 原生查询标识：必须设置nativeQuery = true，否则JPA会将SQL解析为JPQL而非数据库原生语法
• 字段别名映射：SQL中定义的别名需与投影接口的getter方法名匹配，JPA会自动完成结果映射
• 参数绑定：使用?1绑定方法的第一个参数，多参数场景可依次用?2、?3，或用命名参数:centerCode配合@Param("centerCode")注解
• 返回类型：直接返回投影接口的集合，无需额外实体类，减少代码冗余

内容的提问来源于stack exchange，提问作者ashish yadav