Spring Data JDBC org.springframework.data.mapping.MappingException: Could not read value rental_movie from result set

Question

How to select query filter in one-to-one relationship with Spring Data JDBC ?

Schema looks like this, basically 2 tables where Rental references Movie

drop table if exists rental;
drop table if exists movie;

create table movie
(
    id          serial primary key,
    title       text,
    description text
);

create table rental
(
    movie    integer primary key references movie (id),
    duration text,
    price    integer
)

And my code looks like this

@Query("select * from movie where title = :title ")
fun findByTitle(@Param("title") title: String): List<Movie>

But got an exception org.springframework.data.mapping.MappingException: Could not read value rental_movie from result set!

The example project on GitHub.

P.S I am quite new to this and followed this video to learn basics, please help me to do it in proper way

Solution # 1

Use the @Query like this, but still not so good since there can be a lot of columns inside second table

SELECT movie.*,
       rental.price    AS rental_price,
       rental.duration AS rental_duration,
       rental.movie    AS rental_movie
FROM movie
         LEFT OUTER JOIN rental ON rental.movie = movie.id
where movie.title = 'Matrix'

Answer 1

Your solution #1 is currently the correct way to do this.

Query must return columns for all simple properties of the aggregate root, and for all embedded or referenced entities.

If you don't want to do that you can alway specify your own RowMapper or ResultSetExtractor

Let's assume the following classes (similar to those you probably have):

class Movie {

    @Id Long id;
    String title;

    @Embedded
    Actor with;

    Rental rental;
}

class Actor {
    String name;
}

class Rental {
    Integer price;
}

Your select needs to return the following columns:

1. id for the id property of Movie

2. title for the title property of Movie

3. rental_price for the price property of Rental. Note the prefix rental comes from the property namerentalnot from the class nameRental`.

4. rental_movie this column is an artificial id for Rental used to determine if there is a Rental at all or if Movie.rental is null. The value is irrelevant except for the fact if it is null or not. This column is not required if Rental has an id column.

5. name for the property name of the class Actor.

   Note: There is no prefix here. If you want a prefix, e.g. because a class is embedded multiple times you have to put that in the @Embedded annotation.

   _Note #1: There is no artificial id here. For embedded classes there is the onEmpty attribute of the @Embedded annotation to control if when all properties are null, if the whole embedded class is null or if the embedded class gets instantiated with all properties set to null.

With Spring Data JDBC 1.x a missing column causes an exception as you have seen.

From Version 2.0 a missing column will be silently ignored and the property not set.

There is an issue to provide an easier way to define by just defining the where clause: https://jira.spring.io/browse/DATAJDBC-225

Answer 2

data class Movie(
        @Id
        val id: Long?,
        val title: String,
        val description: String,
        val rental: Rental
)

I don't use Kotlin, but I think if you want to query Movie and Rental together, you have to use @OneToOne annotation. Something like(java):

public class Movie {
@OneToOne(mappedBy = "moive", cascade = CascadeType.ALL,
              fetch = FetchType.LAZY, optional = false)
private Rental rental;
}

And your Rental class is not right, need movieId.

public class Rental {

   ...
   @OneToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = "movie_id")
   private Movie movie;

}

you could check some code example.