Hibernate select clause not working

Question

I have a legacy table that has ridiculous number of columns and I am trying to limit the number of columns hibernate populates. If I run just from the "from" clause to the end, it works fine, loads every column, but when I add "Select a.col1, a.col2..." etc., I get back an object array instead of the instance of the bean.

I tried adding a result transformer, but it seems to think that the first column is named "0". I suppose it's already broken by the time the result transformer comes into the picture. I wondered if maybe there was a case mismatch, so I turned everything uppercase, but that didn't help.

UPDATE: Here's the SQL:

Select a.SZ_ABND_NO, a.ASSET_VAL, a.FO_TYP, a.ASSET_ID, a.AGCY_RGN_CD, a.LIT_AGCY_CD, a.ASSET_TYP, a.SZ_AGCY_CD, a.SUB_OFC_ID, a.OFC_ID, a.CA_ID_AGCY, a.PROC_DIST from FlatAssetT a where a.ASSET_ID in (:assetList) order by a.ASSET_ID

The bean is enormous. Those 12 columns are among hundreds (yes plural). I'll give a selected snippet of the bean though.

@Entity
@Table(name = "FLATASSET_T", schema="K702PRDR")
public class FlatAssetT {
    @Column(name="ADPT_DT", nullable=false)
    private Date ADPT_DT;

    @Id
    @Column(name="ASSET_ID", nullable=false)
    private String ASSET_ID;

    @Column(name="ASSET_ID_TYP", nullable=false)
    private String ASSET_ID_TYP;

    @Column(name="SZ_ABND_NO", nullable=false)
    private String SZ_ABND_NO;
[etc.]
    public String getSZ_ABND_NO() {
        return SZ_ABND_NO;
    }
    public void  setSZ_ABND_NO(String arg) {
        this.SZ_ABND_NO=arg;
    }
    public String getASSET_ID() {
        return ASSET_ID;
    }
    public void  setASSET_ID(String arg) {
        this.ASSET_ID=arg;
    }
    public Date getADPT_DT() {
        return ADPT_DT;
    }
    public void  setADPT_DT(Date arg) {
        this.ADPT_DT=arg;
    }
    public String getASSET_ID_TYP() {
        return ASSET_ID_TYP;
    }
    public void  setASSET_ID_TYP(String arg) {
        this.ASSET_ID_TYP=arg;
    }
[etc.]
}

Can you help us and post the bean you're trying to select? And maybe the SQL create of the current table? Also, don't forget to mention if you're using pure JPA or Hibernate API, is it HQL or JPQL? Using the hibernate criteria API? It help us to try and debug the issue :) — André, Nov 27 '13 at 15:48
@André - Using HQL. The table was not created by hibernate. It is a legacy table. It's pretty simple with a string user entered key (asset_id). I don't know anything about the criteria API other than that it exists. I heard that "projections" are helpful here, but I don't have any idea how. — Entropy, Nov 27 '13 at 19:01

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

In addition to the default constructor of the bean have a custom constructor that is taking the limited set of arguments (matching those you are fetching from DB) and then use the 'select new MyBean(..)...' syntax. Refer the below link for a more detailed explanation.

New object with HQL

Also you could give aliases to each column you are selecting and then populate a map as shown in

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-select

select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n )
from Cat cat

May be you could introduce a constructor to your bean accepting a map and then use that (joining the two approaches)

edited May 23 '17 at 12:28

Community

1
1

answered Nov 27 '13 at 16:47

Dev Blanked

7,429
3
24
32

That sounds neat! But unfortunately, I would like the partial select to be dynamic. A single dao method that takes a list of columns to return. This is partly because this is part of a web service and I intend to accept a list of what info they want to return. – Entropy Nov 27 '13 at 17:50
I forgot to put the at in the previous comment, so I am adding a second just to hope that it gets you notified. – Entropy Nov 27 '13 at 19:03

André · Answer 2 · 2013-11-27T19:45:10.667

You can query it using this method from this article

It uses the Apache BeanUtils. The author left the implementation with parameters as "homework" for the readers.

In essence, query the data and iterate over the result creating new instances of the bean and set the available data to it, creating a new list with the beans.

public List find(final String hqlQuery) throws Exception {

    List results = new ArrayList();
    Query query = SessionManager.currentSession().createQuery(hqlQuery);
    Type beanType = query.getReturnTypes()[0];
    Class beanClass = beanType.getReturnedClass();
    String[] columns = extractColumns(hqlQuery);
    String[] attributeNames = getAttributeFieldNames(columns);
    String[] resultFieldNames = getResultFieldNames(columns);
    Iterator iter = query.iterate();
    while(iter.hasNext()) {
        Object[] row = (Object[]) iter.next();
        Object bean = beanClass.newInstance();
        for (int j = 0; j < row.length; j++) {
            if (row[j] != null) {
                initialisePath(bean, attributeNames[j]);
                PropertyUtils.setProperty(bean, attributeNames[j], row[j]);
            }
        }
        results.add(bean);
    }
    return results;
    }

    private static void initialisePath(final Object bean, final String fieldName) throws Exception {
            int dot = fieldName.indexOf('.');
            while (dot >= 0) {
                String attributeName = fieldName.substring(0, dot);
                Class attributeClass = PropertyUtils.getPropertyType(bean, attributeName);
                if (PropertyUtils.getProperty(bean, attributeName) == null) {
                    PropertyUtils.setProperty(bean, attributeName, attributeClass.newInstance());
            }
            dot = fieldName.indexOf('.', dot + 1);
            }
    }

Hibernate select clause not working

2 Answers2