Author: tfischer
Date: Mon Jan 30 18:17:29 2012
New Revision: 1237886
URL: http://svn.apache.org/viewvc?rev=1237886&view=rev
Log:
- TORQUE-100 add clone method for Criteria
- TORQUE-85 offset is now a long value
- Fix SQL generation if ordered by an alias value
- Handle null values in I N and NOT IN clauses correctly
- TORQUE-176 Tests for brackets in sql expression with many chained criterions
- TORQUE-89 Tests for some uniusual order by contents
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
Mon Jan 30 18:17:29 2012
@@ -54,7 +54,8 @@ import org.apache.torque.util.UniqueList
* @author <a href="mailto:[email protected]";>Thomas Vandahl</a>
* @version $Id: Criteria.java 1211142 2011-12-06 21:04:03Z tfischer $
*/
-public class Criteria implements Serializable, CriteriaInterface<Criteria>
+public class Criteria
+ implements Serializable, Cloneable, CriteriaInterface<Criteria>
{
/** Serial version. */
private static final long serialVersionUID = -9001666575933085601L;
@@ -175,7 +176,7 @@ public class Criteria implements Seriali
private int limit = -1;
/** To start the results at a row other than the first one. */
- private int offset = 0;
+ private long offset = 0;
/**
* Aliases for table names. The key of the map is the alias,
@@ -608,7 +609,7 @@ public class Criteria implements Seriali
* result.
* @return A modified Criteria object.
*/
- public Criteria setOffset(int offset)
+ public Criteria setOffset(long offset)
{
this.offset = offset;
return this;
@@ -619,7 +620,7 @@ public class Criteria implements Seriali
*
* @return The value for offset.
*/
- public int getOffset()
+ public long getOffset()
{
return offset;
}
@@ -921,6 +922,21 @@ public class Criteria implements Seriali
return hashCodeBuilder.toHashCode();
}
+ /**
+ * Returns a cloned object.
+ */
+ public Object clone()
+ {
+ try
+ {
+ return super.clone();
+ }
+ catch (CloneNotSupportedException e) {
+ // should not happen as we implement Cloneable
+ throw new RuntimeException(e);
+ }
+ }
+
/*
* ------------------------------------------------------------------------
*
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/CriteriaInterface.java
Mon Jan 30 18:17:29 2012
@@ -140,7 +140,7 @@ public interface CriteriaInterface<T>
*
* @return An int with the value for offset.
*/
- int getOffset();
+ long getOffset();
/**
* Get select columns.
@@ -186,5 +186,5 @@ public interface CriteriaInterface<T>
* result.
* @return A modified Criteria object.
*/
- public T setOffset(int offset);
+ public T setOffset(long offset);
}
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/map/MapHelper.java
Mon Jan 30 18:17:29 2012
@@ -40,10 +40,12 @@ public final class MapHelper
/**
* Returns the table map for a table name.
+ * As aliases and asColumns are resolved, the returned table map need not
+ * contain the same table name as the column.
*
* @param column the column to get the table map for.
* @param criteria A criteria containing the database name and perhaps
- * aliases for the table name, not null.
+ * aliases for the column and table name, not null.
* @param defaultTableMap a default table map which is used if the table
* name cannot be resolved, may be null.
* @return the table map, or null if the table name cannot be resolved.
@@ -55,6 +57,16 @@ public final class MapHelper
{
TableMap result = null;
String tableName = column.getTableName();
+ if (tableName == null)
+ {
+ // try asColumns
+ Column asColumn = (Column) criteria.getAsColumns().get(
+ column.getSqlExpression());
+ if (asColumn != null)
+ {
+ tableName = asColumn.getTableName();
+ }
+ }
if (tableName != null)
{
String databaseName = criteria.getDbName();
@@ -82,4 +94,58 @@ public final class MapHelper
return result;
}
+ /**
+ * Returns the column map for a column.
+ * As aliases and asColumns are resolved, the returned column map need not
+ * contain the same column name as the column.
+ *
+ * @param column the column to get the column map for.
+ * @param criteria A criteria containing the database name and perhaps
+ * aliases for the column and table name, not null.
+ * @return the column map, or null if the column name cannot be resolved.
+ */
+ public static ColumnMap getColumnMap(
+ Column column,
+ CriteriaInterface criteria)
+ {
+ String tableName = column.getTableName();
+ {
+ // try asColumns
+ Column asColumn = (Column) criteria.getAsColumns().get(
+ column.getSqlExpression());
+ if (asColumn != null)
+ {
+ column = asColumn;
+ tableName = asColumn.getTableName();
+ }
+ }
+
+ if (tableName == null)
+ {
+ return null;
+ }
+ String databaseName = criteria.getDbName();
+ DatabaseMap databaseMap = Torque.getDatabaseMap(databaseName);
+ TableMap tableMap = null;
+ if (databaseMap != null)
+ {
+ tableMap = databaseMap.getTable(tableName);
+ }
+ if (tableMap == null)
+ {
+ // try aliases
+ Map<String, String> aliases = criteria.getAliases();
+ if (aliases.get(tableName) != null)
+ {
+ tableName = aliases.get(tableName);
+ tableMap = databaseMap.getTable(tableName);
+ }
+ }
+ // try aliases
+ if (tableMap == null)
+ {
+ return null;
+ }
+ return tableMap.getColumn(column.getColumnName());
+ }
}
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/SqlBuilder.java
Mon Jan 30 18:17:29 2012
@@ -495,12 +495,7 @@ public final class SqlBuilder
for (OrderBy orderBy : orderByList)
{
Column column = orderBy.getColumn();
- TableMap tableMap = MapHelper.getTableMap(column, crit, null);
- ColumnMap columnMap = null;
- if (tableMap != null)
- {
- columnMap = tableMap.getColumn(column.getColumnName());
- }
+ ColumnMap columnMap = MapHelper.getColumnMap(column, crit);
String sqlExpression = column.getSqlExpression();
// Either we are not able to look up the column in the
@@ -524,13 +519,19 @@ public final class SqlBuilder
else
{
orderByClause.add(sqlExpression + ' ' +
orderBy.getOrder());
- selectClause.add(sqlExpression);
+ if (crit.getAsColumns().get(sqlExpression) == null)
+ {
+ selectClause.add(sqlExpression);
+ }
}
}
else
{
orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
- selectClause.add(sqlExpression);
+ if (crit.getAsColumns().get(sqlExpression) == null)
+ {
+ selectClause.add(sqlExpression);
+ }
}
addTableToFromClause(
column,
@@ -624,7 +625,7 @@ public final class SqlBuilder
throws TorqueException
{
int limit = crit.getLimit();
- int offset = crit.getOffset();
+ long offset = crit.getOffset();
if (offset > 0 || limit >= 0)
{
@@ -1281,10 +1282,16 @@ public final class SqlBuilder
boolean ignoreCaseApplied = false;
List<String> inClause = new ArrayList<String>();
+ boolean nullContained = false;
if (whereClausePart.getRValue() instanceof Iterable)
{
for (Object listValue : (Iterable<?>) whereClausePart.getRValue())
{
+ if (listValue == null)
+ {
+ nullContained = true;
+ continue;
+ }
result.getPreparedStatementReplacements().add(listValue);
if (ignoreCase && listValue instanceof String)
{
@@ -1301,6 +1308,11 @@ public final class SqlBuilder
{
for (Object arrayValue : (Object[]) whereClausePart.getRValue())
{
+ if (arrayValue == null)
+ {
+ nullContained = true;
+ continue;
+ }
result.getPreparedStatementReplacements().add(arrayValue);
if (ignoreCase && arrayValue instanceof String)
{
@@ -1322,6 +1334,11 @@ public final class SqlBuilder
+ " Iterable or Array");
}
+ if (nullContained)
+ {
+ result.getSql().append('(');
+ }
+
if (ignoreCaseApplied)
{
result.getSql().append(db.ignoreCase(whereClausePart.getLValue()));
@@ -1335,6 +1352,21 @@ public final class SqlBuilder
.append('(')
.append(StringUtils.join(inClause.iterator(), ","))
.append(')');
+ if (nullContained)
+ {
+ if (whereClausePart.getOperator() == SqlEnum.IN)
+ {
+ result.getSql().append(Criterion.OR)
+
.append(whereClausePart.getLValue()).append(SqlEnum.ISNULL);
+ }
+ else if (whereClausePart.getOperator() == SqlEnum.NOT_IN)
+ {
+ result.getSql().append(Criterion.AND)
+ .append(whereClausePart.getLValue()).append(
+ SqlEnum.ISNOTNULL);
+ }
+ result.getSql().append(')');
+ }
return result;
}
}
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
Mon Jan 30 18:17:29 2012
@@ -1184,7 +1184,7 @@ public class BasePeerImpl implements Ser
log.trace("query took " + (queryEndTime - startTime)
+ " milliseconds");
- int offset;
+ long offset;
Database database = Torque.getDatabase(criteria.getDbName());
if (database.getAdapter().supportsNativeOffset())
{
@@ -1195,7 +1195,7 @@ public class BasePeerImpl implements Ser
offset = criteria.getOffset();
}
- int limit;
+ long limit;
if (database.getAdapter().supportsNativeLimit())
{
limit = -1; //database takes care of offset
@@ -1336,7 +1336,7 @@ public class BasePeerImpl implements Ser
log.trace("query took " + (queryEndTime - startTime)
+ " milliseconds");
- int offset;
+ long offset;
Database database = Torque.getDatabase(criteria.getDbName());
if (database.getAdapter().supportsNativeOffset())
{
@@ -1347,7 +1347,7 @@ public class BasePeerImpl implements Ser
offset = criteria.getOffset();
}
- int limit;
+ long limit;
if (database.getAdapter().supportsNativeLimit())
{
limit = -1; //database takes care of offset
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/Criteria.java
Mon Jan 30 18:17:29 2012
@@ -191,7 +191,7 @@ public class Criteria implements Seriali
private int limit = -1;
/** To start the results at a row other than the first one. */
- private int offset = 0;
+ private long offset = 0;
/**
* Aliases for table names. The key of the map is the alias,
@@ -1659,7 +1659,7 @@ public class Criteria implements Seriali
* @param offset An int with the value for offset.
* @return A modified Criteria object.
*/
- public Criteria setOffset(int offset)
+ public Criteria setOffset(long offset)
{
this.offset = offset;
return this;
@@ -1670,7 +1670,7 @@ public class Criteria implements Seriali
*
* @return An int with the value for offset.
*/
- public int getOffset()
+ public long getOffset()
{
return offset;
}
@@ -1988,7 +1988,7 @@ public class Criteria implements Seriali
public int hashCode()
{
int result = 16;
- result = 37 * result + offset;
+ result = 37 * result + new Long(offset).hashCode();
result = 37 * result + limit;
result = 37 * result + (ignoreCase ? 0 : 1);
result = 37 * result + (singleRecord ? 0 : 1);
Modified:
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/criteria/CriteriaTest.java
Mon Jan 30 18:17:29 2012
@@ -91,9 +91,9 @@ public class CriteriaTest extends BaseTe
}
/**
- * Test basic adding of strings.
+ * Test basic where condition on a string.
*/
- public void testAddString()
+ public void testWhereString()
{
final Column column = new ColumnImpl("myTable", "myColumn");
final String value = "myValue";
@@ -111,6 +111,66 @@ public class CriteriaTest extends BaseTe
}
/**
+ * Test where condition with several ANDs compairing against Strings.
+ */
+ public void testAndString()
+ {
+ final Column column1 = new ColumnImpl("myTable", "myColumn1");
+ final Column column2 = new ColumnImpl("myTable", "myColumn2");
+ final String value1a = "1a";
+ final String value1b = "1b";
+ final String value2a = "2a";
+ final String value2b = "2b";
+
+ // Add the string
+ c.where(column1, value1a)
+ .and(column1, value1b)
+ .and(column2, value2a)
+ .and(column2, value2b);
+
+ // Verify that the Criterion is a composite
+ assertTrue(c.getTopLevelCriterion().isComposite());
+
+ // Verify that what we get out is what we put in
+ assertEquals(
+ column1,
+ c.getTopLevelCriterion().getParts().get(0).getColumn());
+ assertEquals(
+ value1a,
+ c.getTopLevelCriterion().getParts().get(0).getValue());
+ assertEquals(
+ Criteria.EQUAL,
+ c.getTopLevelCriterion().getParts().get(0).getComparison());
+ assertEquals(
+ column1,
+ c.getTopLevelCriterion().getParts().get(1).getColumn());
+ assertEquals(
+ value1b,
+ c.getTopLevelCriterion().getParts().get(1).getValue());
+ assertEquals(
+ Criteria.EQUAL,
+ c.getTopLevelCriterion().getParts().get(1).getComparison());
+ assertEquals(
+ column2,
+ c.getTopLevelCriterion().getParts().get(2).getColumn());
+ assertEquals(
+ value2a,
+ c.getTopLevelCriterion().getParts().get(2).getValue());
+ assertEquals(
+ Criteria.EQUAL,
+ c.getTopLevelCriterion().getParts().get(2).getComparison());
+ assertEquals(
+ column2,
+ c.getTopLevelCriterion().getParts().get(3).getColumn());
+ assertEquals(
+ value2b,
+ c.getTopLevelCriterion().getParts().get(3).getValue());
+ assertEquals(
+ Criteria.EQUAL,
+ c.getTopLevelCriterion().getParts().get(3).getComparison());
+ }
+
+ /**
* Test that nesting Criterions works for equals comparison.
*/
public void testNestedCriterionComparisonEqual() throws TorqueException
@@ -635,6 +695,99 @@ public class CriteriaTest extends BaseTe
}
/**
+ * Test that cloning works.
+ */
+ public void testClone()
+ {
+ c.setOffset(10);
+ c.setLimit(11);
+ c.setIgnoreCase(true);
+ c.setSingleRecord(true);
+ c.setDbName("myDB");
+ c.setAll();
+ c.setDistinct();
+ c.addSelectColumn(new ColumnImpl("Author", "NAME"));
+ c.addSelectColumn(new ColumnImpl("Author", "AUTHOR_ID"));
+ c.addDescendingOrderByColumn(new ColumnImpl("Author", "NAME"));
+ c.addAscendingOrderByColumn(new ColumnImpl("Author", "AUTHOR_ID"));
+ c.addAlias("Writer", "Author");
+ c.addAsColumn("AUTHOR_NAME", new ColumnImpl("Author", "NAME"));
+ c.addJoin(new ColumnImpl("Author", "AUTHOR_ID"),
+ new ColumnImpl("Book", "AUTHOR_ID"),
+ Criteria.INNER_JOIN);
+ c.where(new ColumnImpl("Author", "NAME"), "author%", Criteria.LIKE);
+
+ // Clone the object
+ Criteria cClone = (Criteria) c.clone();
+
+ // Check the clone
+ assertEquals(c.getTopLevelCriterion(), cClone.getTopLevelCriterion());
+ assertEquals(10, cClone.getOffset());
+ assertEquals(c.getOffset(), cClone.getOffset());
+ assertEquals(11, cClone.getLimit());
+ assertEquals(c.getLimit(), cClone.getLimit());
+ assertEquals(true, cClone.isIgnoreCase());
+ assertEquals(c.isIgnoreCase(), cClone.isIgnoreCase());
+ assertEquals(true, cClone.isSingleRecord());
+ assertEquals(c.isSingleRecord(), cClone.isSingleRecord());
+ assertEquals("myDB", cClone.getDbName());
+ assertEquals(c.getDbName(), cClone.getDbName());
+ List<String> selectModifiersClone = cClone.getSelectModifiers();
+ assertTrue(selectModifiersClone.contains(
+ Criteria.ALL.toString()));
+ assertTrue(selectModifiersClone.contains(
+ Criteria.DISTINCT.toString()));
+ assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers());
+ List<Column> selectColumnsClone = cClone.getSelectColumns();
+ assertTrue(selectColumnsClone.contains(
+ new ColumnImpl("Author", "NAME")));
+ assertTrue(selectColumnsClone.contains(
+ new ColumnImpl("Author", "AUTHOR_ID")));
+ assertEquals(c.getSelectColumns(), cClone.getSelectColumns());
+ List<OrderBy> orderByColumnsClone = cClone.getOrderByColumns();
+ assertTrue(orderByColumnsClone.contains(new OrderBy(
+ new ColumnImpl("Author.NAME"),
+ SqlEnum.DESC,
+ false)));
+ assertTrue(orderByColumnsClone.contains(new OrderBy(
+ new ColumnImpl("Author.AUTHOR_ID"),
+ SqlEnum.ASC,
+ false)));
+ assertEquals(c.getOrderByColumns(), cClone.getOrderByColumns());
+ Map<String, String> aliasesClone = cClone.getAliases();
+ assertTrue(aliasesClone.containsKey("Writer"));
+ assertEquals("Author", aliasesClone.get("Writer"));
+ assertEquals(c.getAliases(), cClone.getAliases());
+ Map<String, Column> asColumnsClone = cClone.getAsColumns();
+ assertTrue(asColumnsClone.containsKey("AUTHOR_NAME"));
+ assertEquals(new ColumnImpl("Author", "NAME"),
+ asColumnsClone.get("AUTHOR_NAME"));
+ assertEquals(c.getAsColumns(), cClone.getAsColumns());
+
+ // Check Joins
+ List<Join> joinsClone = cClone.getJoins();
+ Join joinClone = joinsClone.get(0);
+ assertEquals(new ColumnImpl("Author", "AUTHOR_ID"),
+ joinClone.getLeftColumn());
+ assertEquals(new ColumnImpl("Book", "AUTHOR_ID"),
+ joinClone.getRightColumn());
+ assertEquals(Criteria.INNER_JOIN, joinClone.getJoinType());
+ assertEquals(c.getJoins(), cClone.getJoins());
+
+ // Some Criterion checks
+ Criterion cnClone = cClone.getTopLevelCriterion();
+ assertEquals("author%", cnClone.getValue());
+ assertEquals(Criteria.LIKE, cnClone.getComparison());
+ assertEquals(c.getTopLevelCriterion().isIgnoreCase(),
cnClone.isIgnoreCase());
+
+ // Confirm that equals() checks all of the above.
+ assertEquals(c, cClone);
+
+ // Check hashCode() too.
+ assertEquals(c.hashCode(), cClone.hashCode());
+ }
+
+ /**
* Test that {@link Criteria#equals(Object)} works correctly for a simple
* Criteria object.
* @throws TorqueException
Modified:
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
Mon Jan 30 18:17:29 2012
@@ -43,6 +43,74 @@ import org.apache.torque.om.NumberKey;
public class SqlBuilderTest extends BaseTestCase
{
/**
+ * Test where condition with several ANDs compairing against Strings.
+ */
+ public void testAndString() throws Exception
+ {
+ final Column column1 = new ColumnImpl("myTable", "myColumn1");
+ final Column column2 = new ColumnImpl("myTable", "myColumn2");
+ final String value1a = "1a";
+ final String value1b = "1b";
+ final String value2a = "2a";
+ final String value2b = "2b";
+ Criteria c = new Criteria().where(column1, value1a)
+ .and(column1, value1b)
+ .and(column2, value2a)
+ .and(column2, value2b);
+
+ Query query = SqlBuilder.buildQuery(c);
+
+ assertEquals(
+ "SELECT FROM myTable "
+ + "WHERE (myTable.myColumn1=? "
+ + "AND myTable.myColumn1=? "
+ + "AND myTable.myColumn2=? "
+ + "AND myTable.myColumn2=?)",
+ query.toString());
+ List<String> expectedReplacements = new ArrayList<String>();
+ expectedReplacements.add(value1a);
+ expectedReplacements.add(value1b);
+ expectedReplacements.add(value2a);
+ expectedReplacements.add(value2b);
+ assertEquals(
+ expectedReplacements,
+ query.getPreparedStatementReplacements());
+ }
+
+ /**
+ * Test where condition with several ANDs compairing against Strings.
+ */
+ public void testAndCriterions() throws Exception
+ {
+ final Column column1 = new ColumnImpl("myTable", "myColumn1");
+ final Column column2 = new ColumnImpl("myTable", "myColumn2");
+ final String value1a = "1a";
+ final String value1b = "1b";
+ final String value2a = "2a";
+ Criterion criterion1 = new Criterion(column1, value1a);
+ Criterion criterion2 = new Criterion(column1, value1b);
+ Criterion criterion3 = new Criterion(column2, value2a);
+ criterion1.and(criterion2).and(criterion3);
+ Criteria c = new Criteria().where(criterion1);
+
+ Query query = SqlBuilder.buildQuery(c);
+
+ assertEquals(
+ "SELECT FROM myTable "
+ + "WHERE (myTable.myColumn1=? "
+ + "AND myTable.myColumn1=? "
+ + "AND myTable.myColumn2=?)",
+ query.toString());
+ List<String> expectedReplacements = new ArrayList<String>();
+ expectedReplacements.add(value1a);
+ expectedReplacements.add(value1b);
+ expectedReplacements.add(value2a);
+ assertEquals(
+ expectedReplacements,
+ query.getPreparedStatementReplacements());
+ }
+
+ /**
* Test that unknown columns are treated case-insensitive if ignoreCase
* is set.
*/
@@ -149,6 +217,60 @@ public class SqlBuilderTest extends Base
query.toString());
}
+ public void testOrderByWithFunction() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn(
+ new ColumnImpl("count(table.column1)"));
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT count(table.column1) FROM table "
+ + "ORDER BY count(table.column1) ASC",
+ query.toString());
+ }
+
+ public void testOrderByWithAsColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("asColumn", stringColumnMap);
+ criteria.addAscendingOrderByColumn("asColumn");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 AS asColumn "
+ + "FROM TABLE "
+ + "ORDER BY asColumn ASC",
+ query.toString());
+ }
+
+ public void testOrderByWithAsColumnIgnoreCase() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("asColumn", stringColumnMap);
+ criteria.addAscendingOrderByColumn("asColumn", true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 AS asColumn, UPPER(asColumn) "
+ + "FROM TABLE "
+ + "ORDER BY UPPER(asColumn) ASC",
+ query.toString());
+ }
+
+ public void testOrderByWithAsColumnAndAliasIgnoreCase() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", stringColumnMap.getTableName());
+ criteria.addAsColumn(
+ "asColumn",
+ new ColumnImpl("alias", stringColumnMap.getColumnName()));
+ criteria.addAscendingOrderByColumn("asColumn", true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.COLUMN1 AS asColumn, UPPER(asColumn) "
+ + "FROM TABLE alias "
+ + "ORDER BY UPPER(asColumn) ASC",
+ query.toString());
+ }
+
public void testOrderByAscendingIgnoreCaseString() throws Exception
{
Criteria criteria = new Criteria();
@@ -1152,39 +1274,38 @@ public class SqlBuilderTest extends Base
{
Criteria criteria = new Criteria();
criteria.addSelectColumn("table.column1");
- String[] inValue = new String[] {"a", "b", null};
+ String[] inValue = new String[] {"a", "b", null, null};
criteria.whereIn("table.column2", inValue.clone());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 IN (?,?,?)",
+ + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(inValue.length, replacements.size());
+ assertEquals(2, replacements.size());
assertEquals(inValue[0], replacements.get(0));
assertEquals(inValue[1], replacements.get(1));
- assertEquals(inValue[2], replacements.get(2));
}
public void testCriterionInArrayIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn("table.column1");
- String[] inValue = new String[] {"a", "b", null};
+ String[] inValue = new String[] {"a", "b", null, null};
criteria.whereIn("table.column2", inValue.clone());
criteria.setIgnoreCase(true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+ + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?))"
+ + " OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(inValue.length, replacements.size());
+ assertEquals(2, replacements.size());
assertEquals(inValue[0], replacements.get(0));
assertEquals(inValue[1], replacements.get(1));
- assertEquals(inValue[2], replacements.get(2));
}
public void testCriterionInList() throws Exception
@@ -1193,20 +1314,20 @@ public class SqlBuilderTest extends Base
criteria.addSelectColumn("table.column1");
List<Integer> inList = new ArrayList<Integer>();
inList.add(1);
+ inList.add(null);
inList.add(2);
inList.add(null);
criteria.whereIn("table.column2", inList);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 IN (?,?,?)",
+ + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
query.toString());
assertEquals(
- inList.size(),
+ 2,
query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
assertEquals(2, query.getPreparedStatementReplacements().get(1));
- assertEquals(null, query.getPreparedStatementReplacements().get(2));
}
public void testCriterionInListIgnoreCase() throws Exception
@@ -1217,18 +1338,43 @@ public class SqlBuilderTest extends Base
inList.add("a");
inList.add("b");
inList.add(null);
+ inList.add(null);
criteria.whereIn("table.column2", inList);
criteria.setIgnoreCase(true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+ + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?))"
+ + " OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(inList.size(), replacements.size());
+ assertEquals(2, replacements.size());
assertEquals("a", replacements.get(0));
assertEquals("b", replacements.get(1));
- assertEquals(null, replacements.get(2));
+ }
+
+ public void testCriterionNotInList() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+ List<Integer> inList = new ArrayList<Integer>();
+ inList.add(1);
+ inList.add(null);
+ inList.add(2);
+ inList.add(null);
+ criteria.where(
+ "table.column2",
+ inList,
+ Criteria.NOT_IN);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE (table.column2 NOT IN (?,?) "
+ + "AND table.column2 IS NOT NULL)",
+ query.toString());
+ assertEquals(2, query.getPreparedStatementReplacements().size());
+ assertEquals(1, query.getPreparedStatementReplacements().get(0));
+ assertEquals(2, query.getPreparedStatementReplacements().get(1));
}
public void testCriterionInLargeArray() throws TorqueException
Modified:
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java?rev=1237886&r1=1237885&r2=1237886&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTestOldCriteria.java
Mon Jan 30 18:17:29 2012
@@ -1109,17 +1109,16 @@ public class SqlBuilderTestOldCriteria e
criteria.addSelectColumn("table.column1");
criteria.add(
"table.column2",
- new String[] {"a", "b", null},
+ new String[] {"a", "b", null, null},
Criteria.IN);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 IN (?,?,?)",
+ + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
query.toString());
- assertEquals(3, query.getPreparedStatementReplacements().size());
+ assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals("a", query.getPreparedStatementReplacements().get(0));
assertEquals("b", query.getPreparedStatementReplacements().get(1));
- assertEquals(null, query.getPreparedStatementReplacements().get(2));
}
public void testCriterionInArrayIgnoreCase() throws Exception
@@ -1128,19 +1127,19 @@ public class SqlBuilderTestOldCriteria e
criteria.addSelectColumn("table.column1");
criteria.add(
"table.column2",
- new String[] {"a", "b", null},
+ new String[] {"a", "b", null, null},
Criteria.IN);
criteria.setIgnoreCase(true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+ + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
+ + "OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(3, replacements.size());
+ assertEquals(2, replacements.size());
assertEquals("a", replacements.get(0));
assertEquals("b", replacements.get(1));
- assertEquals(null, replacements.get(2));
}
public void testCriterionInList() throws Exception
@@ -1151,6 +1150,7 @@ public class SqlBuilderTestOldCriteria e
inList.add(1);
inList.add(2);
inList.add(null);
+ inList.add(null);
criteria.add(
"table.column2",
inList,
@@ -1158,12 +1158,12 @@ public class SqlBuilderTestOldCriteria e
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 IN (?,?,?)",
+ + "WHERE (table.column2 IN (?,?) "
+ + "OR table.column2 IS NULL)",
query.toString());
- assertEquals(3, query.getPreparedStatementReplacements().size());
+ assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
assertEquals(2, query.getPreparedStatementReplacements().get(1));
- assertEquals(null, query.getPreparedStatementReplacements().get(2));
}
public void testCriterionInListIgnoreCase() throws Exception
@@ -1172,6 +1172,7 @@ public class SqlBuilderTestOldCriteria e
criteria.addSelectColumn("table.column1");
List<String> inList = new ArrayList<String>();
inList.add("a");
+ inList.add(null);
inList.add("b");
inList.add(null);
criteria.add(
@@ -1182,13 +1183,13 @@ public class SqlBuilderTestOldCriteria e
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2) IN (UPPER(?),UPPER(?),?)",
+ + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
+ + "OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(3, replacements.size());
+ assertEquals(2, replacements.size());
assertEquals("a", replacements.get(0));
assertEquals("b", replacements.get(1));
- assertEquals(null, replacements.get(2));
}
public void testCriterionInLargeArray() throws TorqueException
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
