How to get a specific "row" from a DB using JPA (EclipseLink) when the primary key is Auto Incremented?

Question

Say I have this table

CREATE TABLE person(
    person_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    x_cordinates INT,
    y_cordinates INT
);

In the past I have used

Person person = EntityManager.find(Person.class, primaryKey);

But the primary key in this instance is auto incremented, is it possible to get a row back using the other colums such as first_name and last_name to get the primary key along with the other values ?

Answer 1

You can create a NamedQuerry like that :

@NamedQuery(name="person.findAll", query="SELECT p FROM Person p WHERE like :first_name") 

And can assign a value to "first_name" like that :

query.setParamter("fist_name", "%name%");

You can read this documentation.

Answer 2

Use method createQuery or createNativeQuery of entityManager.

With createQuery you have to use JPQL syntax and with createNativeQuery, you've to use the standard SQL syntax.

For example :

Query query = EntityManager.createQuery("select * from person p where p.first_name = :fname");
17.
query.setParameter("fname", firstName);
Person p = query.getSingleResult();