Get single row in JPA

Question

How to get single row from entity in JPA?

Table: Employee

    @Id
    private int empId;
    private String empName;
    ...

JPA by default return List. I`m trying to fetch single row.

EmployeeRepository :-

    public Employee findByEmpName(String empName);

Another way is to do it, @Query should be use.

    @Query(value="select e from Employee e where empName = ?1 limit 1", nativeQuery=true)
    public Employee findByEmpName(String empName);

How can i ensure that it return single row and correct result. Any help appreciated.. Thanks in advance.

JPQL doesnt have "LIMIT", as any basic JPA documentation would tell you, and `@Query` and XXXRepository is nothing to do with the JPA API either for that matter, it is Spring Data JPA — Neil Stockton, May 25 '17 at 06:30

score 20 · Answer 1 · answered May 31 '18 at 20:35

20

There is an out of the box solution, in repository you can name method as findFirstBy... or findTopBy..., that should do the thing.

You can find more in Spring reference documentation

answered May 31 '18 at 20:35

milosdju

783
12
27

Zeromus · Accepted Answer · 2017-05-25T07:25:00.853

13

You don't need to "ensure" anything.

If you dont have a collection of sort specified as return (e.g. List<Employee> instead of Employee ) and your query return more than one result it will launch a javax.persistence.NonUniqueResultException: result returns more than one elements.

If your query return more rows and you want only one of those either add a condition to differentiate which one of those rows you actually want or, if they are the same, add a good old distinct

How can i ensure that it return single row and correct result.

That's your job when writing the query

edited May 25 '17 at 07:25

answered May 25 '17 at 07:17

Zeromus

4,472
8
32
40

Doesn't work with @Query and "nativeQuery = true" – Vladislav Ashikhin Nov 25 '21 at 12:38

score 2 · Answer 3 · answered May 25 '17 at 06:30

2

JPA have a method to fetch a single row getSingleResult, but it's recommended to fetch a list and extract the first element over getResultList.

See: Example

answered May 25 '17 at 06:30

fireandfuel

732
1
9
22

Thanks, getSingleResult and getResultList can be used if we are making query and using entitymanager. But here i`m using JPARepository query method. link: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods – Akkave May 25 '17 at 06:54
@Akkave I haven't worked with Spring's JPARepository yet. Please edit your question that you use Spring's JPARepository. – fireandfuel May 26 '17 at 14:27

score 0 · Answer 4 · answered May 25 '17 at 06:28

0

If you want to select only one row from the result set then you can limit number of records by using the query.setMaxResults method:while creating the jpa query.

example : criteria.setMaxResults(25); : it only fetch 25 records out of 100 records.

answered May 25 '17 at 06:28

Lokesh Nandanwar

101
7

1

Thanks for reply. I`m not using JPA Criteria API, I`m using JPARepository. – Akkave May 25 '17 at 06:51
Do you use query dsl to create queries? – Lokesh Nandanwar May 25 '17 at 07:02
see this https://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation?noredirect=1&lq=1 maybe it can help – Lokesh Nandanwar May 25 '17 at 07:04

score -3 · Answer 5 · answered Oct 30 '17 at 01:15

-3

In case someone wants just a single row from a database using JpaRepository (spring-data-jpa), I found this very useful:

repository.findAll().stream().findFirst().orElseThrow(...)

Since this is a stream, I suppose that rows (or rather: Objects) are fetched row by row.

answered Oct 30 '17 at 01:15

Igor

1,582
6
19
49

3

I think this is a very heavy solution as you are fetching all records from the tale first. This is a stream but after you fetch it all from the database. – shashwat Jul 12 '20 at 09:03

Get single row in JPA

5 Answers5