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?

Question

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)

Jens Schauder · Accepted Answer · 2020-02-07T06:41:32.130

3

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.

edited Feb 07 '20 at 06:41

answered Mar 14 '19 at 06:52

Jens Schauder

77,657
34
181
348

I think I really liked the 3rd option. Helps me to keep my queries isolated and still make use of spring's EntityRowMapper. Didn't really like the option 1 for 2 reasons. 1. Makes it more MSSQL dependent and moving away from ANSI standards, so makes it real hard to change DB. 2. The entire object with NULL values (unnecessary) are serialized over wire, which adds additional overhead. Very helpful. Thank you! – Seetha Mar 14 '19 at 14:00
We do see the document suggesting us to have {@AllArgsConstructor} as part of best practices for better performance(will use method handlers). If not this will end up using reflection and cost a bit more. But when you have a BaseModel class and have your domain model inherit that base class, the {@AllArgsConstructor} from lombok wont include all the parameters from your base class. Any suggestions for this? – Seetha Mar 14 '19 at 14:05
1

I won't worry too much about that performance. It is 10% of the object creation/property setting which should be dwarfed by actually accessing the database. – Jens Schauder Mar 14 '19 at 15:08
2

I also discussed this with a coworker it looks we might actually change that behaviour to use `NULL` as a default. If you want to create an issue for that go ahead. – Jens Schauder Mar 14 '19 at 15:10
1

Thanks a lot. created this jira issue - https://jira.spring.io/browse/DATAJDBC-341 – Seetha Mar 14 '19 at 18:28

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?

1 Answers1