Caused by: java.sql.SQLException: Column 'id' not found. But it's there

Question

When trying to execute this query (it's supposed to retrieve unique "name + last_name + patronymic + date_of_birth" combinations)

    @Query(value = """
            SELECT name, last_name, patronymic, MIN(sex) as sex,
            date_of_birth, DATEDIFF(CURDATE(), date_of_birth) / 365 AS age
            FROM users_db.persons
            GROUP BY name, last_name, patronymic, date_of_birth
            ORDER BY name, last_name, patronymic
            """, nativeQuery = true)
    List<Person> getUniqueNameDatePairs();

I get this weird message

Caused by: java.sql.SQLException: Column 'id' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
    at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:584)
    at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.resolveColumnPosition(AbstractResultSetAccess.java:65)
    ... 61 more

I know for sure the column is there. I just checked it in MySQL Workbench

Here's my @Entity. The field is called id too, it should be mapped just fine

@Builder(setterPrefix = "with")
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Table(name = "persons")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // here
    private String name;
    @Column(name = "last_name")
    private String lastName;
    private String patronymic;
    @Column(name = "date_of_birth")
    @Temporal(TemporalType.DATE)
    private LocalDate dateOfBirth;
    private Sex sex;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Person person = (Person) o;

        return id.equals(person.id);
    }

    @Override
    public int hashCode() {
        return id.hashCode();
    }

    @Override
    public String toString() {
        return new StringJoiner(", ", Person.class.getSimpleName() + "[", "]")
                .add("id=" + id)
                .add("name='" + name + "'")
                .add("lastName='" + lastName + "'")
                .add("patronymic='" + patronymic + "'")
                .add("dateOfBirth=" + dateOfBirth)
                .add("sex=" + sex)
                .toString();
    }
}

Here are some of my pom.xml dependencies so that you have a better understanding of my app. Notice it's not a Spring Boot app

    <dependency>
      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-jpa</artifactId>
      <version>3.1.2</version>
    </dependency>

    <dependency>
      <groupId>jakarta.persistence</groupId>
      <artifactId>jakarta.persistence-api</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId>org.hibernate.orm</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>6.2.7.Final</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>6.0.11</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>6.0.11</version>
    </dependency>

    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.1.0</version>
    </dependency>

I want to stress that some operations, specifically creating a table and persisting an entity, work fine in my app

The problem may have to do with id missing in the result set. But it's how the entry test task I'm trying to complete is phrased. id indeed shouldn't be in the result set. I don't understand why Hibernate can't simply initialize the field with null if it's not in the result set (that's what I expected)

Also, note Person doesn't have such a field, age. Again, it's how the task is phrased (or at least, it's my understanding of it). I should somehow put out the retrieved rows in the log while also not cluttering my table with unnecessary columns (age is easily derived from DOB and should be regularly updated so I shouldn't include it in the table)

What could be the problem and what are the ways to fix it?

The following (similar) questions don't seem to be helpful:

Have you tried to have the column be named something like `person_id`? Hibernate has historically used the name `id` for internal things and it has never worked for me. One could easily argue that `person_id` is more self documented too. — stdunbar, Aug 17 '23 at 15:53
`id` worked fine before, in my earlier projects. `person_id` sounds a lot like a foreign key rather than a primary key — JoreJoh, Aug 17 '23 at 15:58
Matter of opinion of course. If your primary key and foreign key have different names your schema is much harder to read. But `id` has always beat me up with Hibernate. — stdunbar, Aug 17 '23 at 16:00
Maybe the problem is that you didn't add the column `id` to the 'SELECT` in the query? — Maksim Eliseev, Aug 17 '23 at 17:02
@MaksimEliseev yes, but it would be peachy if I could somehow get away with it — JoreJoh, Aug 17 '23 at 17:07

Caused by: java.sql.SQLException: Column 'id' not found. But it's there

0 Answers0