-
1. Re: Checking a field against a list of values
sesques May 20, 2004 7:23 AM (in response to guidoscalise)HI,
JBossQL supports parameters in the IN operator. But is the number of your parameters always the same ?
If not, you should use DynamicQL instead.
Pascal -
2. Re: Checking a field against a list of values
guidoscalise May 20, 2004 7:27 AM (in response to guidoscalise)"sesques" wrote:
HI,
JBossQL supports parameters in the IN operator. But is the number of your parameters always the same ?
If not, you should use DynamicQL instead.
Pascal
Thanks for your prompt reply!
No, the list of parameters varies... :(
I've been searching all the web looking for DynamycQL/JBossQL examples/tutorials/documentation, but I haven't found anything... Where can I access some (JBoss/Dynamic)QL documentation?
Kind Regards,
Guido Scalise
Buenos Aires - Argentina -
3. Re: Checking a field against a list of values
sesques May 20, 2004 7:33 AM (in response to guidoscalise)Hi,
Perhaps a bad news to you. JBossQL and DynamicQL is documented in the JBoss DOCO, and you have to buy it.
The DOCO is "JBoss AdminDevel Documentation".
Pascal -
4. Re: Checking a field against a list of values
hernant May 20, 2004 7:58 AM (in response to guidoscalise)Que haces Guido,
te paso un ejemplo de un entity bean usando xdoclet:
a nivel de clase (despues de los imports) pone estos tags:
* @ejb.select
* signature="java.util.Collection ejbSelectGeneric( java.lang.String jbossql, java.lang.Object[] args )"
* result-type-mapping="Local"
* method-intf="LocalHome"
* query=""
* @jboss.query
* signature="java.util.Collection ejbSelectGeneric( java.lang.String jbossql, java.lang.Object[] args )"
* dynamic="true"
*
despues agregás estos metodos al entity mismo:
/**
* @jboss.dynamic-ql
*/
public abstract java.util.Collection ejbSelectGeneric ( String jbossQl, Object[]arguments ) throws FinderException;
/**
* @ejb.home-method
* tview-type="local"
*/
public java.util.Collection ejbHomeBusquedasGenericas(
String node_name, String type, String alias_name, String description, String login, String passwd, String transfer_dir, String host_alias, String operational_status, String ip_address, String cv_id, String node_type ) throws javax.ejb.FinderException{
java.util.Collection params = new java.util.ArrayList();
int paramNumber = 1;
boolean yaHayUno = false;
// generate JBossQL query
StringBuffer jbossQl = new StringBuffer();
jbossQl.append("SELECT OBJECT(u) ");
jbossQl.append("FROM Dyt_com_nodeSchema AS u ");
if (
(node_name!=null
&& !node_name.equalsIgnoreCase("todos")
&& !node_name.equalsIgnoreCase("")
)
|| (type!=null
&& !type.equalsIgnoreCase("todos")
&& !type.equalsIgnoreCase("")
)
|| (alias_name!=null
&& !alias_name.equalsIgnoreCase("todos")
&& !alias_name.equalsIgnoreCase("")
)
|| (description!=null
&& !description.equalsIgnoreCase("todos")
&& !description.equalsIgnoreCase("")
)
|| (login!=null
&& !login.equalsIgnoreCase("todos")
&& !login.equalsIgnoreCase("")
)
|| (passwd!=null
&& !passwd.equalsIgnoreCase("todos")
&& !passwd.equalsIgnoreCase("")
)
|| (transfer_dir!=null
&& !transfer_dir.equalsIgnoreCase("todos")
&& !transfer_dir.equalsIgnoreCase("")
)
|| (host_alias!=null
&& !host_alias.equalsIgnoreCase("todos")
&& !host_alias.equalsIgnoreCase("")
)
|| (operational_status!=null
&& !operational_status.equalsIgnoreCase("todos")
&& !operational_status.equalsIgnoreCase("")
)
|| (ip_address!=null
&& !ip_address.equalsIgnoreCase("todos")
&& !ip_address.equalsIgnoreCase("")
)
|| (cv_id!=null
&& !cv_id.equalsIgnoreCase("todos")
&& !cv_id.equalsIgnoreCase("")
)
|| (node_type!=null
&& !node_type.equalsIgnoreCase("todos")
&& !node_type.equalsIgnoreCase("")
)
)
{
jbossQl.append("WHERE");
}
if (node_name != null
&& !node_name.equalsIgnoreCase("todos")
&& !node_name.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.node_name=?" + paramNumber++);
params.add(node_name);
yaHayUno=true;
}
if (type != null
&& !type.equalsIgnoreCase("todos")
&& !type.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.type=?" + paramNumber++);
params.add(type);
yaHayUno=true;
}
if (alias_name != null
&& !alias_name.equalsIgnoreCase("todos")
&& !alias_name.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.alias_name=?" + paramNumber++);
params.add(alias_name);
yaHayUno=true;
}
if (description != null
&& !description.equalsIgnoreCase("todos")
&& !description.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.description=?" + paramNumber++);
params.add(description);
yaHayUno=true;
}
if (login != null
&& !login.equalsIgnoreCase("todos")
&& !login.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.login=?" + paramNumber++);
params.add(login);
yaHayUno=true;
}
if (passwd != null
&& !passwd.equalsIgnoreCase("todos")
&& !passwd.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.passwd=?" + paramNumber++);
params.add(passwd);
yaHayUno=true;
}
if (transfer_dir != null
&& !transfer_dir.equalsIgnoreCase("todos")
&& !transfer_dir.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.transfer_dir=?" + paramNumber++);
params.add(transfer_dir);
yaHayUno=true;
}
if (host_alias != null
&& !host_alias.equalsIgnoreCase("todos")
&& !host_alias.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.host_alias=?" + paramNumber++);
params.add(host_alias);
yaHayUno=true;
}
if (operational_status != null
&& !operational_status.equalsIgnoreCase("todos")
&& !operational_status.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.operational_status=?" + paramNumber++);
params.add(operational_status);
yaHayUno=true;
}
if (ip_address != null
&& !ip_address.equalsIgnoreCase("todos")
&& !ip_address.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.ip_address=?" + paramNumber++);
params.add(ip_address);
yaHayUno=true;
}
if (cv_id != null
&& !cv_id.equalsIgnoreCase("todos")
&& !cv_id.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.cv_id=?" + paramNumber++);
params.add(cv_id);
yaHayUno=true;
}
if (node_type != null
&& !node_type.equalsIgnoreCase("todos")
&& !node_type.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.node_type=?" + paramNumber++);
params.add(node_type);
yaHayUno=true;
}
Object[] args = params.toArray(new Object[params.size()]);
//ejecuto ejbql
Collection col=ejbSelectGeneric ( jbossQl.toString(), args );
//si la consulta anterior no me devolvio ningun registro, pruebo con los LIKE
if(col.size()==0)
{
paramNumber = 1;
yaHayUno = false;
// generate JBossQL query
jbossQl = new StringBuffer();
jbossQl.append("SELECT OBJECT(u) ");
jbossQl.append("FROM Dyt_com_nodeSchema AS u ");
if (
(node_name!=null
&& !node_name.equalsIgnoreCase("todos")
&& !node_name.equalsIgnoreCase("")
)
|| (type!=null
&& !type.equalsIgnoreCase("todos")
&& !type.equalsIgnoreCase("")
)
|| (alias_name!=null
&& !alias_name.equalsIgnoreCase("todos")
&& !alias_name.equalsIgnoreCase("")
)
|| (description!=null
&& !description.equalsIgnoreCase("todos")
&& !description.equalsIgnoreCase("")
)
|| (login!=null
&& !login.equalsIgnoreCase("todos")
&& !login.equalsIgnoreCase("")
)
|| (passwd!=null
&& !passwd.equalsIgnoreCase("todos")
&& !passwd.equalsIgnoreCase("")
)
|| (transfer_dir!=null
&& !transfer_dir.equalsIgnoreCase("todos")
&& !transfer_dir.equalsIgnoreCase("")
)
|| (host_alias!=null
&& !host_alias.equalsIgnoreCase("todos")
&& !host_alias.equalsIgnoreCase("")
)
|| (operational_status!=null
&& !operational_status.equalsIgnoreCase("todos")
&& !operational_status.equalsIgnoreCase("")
)
|| (ip_address!=null
&& !ip_address.equalsIgnoreCase("todos")
&& !ip_address.equalsIgnoreCase("")
)
|| (cv_id!=null
&& !cv_id.equalsIgnoreCase("todos")
&& !cv_id.equalsIgnoreCase("")
)
|| (node_type!=null
&& !node_type.equalsIgnoreCase("todos")
&& !node_type.equalsIgnoreCase("")
)
)
{
jbossQl.append("WHERE");
}
if (node_name != null
&& !node_name.equalsIgnoreCase("todos")
&& !node_name.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.node_name LIKE '%" + node_name.toString() + "%'");
yaHayUno=true;
}
if (type != null
&& !type.equalsIgnoreCase("todos")
&& !type.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.type LIKE '%" + type.toString() + "%'");
yaHayUno=true;
}
if (alias_name != null
&& !alias_name.equalsIgnoreCase("todos")
&& !alias_name.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.alias_name LIKE '%" + alias_name.toString() + "%'");
yaHayUno=true;
}
if (description != null
&& !description.equalsIgnoreCase("todos")
&& !description.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.description LIKE '%" + description.toString() + "%'");
yaHayUno=true;
}
if (login != null
&& !login.equalsIgnoreCase("todos")
&& !login.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.login LIKE '%" + login.toString() + "%'");
yaHayUno=true;
}
if (passwd != null
&& !passwd.equalsIgnoreCase("todos")
&& !passwd.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.passwd LIKE '%" + passwd.toString() + "%'");
yaHayUno=true;
}
if (transfer_dir != null
&& !transfer_dir.equalsIgnoreCase("todos")
&& !transfer_dir.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.transfer_dir LIKE '%" + transfer_dir.toString() + "%'");
yaHayUno=true;
}
if (host_alias != null
&& !host_alias.equalsIgnoreCase("todos")
&& !host_alias.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.host_alias LIKE '%" + host_alias.toString() + "%'");
yaHayUno=true;
}
if (operational_status != null
&& !operational_status.equalsIgnoreCase("todos")
&& !operational_status.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.operational_status LIKE '%" + operational_status.toString() + "%'");
yaHayUno=true;
}
if (ip_address != null
&& !ip_address.equalsIgnoreCase("todos")
&& !ip_address.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.ip_address LIKE '%" + ip_address.toString() + "%'");
yaHayUno=true;
}
if (cv_id != null
&& !cv_id.equalsIgnoreCase("todos")
&& !cv_id.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.cv_id LIKE '%" + cv_id.toString() + "%'");
yaHayUno=true;
}
if (node_type != null
&& !node_type.equalsIgnoreCase("todos")
&& !node_type.equalsIgnoreCase("")
){
if (yaHayUno) jbossQl.append(" AND");
jbossQl.append(" u.node_type LIKE '%" + node_type.toString() + "%'");
yaHayUno=true;
}
args = params.toArray(new Object[params.size()]);
col=ejbSelectGeneric ( jbossQl.toString(), args );
}
return col;
}
lo que te estoy pasando es un ejemplo mío, reemplazalo con lo que vos necesitas.
Me interesa saber en donde estan laburando con jboss aca en buenos aires, pensé que era el unico...
Saludos,
Hernan Terzian.
PD: che, espero aparecer en los creditos de tu codigo ;) -
5. Re: Checking a field against a list of values
be082032 May 2, 2006 4:50 PM (in response to guidoscalise)Muchos gracias Hernan,
you really helped me out here, and your example worked from the first time.
Great stuff!!
Jean-Paul