JPA query is throwing a ‘not found’ error for column that isn’t in query

I’m trying to call for only specific columns in my DB but the query is throwing an error saying that the next column is not found (even though it’s not in the query)

Is there a problem in the join that’s doin this? I’m a bit perplexed as to this problem.

JPA Query from ConnectionRequestRepository –

@Query(value = "SELECT connection_request.id, connection_request.userId, connection_request.dateTimeCompleted, connection_request.phoneNumber " +
        "FROM ***.connection_request " +
        "INNER JOIN ***.user " +
        "ON ***.connection_request.userID = ***.user.id " +
        "WHERE connection_request.dateTimeCompleted IS NULL " +
        "LIMIT 1", nativeQuery = true)
ConnectionRequest findActiveRequest();

Service –

public ConnectionRequest getActiveRequestToCaller() {
   return connectionRequestRepository.findActiveRequest();
}

Controller –

ConnectionRequest currentLocationRequest = connectionRequestService.getActiveRequestToCaller();

ConnectionRequest Class –

@Entity
@Table(name = "connection_request")
public class ConnectionRequest {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
@NotNull
private Long id;
@Column(name = "userId")
@NotNull
private Long userId;
@Column(name = "uuid")
@NotNull
private UUID uuid;
@Column(name = "phoneNumber")
@NotNull
private String phoneNumber;
@Column(name = "locationSource")
@NotNull
private String locationSource;
@Column(name = "dateTimeRequested")
private Timestamp dateTimeRequested;
@Column(name = "dateTimeRequestExpires")
private Timestamp dateTimeRequestExpires;
@Column(name = "dateTimeCompleted")
private Timestamp dateTimeCompleted;
@Column(name = "s3Bucket")
private String s3Bucket;
@Column(name = "s3Key")
private String  s3Key;
@Column(name = "s3Etag")
private String s3Etag;
@Column(name = "s3Sha256Hash")
private String s3Sha256Hash;
@Column(name = "isScheduledForCompletion")
@NotNull
private Integer isScheduledForCompletion;

public ConnectionRequest() {}

public ConnectionRequest(Long id,
                         Long userId,
                         UUID uuid,
                         String phoneNumber,
                         String locationSource,
                         Timestamp dateTimeRequested,
                         Timestamp dateTimeRequestExpires,
                         Timestamp dateTimeCompleted,
                         String s3Bucket,
                         String s3Key,
                         String s3Etag,
                         String s3Sha256Hash,
                         Integer isScheduledForCompletion) {
    this.id = id;
    this.userId = userId;
    this.uuid = uuid;
    this.phoneNumber = phoneNumber;
    this.locationSource = locationSource;
    this.dateTimeRequested = dateTimeRequested;
    this.dateTimeRequestExpires = dateTimeRequestExpires;
    this.dateTimeCompleted = dateTimeCompleted;
    this.s3Bucket = s3Bucket;
    this.s3Key = s3Key;
    this.s3Etag = s3Etag;
    this.s3Sha256Hash = s3Sha256Hash;
    this.isScheduledForCompletion = isScheduledForCompletion;
}

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public Long getUserId() {
    return userId;
}

public void setUserId(Long userId) {
    this.userId = userId;
}

public UUID getUuid() {
    return uuid;
}

public void setUuid(UUID uuid) {
    this.uuid = uuid;
}

public String getPhoneNumber() {
    return phoneNumber;
}

public void setPhoneNumber(String phoneNumber) {
    this.phoneNumber = phoneNumber;
}

public String getLocationSource() {
    return locationSource;
}

public void setLocationSource(String locationSource) {
    this.locationSource = locationSource;
}

public Timestamp getDateTimeRequested() {
    return dateTimeRequested;
}

public void setDateTimeRequested(Timestamp dateTimeRequested) {
    this.dateTimeRequested = dateTimeRequested;
}

public Timestamp getDateTimeRequestExpires() {
    return dateTimeRequestExpires;
}

public void setDateTimeRequestExpires(Timestamp dateTimeRequestExpires) {
    this.dateTimeRequestExpires = dateTimeRequestExpires;
}

public Timestamp getDateTimeCompleted() {
    return dateTimeCompleted;
}

public void setDateTimeCompleted(Timestamp dateTimeCompleted) {
    this.dateTimeCompleted = dateTimeCompleted;
}

public String getS3Bucket() {
    return s3Bucket;
}

public void setS3Bucket(String s3Bucket) {
    this.s3Bucket = s3Bucket;
}

public String getS3Key() {
    return s3Key;
}

public void setS3Key(String s3Key) {
    this.s3Key = s3Key;
}

public String getS3Etag() {
    return s3Etag;
}

public void setS3Etag(String s3Etag) {
    this.s3Etag = s3Etag;
}

public String getS3Sha256Hash() {
    return s3Sha256Hash;
}

public void setS3Sha256Hash(String s3Sha256Hash) {
    this.s3Sha256Hash = s3Sha256Hash;
}

public Integer getIsScheduledForCompletion() {
    return isScheduledForCompletion;
}

public void setIsScheduledForCompletion(Integer isScheduledForCompletion) {
    this.isScheduledForCompletion = isScheduledForCompletion;
}

}

