Do I have to select all entity properties when use JPA native query?

Question

I need to use JPA native query (Hibernate is used) for some complex business logics and I found that apparently all the entity properties need to be in the SELECT clause in my native query. Otherwise JPA always throws

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

so I wonder whether this is something necessary or I miss something in my code/configuration as I cannot find out any JPA specification regards this. My code is below:

table schema

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 entity code (just a 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;
  }
}

and the query code (just an easy one to show the issue. real query is much complex):

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();

note that occupiedOfficer property of the Address entity is not added in the SELECT clause and I get the

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

if I add the occupiedOfficer property (which I don't need), the query works and correct results are returned. Any ideas about this?

You may use * if you don't want to write column names. Or you may use @SqlResultSetMapping if don't want to fetch extra columns from the database (I think it's not dangerous for read-only data) — Multisync, Oct 20 '14 at 01:26
how to use @SqlResultSetMapping to resolve my question? As I know the SqlResultSetMapping just helps map entity properties and columns in case their names are different — danny, Oct 20 '14 at 02:46
http://stackoverflow.com/questions/25930628/how-to-fetch-only-specific-columns-in-a-namedquery — Multisync, Oct 20 '14 at 03:04

score 5 · Accepted Answer · answered Oct 20 '14 at 01:12

5

§3.10.16.1 "Returning Managed Entities from Native Queries" of the JPA spec says:

When an entity is to be returned from a native query, the SQL statement should select all of the columns that are mapped to the entity object. This should include foreign key columns to related entities. The results obtained when insufficient data is available are undefined.

In other words, JPA does not support partial entity population from native queries (if at all).

answered Oct 20 '14 at 01:12

Steve C

18,876
5
34
37

1

Perfect answer! But why? is it very hard to implement a partial query? The current implementation (must return all columns) is intuitive and inefficient! – danny Oct 20 '14 at 02:43
In your particular example I doubt that you could measure the difference – Steve C Oct 20 '14 at 02:58

Do I have to select all entity properties when use JPA native query?

1 Answers1