[FIXED] Hiberante Native Query, column not found

Issue

I am trying to print the SQL query result but says column not found,

public class SearchDriver {

    @Autowired
    EntityManager entityManager;

    public void  search(String key, String value){
List<Driver> resultDerivers = entityManager.createNativeQuery("select * from driver where "+key+"="+value).getResultList();

       System.out.println(resultDerivers);

//end of method 

}

Error

Hibernate: select * from driver where age=45

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement   
2021-02-08 18:59:19.967  WARN 11936 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42122, SQLState: 42S22
2021-02-08 18:59:19.967 ERROR 11936 --- [nio-8080-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column "45" not found; SQL statement:
select * from driver where username=driver05 [42122-197]
2021-02-08 18:59:19.983 ERROR 11936 --- [nio-8080-exec-8] com.util.LoggingInterceptor      : method: GET        uri: /v1/drivers/searchBy/username/driver05      status: 200     remoteAddress: 0:0:0:0:0:0:0:1

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
        at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
       
        at com.freenow.service.driver.DefaultDriverService.searchByCriteria(DefaultDriverService.java:126) ~[classes/:na]
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.0.10.RELEASE.jar:5.0.10.RELEASE]

Solution

The String values in sql query must be enclosed by quotes so in your case, your code should check if the value is a string and in this case add ' before and after the add the value to the query. Or to make it cleaner make to different methods one for numerical values and one for String and call one or other depending on the data that you want to use as filter:

    public void  search(String key, Int value){
    List<Driver> resultDerivers = entityManager.createNativeQuery("select * from driver where "+key+"="+value).getResultList();
}

    public void  search(String key, String value){
    List<Driver> resultDerivers = entityManager.createNativeQuery("select * from driver where "+key+"='"+value+"'").getResultList();
}

Answered By - JArgente
Answer Checked By - Clifford M. (JavaFixing Volunteer)