Answer

Since you are mapping your native query to entity object, hibernate finds out that is an entity object due to all these annotation and everything, it is checking if this object is in persistence context cache or not.

So, it doesn’t find it and creates one, but then it tries to map the resultset from your query to this entity object but at the time of mapping it doesn’t find all the columns to initialise the entity (notice here column is expected but if value is null it may work).

But, if all columns are provided it is able to map to entity object.

Let me give you this example, suppose I have this Car entity

@Entity
@Table(name = "car")
public class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    @NotBlank(message = "car name`s must be not empty")
    private String name;
    @Column(name = "production_year", nullable = false)
    private LocalDateTime productionYear;
    private boolean tested;
    @ManyToOne
    @JoinColumn(name = "brand_id")
    private Brand brand;
}

and my repository method is this

@Query(value = "select id, name, brand_id, production_year from car", nativeQuery = true)
    List<Car> findAllCars();

Notice that tested is not there, so hibernate issue same error

could not execute query [select id, name, brand_id, production_year from car]

java.sql.SQLException: Column 'tested' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.23.jar:8.0.23]

But if I include tested

 @Query(value = "select id, name, brand_id, production_year, tested from car", nativeQuery = true)
    List<Car> findAllCars();

Hibernate does this

      : Unable to locate native-sql query plan in cache; generating (select id, name, brand_id, production_year, tested from car)
2021-04-09 00:54:02.143 TRACE 40352 --- [nio-8080-exec-2] o.h.loader.custom.sql.SQLCustomQuery     : Starting processing of sql query [select id, name, brand_id, production_year, tested from car]
2021-04-09 00:54:02.198 TRACE 40352 --- [nio-8080-exec-2] o.h.l.c.sql.SQLQueryReturnProcessor      : Mapping alias [alias1] to entity-suffix [0_]
2021-04-09 00:54:02.206 TRACE 40352 --- [nio-8080-exec-2] org.hibernate.internal.SessionImpl       : SQL query: select id, name, brand_id, production_year, tested from car
2021-04-09 00:54:02.223 DEBUG 40352 --- [nio-8080-exec-2] org.hibernate.SQL                        : select id, name, brand_id, production_year, tested from car
Hibernate: select id, name, brand_id, production_year, tested from car
2021-04-09 00:54:02.364 TRACE 40352 --- [nio-8080-exec-2] o.h.r.j.i.ResourceRegistryStandardImpl   : Registering statemen

If you look further down, it will give something like this

 : Object not resolved in any cache: [com.example.stackoverflow.model.Brand#1]
2021-04-09 00:54:02.559 TRACE 40352 --- [nio-8080-exec-2] o.h.p.entity.AbstractEntityPersister     : Fetching entity: [com.example.stackoverflow.model.Brand#1]
2021-04-09 00:54:02.560 DEBUG 40352 --- [nio-8080-exec-2] org.hibernate.SQL                        : select brand0_.id as id1_0_0_, brand0_.name as name2_0_0_, brand0_.production_year as producti3_0_0_ from brand brand0_ where brand0_.id=?
Hibernate: select brand0_.id as id1_0_0_, brand0_.name as name2_0_0_, brand0_.production_year as producti3_0_0_ from brand brand0_ where brand0_.id=?
2021-04-09 00:54:02.562 TRACE 40352 --- [nio-8080-exec-2] o.h.r.j.i.ResourceRegistryStandardImpl   : Registering statement [HikariProxyPreparedStatement

And in this case it successfully converts.

Solution to your problem

So if you want to use entity to be returned, you would have to provide all fields, otherwise you can use interface projections or something this question suggests