JPQL, Hibernate Syntax Error while querying the database

Question

I have these classes:

public class UserCredential {

   @NotNull(message ="The password must not be null !")
   @NotEmpty(message="The password must not be empty !")
   private String password;

   @NotNull(message ="The email/name must not be null !")
   @NotEmpty(message="The email/name must not be empty !")
   private String email;
     //GETTERS AND SETTERS
}

and my real Entity is like this :

@Entity
public class BlogUser {

    @Id
    @GeneratedValue
    private Long id;

    private String name;
    private String email;
    private String password;
    private String website;

    //GETTERS AND SETTERS
}

I use Spring and In my repository tier I have a method like this :

public boolean isValidLogin(UserCredential userCredential) {
    List <BlogUser> list  = em.createQuery("SELECT c FROM BlogUser c WHERE c.email = " + userCredential.getEmail()+ " AND c.password = "    + userCredential.getPassword()).getResultList();

    if (list.size() == 1) {
        return true;
    } else {
        return false;
    }
} 

but when my program is going to execute the query I get this error:

HTTP Status 500 - Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

type Exception report

message Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:948)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:180)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

root cause

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
    org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:277)
    com.sts.repository.UserRepositoryImpl.isValidLogin(UserRepositoryImpl.java:52)
    com.sts.service.UserServiceImpl.isValidLogin(UserServiceImpl.java:37)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
    com.sun.proxy.$Proxy44.isValidLogin(Unknown Source)
    com.sts.controller.Welcome.loginUser(Welcome.java:53)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:180)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

root cause

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
    org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    org.hibernate.loader.Loader.doQuery(Loader.java:900)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    org.hibernate.loader.Loader.doList(Loader.java:2526)
    org.hibernate.loader.Loader.doList(Loader.java:2512)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    org.hibernate.loader.Loader.list(Loader.java:2337)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:356)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1269)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
    com.sts.repository.UserRepositoryImpl.isValidLogin(UserRepositoryImpl.java:52)
    com.sts.service.UserServiceImpl.isValidLogin(UserServiceImpl.java:37)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
    com.sun.proxy.$Proxy44.isValidLogin(Unknown Source)
    com.sts.controller.Welcome.loginUser(Welcome.java:53)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:180)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

root cause

org.postgresql.util.PSQLException: ERROR: column "mehdi" does not exist
  Position: 286
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
    org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
    org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
    org.hibernate.loader.Loader.doQuery(Loader.java:900)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
    org.hibernate.loader.Loader.doList(Loader.java:2526)
    org.hibernate.loader.Loader.doList(Loader.java:2512)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
    org.hibernate.loader.Loader.list(Loader.java:2337)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:356)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1269)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
    com.sts.repository.UserRepositoryImpl.isValidLogin(UserRepositoryImpl.java:52)
    com.sts.service.UserServiceImpl.isValidLogin(UserServiceImpl.java:37)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
    com.sun.proxy.$Proxy44.isValidLogin(Unknown Source)
    com.sts.controller.Welcome.loginUser(Welcome.java:53)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:180)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

This is the my example query trace:

    Hibernate: 
select
    tuser0_.id as id1_1_,
    tuser0_.email as email2_1_,
    tuser0_.name as name3_1_,
    tuser0_.password as password4_1_,
    tuser0_.website as website6_1_ 
from
    BlogUser tuser0_ 
where
    tuser0_.email=mehdi
    and tuser0_.password=123456

you think, what is the problem ???

Answer 1

When building your query without parameters (it's a bad idea) you need to take care of quote and type convertion etc...

List <BlogUser> list  = em.createQuery("SELECT c FROM BlogUser c WHERE c.email = '" + userCredential.getEmail()+ "' AND c.password = '"    + userCredential.getPassword()+"'").getResultList();

A better solution is to use parameters :

List <BlogUser> list  = em.createQuery("SELECT c FROM BlogUser c WHERE c.email = :email AND c.password = :pwd")
    .setParameter("email",userCredential.getEmail())
    .setParameter("pwd",userCredential.getPassword())
    .getResultList();