Two comments.
First, for the query you're running, you probably want an object
select query, rather than SQLTemplate:
SelectQuery q = new SelectQuery(Person.class);
Expression qual =
ExpressionFactory.matchExp(Person.COUNTRY_PROPERTY,country);
qual =
qual.andExp(ExpressionFactory.matchExp(Person.NAME_PROPERTY,name);
q.setQualifier(qual);
dataContext().performQuery(qual);
I typically use SQLTemplate for performance reasons, or to express
some query that doesn't necessarily correlate to a particular object
in the db (aggregrate-function queries, like sums, counts, etc.).
Otherwise, I stick with object-select queries. You could even create
the above object select query in the modeler and just reference it by
name in the code:
dataContext.performQuery("PersonSelectQuery",params);//2.0.4 only; for
3.0, you would used a NamedQuery object; params is a map with the
appropriate parameters, as below.
But if you really want to use SQLTemplate, then you should do two
things:
1) use #result to describe the results that cayenne should expect
from your query (instead of select * from, use select #result(...)[,
#result(...)] from).
2) use #bind or #bindEqual directives for parameter binding. This
ultimately results in Cayenne using prepared statements and plugging
in parameters supplied from a map at runtime, so that the parameter is
properly escaped by the database driver when you execute the query.
For example:
SQLTemplate template = new SQLTemplate(Person.class,
"SELECT #result(...) FROM people where country_id
#bindEqual($countryId) and name #bindEqual($name)");
Map<String,Object> params = new HashMap<String,Object>();
params.put("countryId",country.getId());
params.put("name",name);
template.setParameters(params);
dataContext().performQuery(template);
See: http://cayenne.apache.org/doc20/scripting-sqltemplate.html for
more information on #result, #bind, and #bindEqual
See: http://cayenne.apache.org/doc20/selectquery.html for more
information on select queries.
Robert
On Feb 11, 2009, at 2/1110:45 PM , Mark Fraser wrote:
hello,
I am using Cayenne 2.0.4 in a standalone application with Derby
embedded.
The following code breaks for obvious reasons when there is an
apostrophe ("'") in the person's name.
---------
SQLTemplate template = new SQLTemplate(Person.class, "SELECT * FROM
people where country_id=" + String.valueOf(country.getId()) + " and
name='" + name + "'");
List res = dataContext().performQuery(template);
---------
What is the best (Cayenne specific or otherwise) approach to dealing
with this problem?
Thanks
