How should I pass values into JPQL query?

I’m working on test, that will check if object had been saved into database. Test will save object with unique values and after that I want to fetch this object from database and check if it is not null. Id is auto generated, so I need to find this object by name.

My test looks like this:

@Test
public void shouldAddNewEmployee(){

    String firstNameTest = "Jon";
    String lastNameTest = "Doe";
    double salary1test = 10.0;
    String salary2test = "10.00";
    String localityTest = "LosAngeles";
    String zipCodeTest = "00-000";
    String streetTest = "LosAngeles str.";
    int streetNumberTest = 10;

    Employee testEmployee = new Employee(
            firstNameTest,
            lastNameTest,
            salary1test,
            salary2test,
            localityTest,
            zipCodeTest,
            streetTest,
            streetNumberTest);

    EmployeeRepository.addNewEmployeeFromCLI(testEmployee);

    String customQueryFindJonDoe = "select e from Employee e where e.firstName = Jon and e.lastName = Doe";
    EntityManager entityManager = JPAUtils.openEntityManager();

    List<Employee> foundJonDoe = entityManager.createQuery(customQueryFindJonDoe).getResultList();

    Assertions.assertNotEquals(null, foundJonDoe);

    if (entityManager.isOpen()) {
        entityManager.close();
    }


}

​x
 
@Testpublic void shouldAddNewEmployee(){​    String firstNameTest = "Jon";    String lastNameTest = "Doe";    double salary1test = 10.0;    String salary2test = "10.00";    String localityTest = "LosAngeles";    String zipCodeTest = "00-000";    String streetTest = "LosAngeles str.";    int streetNumberTest = 10;​    Employee testEmployee = new Employee(            firstNameTest,            lastNameTest,            salary1test,            salary2test,            localityTest,            zipCodeTest,            streetTest,            streetNumberTest);​    EmployeeRepository.addNewEmployeeFromCLI(testEmployee);​    String customQueryFindJonDoe = "select e from Employee e where e.firstName = Jon and e.lastName = Doe";    EntityManager entityManager = JPAUtils.openEntityManager();​    List<Employee> foundJonDoe = entityManager.createQuery(customQueryFindJonDoe).getResultList();​    Assertions.assertNotEquals(null, foundJonDoe);​    if (entityManager.isOpen()) {        entityManager.close();    }​​} ​

after running it I get error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘Jon’ in ‘where clause’

How should this query look like?

Answer

You have to set the parameters on the query:

String customQueryFindJonDoe = 
       "select e from Employee e where e.firstName = :firstname and e.lastName = :lastName";
EntityManager entityManager = JPAUtils.openEntityManager();

TypedQuery<Employee> q = entityManager.createQuery(customQueryFindJonDoe, Employee.class);
q.setParameter("firstname", "Jon");
q.setParameter("lastName", "Doe");

List<Employee> foundJonDoe = q.getResultList();

 
String customQueryFindJonDoe =        "select e from Employee e where e.firstName = :firstname and e.lastName = :lastName";EntityManager entityManager = JPAUtils.openEntityManager();​TypedQuery<Employee> q = entityManager.createQuery(customQueryFindJonDoe, Employee.class);q.setParameter("firstname", "Jon");q.setParameter("lastName", "Doe");​List<Employee> foundJonDoe = q.getResultList();​

More about querying you can find here:

https://docs.oracle.com/javaee/7/tutorial/persistence-querylanguage002.htm#BNBRG https://www.objectdb.com/java/jpa/query

Or you buy a book https://www.apress.com/de/book/9781484234198

Advertisement

Answer