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 Thomas Vandahl * @version $Id: Criteria.java 1211142 2011-12-06 21:04:03Z tfischer $ */ -public class Criteria implements Serializable, CriteriaInterface +public class Criteria + implements Serializable, Cloneable, CriteriaInterface { /** 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 * * @return An int with the value for offset. */ - int getOffset(); + long getOffset(); /** * Get select columns. @@ -186,5 +186,5 @@ public interface CriteriaInterface * 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 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 inClause = new ArrayList(); + 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 selectModifiersClone = cClone.getSelectModifiers(); + assertTrue(selectModifiersClone.contains( + Criteria.ALL.toString())); + assertTrue(selectModifiersClone.contains( + Criteria.DISTINCT.toString())); + assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers()); + List selectColumnsClone = cClone.getSelectColumns(); + assertTrue(selectColumnsClone.contains( + new ColumnImpl("Author", "NAME"))); + assertTrue(selectColumnsClone.contains( + new ColumnImpl("Author", "AUTHOR_ID"))); + assertEquals(c.getSelectColumns(), cClone.getSelectColumns()); + List 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 aliasesClone = cClone.getAliases(); + assertTrue(aliasesClone.containsKey("Writer")); + assertEquals("Author", aliasesClone.get("Writer")); + assertEquals(c.getAliases(), cClone.getAliases()); + Map 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 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 expectedReplacements = new ArrayList(); + 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 expectedReplacements = new ArrayList(); + 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 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 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 inList = new ArrayList(); 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 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 inList = new ArrayList(); + 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 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 inList = new ArrayList(); 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 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: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org