Example queries help

quinton_wall · **Posted:** Thu Jun 17, 2004 10:55 am

Hi,

I am hoping someone might be able to notice something that I am missing on a simple Example query. I have used http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html#querycriteria-examples as a reference to produce the following code:

Code:

ApbecEducationalCounselor ecdata = new ApbecEducationalCounselor();
            ecdata.setAlumSeqNum(new Long(alumID));
            
            List ecList = hbnsession.createCriteria(edu.mit.admissions.model.ApbecEducationalCounselor.class)
                                    .add( Example.create(ecdata))
                                    .list();

where alumSeqNum is a unique identifier.

When i run the code the following SQL is generated:

Code:

Hibernate: select this.ALUM_SEQ_NUM as ALUM_SEQ1_0_, this.MAIL_NAME as MAIL_NAME0_, this.FIRST_NAME as FIRST_NAME0_, this.LAST_NAME as LAST_NAME0_, this.HOME_STREET_ADDRESS1 as HOME_STR5_0_, this.HOME_STREET_ADDRESS2 as HOME_STR6_0_, this.HOME_CITY as HOME_CITY0_, this.HOME_STATE as HOME_STATE0_, this.HOME_ZIP as HOME_ZIP0_, this.HOME_COUNTRY as HOME_CO10_0_, this.HOME_PHONE as HOME_PHONE0_, this.COMPANY_NAME as COMPANY12_0_, this.COMPANY_NAME2 as COMPANY13_0_, this.BUS_STREET1 as BUS_STR14_0_, this.BUS_STREET2 as BUS_STR15_0_, this.BUS_CITY as BUS_CITY0_, this.BUS_STATE as BUS_STATE0_, this.BUS_ZIP as BUS_ZIP0_, this.BUS_COUNTRY as BUS_COU19_0_, this.BUS_PHONE as BUS_PHONE0_, this.BUS_PHONE_EXT as BUS_PHO21_0_, this.EMAIL_ADDRESS as EMAIL_A22_0_, this.EMAIL_RELEASE as EMAIL_R23_0_, this.PHONE_PREF as PHONE_PREF0_, this.MAIL_PREF as MAIL_PREF0_, this.REF_PREF as REF_PREF0_, this.CHAIR_CODE as CHAIR_CODE0_, this.DEGREE_MAJOR_DEFAULT as DEGREE_28_0
_, this.DEGREE_YEAR_DEFAULT as DEGREE_29_0_ from APBEC_EDUCATIONAL_COUNSELOR this where (1=1)

Notice the missing where clause.

Below is the snippet from the mapping file (note I removed the extra column definitions for clarity):

Code:

 <id
        name="alumSeqNum"
        type="java.lang.Long"
        column="ALUM_SEQ_NUM"
    >
        <meta attribute="field-description">
           @hibernate.id
            generator-class="assigned"
            type="java.lang.Long"
            column="ALUM_SEQ_NUM"

        </meta>
        <generator class="assigned" />
    </id>

I am using Oracle 8i as the db and connecting to the database via a datasource defined in weblogic (using the Oracle thin drivers). The db connection works as all the records are returned (which is correct as there is no where clause of course).

Any assistance would be appreciated.

Thanks!

michael · **Posted:** Thu Jun 17, 2004 10:59 am

The id property is ignored for examples.

quinton_wall · **Posted:** Thu Jun 17, 2004 11:05 am

thanks...

so something like (taken from the docs):

Code:

List cats= sess.createCriteria(Cat.class)
    .add( Expression.like("name", "Fritz%") )
    .list();

where name = id column (in this example) would be the suggested approach?

Regards

michael · **Posted:** Thu Jun 17, 2004 11:12 am

Exactly

quinton_wall · **Posted:** Thu Jun 17, 2004 11:30 am

thank you.

My code now looks like:

Code:

 List ecList = hbnsession.createCriteria(ApbecEducationalCounselor.class)
                .add(Expression.eq("alumSeqNum", new Long(alumID)))
                .list();

which produces the following where clause:

Code:

where this.ALUM_SEQ_NUM=?

It does not seem to add the value of 'alumID' which I know is being set correctly (from log messages just prior to the above code snippet). I also tried adding another Expression on another field with the same error.

Any clues? From what I can see in the docs and sample apps it is all correct..

quinton_wall · **Posted:** Thu Jun 17, 2004 1:02 pm

sorry for the last post.

it seems that such a 'show_sql output of

Code:

where this.ALUM_SEQ_NUM=?

actually includes the query criteria. It just doesnt show it in the debug output which is what I expected.

thanks for your help!

gus · **Posted:** Mon Jun 28, 2004 7:05 am

Is there a possibility of getting an override setting in Example at some point to allow the use of id properties? Looking at the code, it's also tossing out version properties (another candidate for override).

I specifcally want to be able to query by example on id properties at times. We've a legacy database, full of meaningful key columns (chuck full of composite id mappings) Perhaps that makes writing QBE logic excessively difficult?

Gonna have to figure out a work around for my use.

Doesn't appear I can fix the problem by subclassing Example, the isPropertyIncluded method is declared private. Perhaps an alternative like so?

Code:

   protected boolean isPropertyIncluded(Object value, String name, Type type) {
      return !excludedProperties.contains(name) &&
         (allowAssociations || !type.isAssociationType()) &&
         selector.include(value, name, type);
   }
   /**
    * Allow association type properties to be used in the example.
    */
   public boolean enableAssociations() {
      allowAssociations = true;
   }

Not even sure this would work.

--gus