No entity found for query exception even there is a row for that query

Question

I wrote simple program for login a user.I can get all results from table and it works correctly , but when I want to check if a user with name and password exists always throw no entity found for query exception and I know there is a row in table with that name and password. this is my code :

public List<Person> getList(){
    return entityManager.createQuery("select entity from person entity").getResultList();
}
public Person logIn(String name,String password){
      String selectQuery = "select entity from person entity where name=:name and password=:password";
      Person person = (Person)entityManager.createQuery(selectQuery)
              .setParameter("name",name)
              .setParameter("password",password)
              .getSingleResult();

      System.out.println(person);
      return person;
}

this is my entity class for Person

@Entity(name = "person")
    @Table(name = "PERSON")
public class Person {
    @Id
    @Column(name = "ID",columnDefinition = "NUMBER")
    @SequenceGenerator(name = "PERSON_SEQ",sequenceName = "PERSON_SEQ",allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO,generator = "PERSON_SEQ")
    private Long id;
    @Column(name = "NAME",columnDefinition = "NVARCHAR2(30)")
    @NotNull(errorCode = "Null",message = "Name can't be null")
    @NotEmpty(errorCode = "Empty",message = "Name can't be empty")

    private String name;

    @Column(name = "FAMILY",columnDefinition = "NVARCHAR2(30)")
    @NotNull(errorCode = "Null",message = "Family can't be null")
    @NotEmpty(errorCode = "Empty",message = "Family can't be empty")
    private String family;

    @Column(name = "Password",columnDefinition = "VARCHAR2(256)")
    @NotNull(errorCode = "Null",message = "Family can't be null")
    @NotEmpty(errorCode = "Empty",message = "Family can't be empty")
    private String password; /*setter and getter methods....*/

mariuss · Accepted Answer · 2018-10-04 19:02:57Z

2

The method getSingleResult() may throw NonUniqueResultException, if there are multiple rows. So unless you have unique constraints in your table this might occur in the future.

Also make sure to use the try/catch block to know if you have a result or not:

      String selectQuery = "select entity from person entity where name=:name and password=:password";
      Person person = null;

      try{
       person = (Person)entityManager.createQuery(selectQuery)
              .setParameter("name",name)
              .setParameter("password",password)
              .getSingleResult();
      }
      catch(NoResultException e){
        //TO-DO do something if query retrieves nothing
      }       

      System.out.println(person);
      return person;

If not you can always use the above method getResultList() and just call get(0) for the first result.

If you really think the query returns a row, check your sql statement again.

EDIT :

You could give it a try with a native query

    String selectQuery = "SELECT * FROM PERSON where name = ?1 and password = ?2";

    Query q = em.createNativeQuery(selectQuery);
    q.setParameter(1, name);
    q.setParameter(2, password);

    person = (Person) q.getSingleResult();

edited Oct 4, 2018 at 19:02

answered Oct 4, 2018 at 14:57

mariuss

1,2572 gold badges16 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

saba safavi Over a year ago

I handle this exception in my controller.I have this problem for example there is record in table that name is saba and password is 1234 but I get no entity found when I use login method with these parameters

mariuss Over a year ago

check the table name, check if the parameters's values are correct(debug). also shouldn't the query be like :'select p from person p where p.name=:name and p.password=:password' ?

saba safavi Over a year ago

I edit my query like yours and remove try catch to see complete log ,this exception happened : SQLGrammarException: ORA-00904: "ENTITY": invalid identifier

mariuss Over a year ago

@sabasafavi can you post the table schema and entity class in your question ?

mariuss Over a year ago

maybe you could try with a native query @sabasafavi , see above.

Guilherme Alencar · Accepted Answer · 2018-10-04 15:53:10Z

1

I believe Person has to be camelcase as follows:

select entity from Person entity

edited Oct 4, 2018 at 15:53

answered Oct 4, 2018 at 14:47

Guilherme Alencar

1,44313 silver badges21 bronze badges

Collectives™ on Stack Overflow

No entity found for query exception even there is a row for that query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related