0

I want to perform a SELECT with several conditions, using Ibator's generated Example classes.

As described here in the usage notes, it's fairly easy to use criteria.andFieldIsSomething() and example.or(example) to compose a WHERE clause with multiple OR and AND operators.

I could just write this:

example.createCriteria().andIntegerIsEqualTo(int).andDateIsEqualTo(someday);
example.or(example.createCriteria().andIntegerIsNull().andDateIsEqualTo(someday));
example.or(example.createCriteria().andIntegerIsEqualTo(int).andDateIsNull());
example.or(example.createCriteria().andIntegerIsNull().andDateIsNull());

But first, it's kind of tedious and redundant, and it would output the following code:

SELECT * FROM zeTable
WHERE (integer = int AND date = someday)
OR (integer IS NULL AND date = someday)
OR (integer = int AND date IS NULL)
OR (integer IS NULL AND date IS NULL);

A more elegant (and probably efficient) way of writing that would be:

SELECT * FROM zeTable
WHERE (integer IS NULL OR integer = int)
AND (date IS NULL OR date = someday);

The test for NULL prevents me from using the .andFieldSomethingIn(List values) method efficiently, and while I kept this example simple, the code I have to write implies crossing 5 or 6 fields like that, which could amount to 36 different Criteria.

This seems ridiculous to me, so I assume there must be a better way. Can someone suggest something ?

Silver Quettier
  • 2,045
  • 2
  • 26
  • 53

5 Answers5

1

Tl;dr: is it not possible. Deal with it.

I spend some time investigating this, and this question was posted here for over a month. It seems there is no way to this with Ibator's Criteria and Example classes.

My guess is, it's not meant for that. Workaround: if you want complex clauses and effciency, write your own logic in the SQL_MAP.

It's what I ended up doing. If anyone else can offer a better answer later, I'll accept it.

Silver Quettier
  • 2,045
  • 2
  • 26
  • 53
1

When you want to work with complex SQL queries, consider using a different design approach

Try using these annotations and map results to bean , unable to find full article now but here is snippets on approach :-

org.apache.ibatis.annotations.Delete;
org.apache.ibatis.annotations.Insert;
org.apache.ibatis.annotations.Options;
org.apache.ibatis.annotations.Param;
org.apache.ibatis.annotations.Result;
org.apache.ibatis.annotations.Results;
org.apache.ibatis.annotations.Select;

final String SELECT_BY_ID = "SELECT * FROM CONTACT WHERE CONTACT_ID = #
{id}";

/**
* Returns a Contact instance from the database.
* @param id primary key value used for lookup.
* @return A Contact instance with a primary key value equals to pk. null
if there is no matching row.
*/
@Select(SELECT_BY_ID)
@Results(value = {
@Result(property="id"),
@Result(property="name", column="CONTACT_NAME"),
@Result(property="phone", column="CONTACT_PHONE"),
@Result(property="email", column="CONTACT_EMAIL")
})
Contact selectById(int id);
sundhar
  • 11
  • 1
0

I've also done a lot of search on this but found no way.

I think such functions in example/criteria classes should be used in many circumstances for complex queries. It would be better that mybatis generator can provide this directly.

Chet
  • 1
0

It's an old thread, I realize, but you could extend the example classes to include isNull, isEmpty, and other frequently used features.

user991945
  • 103
  • 1
  • 3
  • 14
0

I see below is the only possible way to do this. you can hack Generated Example Clause and add your own custom equal method

Example Class:

andIntegerIsCustomEqualTo(int){
  addCriterion("(integer is null OR integer="+int+")");
}

Use Custom example class Method As like below:

example.createCriteria().andIntegerIsCustomEqualTo(int);
SureshV
  • 1