Spring Data JPA自定义SQL查询报错：无法解析根实体api_key

我在用Spring Boot + Spring Data JPA编写自定义SQL查询时遇到异常，核心错误是org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'api_key'，以下是相关代码和表结构：

User实体类 #

@Entity
public class User {
  @Id @NotBlank private String userName;

  // TODO: store hashed password
  @Column(nullable = false)
  @NotBlank
  private String password;

  @OneToMany(fetch = FetchType.LAZY, targetEntity = Event.class, cascade = CascadeType.ALL)
  List<Event> events;
}

Event实体类 #

@Entity
public class Event {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long eventId;

  @NotBlank private String eventName;
  @OneToMany(targetEntity = Dimension.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  @NotEmpty
  private List<Dimension> dimensions;

  @OneToMany(targetEntity = ApiKey.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private List<ApiKey> apiKeys;
}

ApiKey实体类 #

@Entity
public class ApiKey {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  private String apiKey;
  private long expiryTime;
}

UserRepository代码 #

@Repository
public interface UserRepository extends CrudRepository<User, String> {
  @Query("""
    SELECT user_events.user_user_name AS user_name,
           event_api_keys.event_event_id AS event_id,
           api_key.api_key,
           api_key.expiry_time
    FROM api_key
    JOIN event_api_keys ON api_key.id = event_api_keys.api_keys_id
    JOIN user_events ON user_events.events_event_id = event_api_keys.event_event_id
    WHERE api_key='51018598c935462189a4941d57388456'
    """)
  List<ApiKeyMeta> fetchApiKeyMeta(@Param("apiKey") String apiKey);
}

生成的SQL表结构 #

mysql> describe user;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| user_name | varchar(255) | NO   | PRI | NULL    |       |
| password  | varchar(255) | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+


mysql> describe event;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| event_id   | bigint       | NO   | PRI | NULL    |       |
| event_name | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+


mysql> describe user_events;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| user_user_name  | varchar(255) | NO   | MUL | NULL    |       |
| events_event_id | bigint       | NO   | PRI | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+


mysql> describe event_api_keys;
+----------------+--------+------+-----+---------+-------+
| Field          | Type   | Null | Key | Default | Extra |
+----------------+--------+------+-----+---------+-------+
| event_event_id | bigint | NO   | MUL | NULL    |       |
| api_keys_id    | bigint | NO   | PRI | NULL    |       |
+----------------+--------+------+-----+---------+-------+


mysql> describe api_key;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | bigint       | NO   | PRI | NULL    |       |
| api_key     | varchar(255) | YES  |     | NULL    |       |
| expiry_time | bigint       | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

抛出的异常 #

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.watchman.metaserver.user.repository.UserRepository.fetchApiKeyMeta(java.lang.String); Reason: Validation failed for query for method public abstract java.util.List com.watchman.metaserver.user.repository.UserRepository.fetchApiKeyMeta(java.lang.String)

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.watchman.metaserver.user.repository.UserRepository.fetchApiKeyMeta(java.lang.String)

Caused by: java.lang.IllegalArgumentException: org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'api_key'

Caused by: org.hibernate.query.sqm.UnknownEntityException: Could not resolve root entity 'api_key'

1. 核心问题 #

Spring Data JPA的@Query注解默认解析为JPQL（Java Persistence Query Language），JPQL中需要引用实体类名而非数据库表名。你写的是原生SQL，但未声明，导致Hibernate找不到名为api_key的实体（你的实体类是ApiKey）。

2. 两种修复方式 #

方式一：使用原生SQL查询

在@Query中添加nativeQuery = true，明确告诉框架这是原生SQL，同时修正查询中字段与表名的歧义问题，绑定参数：

@Query(value = """
    SELECT user_events.user_user_name AS user_name,
           event_api_keys.event_event_id AS event_id,
           api_key.api_key,
           api_key.expiry_time
    FROM api_key
    JOIN event_api_keys ON api_key.id = event_api_keys.api_keys_id
    JOIN user_events ON user_events.events_event_id = event_api_keys.event_event_id
    WHERE api_key.api_key = :apiKey
    """, nativeQuery = true)
List<ApiKeyMeta> fetchApiKeyMeta(@Param("apiKey") String apiKey);

方式二：使用JPQL查询

基于实体类和关联关系编写查询，JPA会自动处理中间关联表，更符合JPA设计思想：

@Query("""
    SELECT u.userName AS user_name, e.eventId AS event_id, ak.apiKey, ak.expiryTime
    FROM User u
    JOIN u.events e
    JOIN e.apiKeys ak
    WHERE ak.apiKey = :apiKey
    """)
List<ApiKeyMeta> fetchApiKeyMeta(@Param("apiKey") String apiKey);

3. 额外注意事项 #

• 确保ApiKeyMeta是符合投影要求的DTO类，拥有匹配的字段或全参构造函数，否则无法映射查询结果。
• 原实体类的@OneToMany关联建议补充mappedBy属性明确双向关联关系，避免JPA自动生成的中间表出现异常。

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