JPA: Using criteriabuilder to find entities: Attribute name different from annotation?

Question

I have a mysql database with employee information, each employee has a technical id as primary key. In MySQL to selcet row(s) matching criteria, i can just use to get the following statement (works)

SELECT * FROM database_test.employee WHERE fist_name='First1';

In Java i can also use this as a native statement to get what i want (works):

        List<EmployeeEntity2> objects = m_em.createNativeQuery(
            "SELECT * database_test.employee WHERE first_name='First1'",
            EmployeeEntity2.class).getResultList();

However, i wanted to use the Criteriabuilder to get the same result and later generalize it for multiple columnName=columnEntry selections.

public List<EmployeeEntity2> testNoParameter() {

    //Based on https://www.objectdb.com/java/jpa/query/criteria

    CriteriaBuilder cb = m_em.getCriteriaBuilder();

    CriteriaQuery<EmployeeEntity2> q = cb.createQuery(EmployeeEntity2.class);
    Root<EmployeeEntity2> c = q.from(EmployeeEntity2.class);
    ParameterExpression<String> p = cb.parameter(String.class);
    //Works
    //q.select(c).where(cb.equal(c.get("firstName"), p));
    //Won't work
    q.select(c).where(cb.equal(c.get("first_name"), p));        
    TypedQuery<EmployeeEntity2> query = m_em.createQuery(q);

    query.setParameter(p, "First1");
    List<EmployeeEntity2> results = query.getResultList();
    return results;
}

Using "fist_name" - the column name annotation from the Entity - will yield the following java.lang.IllegalArgumentException with:

    Unable to locate Attribute  with the the given name [first_name] on this ManagedType [xx.xxx.database.EmployeeEntity2]

EmployeeEntity2 has "fist_name" annotation:

@Column(name = "first_name", nullable = false)
@Override
public String getFirstName() {
    return super.getFirstName();
}

So "first_name" should exist, however (with some debugging) i found out that the attribute expected is for some reason "firstName" instead - which i have not defined/annotated - so where does it come from - and how can i use the column names actually defined in the database (column = "first_name")?

Answer 1

You should use property name of entity (not column name) to use it in criteria builder so instead of

 q.select(c).where(cb.equal(c.get("first_name"), p));  

use

 q.select(c).where(cb.equal(c.get("firstName"), p));  

CriteriaBuilder is RDBMS schema agnostic, so you use your model (entities), not schema (table names etc).

In JPA you dont normally use SQL but JPQL. Equivalent of your SQL in JPQL would be something like

"SELECT e FROM EmployeEntity2 e WHERE e.firstName='First1'"

Both CriteriaQuery tree and JPQL string are transformed down to the same query tree later on (can't remember the name), so they both must comply to the very same rules.