I have very simple Entity class and two native query in it .
QUERY1 is run right and return PersonEntity , but the QUERY2 return exception (invalid column name !!! but my column name is exactly this) ;
MY ENTITY CLASS :
@Entity
@Table (name="PERSON")
@NamedNativeQueries({
@NamedNativeQuery(
name = "QUERY1",
query = "SELECT * FROM PERSON " , resultClass = PersonEntity.class
)
,
@NamedNativeQuery(name ="QUERY2" ,
query = "SELECT FIRSTNAME FROM PERSON " , resultClass = PersonEntity.class
)
})
public class PersonEntity implements Serializable
{
private static final long serialVersionUID = 1L;
public PersonEntity() {}
@Id
@Column(name="ID" , columnDefinition="NUMBER")
@SequenceGenerator(name="MySeq" , sequenceName="MYGEN")
@GeneratedValue(strategy=GenerationType.AUTO , generator="MySeq")
private Integer id;
@Basic
@Column(name="FIRSTNAME" , columnDefinition="NVARCHAR2(20)")
private String firstName;
@Basic
@Column(name="LASTNAME" , columnDefinition="NVARCHAR2(20)")
private String lastName;
@Basic
@Column(name="EMAIL" , columnDefinition="NVARCHAR2(20)")
private String email;
DAO CLASS :
@Repository
public class PersonDaoImpl implements PersonDao {
@PersistenceContext
private EntityManager manager;
@Transactional
public void selectPersons(String name) {
//THIS IS RIGHT
Query query = manager.createNamedQuery("QUERY1");
PersonEntity personEntity= (PersonEntity) query.getSingleResult();
System.out.println(personEntity.getFirstName());
System.out.println(personEntity.getEmail());
System.out.println(personEntity.getLastName());
System.out.println(personEntity.getId());
//FALSE !!!!
Query query2 = manager.createNamedQuery("QUERY2");
String firstName = (String) query2.getSingleResult();
}
@Transactional
public void insertPerson(PersonEntity person) {
manager.persist(person);
}
}
EXCEPTION :
29-Apr-2017 01:08:14.055 SEVERE [http-nio-8080-exec-8] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [spring] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Invalid column name] with root cause
java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3711)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2763)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:434)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:104)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy36.getInt(Unknown Source)
at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:66)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:65)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:269)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:265)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238)
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:357)
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:702)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:635)
at org.hibernate.loader.Loader.doQuery(Loader.java:856)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
at org.hibernate.loader.Loader.doList(Loader.java:2463)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
at org.hibernate.loader.Loader.list(Loader.java:2274)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1585)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:224)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:280)
at dao.PersonDaoImpl.selectPersons(PersonDaoImpl.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy26.selectPersons(Unknown Source)
at bl.PersonManager.getPerson(PersonManager.java:21)
at controller.PersonController.getPerson(PersonController.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:706)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Advertisement
Answer
The problem is that your second query returns a scalar value and resultClass is trying to map the returned value to an Entity.
Even changing resultClass to String.class should result in an exception like:
org.hibernate.MappingException: Unknown entity: java.lang.String
So just remove the entire resultClass attribute:
@NamedNativeQuery(name ="QUERY2" ,
query = "SELECT FIRSTNAME FROM PERSON"
)
But receiving scalar values with native queries seems only working properly if you use the JPA variants of the native query annotations:
import javax.persistence.NamedNativeQueries; import javax.persistence.NamedNativeQuery;
If you use the Hibernate ones
import org.hibernate.annotations.NamedNativeQueries; import org.hibernate.annotations.NamedNativeQuery;
You will get something like:
org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported
Tested with Hibernate in version 5.0.12.Final.
If you want to keep you Hibernate annotations for any reason, there is also some kind of workaround to go with instead.
Define a result set mapping on your entity and use it for the scalar query with the resultSetMapping attribute:
@SqlResultSetMapping(name = "scalarResult", columns = { @ColumnResult(name = "FIRSTNAME") })
@NamedNativeQueries({ @NamedNativeQuery(name = "QUERY2", query = "SELECT FIRSTNAME FROM PERSON", resultSetMapping = "scalarResult") })