why my mysql query is not working in JPA?

Question

@Query(value = "select * from employee where ? = ?", nativeQuery = true)
    public List<Employee> getEmployees(String key, String value);

The Structure of Employee is

Id

Age

Name

when i call getEmployees with("Age",21) im getting List with zero length. in the console the query is displayed as

select * from employee where ? = ?

Are you sure you can have the column name as a parameter as well and not just the value? If it is ok then the next question is if the age value you send will get implicitly converted to the data type of the Age column, String to int? — Joakim Danielson, Mar 12 '19 at 07:06
I am not an expert on JPA but can't you see the actual SQL query (after ? has been replaced with values) in some log? — Joakim Danielson, Mar 12 '19 at 07:14
select * from employee where ? = ? this is what I got in the log, the passed values didn't replace the '?' placeholder — Madhusudhan Aradya, Mar 12 '19 at 07:17
Maybe you could put in [an effort](https://stackoverflow.com/questions/4362876/how-to-view-the-sql-queries-issued-by-jpa) — Joakim Danielson, Mar 12 '19 at 07:20
in the SQL layer both are considered as values, I think this wont work like this, prepared statements in SQL can do the job for you. — Adarsh Gangadharan, Mar 12 '19 at 07:28

score 1 · Accepted Answer · answered Mar 12 '19 at 07:30

1

Use JPQL for custom Query

public List getEmployees(String key, String value) 
{

    Query query = (Query) entityManager.createQuery("from Employee where " + key + " = " + value);
    return query.getResultList();
}

answered Mar 12 '19 at 07:30

ArunKumar M N

1,256
1
10
22

1

Autowire the EntityManager. – ArunKumar M N Mar 12 '19 at 12:56

score 0 · Answer 2 · answered Mar 12 '19 at 09:01

0

Use JPQL, so the query will look something like this.

@Query(value = "select * from employee where :key = :value", nativeQuery = true)
public List < Employee > getEmployees(@Param("key") String key, @Param("value") String value);

answered Mar 12 '19 at 09:01

Santhosh A

31
1
1
6

Didn't work for me Still, it is showing "select * from employee where ? = ?" – Madhusudhan Aradya Mar 12 '19 at 09:40
Hibernate uses prepared statements so you can't see the full SQL. You can set the log level for org.hibernate.type to trace to see the values bound to the parameters. So that's the reason you see '?'. See this link for better understanding [https://stackoverflow.com/questions/2536829/hibernate-show-real-sql/2536835#2536835] Try not passing the key and manually pass the key: `@Query(value = "select * from employee where age = :value", nativeQuery = true) public List < Employee > getEmployees(@Param("key") String key, @Param("value") String value);` – Santhosh A Mar 12 '19 at 09:53
The key and value will be dynamic, the user may enter **id=1** or **name=john** or **age=23** so, "select * from employee where age = :value", nativeQuery = true" this will return employee record based only on age. – Madhusudhan Aradya Mar 12 '19 at 13:03

why my mysql query is not working in JPA?

2 Answers2