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();
    }


} 

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();

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