negation statement

Ot · **Posted:** Fri Jan 02, 2004 1:46 am

I'd like to perform a query to my mySQL database similar to this:
select * from myTable where id != 2 ;

How to perform that with the Criteria interface (I mean, i just don't understand the negation. The rest of the criteria works fine)? Using the Expression.not method is the opposite of using the Expression.eq method?

thaks a lot

michael · **Posted:** Fri Jan 02, 2004 5:04 am

.add( Expression.not(Expression.eq("id", new Integer(2) ) ) )

Ot · **Posted:** Fri Jan 02, 2004 11:23 am

This the code I'm using;

Code:

Criteria crit = session.createCriteria(Lote.class);
crit = crit.add(Expression.eq("id_quadra",  new Long(quadra.getId())));
crit = crit.add(Expression.eq("livre", new Boolean(true)));
crit = crit.add(Expression.not(Expression.eq("id", new Long(l.getId()))));
List ret = crit.list();

I want the id column of my mapped bean Lote to be different of l.getId(), which, for sure, brings the value I'm trying to compare.
That is not working.

Thanks in advance

michael · **Posted:** Fri Jan 02, 2004 12:11 pm

What do you mean by "that is not working". What exactly is the problem? Is the generated SQL wrong? Are there exceptions? Please be more specific.

Ot · **Posted:** Fri Jan 02, 2004 12:17 pm

I mean the Criteria.list() method, which returns a List of results from the query, is returning a list of size 0. But the correct query if supplied in the mysql terminal, returns a large list.
If I try a crit.toString, I get this output:

[id_quadra=1, livre=true, not id=1]

As I see, the statements are correctly set to the values I want to query.
that is it. It is not returning the results it should. It is not working.

Thanks again

michael · **Posted:** Fri Jan 02, 2004 12:19 pm

Have you checked Hibernates SQL output? Is the correct SQL being generated?

Ot · **Posted:** Fri Jan 02, 2004 12:44 pm

how to get the generated sql?

michael · **Posted:** Fri Jan 02, 2004 1:28 pm

Set hibernate.show_sql = true in your properties

Ot · **Posted:** Fri Jan 02, 2004 1:40 pm

Yeah, the show sql is already set to true. I thought you were talking about getting the sql outptut in the java code way.
by the way, I just looking around the forum and found a thread named "How to make Not IN by criteria". That is exaclty the same situation i'm facing here. I tested the "not in" statement in mysql terminal, and it returned me the same results hibernate returns when using the Expression.Not(Expression.in) method. That makes me think hibernate is not implementing it the right way. The "in not" statement in mysql works, but "not in" does not.

Thanks a lot

michael · **Posted:** Fri Jan 02, 2004 1:50 pm

So is the generated SQL in your case now correct or not?

Ot · **Posted:** Fri Jan 02, 2004 2:02 pm

yeah, the sql is correct. but it generates a " select * from lotes where not id in...", which returns nothing. it should generate "select * from lotes where id not in...", which returns the expected rsults.
Problem still not solved ;( I am about to implement a pure java.util.List search .

michael · **Posted:** Fri Jan 02, 2004 2:05 pm

In your example you nowhere showed a not in criteria. I don't follow you. If you actually do use not in, you can use the Criterion I posted in the thread you referenced.