使用JPA本机查询时，是否必须选择所有实体属性？

danny 发表于 Dev

丹尼

对于某些复杂的业务逻辑，我需要使用JPA本机查询（使用Hibernate），并且我发现显然所有实体属性都需要在本机查询的SELECT子句中。否则，JPA总是抛出

java.sql.SQLException: Column 'xxx' not found.

所以我想知道这是否是必需的，还是因为我找不到任何JPA规范对此而在我的代码/配置中遗漏了什么。我的代码如下：

表架构

CREATE TABLE `casaddress` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`addressLine1` varchar(255) DEFAULT NULL,
`addressLine2` varchar(255) DEFAULT NULL,
`companyId` bigint(20) DEFAULT NULL,
`country` varchar(255) DEFAULT NULL,
`effectiveDate` date DEFAULT NULL,
`occupiedOfficer` varchar(255) DEFAULT NULL,
`postcode` varchar(255) DEFAULT NULL,
`suburb` varchar(255) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`version` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK616A8A7F1FD01BA8` (`companyId`),
CONSTRAINT `FK616A8A7F1FD01BA8` FOREIGN KEY (`companyId`) REFERENCES `cascompany` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

JPA实体代码（仅是pojo）

@Entity(name = "CASAddress")
    public class Address implements Serializable {

    private static final long serialVersionUID = -2903861598526729415L;

    public static final String TYPE = "type";

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Version
    private Long version;

    private String addressLine1;

    private String addressLine2;

    private String suburb;

    private String postcode;

    private String country;

    private String occupiedOfficer;

    @Temporal(value = TemporalType.DATE)
    private Date effectiveDate;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "companyId", nullable = false, updatable = false, insertable = false)
    private CASCompany company;

    private Long companyId;

    private AddressType type;

    public Address() {

  }

  /**
   * @return the addressLine1
   */
  public String getAddressLine1() {
    return addressLine1;
  }

  /**
   * @param addressLine1 the addressLine1 to set
   */
  public void setAddressLine1(String addressLine1) {
    this.addressLine1 = addressLine1;
  }

  /**
   * @return the addressLine2
   */
  public String getAddressLine2() {
    return addressLine2;
  }

  /**
   * @param addressLine2 the addressLine2 to set
   */
  public void setAddressLine2(String addressLine2) {
    this.addressLine2 = addressLine2;
  }

  /**
   * @return the suburb
   */
  public String getSuburb() {
    return suburb;
  }

  /**
   * @param suburb the suburb to set
   */
  public void setSuburb(String suburb) {
    this.suburb = suburb;
  }

  /**
   * @return the postcode
   */
  public String getPostcode() {
    return postcode;
  }

  /**
   * @param postcode the postcode to set
   */
  public void setPostcode(String postcode) {
    this.postcode = postcode;
  }

  /**
   * @return the country
   */
  public String getCountry() {
    return country;
  }

  /**
   * @param country the country to set
   */
  public void setCountry(String country) {
    this.country = country;
  }

  /**
   * @return the occupiedOfficer
   */
  public String getOccupiedOfficer() {
    return occupiedOfficer;
  }

  /**
   * @param occupiedOfficer the occupiedOfficer to set
   */
  public void setOccupiedOfficer(String occupiedOfficer) {
    this.occupiedOfficer = occupiedOfficer;
  }

  /**
   * @return the effectiveDate
   */
  public Date getEffectiveDate() {
    return effectiveDate;
  }

  /**
   * @param effectiveDate the effectiveDate to set
   */
  public void setEffectiveDate(Date effectiveDate) {
    this.effectiveDate = effectiveDate;
  }

  /**
   * @return the companyId
   */
  public Long getCompanyId() {
    return companyId;
  }

  /**
   * @param companyId the companyId to set
   */
  public void setCompanyId(Long companyId) {
    this.companyId = companyId;
  }

  /**
   * @return the type
   */
  public AddressType getType() {
    return type;
  }

  /**
   * @param type the type to set
   */
  public void setType(AddressType type) {
    this.type = type;
  }
}

和查询代码（只是一个容易显示问题的代码。实际查询非常复杂）：

String query =
    "SELECT ad.id, ad.version, ad.addressLine1, ad.addressLine2, ad.suburb, ad.country, 
    ad.postcode, ad.type, ad.companyId, ad.effectiveDate from CASAddress ad";
    Query q = entityManager.createNativeQuery(query, Address.class);
   List<Address> addresses = q.getResultList();

请注意，没有在SELECT子句中添加Address实体的被占用的Officer属性，并且我得到了

Caused by: org.hibernate.exception.SQLGrammarException: Column 'occupiedOfficer' not found.
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:108)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy198.getString(Unknown Source)
    at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:66)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:65)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:261)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:247)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:332)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2873)
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1668)
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1600)
    at org.hibernate.loader.Loader.getRow(Loader.java:1500)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:712)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:940)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1783)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:231)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    ... 79 more
Caused by: java.sql.SQLException: Column 'occupiedOfficer' not found.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1163)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5729)
    at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:811)
    at com.alibaba.druid.filter.FilterAdapter.resultSet_getString(FilterAdapter.java:1754)
    at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:809)
    at com.alibaba.druid.filter.FilterAdapter.resultSet_getString(FilterAdapter.java:1754)
    at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:809)
    at com.alibaba.druid.filter.stat.StatFilter.resultSet_getString(StatFilter.java:930)
    at com.alibaba.druid.filter.FilterChainImpl.resultSet_getString(FilterChainImpl.java:809)
    at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getString(ResultSetProxyImpl.java:693)
    at com.alibaba.druid.pool.DruidPooledResultSet.getString(DruidPooledResultSet.java:257)
    at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:104)
    ... 104 more

如果我添加了占用的Officer属性（不需要），则查询有效并返回正确的结果。有什么想法吗？

史蒂夫·C

§3.10.16.1JPA规范的“从本地查询返回托管实体”说：