Spring data JPA request behaves unexpectedly
I have the following objects:
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 the method to findFirstByOrderIdOrderByLastModifiedDesc
return the first row from the table Order_status_sas
where order.id = <some_id>
sorted by field lastModified
, but in the 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 a single string, but it does return a list of strings. It seems that Spring Data is not looking at the word First
in my method name. Also, I am getting 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 am I doing wrong and how can I achieve my goal?
Revision:
I edited mine StatusesWareHouseRepository
with a 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 didn't change. It looks like this:
select ...
from order_status_sas s
where s.order_id=?
order by s.last_modified desc
source to share
Ok I got it @PriduNeemre point. Let's leave the DB model and go back to the JPA question. Here's another example:
@Entity
public class Client {
....
}
public interface ClientRepository extends CrudRepository<Client, Integer> {
Client findFirstByOrderByNameDesc();
}
The hibernate request still looks like this:
select ...
from clients c
order by c.name desc
source to share
Have you tried adding an annotation @Query
(see here ) on top of your method findFirstByOrderIdOrderByLastModifiedDesc(..)
to specify the expected behavior manually? A (unrelated) example of how this might 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 body of the annotation is actually JPQL , not SQL. For more examples in annotation, @Query
see Spring Data Docs here .
PS: I also have conflicting feelings regarding the structure of the domain object, that is, does the instance really Order_sas
need to be stored in the instance Order_status_sas
- isn't it? Typically you want to store the referenced entities in your main domain entity, not the other way around. (There is a slight chance that I just don't understand this.)
EDIT: I would even say that given your current domain model, Hibernate does everything right, except for the lack of a LIMIT 1 clause to limit the expected result set to a single row. The query itself is highly inefficient and can be improved by fixing your skewed domain model.
source to share