Hibernate/JPA使用createNativeQuery左连接报错：重复SQL别名

问题出在你用SELECT *查询多关联表的时候——多个表存在同名字段（比如这里的tester_id），Hibernate自动识别字段别名时，没法区分这些重复的字段，就抛出了这个异常。

解决方法有两种：

• 直接指定需要的字段并给重复字段加别名
  别用SELECT *，只挑你实际需要的字段查询，对重复的字段设置唯一别名，示例代码：

  final Query query = entityManager.createNativeQuery(
      "SELECT p.program_id, p.test_id, vt.tester_id AS vt_tester_id, bv.vendor_id, bp.alpha_program_id " +
      "FROM program p LEFT JOIN tester vt on p.test_id = vt.test_id " +
      "LEFT JOIN vendor bv on vt.vendor_id = bv.vendor_id " +
      "LEFT JOIN alpha_program bp on p.program_id = bp.program_id " +
      "WHERE vt.status IN ('BLOCKED') ORDER BY p.creation_date asc limit 30"
  );
  

  这样每个字段的别名都是唯一的，Hibernate就能正常处理了。

• 用@SqlResultSetMapping做结果映射
  如果需要把查询结果映射到实体类，可以定义结果映射规则，明确每个字段对应的实体属性，同时处理重复字段。比如在实体类上添加映射配置：

  @SqlResultSetMapping(
      name = "ProgramWithRelationsMapping",
      entities = {
          @EntityResult(entityClass = Program.class, fields = {
              @FieldResult(name = "programId", column = "program_id"),
              @FieldResult(name = "testId", column = "test_id")
          }),
          @EntityResult(entityClass = Tester.class, fields = {
              @FieldResult(name = "testerId", column = "vt_tester_id")
          })
          // 其他关联实体的映射按需求添加
      }
  )
  

  然后查询时指定这个映射名称：

  final Query query = entityManager.createNativeQuery(
      "SELECT p.program_id, p.test_id, vt.tester_id AS vt_tester_id, bv.vendor_id, bp.program_id AS bp_program_id " +
      "FROM program p LEFT JOIN tester vt on p.test_id = vt.test_id " +
      "LEFT JOIN vendor bv on vt.vendor_id = bv.vendor_id " +
      "LEFT JOIN alpha_program bp on p.program_id = bp.program_id " +
      "WHERE vt.status IN ('BLOCKED') ORDER BY p.creation_date asc limit 30",
      "ProgramWithRelationsMapping"
  );
  

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