-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 15 posts ] 
  Previous topic | Next topic 
Author Message
catcat
 Post subject: How to make Not IN by criteria
PostPosted: Fri Jan 02, 2004 4:01 am 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
Hello,

I want to make a SQL like following,
Code:
SELECT * FROM Customer where Cus_Id NOT IN  ("8","9")


How can I achieve by Hibernate Expression/Criteria ?
"8","9", etc is Id of the Customer and I have already holding a List of Customer

Here is the Simple of my code
Code:
// First Customer List
List customerList = new ArrayList();
// Do Retreive CustomerList from DB through Hibernate


// Second Customer List
List customerListResult = new ArrayList();
/*  Want to have a customerListResult which include all Customer which does not already include in customerList
*/

// How to do that here ?


Anyone have idea ? Thanks
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jan 02, 2004 5:08 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
with customerList containing your ids to use in the not in:

Code:
session.createCriteria(Customer.class)
    .add( Expression.not( Expression.in("id", customerList) ) )
Top
 Profile  
 
catcat
 Post subject:
PostPosted: Fri Jan 02, 2004 5:57 am 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
gloeglm wrote:
Code:
session.createCriteria(Customer.class)
    .add( Expression.not( Expression.in("id", customerList) ) )


The above does not work. The sql generate by above will create sql like following

Code:
.......  from Customer this where not this.Cus_Id in (?, ?, ?)


The not is seperate by the field name instead of NOT IN

One more followup question.
I have the Cus_Id defines as java type long.

I have the following error code
Code:
java.lang.ClassCastException at net.sf.hibernate.type.LongType.set(LongType.java:32)
................
................

when using
[code]session.createCriteria(Customer.class)
    .add( Expression.in("id", customerList) ) [/code]

What should I do for the above error ? Is it I cannot use long type ?
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jan 02, 2004 7:15 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Hm, this may be still lacking from the Criteria API. You can however add this easily by defining your own Criterion object. It should look like this:

Code:
public class NotInExpression extends AbstractCriterion {

   private final String propertyName;
   private final Object[] values;
   
   NotInExpression(String propertyName, Object[] values) {
      this.propertyName = propertyName;
      this.values = values;
   }

   public String toSqlString(SessionFactoryImplementor sessionFactory, Class persistentClass, String alias, Map aliasClasses)
   throws HibernateException {
      String params;
      if ( values.length>0 ) {
         params = StringHelper.repeat( "?, ", values.length-1 );
         params += "?";
      }
      else {
         params = StringHelper.EMPTY_STRING;
      }
      String condition = " not in (" + params + ')';
      return StringHelper.join(
         " and ",
         StringHelper.suffix(
            getColumns(sessionFactory, persistentClass, propertyName, alias, aliasClasses),
            condition
         )
      );
   }
   
   public TypedValue[] getTypedValues(SessionFactoryImplementor sessionFactory, Class persistentClass, Map aliasClasses) throws HibernateException {
      TypedValue[] tvs = new TypedValue[ values.length ];
      for ( int i=0; i<tvs.length; i++ ) {
         tvs[i] = getTypedValue( sessionFactory, persistentClass, propertyName, values[i], aliasClasses );
      }
      return tvs;
   }

   public String toString() {
      return propertyName + " not in (" + StringHelper.toString(values) + ')';
   }
   
}


Then pass an instance of it to criteria.add
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jan 02, 2004 7:39 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Code:
where not (this.Cus_Id in (?, ?, ?))

should actually be the same as

Code:
where (this.Cus_Id not in (?, ?, ?))

However if you do

Code:
SELECT *
FROM table
WHERE NOT
id
IN ( 1, 2, 3, 4, 5 )

this does not work, at least tested with mysql. Placing the proper brackets manually does produce the correct results however. Do other databases have the same problem?
Top
 Profile  
 
awt
 Post subject:
PostPosted: Fri Jan 02, 2004 10:59 am 
Beginner
Beginner

Joined: Thu Dec 11, 2003 9:54 am
Posts: 25
Oracle 9i havn't this problem:

you could even write with both NOT, like:

Code:
SELECT *
FROM table
WHERE NOT
id
not IN ( 1, 2, 3, 4, 5 )




gloeglm wrote:
Code:
where not (this.Cus_Id in (?, ?, ?))

should actually be the same as

Code:
where (this.Cus_Id not in (?, ?, ?))

However if you do

Code:
SELECT *
FROM table
WHERE NOT
id
IN ( 1, 2, 3, 4, 5 )

this does not work, at least tested with mysql. Placing the proper brackets manually does produce the correct results however. Do other databases have the same problem?
Top
 Profile  
 
michael
 Post subject:
PostPosted: Fri Jan 02, 2004 8:09 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Created a JIRA entry with proposed patch: HB-597
Top
 Profile  
 
catcat
 Post subject:
PostPosted: Fri Jan 02, 2004 10:13 pm 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
Code:
session.createCriteria(Customer.class)
    .add( Expression.not( Expression.in("id", customerList) ) )


When creating such criteria. I will get a java.lang.ClassCastException Exception. It complains the object in the customerList cannot be converted to the Long Type.

The customerList stored customer objects.
Id in Customer Class is defined as java long and it is the Id of Customer

Code:
<id name="id" column="id" type="long" unsaved-value="0">
  <generator class="native" />
</id>


Hibernate Version : 2.1 Final.
DB: mySQL 4.0.15

Anyone have this kind of error as well ?
Top
 Profile  
 
catcat
 Post subject:
PostPosted: Fri Jan 02, 2004 10:38 pm 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
I have the same problem in Hibernate Version : 2.1.1 as well.
Top
 Profile  
 
awt
 Post subject:
PostPosted: Sat Jan 03, 2004 9:56 am 
Beginner
Beginner

Joined: Thu Dec 11, 2003 9:54 am
Posts: 25
What happens if you will use like next one in your mapping:

Code:
    <id
        name="url"
        type="java.lang.String"
        column="URL"
    >
        <generator class="assigned" />
    </id>



-awt
Top
 Profile  
 
michael
 Post subject:
PostPosted: Sat Jan 03, 2004 12:16 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Of course you have to pass a list of ids, not a List of customer objects. This is not really logical, id being an element of a collection with a totally different object type.
Top
 Profile  
 
catcat
 Post subject:
PostPosted: Mon Jan 05, 2004 12:30 am 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
Is there any more proper way to do this ?
(i.e. Select some entities where the entities are not occured in the collection. )
I think making the conversion is not very good.
Top
 Profile  
 
michael
 Post subject:
PostPosted: Mon Jan 05, 2004 12:40 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
No there is no other way using the criteria api. Probably writing your own criterion, extending InExpression and overriding getTypedValues.
Top
 Profile  
 
catcat
 Post subject:
PostPosted: Mon Jan 05, 2004 10:43 pm 
Beginner
Beginner

Joined: Fri Jan 02, 2004 3:27 am
Posts: 23
Location: Hong Kong
Thanks gloeglm. One more question, what customerList supposed to be ?

Code:
session.createCriteria(Customer.class)
    .add( Expression.not( Expression.in("id", customerList) ) )


I have the id defines as java long type for customer entity, however, the Expression.XX method required parameters either object array or List,

How can I convert the long array (ie. long id[] ) to the accepted parameter ?
Top
 Profile  
 
michael
 Post subject:
PostPosted: Tue Jan 06, 2004 8:07 am 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
You have to wrap the longs in Long objects
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 15 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.