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/04/03 17:11:44 UTC
Re: Custom select string for Expressions?
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
>
>
>
>