java.sql.SQLSyntaxErrorException : No such column: id

Question

java.sql.SQLSyntaxErrorException: No such column: id

当表已包含id列时

我正在尝试使用以下 native SQL 查询来运行来自 Repository 类的 MariaDB SQL 中的预期结果:

SELECT name
FROM table1 t1
WHERE t1.table2_id IN (
  SELECT id
  FROM table2 t2
  WHERE t2.column1_number = 1 AND
        t2.id IS NOT NULL
  )
ORDER BY t1.name 

实体类如下:

@Entity
@Table(name = "table1")
public class Table1 {

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Integer number;

    @ManyToOne
    @JoinColumn(name = "table2_id")
    private Table2 table2;

   // Getters and Setters
}

@Entity
@Table(name="table2")
public class Table2 {
    private  Integer id;
    private  Integer column1_number;

    @Override
    public Integer getId() {
        return id;
    }

    @Override
    public void setId(Integer id) {
        this.id = id;
    }

   // Getters and Setters
}

存储库是:

@Repository
public interface Table1Repository extends JpaRepository<Table1, Integer> {

    @Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);
}

关于为什么我在运行时收到此错误有什么想法吗？编译时不会出现此错误。

Answer 1

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<Table1> findByNumberOrderByName(Integer number);

应该是List<String>而不是List<Table1>

所以解决方案是:

@Query(value = "SELECT name FROM table1 t1 WHERE t1.table2_id IN (SELECT id FROM table2 t2 WHERE t2.column1_number = ?1 AND t2.id IS NOT NULL) ORDER BY t1.name", nativeQuery = true)
    List<String> findByNumberOrderByName(Integer number);