0

I’m using JPA 2.1, Hibernate 4.1.3.Final, and MySQL 5.5.37. How do I map a result set to a @Transient collection? In my entity, I have set up

@Entity
@Table(name = "category", uniqueConstraints = { @UniqueConstraint(columnNames = { "NAME", "PD_ONLY" }) })
@SqlResultSetMapping(name = "categoryResults", classes = { @ConstructorResult(targetClass = org.mainco.subco.core.domain.Category.class, columns = {
        @ColumnResult(name = "id"), @ColumnResult(name = "name"), @ColumnResult(name = "pd_only"),
        @ColumnResult(name = "subjects") }) })
public class Category implements Serializable, Comparable<Category>
{

    @Id
    @Column(name = "ID")
    @GeneratedValue(generator = "uuid-strategy")
    private String id;

    private String name;

    @Column(name = "PD_ONLY")
    private Boolean pdOnly;

    @Transient
    private Set<Subject> subjects;

and then I’m attempting to use a native query to map things …

final Query q = m_entityManager.createNativeQuery(
                                   "select distinct c.id, " + 
                                   "                c.name, " + 
                                   "                c.pd_only, " + 
                                   "                e.subject_id FROM category c, " + 
                                   "                                  resource_category rc, " + 
                                   "                                  product_resource pr, " + 
                                   "                                  product p, " + 
                                   "                                  product_ebook pe, " + 
                                   "                                  book e where c.pd_only = :pdOnly and c.id = rc.category_id and rc.resource_id = pr.resource_id and pr.product_id = p.id and p.id = pe.product_id and pe.ebook_id = e.id and c.id = 'Curriculum Maps';",
                                   "categoryResults")
                                   .setParameter("pdOnly", pdOnly ? "1" : "0");
    return q.getResultList();

but I get the below exception …

javax.persistence.PersistenceException: org.hibernate.HibernateException: Could not resolve column name in result set [subjects]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1166)
    at org.hibernate.loader.custom.JdbcResultMetadata.resolveColumnPosition(JdbcResultMetadata.java:67)
    at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:59)
    at org.hibernate.loader.custom.ConstructorResultColumnProcessor.performDiscovery(ConstructorResultColumnProcessor.java:58)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:498)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2073)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:909)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    at org.mainco.subco.core.repo.CategoryDaoImpl.findCategories(CategoryDaoImpl.java:78)
    at org.mainco.subco.core.repo.CategoryDaoImpl.findNonPDCategories(CategoryDaoImpl.java:38)
    at org.mainco.subco.core.repo.CategoryDaoIT.testFindNonPDCategories(CategoryDaoIT.java:30)

Any ideas on how to map what is returned from my native query into a result set? I’m also open to not using native SQL and doing something with CriteriaBuilder/JPQL.

Xstian
  • 8,184
  • 10
  • 42
  • 72
Dave
  • 15,639
  • 133
  • 442
  • 830
  • how do you think a row of data could possibly be mapped to a Collection? Each row only has a single "subject_id", so you want 1 element in each Collection? – Neil Stockton Apr 11 '15 at 09:15
  • That is why I asked the question! I would like to know how to do this. – Dave Apr 13 '15 at 14:09

1 Answers1

0

You can get the result list as raw list of Object[]. Then you can cast the objects and mount the instance exactly as you need.

See the code snippet below. I have not tested the code, so if you can try it out, please let us know the result. 

// Here you get the result set as a raw list of objects
@SuppressWarnings("unchecked")
List<Object[]> resultList = nq.getResultList();

// Maybe you will need a set of Category for the "categoryResults"
Set<Category> categoryResults = new HashSet<>();

// Here you cast the whole result set
for (Object[] record : resultList) {
    // Sets Category
    Category category = new Category();
    category.setId((Integer) record[0]);
    category.setName((String) record[1]);
    category.setPdOnly((Boolean) record[2]);

    // Sets Subject
    Subject subject = new subject();
    subject.setId((Integer) record[3]);

    // Adds Subject to the HashSet
    Set<Subject> subjectsSet = new HashSet<>();
    subjectsSet.add(subject);

    // Here you have what you need, the setting of the transient field
    category.setSubjects(subjectsSet);

    // Finally adds category onto results
    categoryResults.add(category);
}
WouldMake
  • 1
  • 1