Hi,
one thing I learn about Crieria, is when you have complex SQL statement
to write, use Criteria.CUSTOM !
That way you have the full control of your where-clause :
Criteria crit = new Criteria();
crit.add(PersonPeer.FIRST_NAME, "UPPER(" + PersonPeer.LAST_NAME + " || "
+ PersonPeer.FIRST_NAME + ") LIKE UPPER( ? )", Criteria.CUSTOM);
You could try to be more db-independant using crit.getDb().ignoreCase(
String ), but I didn't found anything for the concatenation symbol ' || ' .
On my project, we created a specific Criteria with helper metthods like
this one :
(sorry for the french-comments ..)
/**
* Ajout d'un critere UPPER( column ) LIKE UPPER( '%' + likeClause
+ '%' ) .
* Le '%' n'est ajout� en debut et fin de clause que si likeClause
ne contient aucun caractere '%'.
* likeClause est echap�e et mise entre quote, par
SqlExpression.quoteAndEscapeText()
* @param column column a comparer
* @param likeClause critere de comparaison
*/
public void addLikeIgnoreCase(String column, String likeClause) {
try {
DB db = getDb();
//si on ne trouve pas de caracterer %, on en rajoute un a la
fin.
if ((likeClause != null) && (likeClause.length() != 0)) {
if (likeClause != null && likeClause.indexOf('%') == -1) {
likeClause = "%" + likeClause + "%";
}
//on echappe et quote la chaine de comparaison ;
likeClause =
SqlExpression.quoteAndEscapeText(likeClause, db);
this.add(column, (Object) (db.ignoreCase(column) + " " +
Criteria.LIKE + " " + db.ignoreCase(likeClause)), Criteria.CUSTOM);
}
} catch (TorqueException te) {
logger.error("", te);
throw new IllegalStateException(te.getMessage());
}
}
With such helper methods, your statement could be simplified with ;
MyCriteria crit = new MyCriteria();
crit.addLikeIgnoreCase(PersonPeer.LAST_NAME + " || " +
PersonPeer.FIRST_NAME, <paramValue>);
(not tested)
Bye,
Alexis Haumont.
Taavi Tiirik wrote:
>Dear torque users,
>
>Please help me with writing following select criteria for torque.
>
>I have person table with fields first_name and last_name.
>Sql equivalent of the query looks like this:
>
>select * from person where
>upper( last_name || ' ' || first_name ) like upper( ? )
>
>Given parameter can be something like this:
> "%Smith J%" => returns all John Smiths,
> "%john%" => returns all Johns, etc.
>
>How can it be done with Torque? Can it be done with Torque?
>
>with best wishes,
>Taavi
>
>
>
>--
>To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
>For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
