Query By Example not working with "id" field/prima

pellyg · **Joined:** Fri Oct 27, 2006 3:37 pm **Posts:** 3

I'm running Hibernate version 3 on MySql 4. I have just begun using Query By Example and my initial programs were working fine. I ran into a problem when I tried to use an org.hibernate.critereon.Example constrained by my table's "id" field, which is the primary key of the table and is set as the "id" in my hibernate.cfg.xml.

I found that constraining by an id (eg, user.setId(5) and user.setName('foo')) led to the id being ignored in the generated SQL. I tried adding a Restrictions.sqlRestriction on the ID column (shown in the snippet below) and the query was correctly performed an filtered by ID, but without this workaround, I can't use an id in my Example.

Is there a different/better way I should be going about this?

hibernate.cfg:

Code:

<class name="User" table="user">
    <id name="id" column="id" type="long">
      <generator class="identity"/>
    </id>
    <property name="firstname" />
</class>

Code between sessionFactory.openSession() and session.close():

Code:

    Example userExample = Example.create(ex);
    userExample.ignoreCase();
    Criteria crit = getSessionFactory().openSession()
        .createCriteria(User.class).add(userExample);
     // workaround that gets the job done....but is it necessary?
    if (ex.getId() != null) {
      crit.add(Restrictions.sqlRestriction(
          String.format("id = '%s'",ex.getId())));
    }
    return crit.list();

Generated SQL without the if=statement above:
Hibernate: select this_.id as id18_0_, <other fields removed> from users this_ where (lower(this_.name)=?)

regisdev · **Joined:** Wed Jan 03, 2007 5:03 pm **Posts:** 4

I have the same problem,

I m not sure but i don't think so this feature exist in the Example class, it doesn't care about the identifier, if you load an object by ID you have to use get()/load() method. It is very silly do not have the permission to specify or take care of the ID(identifier) in the Example classe (QBE).

i got a bad response for you and for me. It is a bad issue, an JIRA issue have been created for resolve this point

http://forum.hibernate.org/viewtopic.php?t=938036

the best way is to use Criteria without QBE ! at this time !

pellyg · **Joined:** Fri Oct 27, 2006 3:37 pm **Posts:** 3

Thanks - At least this is a know issue/limitation. I'll use my workaround. If Gavin isn't spending all his time on Seam, maybe he'll have some time to come back to this one day (...wishful thinking?).

-Greg Pelly

olak · **Joined:** Wed Jan 31, 2007 6:54 am **Posts:** 22

I just got stopped by the same problem, I have many composite keys, and wants to do queries on part of the composite key.

It is very strange that such a limitation exists, since this is the only way to do queries without having to specify the name of the property explicitly, like "property".
Having to specify it, you end up with having to change "property" in the query if you change the name of the property in the class, which feels rather messy and hack-like...anyone knows of alternative approaches?

Flash · **Joined:** Fri Jan 05, 2007 9:15 am **Posts:** 5

I've hit the problem, too. This is a really bad limitation.
I found something about that point in the docs (Chapter 15.6):

Quote:

Version properties, identifiers and associations are ignored.

So I'll try to use that workaround specified by pellyg.

Thanks for that.

mayesa · **Joined:** Wed Mar 07, 2007 4:37 pm **Posts:** 1

I've hit the problem, too. But.. I've found a workaround.

Problem description
What I need to do, is not resolved by a "use load() or get()" answer. I want to use nested examples (nested objects) and filter by a nested object id (as a side effect, the workaround also solves the original issue).

Supossing foo object "has a" bar object, and that barExample, is an example restricted to an specific bar id, I want to perform something like:

Code:

session.createCriteria(Foo.class).add(fooExample).createCriteria("bar").add(barExample);

Hibernate resolves this, by joining foo and bar correctly.. BUT when I use a barExample that restricts the bar id, Hibernate does not append the "where bar.id == ?" clause to the generated SQL query. It retrieves a full join without the restriction I'm impossing to the barExample id.
Does anyone know the reason why it was implemented this way?
If I restrict a barExample property that it's not bar.id it works perfect, and Hibernate appends the "and bar.someProperty like ?" clause to the join.
Please let me know if I'm not being clear with my description of the problem.

Workaround.
You will have to add the following code to org.hibernate.criterion.Example.

Note: I'm using hibernate 3.1.3 so, may be the line numbers are different from yours.

Near line: 206 before

Code:

if ( buf.length()==1 ) buf.append("1=1"); //yuck!

Add the following line

Code:

appendPrimaryKeyCondition(criteria,criteriaQuery,buf);

Also add the following method:

Code:

