CODEWITHSUNDEEP
javaspring-data-jpa
polis
polis

Reputation: 805

Spring data JPA query behaves unexpectedly

I have these entities:

public class Order_status_sas {
   private Order_sas order;
   private Date lastModified;
   ...
}

public class Order_sas {
   private long id;
   ...
}

My CrudRepository:

public interface StatusesWareHouseRepository extends CrudRepository<Order_status_sas, Long> {
   Order_status_sas findFirstByOrderIdOrderByLastModifiedDesc(long id);
}

I expect that method findFirstByOrderIdOrderByLastModifiedDesc would return first row from table Order_status_sas, where order.id = <some_id> sorted by field lastModified, but in log I see this query:

Hibernate: select ... 
       from order_status_sas a 
          left outer join orders_sas b 
            on a.order_id=b.id 
       where b.id=? 
       order by a.last_modified desc

This query does not return me one row, but returns a list of rows. It seems that Spring Data do not look at word First in my method name. Also, I get an Exception:

org.springframework.dao.IncorrectResultSizeDataAccessException: 
result returns more than one elements; nested exception is javax.persistence.NonUniqueResultException: result returns more than one elements

Please, tell me what I am doing wrong and how can I achieve my purpose?

EDITED: I edited my StatusesWareHouseRepository with custom query:

@Query("select s from Order_status_sas s where s.order.id = ?1 order by s.lastModified desc limit 1")
Order_status_sas findFirstByOrderIdOrderByLastModifiedDesc(long id);

but the query, executed by Hibernate, haven't changed. It looks like this:

select ... 
from order_status_sas s 
where s.order_id=? 
order by s.last_modified desc

Upvotes: 3

Views: 7283

Answers (2)

polis
polis

Reputation: 805

OK, I understood @PriduNeemre point. Lets leave the DB model and come back to the JPA question. Here is another example:

@Entity
public class Client {
   ....
}

public interface ClientRepository extends CrudRepository<Client, Integer> {

Client findFirstByOrderByNameDesc();
}

Hibernate query still looks like this:

select ... 
from clients c 
order by c.name desc

Upvotes: 2

Priidu Neemre
Priidu Neemre

Reputation: 3062

Have you tried adding a @Query annotation (see here) on top of your findFirstByOrderIdOrderByLastModifiedDesc(..) method to specify the expected behaviour by hand? A (non-related) example on how this could work:

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {

    @Query("SELECT I FROM Invoice I JOIN I.customer C JOIN C.user U WHERE 
        U.username = :username")
    public List<Invoice> findInvoicesByUsername(@Param("username")
        String username);
}

Note that the query language used in the annotation body is in fact JPQL, not SQL. For more examples on the @Query annotation, see the Spring Data docs here.

PS: I'm also having conflicted feelings about your domain object structure, i.e. whether an instance of Order_sas should really be stored in an instance of Order_status_sas - shouldn't it be the other way around? Normally you would want to store the reference objects in your main domain object, not vice versa. (There's a slight possibility that I'm just not getting it right, though.)

EDIT: I would even go as far as to say that considering your current domain model, Hibernate is doing everything right except missing a LIMIT 1 clause to limit the expected resultset to one single row. The query itself is extremely inefficient, though, and could be improved by fixing your skewed domain model.

Upvotes: 0

Related Questions