You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Tobias SCHOESSLER <To...@unvienna.org> on 2006/03/29 09:09:19 UTC
Custom select string for Expressions?
I like cayenne expressions a lot, instead of writing complex joins in sql
these path expressions are very handy. Though I haven't found a way to
express things I use to have in the SELECT part of an SQL statement. When I
need distinct values, aggregations. how can I do things like SELECT
DISTINCT ... SUM(), etc.
Ideally I would like to use path Expression to define my select but apply
my own SELECT string for the execution. Is that possible?
thanks
Tobias
______________________________________
Tobias Schoessler, Java Developer
Information Management Unit
Information Technology Service
United Nations Office on Drugs and Crime
Tel: (+43-1) 26060-5173
Websites: www.unov.org, www.unodc.org
______________________________________
Impossible is not a fact, only an opinion
Re: Expression returning always 0 rows...
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Ivan,
On Mar 30, 2006, at 11:39 AM, I. Venuti wrote:
> [bind: '', '', '', '', '', '', '']
This is the cause of the problem. You are using empty strings ("")
for parameters. For a parameter to be excluded from processing it has
to be absent from parameter map (null or "" are both considered valid
parameters).
To achieve the desired result you'll have manually preprocess
parameter map before passing it to Cayenne, removing all empty string
values.
Andrus
Expression returning always 0 rows...
Posted by "I. Venuti" <i....@caribel.it>.
Hi,
I'm costructing an Expression for creating a SelectQuery:
final Expression template = Expression.fromString(
("db:dt_start=$data or db:dt_fine=$data "+
"and db:titolo_ita like $titolo and "+
"(db:titolo_ita like $chiave "+
"or db:titolo_eng like $chiave "+
"or db:abs_ita like $chiave "+
"or db:abs_eng like $chiave "+
")").toLowerCase()
);
I'm expeting that when there are no parameters I can retreave all the
rows in the table.
Unfortunatly this isn't the case: I alway retrieve 0 rows.
Here is the line executed (from the logger):
INFO [http-8090-Processor4 03-30 08:32:42] QueryLogger: SELECT
t0.abs_eng, t0.abs_ita, t0.codfis, t0.descr_eng, t0.descr_ita,
t0.dt_fine, t0.dt_ins, t0.dt_start, t0.in_home, t0.mostra_data,
t0.pubblica, t0.tipo, t0.titolo_eng, t0.titolo_ita, t0.id_news FROM
db.news t0 WHERE (t0.dt_start = (?)) OR ((t0.dt_fine = (?)) AND
(t0.titolo_ita LIKE (?)) AND ((t0.titolo_ita LIKE (?)) OR (t0.titolo_eng
LIKE (?)) OR (t0.abs_ita LIKE (?)) OR (t0.abs_eng LIKE (?)))) [bind: '',
'', '', '', '', '', ''] - prepared in 20 ms.
INFO [http-8090-Processor4 03-30 08:32:43] QueryLogger: === returned 0
rows. - took 40 ms.
Where is my mistake? Thank a lot!
-- Ivan
Re: Custom select string for Expressions?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Don't know Informix details, but generally DbAdapter takes care of
that in a db-specific manner. Our Informix adapter effort got stuck
for now [1]. So I suggest to first check whether
ExpressionFactpry.likeIgnoreCaseExp() works. If it doesn't, you have
two choices - customize the adapter for Informix or use SQLTemplate.
Andrus
* [1] http://objectstyle.org/jira/browse/CAY-300
On Mar 30, 2006, at 2:24 PM, I. Venuti wrote:
> I think that also for specify "lower( strField) like $what" I
> should use SQLTemplate, is it correct?
> I'm askig becouse I use Informix as database server and I must
> express that "like" operation should be done in a case-insensitive
> manner...
>
> Thanks
>
> -- Ivan
>
> Andrus Adamchik ha scritto:
>> Hi Tobias,
>>
>> Unfortunately mixing expressions and aggregate syntax is not
>> possible. You'd have to use SQLTemplate.
>>
>> Andrus
>>
>>
>> On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>>
>>>
>>> I like cayenne expressions a lot, instead of writing complex
>>> joins in sql
>>> these path expressions are very handy. Though I haven't found a
>>> way to
>>> express things I use to have in the SELECT part of an SQL
>>> statement. When I
>>> need distinct values, aggregations. how can I do things like SELECT
>>> DISTINCT ... SUM(), etc.
>>>
>>> Ideally I would like to use path Expression to define my select
>>> but apply
>>> my own SELECT string for the execution. Is that possible?
>>>
>>> thanks
>>>
>>> Tobias
>>>
>>> ______________________________________
>>> Tobias Schoessler, Java Developer
>>> Information Management Unit
>>> Information Technology Service
>>> United Nations Office on Drugs and Crime
>>>
>>> Tel: (+43-1) 26060-5173
>>> Websites: www.unov.org, www.unodc.org
>>> ______________________________________
>>> Impossible is not a fact, only an opinion
Re: Custom select string for Expressions?
Posted by "I. Venuti" <i....@caribel.it>.
I think that also for specify "lower( strField) like $what" I should use
SQLTemplate, is it correct?
I'm askig becouse I use Informix as database server and I must express
that "like" operation should be done in a case-insensitive manner...
Thanks
-- Ivan
Andrus Adamchik ha scritto:
> Hi Tobias,
>
> Unfortunately mixing expressions and aggregate syntax is not possible.
> You'd have to use SQLTemplate.
>
> Andrus
>
>
> On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>
>>
>> I like cayenne expressions a lot, instead of writing complex joins in
>> sql
>> these path expressions are very handy. Though I haven't found a way to
>> express things I use to have in the SELECT part of an SQL statement.
>> When I
>> need distinct values, aggregations. how can I do things like SELECT
>> DISTINCT ... SUM(), etc.
>>
>> Ideally I would like to use path Expression to define my select but
>> apply
>> my own SELECT string for the execution. Is that possible?
>>
>> thanks
>>
>> Tobias
>>
>> ______________________________________
>> Tobias Schoessler, Java Developer
>> Information Management Unit
>> Information Technology Service
>> United Nations Office on Drugs and Crime
>>
>> Tel: (+43-1) 26060-5173
>> Websites: www.unov.org, www.unodc.org
>> ______________________________________
>> Impossible is not a fact, only an opinion
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.3.3/295 - Release Date: 28/03/2006
>
>
Re: Custom select string for Expressions?
Posted by Tobias SCHOESSLER <To...@unvienna.org>.
Andrus,
I tried ... this is not really a marriage but it works for me. I post the
code, maybe someone finds it useful. I came up with a modified
SelectTranslator called CustomSelectTranslator, see code below ...
Attachments still don't work i guess.
My goal was to use cayenne Expressions to generate FROM and WHERE clauses
for a SQL statement. I would use these in combination with a custom Select
Clause for aggregations, distinct selects etc. .
This is basically a copy of the original cayenne SelectTranslator. I added
a property for customSelectString.
I replaced the part generating the Select String in createSqlString with
the customSelectString property.
I added a private initSqlString(String sqlString) borrowed from
QueryAssembler.initPreparedStatement. instead of preparing the statement it
substitutes the ? in the previously created SQL statement String with
values. Here I took a shortcut I guess ... creating correct SQL from sql
String types ...
Finally I altered newAliasForTable to generate sounding aliases so I can
use them in the custom Select clause. Another shortcut I guess, as I am not
using the full qualified table.
Tobias.
package org.unodc.util.cayenne;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.objectstyle.cayenne.CayenneRuntimeException;
import org.objectstyle.cayenne.access.trans.OrderingTranslator;
import org.objectstyle.cayenne.access.trans.QualifierTranslator;
import org.objectstyle.cayenne.access.trans.QueryAssembler;
import org.objectstyle.cayenne.access.trans.SelectQueryTranslator;
import org.objectstyle.cayenne.access.util.ResultDescriptor;
import org.objectstyle.cayenne.map.Attribute;
import org.objectstyle.cayenne.map.DbAttribute;
import org.objectstyle.cayenne.map.DbEntity;
import org.objectstyle.cayenne.map.DbJoin;
import org.objectstyle.cayenne.map.DbRelationship;
import org.objectstyle.cayenne.map.DerivedDbEntity;
import org.objectstyle.cayenne.map.EntityInheritanceTree;
import org.objectstyle.cayenne.map.ObjAttribute;
import org.objectstyle.cayenne.map.ObjEntity;
import org.objectstyle.cayenne.map.ObjRelationship;
import org.objectstyle.cayenne.query.PrefetchSelectQuery;
import org.objectstyle.cayenne.query.SelectQuery;
/**
* Class that serves as a translator of SELECT queries to JDBC statements.
*
* @author Andrei Adamchik
*/
public class CustomSelectTranslator extends QueryAssembler implements
SelectQueryTranslator {
protected static final int[] UNSUPPORTED_DISTINCT_TYPES = new int[] {
Types.BLOB, Types.CLOB, Types.LONGVARBINARY, Types.LONGVARCHAR };
protected static boolean isUnsupportedForDistinct(int type) {
for (int i = 0; i < UNSUPPORTED_DISTINCT_TYPES.length; i++) {
if (UNSUPPORTED_DISTINCT_TYPES[i] == type) {
return true;
}
}
return false;
}
private final Map aliasLookup = new HashMap();
private final List columnList = new ArrayList();
private final List tableList = new ArrayList();
private final List aliasList = new ArrayList();
private final List dbRelList = new ArrayList();
private List groupByList;
private int aliasCounter;
private boolean suppressingDistinct;
private String customSelectString;
public String getCustomSelectString() {
return customSelectString;
}
public void setCustomSelectString(String customSelectString) {
this.customSelectString = customSelectString;
}
/**
* If set to <code>true</code>, indicates that distinct select query
is
* required no matter what the original query settings where. This
flag can
* be set when joins are created using "to-many" relationships.
*/
private boolean forcingDistinct;
/**
* Returns a list of DbAttributes representing columns in this query.
*/
protected List getColumns() {
return columnList;
}
/**
* Returns query translated to SQL. This is a main work method of the
* SelectTranslator.
*/
public String createSqlString() throws Exception {
forcingDistinct = false;
// build column list
buildColumnList();
QualifierTranslator tr = adapter.getQualifierTranslator(this);
// build parent qualifier
// Parent qualifier translation must PRECEED main qualifier
// since it will be appended first and its parameters must
// go first as well
String parentQualifierStr = null;
if (getSelectQuery().isQualifiedOnParent()) {
tr.setTranslateParentQual(true);
parentQualifierStr = tr.doTranslation();
}
// build main qualifier
tr.setTranslateParentQual(false);
String qualifierStr = tr.doTranslation();
// build GROUP BY
buildGroupByList();
// build ORDER BY
OrderingTranslator orderingTranslator = new
OrderingTranslator(this);
String orderByStr = orderingTranslator.doTranslation();
// assemble
StringBuffer queryBuf = new StringBuffer();
queryBuf.append("SELECT ");
queryBuf.append(this.getCustomSelectString());
/* if (forcingDistinct || getSelectQuery().isDistinct()) {
// check if DISTINCT is appropriate
// side effect: "suppressingDistinct" flag may end up
being flipped
// here
suppressingDistinct = false;
Iterator it = getColumns().iterator();
while (it.hasNext()) {
DbAttribute attribute = (DbAttribute) it.next();
if (attribute != null &&
isUnsupportedForDistinct(attribute.getType())) {
suppressingDistinct = true;
break;
}
}
if (!suppressingDistinct) {
queryBuf.append("DISTINCT ");
}
}
List selectColumnExpList = new ArrayList();
for (int i = 0; i < columnList.size(); i++) {
selectColumnExpList.add(getColumn(i));
}
// append any column expressions used in the order by if this
query
// uses the DISTINCT modifier
if (forcingDistinct || getSelectQuery().isDistinct()) {
List orderByColumnList =
orderingTranslator.getOrderByColumnList();
for (int i = 0; i < orderByColumnList.size(); i++) {
String orderByColumnExp = (String)
orderByColumnList.get(i);
if (selectColumnExpList.contains(orderByColumnExp)
== false)
selectColumnExpList.add(orderByColumnExp);
}
}
// append columns (unroll the loop's first element)
int columnCount = selectColumnExpList.size();
queryBuf.append((String) selectColumnExpList.get(0));
// assume there is at least 1 element
for (int i = 1; i < columnCount; i++) {
queryBuf.append(", ");
queryBuf.append((String) selectColumnExpList.get(i));
}*/
// append from clause
queryBuf.append(" FROM ");
// append table list (unroll loop's 1st element)
int tableCount = tableList.size();
appendTable(queryBuf, 0); // assume there is at least 1 table
for (int i = 1; i < tableCount; i++) {
queryBuf.append(", ");
appendTable(queryBuf, i);
}
// append db relationship joins if any
boolean hasWhere = false;
int dbRelCount = dbRelList.size();
if (dbRelCount > 0) {
hasWhere = true;
queryBuf.append(" WHERE ");
appendDbRelJoins(queryBuf, 0);
for (int i = 1; i < dbRelCount; i++) {
queryBuf.append(" AND ");
appendDbRelJoins(queryBuf, i);
}
}
// append parent qualifier if any
if (parentQualifierStr != null) {
if (hasWhere) {
queryBuf.append(" AND (");
queryBuf.append(parentQualifierStr);
queryBuf.append(")");
} else {
hasWhere = true;
queryBuf.append(" WHERE ");
queryBuf.append(parentQualifierStr);
}
}
// append group by
boolean hasGroupBy = false;
if (groupByList != null) {
int groupByCount = groupByList.size();
if (groupByCount > 0) {
hasGroupBy = true;
queryBuf.append(" GROUP BY ");
appendGroupBy(queryBuf, 0);
for (int i = 1; i < groupByCount; i++) {
queryBuf.append(", ");
appendGroupBy(queryBuf, i);
}
}
}
// append qualifier
if (qualifierStr != null) {
if (hasGroupBy) {
queryBuf.append(" HAVING ");
queryBuf.append(qualifierStr);
} else {
if (hasWhere) {
queryBuf.append(" AND (");
queryBuf.append(qualifierStr);
queryBuf.append(")");
} else {
hasWhere = true;
queryBuf.append(" WHERE ");
queryBuf.append(qualifierStr);
}
}
}
// append prebuilt ordering
if (orderByStr != null) {
queryBuf.append(" ORDER BY ").append(orderByStr);
}
return initSqlString(queryBuf.toString());
}
/**
* Returns true if SelectTranslator determined that a query requiring
* DISTINCT can't be run with DISTINCT keyword for internal reasons.
If this
* method returns true, DataNode may need to do in-memory distinct
* filtering.
*
* @since 1.1
*/
public boolean isSuppressingDistinct() {
return suppressingDistinct;
}
private SelectQuery getSelectQuery() {
return (SelectQuery) getQuery();
}
/**
* Creates a list of columns used in the query.
*/
private void buildColumnList() {
newAliasForTable(getRootDbEntity());
appendAttributes();
}
/**
* Creates a list of columns used in the query's GROUP BY clause.
*/
private void buildGroupByList() {
DbEntity dbEntity = getRootDbEntity();
if (dbEntity instanceof DerivedDbEntity) {
groupByList = ((DerivedDbEntity)
dbEntity).getGroupByAttributes();
}
}
/**
* Returns a list of DbAttributes used in query.
*/
private void appendAttributes() {
DbEntity dbe = getRootDbEntity();
SelectQuery q = getSelectQuery();
// extract custom attributes from the query
if (q.isFetchingCustomAttributes()) {
List custAttrNames = q.getCustomDbAttributes();
int len = custAttrNames.size();
for (int i = 0; i < len; i++) {
Attribute attr = dbe.getAttribute((String)
custAttrNames.get(i));
if (attr == null) {
throw new CayenneRuntimeException("Attribute
does not exist: " + custAttrNames.get(i));
}
columnList.add(attr);
}
} else {
// build a list of attributes mentioned in ObjEntity +
PK's + FK's +
// GROUP
// BY's
ObjEntity oe = getRootEntity();
EntityInheritanceTree tree = null;
if (q.isResolvingInherited()) {
tree = getRootInheritanceTree();
}
// ObjEntity attrs
Iterator attrs = (tree != null) ?
tree.allAttributes().iterator() : oe.getAttributes().iterator();
while (attrs.hasNext()) {
ObjAttribute oa = (ObjAttribute) attrs.next();
Iterator dbPathIterator = oa.getDbPathIterator();
while (dbPathIterator.hasNext()) {
Object pathPart = dbPathIterator.next();
if (pathPart instanceof DbRelationship) {
DbRelationship rel = (DbRelationship)
pathPart;
dbRelationshipAdded(rel);
} else if (pathPart instanceof DbAttribute) {
DbAttribute dbAttr = (DbAttribute)
pathPart;
if (dbAttr == null) {
throw new
CayenneRuntimeException("ObjAttribute has no DbAttribute: " +
oa.getName());
}
if (!columnList.contains(dbAttr)) {
columnList.add(dbAttr);
}
}
}
}
// relationship keys
Iterator rels = (tree != null) ?
tree.allRelationships().iterator() : oe.getRelationships().iterator();
while (rels.hasNext()) {
ObjRelationship rel = (ObjRelationship)
rels.next();
DbRelationship dbRel = (DbRelationship)
rel.getDbRelationships().get(0);
List joins = dbRel.getJoins();
int jLen = joins.size();
for (int j = 0; j < jLen; j++) {
DbJoin join = (DbJoin) joins.get(j);
DbAttribute src = join.getSource();
if (!columnList.contains(src)) {
columnList.add(src);
}
}
}
// add remaining needed attrs from DbEntity
Iterator dbattrs = dbe.getPrimaryKey().iterator();
while (dbattrs.hasNext()) {
DbAttribute dba = (DbAttribute) dbattrs.next();
if (!columnList.contains(dba)) {
columnList.add(dba);
}
}
// May require some special handling for prefetch selects
// if the prefetch is of a certain type
if (q instanceof PrefetchSelectQuery) {
PrefetchSelectQuery pq = (PrefetchSelectQuery) q;
ObjRelationship r = pq.getLastPrefetchHint();
if ((r != null) && (r.getReverseRelationship() ==
null)) {
// Prefetching a single step toMany
relationship which
// has no reverse obj relationship. Add the
FK attributes
// of the relationship (wouldn't otherwise be
included)
DbRelationship dbRel = (DbRelationship)
r.getDbRelationships().get(0);
List joins = dbRel.getJoins();
int jLen = joins.size();
for (int j = 0; j < jLen; j++) {
DbJoin join = (DbJoin) joins.get(j);
DbAttribute target = join.getTarget();
if (!columnList.contains(target)) {
columnList.add(target);
}
}
}
}
}
}
private String getColumn(int index) {
DbAttribute attr = (DbAttribute) columnList.get(index);
String alias = aliasForTable((DbEntity) attr.getEntity());
return attr.getAliasedName(alias);
}
private void appendGroupBy(StringBuffer queryBuf, int index) {
DbAttribute attr = (DbAttribute) groupByList.get(index);
DbEntity ent = (DbEntity) attr.getEntity();
queryBuf.append(attr.getAliasedName(aliasForTable(ent)));
}
private void appendTable(StringBuffer queryBuf, int index) {
DbEntity ent = (DbEntity) tableList.get(index);
queryBuf.append(ent.getFullyQualifiedName());
// The alias should be the alias from the same index in
aliasList, not
// that
// returned by aliasForTable.
queryBuf.append(' ').append((String) aliasList.get(index));
}
private void appendDbRelJoins(StringBuffer queryBuf, int index) {
DbRelationship rel = (DbRelationship) dbRelList.get(index);
String srcAlias = aliasForTable((DbEntity)
rel.getSourceEntity());
String targetAlias = (String) aliasLookup.get(rel);
boolean andFlag = false;
List joins = rel.getJoins();
int len = joins.size();
for (int i = 0; i < len; i++) {
DbJoin join = (DbJoin) joins.get(i);
if (andFlag) {
queryBuf.append(" AND ");
} else {
andFlag = true;
}
queryBuf.append(srcAlias).append('.').append(join.getSourceName()).append("
= ").append(targetAlias).append('.').append(join.getTargetName());
}
}
/**
* Stores a new relationship in an internal list. Later it will be
used to
* create joins to relationship destination table.
*/
public void dbRelationshipAdded(DbRelationship rel) {
if (rel.isToMany()) {
forcingDistinct = true;
}
String existAlias = (String) aliasLookup.get(rel);
if (existAlias == null) {
dbRelList.add(rel);
// add alias for the destination table of the
relationship
String newAlias = newAliasForTable((DbEntity)
rel.getTargetEntity());
aliasLookup.put(rel, newAlias);
}
}
/**
* Sets up and returns a new alias for a speciafied table.
*/
protected String newAliasForTable(DbEntity ent) {
if (ent instanceof DerivedDbEntity) {
ent = ((DerivedDbEntity) ent).getParentEntity();
}
// tobias: let's make this sound so we can use the column aliases in the
custom select statement
// String newAlias = "t" + aliasCounter++;
String newAlias = ent.getName();
tableList.add(ent);
aliasList.add(newAlias);
return newAlias;
}
public String aliasForTable(DbEntity ent, DbRelationship rel) {
return (String) aliasLookup.get(rel);
}
/**
* Overrides superclass implementation. Will return an alias that
should be
* used for a specified DbEntity in the query (or null if this
DbEntity is
* not included in the FROM clause).
*/
public String aliasForTable(DbEntity ent) {
if (ent instanceof DerivedDbEntity) {
ent = ((DerivedDbEntity) ent).getParentEntity();
}
int entIndex = tableList.indexOf(ent);
if (entIndex >= 0) {
return (String) aliasList.get(entIndex);
} else {
StringBuffer msg = new StringBuffer();
msg.append("Alias not found, DbEntity: '").append(ent !=
null ? ent.getName() : "<null entity>").append("'\nExisting aliases:");
int len = aliasList.size();
for (int i = 0; i < len; i++) {
String dbeName = (tableList.get(i) != null) ?
((DbEntity) tableList.get(i)).getName() : "<null entity>";
msg.append("\n").append(aliasList.get(i)).append("
=> ").append(dbeName);
}
throw new CayenneRuntimeException(msg.toString());
}
}
public boolean supportsTableAliases() {
return true;
}
public ResultDescriptor getResultDescriptor(ResultSet rs) {
if (columnList.size() == 0) {
throw new CayenneRuntimeException("Call 'createStatement'
first");
}
ResultDescriptor descriptor;
if (getSelectQuery().isFetchingCustomAttributes()) {
descriptor = new
ResultDescriptor(getAdapter().getExtendedTypes());
} else {
descriptor = new
ResultDescriptor(getAdapter().getExtendedTypes(), getRootEntity());
}
descriptor.addColumns(columnList);
descriptor.index();
return descriptor;
}
// FROM QueryAssembler
/**
* Initializes Sql string with collected parameters.
* Called internally from "createStatement". Cayenne users
* shouldn't normally call it directly.
*/
private String initSqlString(String sqlString) throws Exception {
if (values != null && values.size() > 0) {
int len = values.size();
for (int i = 0; i < len; i++) {
Object val = values.get(i);
String valStr;
DbAttribute attr = (DbAttribute) attributes.get(i);
// null DbAttributes are a result of inferior qualifier
processing
// (qualifier can't map parameters to DbAttributes and
therefore
// only supports standard java types now)
// hence, a special moronic case here:
if (attr == null) {
// ???
// stmt.setObject(i + 1, val);
} else {
int type = attr.getType();
int precision = attr.getPrecision();
// adapter.bindParameter(stmt, val, i + 1, type,
precision);
// Surround String types with ' for Sybase
// probably bad but I do not understand how to do this
through the adapter ...
if (val instanceof String ) {
valStr = "'"+val+"'";
} else {
valStr = val.toString();
}
sqlString = sqlString.replaceFirst("\\?",valStr);
}
}
}
return sqlString;
}
}
______________________________________
Tobias Schoessler, Java Developer
Information Management Unit
Information Technology Service
United Nations Office on Drugs and Crime
Tel: (+43-1) 26060-5173
Websites: www.unov.org, www.unodc.org
______________________________________
Impossible is not a fact, only an opinion
Andrus Adamchik
<andrus@objectsty
le.org> To
cayenne-user@incubator.apache.org
Thursday, 30 cc
March 2006 16:27
Subject
Re: Custom select string for
Please respond to Expressions?
cayenne-user@incu
bator.apache.org
There is nothing that is user-friendly, but of course it would be
nice to marry the two.
SQLTemplate processing is based on a Velocity template, so it should
be theoretically possible to merge it with SQL generated by
SelectQuery translators. Can't predict how hard or easy this can be,
but look in access.jdbc and access.trans packages if you want to try
it yourself.
Andrus
On Mar 30, 2006, at 6:17 PM, Tobias SCHOESSLER wrote:
> ok, I am in for using SQLTemplate, no problem. But there must be
> something
> helping me create the WHERE/FROM clauses based on a given Expression.
>
> Tobias.
>
> ______________________________________
> Tobias Schoessler, Java Developer
> Information Management Unit
> Information Technology Service
> United Nations Office on Drugs and Crime
>
> Tel: (+43-1) 26060-5173
> Websites: www.unov.org, www.unodc.org
> ______________________________________
> Impossible is not a fact, only an opinion
>
>
>
> Andrus Adamchik
> <andrus@objectsty
>
> le.org> To
> cayenne-
> user@incubator.apache.org
> Wednesday,
> 29 cc
> March 2006 19:32
>
> Subject
> Re: Custom select string for
> Please respond to Expressions?
> cayenne-user@incu
> bator.apache.org
>
>
>
>
>
>
>
>
> Hi Tobias,
>
> Unfortunately mixing expressions and aggregate syntax is not
> possible. You'd have to use SQLTemplate.
>
> Andrus
>
>
> On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>
>>
>> I like cayenne expressions a lot, instead of writing complex joins
>> in sql
>> these path expressions are very handy. Though I haven't found a
>> way to
>> express things I use to have in the SELECT part of an SQL
>> statement. When I
>> need distinct values, aggregations. how can I do things like SELECT
>> DISTINCT ... SUM(), etc.
>>
>> Ideally I would like to use path Expression to define my select but
>> apply
>> my own SELECT string for the execution. Is that possible?
>>
>> thanks
>>
>> Tobias
>>
>> ______________________________________
>> Tobias Schoessler, Java Developer
>> Information Management Unit
>> Information Technology Service
>> United Nations Office on Drugs and Crime
>>
>> Tel: (+43-1) 26060-5173
>> Websites: www.unov.org, www.unodc.org
>> ______________________________________
>> Impossible is not a fact, only an opinion
>
>
>
>
Re: Path Expression => Attribute
Posted by Andrus Adamchik <an...@objectstyle.org>.
You need to prefix DB_PATH expressions with "db:". This is how
expression parser distinguishes between the two types of paths. E.g.:
public DbAttribute pathToDbAttribute(Class baseClass, String path) {
if(!path.startsWith("db:")) {
path = "db:" + path;
}
...
}
Andrus
On Apr 7, 2006, at 4:40 PM, Tobias SCHOESSLER wrote:
> Andrus, this worked fine, thank you. I use this method to lookup the
> ObjAttribute for a given path now.
>
> public ObjAttribute pathToObjAttribute(Class baseClass,
> String path)
> {
> ObjEntity rootEntity =
> ctxt.getEntityResolver().lookupObjEntity(baseClass);
> ObjAttribute attribute =
> (ObjAttribute)Expression.fromString(path).evaluate(rootEntity);
> return attribute;
> }
>
> In the simplest case I feed this the name of an attribute in the
> baseClass
> and it returns me the ObjAttribute. e.G. 'countryCode'
>
> However it turned out that I rather need to use Db Paths to also be
> able to
> specify PK values. I changed the method to
>
> public DbAttribute pathToDbAttribute(Class baseClass, String
> path) {
> DbEntity rootEntity =
> ctxt.getEntityResolver().lookupDbEntity(baseClass);
> DbAttribute attribute =
> (DbAttribute)Expression.fromString(path).evaluate(rootEntity);
> return attribute;
> }
>
> I expect to be able to feed it the corresponding database attribute
> name
> now e.G. COUNTRY_CODE but this fails with the exception
>
> Caused by: org.objectstyle.cayenne.exp.ExpressionException: [v.1.1.3
> September 28 2005] Invalid expression type: 'ObjPath', DB_PATH is
> expected.
> at
> org.objectstyle.cayenne.map.DbEntity.resolvePathComponents
> (DbEntity.java:249)
> at
> org.objectstyle.cayenne.exp.parser.ASTPath.evaluateEntityNode
> (ASTPath.java:108)
> at
> org.objectstyle.cayenne.exp.parser.ASTObjPath.evaluateNode
> (ASTObjPath.java:85)
> at
> org.objectstyle.cayenne.exp.parser.SimpleNode.evaluate
> (SimpleNode.java:243)
> ... 43 more
>
>
>
>
>
>
> Andrus Adamchik
> <andrus@objectsty
>
> le.org> To
> cayenne-
> user@incubator.apache.org
> Monday, 3
> April cc
> 2006 16:44
>
> Subject
> Re: Path Expression =>
> Attribute
> Please respond to
> cayenne-user@incu
> bator.apache.org
>
>
>
>
>
>
>
> You can evaluate an expression with root ObjEntity:
>
> String path = "toArtist.exhibitArray.closingDate";
> ObjEntity rootEntity = context.getEntityResolver().lookupObjEntity
> (Painting.class);
> ObjAttribute attribute = Expression.fromString(path).evaluate
> (rootEntity);
>
> Andrus
>
> On Apr 3, 2006, at 6:36 PM, Tobias SCHOESSLER wrote:
>
>> hi,
>>
>> How do I get from a path expression to the resulting attribute
>> object.
>>
>> E.g. Is there anything that I can use to parse a path like
>>
>> toArtist.exhibitArray.closingDate
>>
>> and get the corresponding ObjAttribute for closingDate.
>>
>> As a precondition i would assume that the path evaluates to an
>> attribute.
>>
>> thanks
>>
>> regards
>>
>> Tobias
>>
>>
>
>
>
>
Re: Path Expression => Attribute
Posted by Tobias SCHOESSLER <To...@unvienna.org>.
Andrus, this worked fine, thank you. I use this method to lookup the
ObjAttribute for a given path now.
public ObjAttribute pathToObjAttribute(Class baseClass, String path)
{
ObjEntity rootEntity =
ctxt.getEntityResolver().lookupObjEntity(baseClass);
ObjAttribute attribute =
(ObjAttribute)Expression.fromString(path).evaluate(rootEntity);
return attribute;
}
In the simplest case I feed this the name of an attribute in the baseClass
and it returns me the ObjAttribute. e.G. 'countryCode'
However it turned out that I rather need to use Db Paths to also be able to
specify PK values. I changed the method to
public DbAttribute pathToDbAttribute(Class baseClass, String path) {
DbEntity rootEntity =
ctxt.getEntityResolver().lookupDbEntity(baseClass);
DbAttribute attribute =
(DbAttribute)Expression.fromString(path).evaluate(rootEntity);
return attribute;
}
I expect to be able to feed it the corresponding database attribute name
now e.G. COUNTRY_CODE but this fails with the exception
Caused by: org.objectstyle.cayenne.exp.ExpressionException: [v.1.1.3
September 28 2005] Invalid expression type: 'ObjPath', DB_PATH is
expected.
at
org.objectstyle.cayenne.map.DbEntity.resolvePathComponents(DbEntity.java:249)
at
org.objectstyle.cayenne.exp.parser.ASTPath.evaluateEntityNode(ASTPath.java:108)
at
org.objectstyle.cayenne.exp.parser.ASTObjPath.evaluateNode(ASTObjPath.java:85)
at
org.objectstyle.cayenne.exp.parser.SimpleNode.evaluate(SimpleNode.java:243)
... 43 more
Andrus Adamchik
<andrus@objectsty
le.org> To
cayenne-user@incubator.apache.org
Monday, 3 April cc
2006 16:44
Subject
Re: Path Expression => Attribute
Please respond to
cayenne-user@incu
bator.apache.org
You can evaluate an expression with root ObjEntity:
String path = "toArtist.exhibitArray.closingDate";
ObjEntity rootEntity = context.getEntityResolver().lookupObjEntity
(Painting.class);
ObjAttribute attribute = Expression.fromString(path).evaluate
(rootEntity);
Andrus
On Apr 3, 2006, at 6:36 PM, Tobias SCHOESSLER wrote:
> hi,
>
> How do I get from a path expression to the resulting attribute
> object.
>
> E.g. Is there anything that I can use to parse a path like
>
> toArtist.exhibitArray.closingDate
>
> and get the corresponding ObjAttribute for closingDate.
>
> As a precondition i would assume that the path evaluates to an
> attribute.
>
> thanks
>
> regards
>
> Tobias
>
>
Re: Path Expression => Attribute
Posted by Andrus Adamchik <an...@objectstyle.org>.
You can evaluate an expression with root ObjEntity:
String path = "toArtist.exhibitArray.closingDate";
ObjEntity rootEntity = context.getEntityResolver().lookupObjEntity
(Painting.class);
ObjAttribute attribute = Expression.fromString(path).evaluate
(rootEntity);
Andrus
On Apr 3, 2006, at 6:36 PM, Tobias SCHOESSLER wrote:
> hi,
>
> How do I get from a path expression to the resulting attribute
> object.
>
> E.g. Is there anything that I can use to parse a path like
>
> toArtist.exhibitArray.closingDate
>
> and get the corresponding ObjAttribute for closingDate.
>
> As a precondition i would assume that the path evaluates to an
> attribute.
>
> thanks
>
> regards
>
> Tobias
>
>
Path Expression => Attribute
Posted by Tobias SCHOESSLER <To...@unvienna.org>.
hi,
How do I get from a path expression to the resulting attribute object.
E.g. Is there anything that I can use to parse a path like
toArtist.exhibitArray.closingDate
and get the corresponding ObjAttribute for closingDate.
As a precondition i would assume that the path evaluates to an attribute.
thanks
regards
Tobias
Re: Custom select string for Expressions?
Posted by Andrus Adamchik <an...@objectstyle.org>.
There is nothing that is user-friendly, but of course it would be
nice to marry the two.
SQLTemplate processing is based on a Velocity template, so it should
be theoretically possible to merge it with SQL generated by
SelectQuery translators. Can't predict how hard or easy this can be,
but look in access.jdbc and access.trans packages if you want to try
it yourself.
Andrus
On Mar 30, 2006, at 6:17 PM, Tobias SCHOESSLER wrote:
> ok, I am in for using SQLTemplate, no problem. But there must be
> something
> helping me create the WHERE/FROM clauses based on a given Expression.
>
> Tobias.
>
> ______________________________________
> Tobias Schoessler, Java Developer
> Information Management Unit
> Information Technology Service
> United Nations Office on Drugs and Crime
>
> Tel: (+43-1) 26060-5173
> Websites: www.unov.org, www.unodc.org
> ______________________________________
> Impossible is not a fact, only an opinion
>
>
>
> Andrus Adamchik
> <andrus@objectsty
>
> le.org> To
> cayenne-
> user@incubator.apache.org
> Wednesday,
> 29 cc
> March 2006 19:32
>
> Subject
> Re: Custom select string for
> Please respond to Expressions?
> cayenne-user@incu
> bator.apache.org
>
>
>
>
>
>
>
>
> Hi Tobias,
>
> Unfortunately mixing expressions and aggregate syntax is not
> possible. You'd have to use SQLTemplate.
>
> Andrus
>
>
> On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>
>>
>> I like cayenne expressions a lot, instead of writing complex joins
>> in sql
>> these path expressions are very handy. Though I haven't found a
>> way to
>> express things I use to have in the SELECT part of an SQL
>> statement. When I
>> need distinct values, aggregations. how can I do things like SELECT
>> DISTINCT ... SUM(), etc.
>>
>> Ideally I would like to use path Expression to define my select but
>> apply
>> my own SELECT string for the execution. Is that possible?
>>
>> thanks
>>
>> Tobias
>>
>> ______________________________________
>> Tobias Schoessler, Java Developer
>> Information Management Unit
>> Information Technology Service
>> United Nations Office on Drugs and Crime
>>
>> Tel: (+43-1) 26060-5173
>> Websites: www.unov.org, www.unodc.org
>> ______________________________________
>> Impossible is not a fact, only an opinion
>
>
>
>
Re: Custom select string for Expressions?
Posted by Tobias SCHOESSLER <To...@unvienna.org>.
ok, I am in for using SQLTemplate, no problem. But there must be something
helping me create the WHERE/FROM clauses based on a given Expression.
Tobias.
______________________________________
Tobias Schoessler, Java Developer
Information Management Unit
Information Technology Service
United Nations Office on Drugs and Crime
Tel: (+43-1) 26060-5173
Websites: www.unov.org, www.unodc.org
______________________________________
Impossible is not a fact, only an opinion
Andrus Adamchik
<andrus@objectsty
le.org> To
cayenne-user@incubator.apache.org
Wednesday, 29 cc
March 2006 19:32
Subject
Re: Custom select string for
Please respond to Expressions?
cayenne-user@incu
bator.apache.org
Hi Tobias,
Unfortunately mixing expressions and aggregate syntax is not
possible. You'd have to use SQLTemplate.
Andrus
On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>
> I like cayenne expressions a lot, instead of writing complex joins
> in sql
> these path expressions are very handy. Though I haven't found a way to
> express things I use to have in the SELECT part of an SQL
> statement. When I
> need distinct values, aggregations. how can I do things like SELECT
> DISTINCT ... SUM(), etc.
>
> Ideally I would like to use path Expression to define my select but
> apply
> my own SELECT string for the execution. Is that possible?
>
> thanks
>
> Tobias
>
> ______________________________________
> Tobias Schoessler, Java Developer
> Information Management Unit
> Information Technology Service
> United Nations Office on Drugs and Crime
>
> Tel: (+43-1) 26060-5173
> Websites: www.unov.org, www.unodc.org
> ______________________________________
> Impossible is not a fact, only an opinion
Re: Custom select string for Expressions?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Tobias,
Unfortunately mixing expressions and aggregate syntax is not
possible. You'd have to use SQLTemplate.
Andrus
On Mar 29, 2006, at 11:09 AM, Tobias SCHOESSLER wrote:
>
> I like cayenne expressions a lot, instead of writing complex joins
> in sql
> these path expressions are very handy. Though I haven't found a way to
> express things I use to have in the SELECT part of an SQL
> statement. When I
> need distinct values, aggregations. how can I do things like SELECT
> DISTINCT ... SUM(), etc.
>
> Ideally I would like to use path Expression to define my select but
> apply
> my own SELECT string for the execution. Is that possible?
>
> thanks
>
> Tobias
>
> ______________________________________
> Tobias Schoessler, Java Developer
> Information Management Unit
> Information Technology Service
> United Nations Office on Drugs and Crime
>
> Tel: (+43-1) 26060-5173
> Websites: www.unov.org, www.unodc.org
> ______________________________________
> Impossible is not a fact, only an opinion