private void appendPrimaryKeyCondition(Criteria criteria, CriteriaQuery cq, StringBuffer buf) {
      EntityPersister meta = (EntityPersister)cq.getFactory().getEntityPersister(cq.getEntityName(criteria));
      String idName = meta.getIdentifierPropertyName();
      Object idValue = meta.getIdentifier(entity, getEntityMode(criteria, cq));
      if (!isNullOrEmpty(idValue)) appendPropertyCondition(
            idName,
            idValue,
            criteria,
            cq,
            buf
         );
   }

Near line 239 before

Code:

return (TypedValue[]) list.toArray(TYPED_VALUES);

Add the following line:

Code:

appendPrimaryKeyValue(criteria,criteriaQuery,list);

You get the idea..

Now, the correspoding code to that method would be something like:

Code:

private void appendPrimaryKeyValue(Criteria criteria, CriteriaQuery cq, List list) {
      EntityPersister meta = (EntityPersister)cq.getFactory().getEntityPersister(cq.getEntityName(criteria));
      Type idType = meta.getIdentifierType();
      Object idValue = meta.getIdentifier(entity, getEntityMode(criteria, cq));
      if (!isNullOrEmpty(idValue)){
         addKeyTypedValue(idValue, idType, list);
      }
   }

   private void addKeyTypedValue(Object idValue, Type idType, List list) {
      if ( idValue!=null ) {
         if ( idValue instanceof String ) {
            String string = (String) idValue;
            if (isIgnoreCaseEnabled) string = string.toLowerCase();
            idValue = string;
         }
         list.add( new TypedValue(idType, idValue, null) );
      }
   }

There are only a couple of things left to change..

Replace line 303

Code:

String op = isLikeEnabled && isString ? " like " : "=";

with:

Code:

boolean isPrimaryKey = isPrimaryKey(criteria, cq, propertyName);
          String op = isLikeEnabled && isString && !isPrimaryKey ? " like " : "=";

And then, add the following method:

Code:

private boolean isPrimaryKey(Criteria criteria, CriteriaQuery cq, Object propertyName) {
      EntityPersister meta = cq.getFactory().getEntityPersister(cq.getEntityName(criteria));
      return (propertyName!= null && propertyName.equals(meta.getIdentifierPropertyName()));
   }

Tell me if these changes worked for you, and if you think it's worth to send the fix to Gavin.

Thanks.

Enchos · **Joined:** Wed May 16, 2007 11:36 am **Posts:** 3

mayesa,

your fix helped a lot. I have the simple case where I need to be able to do like-matching for the id-property of my entity. I've applied your modifications with a few changes to hibernate 3.2 and would really like to see a setting in the Example API that allows you to simply set id-properties as enabled (not excluded).

My modified helper methods:

Code:

private void appendPrimaryKeyCondition(Criteria criteria, CriteriaQuery cq, StringBuffer buf) {
      EntityPersister meta = (EntityPersister) cq.getFactory().getEntityPersister(
            cq.getEntityName(criteria));
      String idName = meta.getIdentifierPropertyName();
      Object idValue = meta.getIdentifier(entity, getEntityMode(criteria, cq));
      Type idType = meta.getIdentifierType();
      if (selector.include(idValue, idName, idType)) {
         appendPropertyCondition(idName, idValue, criteria, cq, buf);
      }
   }
   
   private void appendPrimaryKeyValue(Criteria criteria, CriteriaQuery cq, List list) {
      EntityPersister meta = (EntityPersister) cq.getFactory().getEntityPersister(
            cq.getEntityName(criteria));
      String idName = meta.getIdentifierPropertyName();
      Type idType = meta.getIdentifierType();
      Object idValue = meta.getIdentifier(entity, getEntityMode(criteria, cq));
      if (selector.include(idValue, idName, idType)) {
         addPropertyTypedValue(idValue, idType, list);
      }
   }

Seems like the PropertySelector didn't exist when you wrote that code. I also didn't add the isPrimaryKey check, because I want to match even IDs with the configured matchmode.

andrewyan · **Joined:** Thu Oct 06, 2011 9:42 pm **Posts:** 2

mayesa & Enchos,
Thanks for your fixes.

I'm new to Hibernate. I'm using 3.6.5 and the issue seems to persists (4 years later!!!). I tried to use the fixes mentioned, but I found the Example class has changed a little already. Can someone tell me what's the new fixes like?

Many thanks!

andrewyan · **Joined:** Thu Oct 06, 2011 9:42 pm **Posts:** 2

I found a workaround for this. Since I have control of the model class. Rather than trying to get the QBE on id work, I create an extra attribute in the class pointing to the same id table column. Then all I have to do is set the new attribute to have: updatable=false, insertable=false. I'm now free to query by the newly added attribute...

It took me quite some time to get to this less ugly solution. Hope that helps :)