John McNally wrote:
>
> Let me start off by saying I have been working on making the Peer system
> handle OR where clauses. And I have added the ability to alias tables
> as well.
>
Here is the diff to allow adding OR clauses to a Criteria as well as
being able to specify a table alias. I would appreciate anyone who has
the time to test that I did not break anything in your existing app and
if anyone has any queries that could make use of this functionality you
could test it. Remember when adding OR clauses you do not have all the
convenience methods, so you will need to wrap primitive types yourself.
Index: org/apache/turbine/om/peer/BasePeer.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/om/peer/BasePeer.java,v
retrieving revision 1.23
diff -u -r1.23 BasePeer.java
--- org/apache/turbine/om/peer/BasePeer.java 2001/01/26 23:28:40 1.23
+++ org/apache/turbine/om/peer/BasePeer.java 2001/01/29 23:14:49
@@ -102,7 +102,7 @@
public static final String TABLE_NAME = "TABLE_NAME";
/** Are we in DEBUG mode? */
- private static final boolean DEBUG = false;
+ private static final boolean DEBUG = true;
/** The Turbine default MapBuilder. */
public static final String DEFAULT_MAP_BUILDER =
@@ -472,7 +472,25 @@
while(e.hasMoreElements())
{
String key = (String)e.nextElement();
- tables.add(criteria.getTableName(key));
+ Criteria.Criterion c = criteria.getCriterion(key);
+ String[] tableNames = c.getAllTables();
+ for (int i=0; i<tableNames.length; i++)
+ {
+ String tableName2 =
criteria.getTableForAlias(tableNames[i]);
+ if ( tableName2 != null )
+ {
+ tables.add(
+ new StringBuffer(tableNames[i].length() +
+ tableName2.length() + 1)
+ .append(tableName2).append('
').append(tableNames[i])
+ .toString() );
+ }
+ else
+ {
+ tables.add(tableNames[i]);
+ }
+ }
+
if ( criteria.isCascade() )
{
// This steps thru all the columns in the database.
@@ -849,73 +867,72 @@
for (int i=0; i<select.size(); i++)
{
String columnName = select.get(i);
+ String tableName = null;
selectClause.add(columnName);
int braceplace = columnName.indexOf('(');
if(braceplace == -1)
{
- fromClause.add(columnName.substring(0,
-
columnName.indexOf('.') ));
+ tableName = columnName.substring(0,
+
columnName.indexOf('.') );
}
else
{
- fromClause.add(columnName.substring(braceplace+1,
-
columnName.indexOf('.') ));
+ tableName = columnName.substring(braceplace+1,
+
columnName.indexOf('.') );
}
+ String tableName2 = criteria.getTableForAlias(tableName);
+ if ( tableName2 != null )
+ {
+ fromClause.add(
+ new StringBuffer(tableName.length() +
+ tableName2.length() + 1)
+ .append(tableName2).append(' ').append(tableName)
+ .toString() );
+ }
+ else
+ {
+ fromClause.add(tableName);
+ }
}
Enumeration e = criteria.keys();
while(e.hasMoreElements())
{
String key = (String)e.nextElement();
- String table = criteria.getTableName(key);
- fromClause.add( table );
- if ( criteria.getComparison(key).equals(Criteria.CUSTOM) )
- {
- String custom = criteria.getString(key);
- if ( !custom.equals("") )
- whereClause.add( custom );
- }
- else
- {
- // Would like to take care of a few simple Sql cases
- // here so that they do not have to be specified as
- // CUSTOM.
- boolean ignorCase = (criteria.isIgnoreCase() &&
- (dbMap.getTable(table).getColumn(
criteria.getColumnName(key) ).getType() instanceof String));
- whereClause.add( SqlExpression.build( key,
-
criteria.getValue(key),
-
criteria.getComparison(key),
- ignorCase,
- db ));
-
- // If criteriaColumn is not in the select list, but
- // its table contains a foreign key and its relatedKey
- // is in the select list, assume we want a simple
- // join.
- if ( !selectClause.contains(key) )
- {
- // What if the criteria column is in the select
- // list and the select list contains a column from
- // a table which has another column associated a
- // column in criteria column's table
- ColumnMap[] possibleForeignKeys =
dbMap.getTable(table).getColumns();
- for (int i=0; i<possibleForeignKeys.length; i++)
- {
- if ( possibleForeignKeys[i].isForeignKey()
- &&
selectClause.contains(possibleForeignKeys[i].getRelatedName() ))
- {
- ignorCase = criteria.isIgnoreCase() &&
- (possibleForeignKeys[i].getType()
instanceof String);
- whereClause.add(
SqlExpression.buildInnerJoin(
-
possibleForeignKeys[i].getFullyQualifiedName(),
-
possibleForeignKeys[i].getRelatedName(),
- ignorCase,
- db));
- }
- }
+ Criteria.Criterion criterion =
+ (Criteria.Criterion)criteria.getCriterion(key);
+ Criteria.Criterion[] someCriteria =
+ criterion.getAttachedCriterion();
+ String table = null;
+ for (int i=0; i<someCriteria.length; i++)
+ {
+ String tableName = someCriteria[i].getTable();
+ table = criteria.getTableForAlias(tableName);
+ System.out.println("Adding " + tableName + " to from
list");
+ if ( table != null )
+ {
+ fromClause.add(
+ new StringBuffer(tableName.length() +
+ table.length() + 1)
+ .append(table).append(' ').append(tableName)
+ .toString() );
}
- // More to come ???
- }
+ else
+ {
+ fromClause.add(tableName);
+ table = tableName;
+ }
+
+ boolean ignorCase = (criteria.isIgnoreCase() &&
+ (dbMap.getTable(table).getColumn(
+ criteria.getColumnName(key)).getType() instanceof
String));
+
+ someCriteria[i].setIgnoreCase(ignorCase);
+ }
+
+ criterion.setDB(db);
+ whereClause.add( criterion.toString() );
+
}
if ( orderBy != null && orderBy.size() > 0)
@@ -988,6 +1005,7 @@
if (limitString != null) querySql.setLimit(limitString);
+ if (DEBUG) Log.note("BasePeer.querySql= "+
querySql.toString());
if (DEBUG) System.out.println("BasePeer.querySql= "+
querySql.toString());
return querySql.toString();
Index: org/apache/turbine/util/StringStackBuffer.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/util/StringStackBuffer.java,v
retrieving revision 1.3
diff -u -r1.3 StringStackBuffer.java
--- org/apache/turbine/util/StringStackBuffer.java 2001/01/02 20:12:15
1.3
+++ org/apache/turbine/util/StringStackBuffer.java 2001/01/29 23:14:50
@@ -186,6 +186,16 @@
return isEquiv;
}
+
+ public String[] toStringArray()
+ {
+ String[] ss = new String[size()];
+ for (int i=0; i<size(); i++)
+ {
+ ss[i]=get(i);
+ }
+ return ss;
+ }
}
Index: org/apache/turbine/util/db/Criteria.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/util/db/Criteria.java,v
retrieving revision 1.13
diff -u -r1.13 Criteria.java
--- org/apache/turbine/util/db/Criteria.java 2001/01/29 23:09:21 1.13
+++ org/apache/turbine/util/db/Criteria.java 2001/01/29 23:14:50
@@ -62,6 +62,7 @@
// Turbine Classes
import org.apache.turbine.util.*;
+import org.apache.turbine.util.db.adapter.DB;
import org.apache.turbine.services.db.*;
import org.apache.turbine.util.db.map.*;
import java.math.BigDecimal;
@@ -171,9 +172,8 @@
*/
private int offset = 0;
- // public static final String CUSTOM = "true";
+ private HashMap aliases = null;
-
/**
* Creates a new instance with the default capacity.
*/
@@ -218,6 +218,37 @@
}
/**
+ * Allows one to specify an alias for a table that can
+ * be used in various parts of the SQL.
+ *
+ * @param alias a <code>String</code> value
+ * @param table a <code>String</code> value
+ */
+ public void addAlias(String alias, String table)
+ {
+ if ( aliases == null )
+ {
+ aliases = new HashMap(8);
+ }
+ aliases.put(alias, table);
+ }
+
+ /**
+ * Returns the table name associated with an alias.
+ *
+ * @param alias a <code>String</code> value
+ * @return a <code>String</code> value
+ */
+ public String getTableForAlias(String alias)
+ {
+ if ( aliases == null )
+ {
+ return null;
+ }
+ return (String)aliases.get(alias);
+ }
+
+ /**
* Does this Criteria Object contain the specified key?
*
* @param table The name of the table.
@@ -302,7 +333,7 @@
* @param name String name of column.
* @return A Criterion.
*/
- private Criterion getCriterion(String name)
+ public Criterion getCriterion(String name)
{
return (Criterion)super.get(name);
}
@@ -314,8 +345,7 @@
* @param column String name of column.
* @return A Criterion.
*/
- private Criterion getCriterion(String table,
- String column)
+ public Criterion getCriterion(String table, String column)
{
return getCriterion(
new StringBuffer(table.length() + column.length() + 1)
@@ -324,6 +354,28 @@
}
/**
+ * Method to return criterion that is not added automatically
+ * to this Criteria. This can be used to chain the
+ * Criterions to form a more complex where clause.
+ *
+ * @param table String name of table.
+ * @param column String name of column.
+ * @return A Criterion.
+ */
+ public Criterion getNewCriterion(String table, String column,
+ Object value, String comparison)
+ {
+ return new Criterion(table,column,value,comparison);
+ }
+
+ public Criteria add(Criterion c)
+ {
+ String key = new StringBuffer(c.getTable().length() +
c.getColumn().length() +
1).append(c.getTable()).append('.').append(c.getColumn()).toString();
+ super.put(key, c);
+ return this;
+ }
+
+ /**
* Method to return a String table name.
*
* @param name A String with the name of the key.
@@ -331,7 +383,7 @@
*/
public String getColumnName(String name)
{
- return getCriterion(name).column;
+ return getCriterion(name).getColumn();
}
/**
@@ -342,7 +394,7 @@
*/
public String getComparison(String key)
{
- return getCriterion(key).comparison;
+ return getCriterion(key).getComparison();
}
/**
@@ -382,7 +434,9 @@
public java.util.Date getDate(String table,
String column)
{
- return getDate( new
StringBuffer(table).append('.').append(column)
+ return getDate(
+ new StringBuffer(table.length() + column.length() + 1)
+ .append(table).append('.').append(column)
.toString() );
}
@@ -437,6 +491,7 @@
new StringBuffer(table.length() + column.length() + 1)
.append(table).append('.').append(column)
.toString() );
+
}
/**
@@ -557,6 +612,7 @@
new StringBuffer(table.length() + column.length() + 1)
.append(table).append('.').append(column)
.toString() );
+
}
/**
@@ -1399,35 +1455,16 @@
isEquiv = true;
for (Enumeration e=criteria.keys();
e.hasMoreElements(); )
{
- Object key = e.nextElement();
+ String key = (String)e.nextElement();
if ( this.containsKey(key) )
{
- Object a = this.get(key);
- Object b = criteria.get(key);
- // have to check for arrays separately, so far
we
- // only have a couple array types
- if ( a instanceof Object[] && b instanceof
Object[] )
- {
- if (
!Arrays.equals((Object[])a,(Object[])b) )
- {
- isEquiv = false;
- break;
- }
- }
- else if (a instanceof int[] && b instanceof
int[])
- {
- if ( !Arrays.equals((int[])a,(int[])b) )
- {
- isEquiv = false;
- break;
- }
- }
- else if ( !criteria.get(key).equals(
- this.get(key)) )
+ Criterion a = this.getCriterion(key);
+ Criterion b = criteria.getCriterion(key);
+ if ( !a.equals(b) )
{
isEquiv = false;
break;
- }
+ }
}
else
{
@@ -1442,22 +1479,43 @@
}
/**
- * This is a private inner class that describes an object in the
+ * This is an inner class that describes an object in the
* criteria.
*/
- private final class Criterion
- {
- /** Value of the CO. */
- private Object value = null;
+public class Criterion
+{
+ public static final String AND = " AND ";
+ public static final String OR = " OR ";
- /** Comparison value. */
- private String comparison = "";
+ private boolean ignoreStringCase = false;
- /** Table name. */
- private String table = "";
+ /** Value of the CO. */
+ private Object value;
+
+ /** Comparison value. */
+ private String comparison;
+
+ /** Table name. */
+ private String table;
+
+ /** Column name. */
+ private String column;
- /** Column name. */
- private String column = "";
+ /**
+ * The DB adaptor which might be used to get db specific
+ * variations of sql.
+ */
+ private DB db;
+
+ /**
+ * Another Criterion connected to this one by an OR clause.
+ */
+ private Criterion or;
+
+ /**
+ * Another criterion connected to this one by an AND clause.
+ */
+ private Criterion and;
/**
* Constructor.
@@ -1545,45 +1603,306 @@
comparison = EQUAL;
}
- /**
- * Get the column name.
- *
- * @return A String with the column name.
- */
- public String getColumn()
+ /**
+ * Get the column name.
+ *
+ * @return A String with the column name.
+ */
+ public String getColumn()
+ {
+ return this.column;
+ }
+
+ /**
+ * Get the table name.
+ *
+ * @return A String with the table name.
+ */
+ public String getTable()
+ {
+ return this.table;
+ }
+
+ /**
+ * Get the comparison.
+ *
+ * @return A String with the comparison.
+ */
+ public String getComparison()
+ {
+ return this.comparison;
+ }
+
+ /**
+ * Get the value.
+ *
+ * @return An Object with the value.
+ */
+ public Object getValue()
+ {
+ return this.value;
+ }
+
+ /**
+ * Get the value of db.
+ * The DB adaptor which might be used to get db specific
+ * variations of sql.
+ * @return value of db.
+ */
+ public DB getDb()
+ {
+ return db;
+ }
+
+ /**
+ * Set the value of db.
+ * The DB adaptor might be used to get db specific
+ * variations of sql.
+ * @param v Value to assign to db.
+ */
+ public void setDB(DB v)
+ {
+ this.db = v;
+ if ( and != null )
{
- return this.column;
+ and.setDB(v);
}
+ if ( or != null )
+ {
+ or.setDB(v);
+ }
+ }
- /**
- * Get the table name.
- *
- * @return A String with the table name.
- */
- public String getTable()
+ /**
+ * Sets ignore case.
+ *
+ * @param b True if case should be ignored.
+ * @return A modified Criteria object.
+ */
+ public Criterion setIgnoreCase(boolean b)
+ {
+ ignoreStringCase = b;
+ return this;
+ }
+
+ /**
+ * Is ignore case on or off?
+ *
+ * @return True if case is ignored.
+ * /
+ public boolean isIgnoreCase()
+ {
+ return ignoreStringCase;
+ }
+
+
+ /**
+ * get the criterion from this Criterion's AND field.
+ */
+ public Criterion getAnd()
+ {
+ return and;
+ }
+
+ /**
+ * Append a Criteria onto this Criteria's AND field.
+ */
+ public void and(Criterion criterion)
+ {
+ if (this.and == null)
{
- return this.table;
+ this.and = criterion;
+ }
+ else
+ {
+ this.and.and(criterion);
}
+ }
- /**
- * Get the comparison.
- *
- * @return A String with the comparison.
- */
- public String getComparison()
+ /**
+ * get the criterion from this Criterion's AND field.
+ */
+ public Criterion getOr()
+ {
+ return or;
+ }
+
+ /**
+ * Append a Criterion onto this Criterion's OR field.
+ */
+ public void or(Criterion criterion)
+ {
+ if (this.or == null)
{
- return this.comparison;
+ this.or = criterion;
+ }
+ else
+ {
+ this.or.or(criterion);
}
+ }
- /**
- * Get the value.
- *
- * @return An Object with the value.
- */
- public Object getValue()
+
+ /**
+ * Appends a representation of the Criterion onto the buffer.
+ */
+ public void appendTo(StringBuffer sb)
+ {
+ if (column == null || value == null)
{
- return this.value;
+ return;
}
+ sb.append('(');
+ if ( CUSTOM == comparison )
+ {
+ if ( !"".equals(value) )
+ {
+ sb.append((String)value);
+ }
+ }
+ else
+ {
+ String field = null;
+ if (table == null)
+ {
+ field = column;
+ }
+ else
+ {
+ field = new StringBuffer(table.length() + 1 +
column.length())
+ .append(table).append('.').append(column)
+ .toString();
+ }
+ SqlExpression.build(field, value, comparison,
+ ignoreStringCase, db, sb);
+ }
+
+ if (or != null)
+ {
+ sb.append(OR);
+ or.appendTo(sb);
+ }
+
+ if (and != null)
+ {
+ sb.append(AND);
+ and.appendTo(sb);
+ }
+ sb.append(')');
}
+
+ /**
+ * Build a string representation of the Criterion.
+ *
+ * @return A String with the representation of the Criterion.
+ */
+ public String toString()
+ {
+ if (column == null || value == null)
+ {
+ return "";
+ }
+
+ StringBuffer expr = new StringBuffer(25);
+ appendTo(expr);
+ return expr.toString();
+ }
+
+
+ /**
+ * This method checks another Criteria to see if they contain
+ * the same attributes and hashtable entries.
+ */
+ public boolean equals(Object obj)
+ {
+ if ( this == obj )
+ {
+ return true;
+ }
+
+ if ( (obj == null) || !(obj instanceof Criterion) )
+ {
+ return false;
+ }
+
+ Criterion crit = (Criterion)obj;
+
+ boolean isEquiv = ( (table == null && crit.getTable() == null)
+ || (table != null && table.equals(crit.getTable()))
+ )
+ && column.equals(crit.getColumn())
+ && comparison.equals(crit.getComparison());
+
+ // we need to check for value equality
+ if ( isEquiv )
+ {
+ Object b = crit.getValue();
+ if ( value instanceof Object[] && b instanceof Object[] )
+ {
+ isEquiv &= Arrays.equals((Object[])value, (Object[])b);
+ }
+ else if (value instanceof int[] && b instanceof int[])
+ {
+ isEquiv &= Arrays.equals((int[])value, (int[])b);
+ }
+ else
+ {
+ isEquiv &= value.equals(b);
+ }
+ }
+
+ // check chained criterion
+ isEquiv &= (and == null && crit.getAnd() == null )
+ || (and != null && and.equals(crit.getAnd()));
+
+ isEquiv &= (or == null && crit.getOr() == null )
+ || (or != null && or.equals(crit.getOr()));
+
+ return isEquiv;
+ }
+
+
+ public String[] getAllTables()
+ {
+ StringStackBuffer tables = new StringStackBuffer();
+ addCriterionTable(this, tables);
+ return tables.toStringArray();
+ }
+
+ private void addCriterionTable(Criterion c, StringStackBuffer s)
+ {
+ if ( c != null )
+ {
+ s.add(c.getTable());
+ addCriterionTable(c.getAnd(), s);
+ addCriterionTable(c.getOr(), s);
+ }
+ }
+
+ public Criterion[] getAttachedCriterion()
+ {
+ ArrayList crits = new ArrayList();
+ traverseCriterion(this, crits);
+ Criterion[] crita = new Criterion[crits.size()];
+ for ( int i=0; i<crits.size(); i++ )
+ {
+ crita[i] = (Criterion)crits.get(i);
+ }
+
+ return crita;
+ }
+
+ private void traverseCriterion(Criterion c, ArrayList a)
+ {
+ if ( c != null )
+ {
+ a.add(c);
+ traverseCriterion(c.getAnd(), a);
+ traverseCriterion(c.getOr(), a);
+ }
+ }
+
+}
+
}
Index: org/apache/turbine/util/db/SqlExpression.java
===================================================================
RCS file:
/products/cvs/turbine/turbine/src/java/org/apache/turbine/util/db/SqlExpression.java,v
retrieving revision 1.6
diff -u -r1.6 SqlExpression.java
--- org/apache/turbine/util/db/SqlExpression.java 2001/01/24 17:52:11
1.6
+++ org/apache/turbine/util/db/SqlExpression.java 2001/01/29 23:14:50
@@ -116,24 +116,52 @@
boolean ignoreCase,
DB db )
{
+ int addlength = (ignoreCase) ? 25 : 1;
+ StringBuffer sb = new StringBuffer(column.length() +
+ relatedColumn.length() + addlength );
+ buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
+ return sb.toString();
+ }
+
+ /**
+ * Used to specify a join on two columns.
+ *
+ * @param column A column in one of the tables to be joined.
+ * @param relatedColumn The column in the other table to be
+ * joined.
+ * @param ignoreCase If true and columns represent Strings, the
+ * appropriate function defined for the database will be used to
+ * ignore differences in case.
+ * @param db Represents the database in use for vendor-specific
+ * functions.
+ * @param whereClause A StringBuffer to which the sql expression
+ * will be appended.
+ * @return A join expression, e.g. UPPER(table_a.column_a) =
+ * UPPER(table_b.column_b).
+ */
+ public static void buildInnerJoin( String column,
+ String relatedColumn,
+ boolean ignoreCase,
+ DB db,
+ StringBuffer whereClause)
+ {
if (ignoreCase)
{
- return new StringBuffer()
+ whereClause
.append(db.ignoreCase(column))
.append("=")
- .append(db.ignoreCase(relatedColumn))
- .toString();
+ .append(db.ignoreCase(relatedColumn));
}
else
{
- return new StringBuffer()
+ whereClause
.append(column)
.append("=")
- .append( relatedColumn )
- .toString();
+ .append( relatedColumn );
}
}
+
/**
* Builds a simple SQL expression.
*
@@ -177,14 +205,37 @@
DB db )
throws Exception
{
- /*
- if (criteria == null)
- throw new Exception ("Criteria: null, Column Name: " +
- columnName +
- ", Comparison: " +
- comparison );
+ int addlength = (ignoreCase) ? 40 : 20;
+ StringBuffer sb = new StringBuffer(columnName.length() +
addlength );
+ build(columnName, criteria, comparison, ignoreCase, db, sb);
+ return sb.toString();
+ }
- */
+ /**
+ * Builds a simple sql expression.
+ *
+ * @param columnName A column.
+ * @param criteria The value to compare the column against.
+ * @param comparison One of =, <, >, ^lt;=, >=, <>,
+ * !=, LIKE, etc.
+ * @param ignoreCase If true and columns represent Strings, the
+ * appropriate function defined for the database will be used to
+ * ignore differences in case.
+ * @param db Represents the database in use, for vendor specific
+ * functions.
+ * @param whereClause A StringBuffer to which the sql expression
+ * will be appended.
+ * @return A simple sql expression, e.g. UPPER(table_a.column_a)
+ * LIKE UPPER('ab%c').
+ * @exception Exception, a generic exception.
+ */
+ public static void build( String columnName,
+ Object criteria,
+ String comparison,
+ boolean ignoreCase,
+ DB db,
+ StringBuffer whereClause)
+ {
// Allow null criteria
// This will result in queries like
// select * from table where field = null
@@ -209,18 +260,20 @@
if ( comparison.equals(Criteria.LIKE) )
{
- return buildLike( columnName, (String)criteria, ignoreCase,
db );
+ buildLike( columnName, (String)criteria,
+ ignoreCase, db, whereClause);
}
else if ( comparison.equals(Criteria.IN) ||
comparison.equals(Criteria.NOT_IN) )
{
- return buildIn( columnName, criteria, comparison,
ignoreCase, db );
+ buildIn( columnName, criteria, comparison,
+ ignoreCase, db, whereClause);
}
else
{
if (ignoreCase && db != null)
{
- return new StringBuffer()
+ whereClause
.append(db.ignoreCase(columnName))
.append(comparison)
.append(db.ignoreCase(criteria.toString()))
@@ -228,7 +281,7 @@
}
else
{
- return new StringBuffer()
+ whereClause
.append(columnName)
.append(comparison)
.append(criteria.toString())
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
