How to query more than one columns but not all columns with @Query but still use the Domain Data Model to map with Spring Data JDBC?

My Data model is

 @Getter
 @Setter
 public class Customer {
   @Id private ID id;
   @CreatedDate protected Instant createdAt;
   @LastModifiedDate protected Instant updatedAt;
   @CreatedBy protected String createdBy;
   @LastModifiedBy protected String updatedBy;
   @Version protected Long version;
   private UUID orderId;
   private String offer;
}

My Repository is

public interface CustomerRepository extends CrudRepository<Customer, UUID> {

@Query(
        "SELECT ID, Offer  FROM Customer WHERE orderId = :orderId ")
List<Customer> findCustomerByOrderId(
        @Param("orderId") UUID orderId);
}

This will result in an exception saying 'orderId column not found [42122-190]'. So Spring expects you to always query all the columns. I understand that with JPA we have a strong mapping between the Entities and the Data Schema. But the whole point of spring data JDBC is avoiding the tight coupling between POJO's data model and database schema. Why not the EntityRowMapper is just mapping NULL to the properties which are not part of the query?

Is there a way to tell the RowMapper used, to ignore properties which are not part of the query? Creating separate RowMapper for these simple queries seems a lot of unnecessary work.

I still can work around this by changing the query like

@Query(
        "SELECT ID, Offer, OrderId, null as CreatedAt, null as CreatedBy, null as UpdatedAt, null as UpdatedBy, null as Version  FROM Customer  WHERE orderId = :orderId ")

But this will still serialize the entire object with null values. Am I missing something obvious here?

Note This is not Spring Data JPA. Its Spring Data JDBC.

Edit Looking more into it, the exception is from h2 database lib.

Caused by: org.h2.jdbc.JdbcSQLException: Column "orderid" not found [42122-190]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3129)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3217)
at org.h2.jdbc.JdbcResultSet.getObject(JdbcResultSet.java:522)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:127)

Solution

You can't at least right now.

There are three solutions to this, two of which you already pointed out:

extend your select statement with , NULL as <column-name> for all the missing columns.

I'm not sure if

But this will still serialize the entire object with null values.

means that this isn't working for you in some way.

specify a RowMapper.
You could use a class containing exactly the fields returned by the query. It could even have getters for the other columns if you want an interface implemented by both your normal entity and the partial entity.

You write:

But the whole point of spring data JDBC is to avoid the tight coupling between pojo's data model and database schema.

This is not quite right. An important goal of Spring Data JDBC is to not have a run time connection between entities and table rows. This would require proxies or similar and brings a lot of complexity. But the structural mapping between entities and table is probably going to be stronger (and certainly is right now) since all the variants of mappings available in JPA bring complexity. And the main goal in Spring Data JDBC is to be conceptually simpler than JPA.

You also ask

Why not the EntityRowMapper is just mapping NULL to the properties which are not part of the query?

I'm not sure if I actively thought about it when I coded it but I don't like the idea of defaulting to NULL because this would make it easy to accidentally not load a column because you have a typo in an alias.

But I'm not against alternative solutions. If you have an idea please create a feature request.