You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Marek Šabo <ms...@buk.cvut.cz> on 2010/11/12 23:40:58 UTC
Dynamic SQL object query
Hi all,
I would like to ask few questions. I need to determine count of all
users depending on multivalued form filter.
Before trying I already knew this won't work:
new SQLTemplate(User.class, "SELECT #result('COUNT(id)' 'Long'
'count') from User #chain('AND' 'WHERE') #chunk($userId) id =
#bind($userId) #end #chunk($userName) userInfo.name LIKE $userName #end
#end");
becuase of "userInfo.name" which is object-style. So my first question
is whether there is a way of accomplishing this with EJBQL so I don't
have to work with joins there.
Second, what is the difference between #bind($a) and just $a. Both works
and I suppose it's close to similar.
And last, which is just informative, the parameters can't contain '.',
like $userInfo.name, because of parser?
Best regards,
--
Marek Šabo
Re: Dynamic SQL object query
Posted by Marek Šabo <ms...@buk.cvut.cz>.
Thanks to both of you,
that part regarding SQLI is especially important.
On 11/15/2010 01:43 PM, Mike Kienenberger wrote:
> Another important distinction is that the former will prevent SQL
> injection attacks, while the latter can make such attacks possible.
>
> http://en.wikipedia.org/wiki/SQL_injection
>
> On Sun, Nov 14, 2010 at 2:06 PM, Andrus Adamchik<an...@objectstyle.org> wrote:
>> On Nov 13, 2010, at 12:40 AM, Marek Šabo wrote:
>>
>>
>>> SELECT #result('COUNT(id)' 'Long' 'count')
>>> from User #chain('AND' 'WHERE') #chunk($userId) id = #bind($userId) #end #chunk($userName) userInfo.name LIKE $userName #end #end
>>> becuase of "userInfo.name" which is object-style. So my first question is whether there is a way of accomplishing this with EJBQL so I don't have to work with joins there.
>>
>> Yeah, something like this should work:
>>
>> SELECT COUNT(a) FROM User a WHERE a.id = :userId AND a.userInfo.name like :userName
>>
>>> Second, what is the difference between #bind($a) and just $a. Both works and I suppose it's close to similar.
>> The former results in a JDBC parameter inclusion and a call to PreparedStatement.setXYZ(a), the later is embedded in the SQL as a String. So the former let's JDBC driver to do the right type conversions and is a recommended way to insert *parameter* objects in the query. The later form can be used to dynamically build parts of SQL query that are not parameters. It will work for some parameters as well (e.g. Strings), but won't work for others (e.g. Date).
>>
>> Andrus
--
Marek Šabo
Server Manager
Club SU CVUT Buben
Bubenečská Kolej (421)
XMPP: zeratul021@gmail.com
Re: Dynamic SQL object query
Posted by Mike Kienenberger <mk...@gmail.com>.
Another important distinction is that the former will prevent SQL
injection attacks, while the latter can make such attacks possible.
http://en.wikipedia.org/wiki/SQL_injection
On Sun, Nov 14, 2010 at 2:06 PM, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On Nov 13, 2010, at 12:40 AM, Marek Šabo wrote:
>
>
>> SELECT #result('COUNT(id)' 'Long' 'count')
>> from User #chain('AND' 'WHERE') #chunk($userId) id = #bind($userId) #end #chunk($userName) userInfo.name LIKE $userName #end #end
>
>> becuase of "userInfo.name" which is object-style. So my first question is whether there is a way of accomplishing this with EJBQL so I don't have to work with joins there.
>
>
> Yeah, something like this should work:
>
> SELECT COUNT(a) FROM User a WHERE a.id = :userId AND a.userInfo.name like :userName
>
>> Second, what is the difference between #bind($a) and just $a. Both works and I suppose it's close to similar.
>
> The former results in a JDBC parameter inclusion and a call to PreparedStatement.setXYZ(a), the later is embedded in the SQL as a String. So the former let's JDBC driver to do the right type conversions and is a recommended way to insert *parameter* objects in the query. The later form can be used to dynamically build parts of SQL query that are not parameters. It will work for some parameters as well (e.g. Strings), but won't work for others (e.g. Date).
>
> Andrus
Re: Dynamic SQL object query
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Nov 21, 2010, at 6:18 PM, Marek Šabo wrote:
> 1) When 'id' is not object-mapped ejbql complains about invalid path, I found only this
> http://www.mail-archive.com/user@cayenne.apache.org/msg02992.html
> and it seems to be a dead end. Foolish attempt prefixing with 'db:' didn't work.
Unfortunately yes - it has to be mapped as an ObjAttribute. Another leftover from us trying to copy JPA :-/ We are thinking now how to reconcile it with Cayenne traditional APIs.
> 2) I thought null parameters were supported from 3.0 on, I'm using 3.0.1 and in the following ejbql query:
>
> "SELECT COUNT(u) FROM User u WHERE u.username like :usernam AND u.userInfo.name like :userName"
> eq.setParameter("userName", null);
> eq.setParameter("usernam", "msabo");
>
> results in java.lang.ClassCastException: org.apache.cayenne.ejbql.parser.EJBQLPatternValue cannot be cast to org.apache.cayenne.ejbql.parser.EJBQLEquals
Looks like a bug. Please log a Jira.
Thanks,
Andrus
Re: Dynamic SQL object query
Posted by Marek Šabo <ms...@buk.cvut.cz>.
>> SELECT #result('COUNT(id)' 'Long' 'count')
>> from User #chain('AND' 'WHERE') #chunk($userId) id = #bind($userId) #end #chunk($userName) userInfo.name LIKE $userName #end #end
>> becuase of "userInfo.name" which is object-style. So my first question is whether there is a way of accomplishing this with EJBQL so I don't have to work with joins there.
>
> Yeah, something like this should work:
>
> SELECT COUNT(a) FROM User a WHERE a.id = :userId AND a.userInfo.name like :userName
>
Hi,
I played around with this a little bit and I have encountered 2 problems:
1) When 'id' is not object-mapped ejbql complains about invalid path, I
found only this
http://www.mail-archive.com/user@cayenne.apache.org/msg02992.html
and it seems to be a dead end. Foolish attempt prefixing with 'db:'
didn't work.
2) I thought null parameters were supported from 3.0 on, I'm using 3.0.1
and in the following ejbql query:
"SELECT COUNT(u) FROM User u WHERE u.username like :usernam AND
u.userInfo.name like :userName"
eq.setParameter("userName", null);
eq.setParameter("usernam", "msabo");
results in java.lang.ClassCastException:
org.apache.cayenne.ejbql.parser.EJBQLPatternValue cannot be cast to
org.apache.cayenne.ejbql.parser.EJBQLEquals
Regards,
--
Marek Šabo
Re: Dynamic SQL object query
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Nov 13, 2010, at 12:40 AM, Marek Šabo wrote:
> SELECT #result('COUNT(id)' 'Long' 'count')
> from User #chain('AND' 'WHERE') #chunk($userId) id = #bind($userId) #end #chunk($userName) userInfo.name LIKE $userName #end #end
> becuase of "userInfo.name" which is object-style. So my first question is whether there is a way of accomplishing this with EJBQL so I don't have to work with joins there.
Yeah, something like this should work:
SELECT COUNT(a) FROM User a WHERE a.id = :userId AND a.userInfo.name like :userName
> Second, what is the difference between #bind($a) and just $a. Both works and I suppose it's close to similar.
The former results in a JDBC parameter inclusion and a call to PreparedStatement.setXYZ(a), the later is embedded in the SQL as a String. So the former let's JDBC driver to do the right type conversions and is a recommended way to insert *parameter* objects in the query. The later form can be used to dynamically build parts of SQL query that are not parameters. It will work for some parameters as well (e.g. Strings), but won't work for others (e.g. Date).
Andrus