Spring Data JPA删除操作报Unknown column in 'where clause'错误求助

实体类定义 #

@MappedSuperclass
public abstract class IdEntity {
  @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
  protected Long id;

  @Version
  protected Long version;
}

@MappedSuperclass
public abstract class UidEntity {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    protected String uid;
}

@Entity
public class Third extends UidEntity {
}

@Entity
public class Second extends IdEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "third_id", nullable = false,
        foreignKey = @ForeignKey(name = "fk_second_third_id"))
    private Third third;
}

继承结构实体类 #

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "type_")
public abstract class First extends IdEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "second_id", nullable = false,
        foreignKey = @ForeignKey(name = "fk_first_second_id"))
    private Second second;
}

// 注意：此处存在笔误，应继承First而非FirstClass
@Entity
@DiscriminatorValue("string")
public class StringFirstClass extends First {
    @Column(name = "v_string")
    private String value;
}

Repository定义（Scala） #

trait IdRepository[T <: IdEntity, Id <: java.io.Serializable] extends
  PagingAndSortingRepository[T, Id] with ListCrudRepository[T, Id] with
  JpaSpecificationExecutor[T] {
}

// 实际Repository定义示例
trait FirstClassRepository extends IdRepository[First, Long] {
}

问题代码：错误的Specification #

// 存在两处错误：Root泛型类型错误、链式get未生成正确表关联
(root: Root[Attribute], _: CriteriaQuery[_], builder: CriteriaBuilder) => {
   return builder.equal(root.get("second").get("third").get("uid"), "uuid1234")
}

报错信息 #

org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [delete from first_class where exists(select 1 from second_class s1_0 where s1_0.id=first_class.second_id and t1_0.uid=?)] [Unknown column 't1_0.uid' in 'where clause'] [n/a]; SQL [n/a]

依赖版本 #

• org.springframework.data:spring-data-jpa:jar:3.2.9
• jakarta.persistence:jakarta.persistence-api:jar:3.1.0
• org.hibernate.orm:hibernate-core:jar:6.4.10.Final
• org.springframework:spring-jdbc:jar:6.1.12

解决方案 #

1. 修正Specification逻辑 #

原代码的链式get()调用在批量删除的exists子查询中，无法触发JPA生成正确的表关联语句，导致Third表未被显式join，出现无效表别名。需显式调用join方法构建关联关系：

(root: Root[First], query: CriteriaQuery[_], builder: CriteriaBuilder) => {
    // 显式关联Second表
    val secondJoin = root.join[Second]("second", JoinType.INNER)
    // 显式关联Third表
    val thirdJoin = secondJoin.join[Third]("third", JoinType.INNER)
    // 基于关联后的表字段构建条件
    builder.equal(thirdJoin.get("uid"), "uuid1234")
}

2. 修正实体类笔误 #

将StringFirstClass的父类从FirstClass改为First，否则会出现编译错误，无法正确映射实体继承关系。

3. 问题根源说明 #

在批量删除场景下，JPA Criteria API不会自动为链式get()生成join语句（仅普通查询场景会自动处理），显式join可强制生成完整的关联SQL，确保每个关联表都有合法别名和join条件，避免"Unknown column"错误。

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