How to write a custom @Query in Spring Data JDBC?

Question

In Spring Data JDBC examples, how do I write a simple query in @Query annotation?

e.g. In LegoSet Repository, how do I add a simple findByName query?

When I tried

@Query("select * from lego_set where name = :name") List<LegoSet> findByName(@Param("name") String name); it throws following error:

org.springframework.data.mapping.MappingException: Could not read property @org.springframework.data.annotation.Id() @org.springframework.data.relational.core.mapping.Column(value=handbuch_id, keyColumn=)private java.lang.Long example.springdata.jdbc.basics.aggregate.Manual.id from result set!
...

> Caused by: org.hsqldb.HsqlException: Column not found: manual_handbuch_idat org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) `

Also, the reference document seems to be copied from some generic spring data document since it mentioned derived query which doesn't exist in spring data jdbc yet.

You probably need to set nativeQuery=true. Refer https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-creating-database-queries-with-the-query-annotation/ — user1211, Oct 24 '18 at 22:28
@user1211there is no `nativeQuery` attribute in Spring Data JDBCs `@Query` annotation since all queries are to provided in the native SQL dialect of the used database. — Jens Schauder, Oct 25 '18 at 05:23

score 3 · Answer 1 · answered Nov 26 '19 at 11:24

Just as a completion of @jens-schauder's answer:

The query should be:

    @Query("SELECT ls.id, ls.name, ls.min_age, ls.max_age, " +
            "h.handbuch_id AS manual_handbuch_id, h.author AS manual_author, h.text AS manual_text " +
            "FROM lego_set ls JOIN handbuch h ON ls.id = h.handbuch_id " +
            "WHERE name = :name")
    List<LegoSet> findByName(@Param("name") String name);

Using this method the following test passes:

    @Test
    public void so_52978700() {
        // prepare
        LegoSet cars = createLegoSet("Small Car - 01", 5, 10);
        cars.setManual(new Manual("Just put all the pieces together in the right order", "Jens Schauder"));
        repository.save(cars);

        // execute
        List<LegoSet> actual = repository.findByName("Small Car - 01");

        // verify
        assertThat(actual).hasSize(1);
        assertThat(actual.get(0).getName()).isEqualTo("Small Car - 01");
        assertThat(actual.get(0).getManual().getText()).isEqualTo("Just put all the pieces together in the right order");
    }

score 1 · Answer 2 · answered Oct 25 '18 at 05:37

1

The LegoSet entity has a 1:1 relationship to a Manual. Spring Data JDBC selects such a construct using a join and expects the representative columns in the ResultSet.

Note that it expects the columns representing the Manual entity itself plus the one forming the back-reference to the LegoSet. Also, all column names are to be prefixed by the property name +_, i.e. manual_ in this case.

The error message actually tells you about the missing column (modulo a missing space): Column not found: manual_handbuch_id.

Alternatively, you can also provide your own RowMapper

Regarding the documentation:

You are kind of right. The documentation of (almost) all Spring Data modules includes a generic part which easily leads to confusion. There is a ticket for comming up with a better solution.

answered Oct 25 '18 at 05:37

Jens Schauder

77,657
34
181
348

From the log, I can see the native query for default findAll() is similar to what you mentioned, and I replaced my query with that plus where clause, that worked. But was not sure about naming convention such as manual_handbuch_id. Also noticed for 1 to many, it does it in a separate query. Was hoping it works on 1 on 1 as well. For more complicated cases where the 1 on 1 child class has its own 1 on 1 child, etc.., then writing SQL will be non-trivial and error-prone. – Kevin Z Oct 25 '18 at 15:32
I agree. If you have an idea how to make that less painful, please create a ticket. In the meantime, I guess my expectation is for people to use custom RowMappers for non-trivial stuff a lot. – Jens Schauder Oct 25 '18 at 15:41
How difficult to treat 1 to 1 same as 1 to many in internal implementation? If it is just for performance reason, at least provide this as an option would be nice. Another approach is to provide derived queries as other Spring Data which should cover more than 90% of our cases. I remembered that you mentioned it is on the way in your seminars? Hopefully can see that soon. Thanks. – Kevin Z Oct 25 '18 at 15:59
Joining for 1:many will happen. Same for query derivation. I was thinking about making the writing of custom queries easier. – Jens Schauder Oct 25 '18 at 16:07
Guess I don't fully understand the differences btw Manual and Model in the example. They both are part of the aggregate, why in the \@Query I have to specifically deal with Manual but not Model? Ideally I don't need to worry about children objects in the \@Query. – Kevin Z Oct 25 '18 at 19:45
The technical reason is that one can join as many one-to-one relationships to the aggregate root. But with one-to-many currently, none gets joined (expected) since when you have more than one such relationship you have to pick one to join. Otherwise, you'll end up with a cross product with an exploding cardinality. – Jens Schauder Oct 26 '18 at 04:33
Does spring data jdbc support custom modifiable queries, e.g. a custom insert query? I get `A result was returned when none was expected.` – Tobi Akinyemi Oct 16 '20 at 09:10
@TobiAkinyemi Yes, it does: https://github.com/spring-projects/spring-data-jdbc/blob/9658ac166825528ba90e009e6bb193f8fc8fa005/spring-data-jdbc/src/test/java/org/springframework/data/jdbc/repository/query/QueryAnnotationHsqlIntegrationTests.java#L309 – Jens Schauder Oct 16 '20 at 12:02
I've seen updates working fine, but not inserts (returning id). This worked fine with JPA – Tobi Akinyemi Oct 16 '20 at 12:50
@TobiAkinyemi returning an id is probably the problem. Care to create an issue? https://jira.spring.io/browse/DATAJDBC – Jens Schauder Oct 16 '20 at 14:19
@JensSchauder Just to clarify, is it not possible with spring data jdbc to do this insert and obtain the id? – Tobi Akinyemi Oct 16 '20 at 14:50
@TobiAkinyemi currently not with query annotation. The standard save does that. Or you can write a custom method for it. – Jens Schauder Oct 16 '20 at 17:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/223174/discussion-between-jens-schauder-and-tobi-akinyemi). – Jens Schauder Oct 16 '20 at 17:04

score 0 · Answer 3 · answered Oct 25 '18 at 06:06

0

I think you are trying to execute a native query.So,try as below

@Query(  value = "SELECT * FROM lego_set ls where ls.name = :name",
           nativeQuery = true)
  List<LegoSet> findByName(@Param("name") String name);

This should work.

answered Oct 25 '18 at 06:06

Abdullah Al Mamun

351
3
4
15

3

This is referring to spring data jdbc, not just spring data jpa so nativeQuery won't work. – Kevin Z Oct 25 '18 at 15:19

How to write a custom @Query in Spring Data JDBC?

3 Answers3