Criteria to select only special columns

JasDA · **Joined:** Wed Nov 02, 2005 6:07 am **Posts:** 12

Hi,

is it possible to use the criteria API in that way, that only special columns of a table are selected?

If I use the criteria API every column which is mapping in the .hbm.xml file will be selected by the generated SQL. But sometimes it is useful to select only special columns. Perhaps if I need only 3 columns in a 40 columns table.

Any ideas?

Steven.Kitchen · **Posted:** Fri Jan 20, 2006 6:30 am

I've not actually used this yet, but looking at the Criteria API (in 3.1), what I think you need is to set a projection (select list) for your criteria using setProjection(Projection projection). Projections can be created with factory methods on Projections.

(ProjectionList implements Projection to allow you to set a select list, as well as a single column).

That's the bit of the API docs you need to be looking at, anyway, I think.

Steve.

timrobertson100 · **Posted:** Fri Jan 20, 2006 6:33 am

How about looking at it the other way and mapping most of the columns as lazy and then just bring them all back when you need to. In HQL it's "from tableName fetch all" (in Criteria API it's done using the setFetchMode() I think)

JasDA · **Joined:** Wed Nov 02, 2005 6:07 am **Posts:** 12

I've tried to resolve the problem by using Projections:

Code:

        Criteria crit = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Module.class)
        .setProjection(Projections.distinct(Projections.projectionList()
                             .add(Projections.property("ModNo"))
                             .add(Projections.property("ChainNb"))))
        .addOrder(Order.asc("ModNo"));
        crit.add(Expression.eq("ClientBpCd", "0009"));
        crit.add(Expression.eq("ClientOdsCd", "V"));

The only problem is, that the result is not put into the Module-Obejct. Is it possible to do that?

ramnath · **Posted:** Fri Jan 20, 2006 6:51 am

Hi,

You will get a array (Object[] ) , not Module Object.
see http://www.hibernate.org/hib_docs/refer ... larqueries and
http://www.hibernate.org/hib_docs/refer ... ggregation

TypeCast it to ur need.

JasDA · **Joined:** Wed Nov 02, 2005 6:07 am **Posts:** 12

ramnath wrote:

Hi,

You will get a array (Object[] ) , not Module Object.
see http://www.hibernate.org/hib_docs/refer ... larqueries and
http://www.hibernate.org/hib_docs/refer ... ggregation

TypeCast it to ur need.

If I use the criteria in the following way, I get an array auf Module Objects:

Code:

        Criteria crit = getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(Module.class)
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .add(Expression.eq("ClientBpCd", mod.getClientBpCd()))
            .add(Expression.eq("ClientOdsCd", mod.getClientOdsCd()))
            .addOrder(Order.asc("ModNo"));

Now I want to use the Projection and don't get an Object array but an Module array.

Steven.Kitchen · **Posted:** Fri Jan 20, 2006 10:10 am

If you are only selecting a few of your entity object's properties you are not going to get your entity object back, your executing a report query, and are just going to get back an object array of those properties (is there a way of creating a typesafe Java object as in the last example in http://www.hibernate.org/hib_docs/reference/en/html_single/#queryhql-select?).

If you want to get back your entities, but are worried about selecting large numbers of columns. then you need to consider lazy loading properties as suggested by Tim Robertson
.

JasDA · **Joined:** Wed Nov 02, 2005 6:07 am **Posts:** 12

But unfortunatly there is no "fetch all" in the Criteria API:

http://forum.hibernate.org/viewtopic.ph ... h+criteria