java - spring-hibernate-jpa namedNativeQuery 中的列名无效

Question

我有非常简单的实体类和两个本地查询。

QUERY1 正确运行并返回 PersonEntity ，但是 QUERY2 返回异常(无效的列名 !!! 但我的列名就是这个)；

我的实体类:

@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 类:

@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)

Table Describe

Answer 1

问题是您的第二个查询返回一个标量值，而 resultClass 试图将返回值映射到一个实体。

即使将 resultClass 更改为 String.class 也会导致如下异常:

org.hibernate.MappingException: Unknown entity: java.lang.String

所以只需删除整个 resultClass 属性:

@NamedNativeQuery(name ="QUERY2" ,
        query = "SELECT FIRSTNAME FROM PERSON"
)

但如果您使用 native 查询注释的 JPA 变体，则使用 native 查询接收标量值似乎只能正常工作:

import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;

如果你使用 Hibernate 的

import org.hibernate.annotations.NamedNativeQueries;
import org.hibernate.annotations.NamedNativeQuery;

你会得到这样的东西:

org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported

在版本 5.0.12.Final 中使用 Hibernate 进行了测试。

Mapping native queries

如果您出于任何原因想要保留 Hibernate 注释，也可以使用某种变通方法。

在您的实体上定义一个结果集映射，并将其用于具有 resultSetMapping 属性的标量查询:

@SqlResultSetMapping(name = "scalarResult", columns = { @ColumnResult(name = "FIRSTNAME") })
@NamedNativeQueries({ @NamedNativeQuery(name = "QUERY2", query = "SELECT FIRSTNAME FROM PERSON", resultSetMapping = "scalarResult") })