You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by tf...@apache.org on 2013/02/20 22:06:37 UTC
svn commit: r1448414 [13/14] - in
/db/torque/torque4/trunk/torque-runtime/src: main/java/org/apache/torque/
main/java/org/apache/torque/adapter/ main/java/org/apache/torque/criteria/
main/java/org/apache/torque/map/ main/java/org/apache/torque/oid/ mai...
Modified: db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderOldCriteriaTest.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderOldCriteriaTest.java?rev=1448414&r1=1448413&r2=1448414&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderOldCriteriaTest.java (original)
+++ db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderOldCriteriaTest.java Wed Feb 20 21:06:35 2013
@@ -1,1296 +1,1296 @@
-package org.apache.torque.sql;
-
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-import java.lang.reflect.Array;
-import java.math.BigDecimal;
-import java.util.ArrayList;
-import java.util.List;
-
-import org.apache.torque.BaseTestCase;
-import org.apache.torque.Column;
-import org.apache.torque.Database;
-import org.apache.torque.TorqueException;
-import org.apache.torque.om.NumberKey;
-import org.apache.torque.util.Criteria;
-
-/**
- * Tests for SqlExpression
- *
- * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
- * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
- * @version $Id$
- */
-public class SqlBuilderOldCriteriaTest extends BaseTestCase
-{
- /**
- * Test that unknown columns are treated case-insensitive if ignoreCase
- * is set.
- */
- public void testignoreCaseUnknownColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("unknownTable.column1");
- criteria.add("column1", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT unknownTable.column1 FROM unknownTable "
- + "WHERE UPPER(column1)=UPPER(?)",
- query.toString());
- }
-
- public void testIgnoreCaseStringColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.add(stringColumnMap, "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN1 FROM TABLE "
- + "WHERE UPPER(TABLE.COLUMN1)=UPPER(?)",
- query.toString());
- }
-
- public void testIgnoreCaseIntegerColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(integerColumnMap);
- criteria.add(integerColumnMap, "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 FROM TABLE "
- + "WHERE TABLE.COLUMN4=?",
- query.toString());
- }
-
- public void testOrderByDesc() throws TorqueException
- {
- Criteria criteria = new Criteria();
- criteria.addDescendingOrderByColumn("table.column1");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table.column1 FROM table ORDER BY table.column1 DESC",
- query.toString());
- }
-
- public void testOrderByAsc() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn("table.column1");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table.column1 FROM table ORDER BY table.column1 ASC",
- query.toString());
- }
-
- public void testOrderByNullColumn() throws Exception
- {
- Criteria criteria = new Criteria();
- try
- {
- criteria.addAscendingOrderByColumn((Column) null);
- fail("Exception expected");
- }
- catch (NullPointerException e)
- {
- assertEquals("column is null", e.getMessage());
- }
- }
-
- public void testOrderByMultiple() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn("table.column1");
- criteria.addDescendingOrderByColumn("table2.column2");
- criteria.addAscendingOrderByColumn("table3.column1");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table.column1, table2.column2, table3.column1"
- + " FROM table, table2, table3"
- + " ORDER BY table.column1 ASC,"
- + " table2.column2 DESC,"
- + " table3.column1 ASC",
- query.toString());
- }
-
- public void testOrderByWithDefaultSchema() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn("table.column1");
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table.column1 FROM schema1.table "
- + "ORDER BY table.column1 ASC",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testOrderByAscendingIgnoreCaseString() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn(stringColumnMap, true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
- + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
- query.toString());
- }
-
- public void testOrderByAscendingIgnoreCaseInteger() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn(integerColumnMap, true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 FROM TABLE "
- + "ORDER BY TABLE.COLUMN4 ASC",
- query.toString());
- }
-
- public void testOrderByAscendingIgnoreCaseStringInCriteria()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn(stringColumnMap);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
- + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
- query.toString());
- }
-
- public void testOrderByAscendingIgnoreCaseIntegerInCriteria()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAscendingOrderByColumn(integerColumnMap);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 FROM TABLE "
- + "ORDER BY TABLE.COLUMN4 ASC",
- query.toString());
- }
-
- public void testOrderByDescendingIgnoreCaseString() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addDescendingOrderByColumn(stringColumnMap, true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
- + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
- query.toString());
- }
-
- public void testOrderByDescendingIgnoreCaseInteger() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addDescendingOrderByColumn(integerColumnMap, true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 FROM TABLE "
- + "ORDER BY TABLE.COLUMN4 DESC",
- query.toString());
- }
-
- public void testOrderByDescendingIgnoreCaseStringInCriteria()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addDescendingOrderByColumn(stringColumnMap);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
- + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
- query.toString());
- }
-
- public void testOrderByDescendingIgnoreCaseIntegerInCriteria()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addDescendingOrderByColumn(integerColumnMap);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 FROM TABLE "
- + "ORDER BY TABLE.COLUMN4 DESC",
- query.toString());
- }
-
- public void testAlias() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", "table");
- criteria.addSelectColumn("alias.column1");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 FROM table alias",
- query.toString());
- }
-
- public void testAliasWithDefaultSchema() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", "table");
- criteria.addSelectColumn("alias.column1");
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 FROM schema1.table alias",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testAliasWithIgnoreCaseUnknownColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", "table");
- criteria.addSelectColumn("alias.column1");
- criteria.add("alias.column1", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 FROM table alias "
- + "WHERE UPPER(alias.column1)=UPPER(?)",
- query.toString());
- }
-
- public void testAliasWithIgnoreCaseStringColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", tableMap.getName());
- criteria.addSelectColumn("alias.COLUMN1");
- criteria.add("alias.COLUMN1", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.COLUMN1 FROM TABLE alias "
- + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
- query.toString());
- }
-
- public void testAliasWithIgnoreCaseIntegerColumnType() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", tableMap.getName());
- criteria.addSelectColumn("alias.COLUMN4");
- criteria.add("alias.COLUMN4", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.COLUMN4 FROM TABLE alias "
- + "WHERE alias.COLUMN4=?",
- query.toString());
- }
-
- public void testAliasWithIgnoreCaseStringColumnTypeAndDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", tableMap.getName());
- criteria.addSelectColumn("alias.COLUMN1");
- criteria.add("alias.COLUMN1", "1");
- criteria.setIgnoreCase(true);
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.COLUMN1 FROM schema1.TABLE alias "
- + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testAliasWithIgnoreCaseIntegerColumnTypeAndDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAlias("alias", tableMap.getName());
- criteria.addSelectColumn("alias.COLUMN4");
- criteria.add("alias.COLUMN4", "1");
- criteria.setIgnoreCase(true);
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.COLUMN4 FROM schema1.TABLE alias "
- + "WHERE alias.COLUMN4=?",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testAsColumn() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", stringColumnMap);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE",
- query.toString());
- }
-
- public void testAsColumnWithIgnoreCaseUnknownColumn() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", "table.column");
- criteria.add("columnAlias", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table.column AS columnAlias FROM table"
- + " WHERE UPPER(columnAlias)=UPPER(?)",
- query.toString());
- }
-
- public void testAsColumnWithIgnoreCaseStringColumn() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", stringColumnMap);
- criteria.add("columnAlias", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE"
- + " WHERE UPPER(columnAlias)=UPPER(?)",
- query.toString());
- }
-
- public void testAsColumnWithIgnoreCaseIntegerColumn() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", integerColumnMap);
- criteria.add("columnAlias", "1");
- criteria.setIgnoreCase(true);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 AS columnAlias FROM TABLE"
- + " WHERE columnAlias=?",
- query.toString());
- }
-
- public void testAsColumnWithIgnoreCaseStringColumnAndDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", stringColumnMap);
- criteria.addSelectColumn("columnAlias");
- criteria.add("columnAlias", "1");
- criteria.setIgnoreCase(true);
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN1 AS columnAlias FROM schema1.TABLE"
- + " WHERE UPPER(columnAlias)=UPPER(?)",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testAsColumnWithIgnoreCaseIntegerColumnAndDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("columnAlias", integerColumnMap);
- criteria.addSelectColumn("columnAlias");
- criteria.add("columnAlias", "1");
- criteria.setIgnoreCase(true);
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN4 AS columnAlias FROM schema1.TABLE"
- + " WHERE columnAlias=?",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testInnerJoinImplicit()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column");
- criteria.addJoin("table1.column1", "table2.column2");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table1.column FROM table1, table2"
- + " WHERE table1.column1=table2.column2",
- query.toString());
- }
-
-
- public void testInnerJoinExplicit()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin(
- "table1.column1",
- "table2.column2",
- Criteria.INNER_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table1.column1 FROM table1 INNER JOIN table2"
- + " ON table1.column1=table2.column2",
- query.toString());
- }
-
- public void testInnerJoinWithExcplicitExistingRightTable()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
- criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- // second join condition must be changed in order to satisfy
- // first join condition
- assertEquals(
- "SELECT table1.column1"
- + " FROM table2 INNER JOIN table3"
- + " ON table2.column2=table3.column3"
- + " INNER JOIN table1"
- + " ON table1.column1=table2.column2",
- query.toString());
- }
-
- public void testInnerJoinExcplicitWithExistingRightAndLeftTable()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
- criteria.addJoin("table1.column1", "table4.column4", Criteria.INNER_JOIN);
- criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
- try
- {
- SqlBuilder.buildQuery(criteria);
- fail("Exception expected");
- }
- catch (TorqueException e)
- {
- assertEquals("Unable to create a INNER JOIN "
- + "because both expressions table1 and table2 "
- + "are already in use. Try to create an(other) alias.",
- e.getMessage());
- }
- }
-
- public void testLeftJoin()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table1.column", "table2.column", Criteria.LEFT_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table1.column1"
- + " FROM table1 LEFT JOIN table2"
- + " ON table1.column=table2.column",
- query.toString());
- }
-
- public void testLeftJoinWithExistingRightTable()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
- criteria.addJoin("table1.column1", "table2.column2", Criteria.LEFT_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- // left join must be converted to right join to satisfy
- // first join condition
- assertEquals(
- "SELECT table1.column1"
- + " FROM table2 INNER JOIN table3"
- + " ON table2.column2=table3.column3"
- + " RIGHT JOIN table1"
- + " ON table1.column1=table2.column2",
- query.toString());
- }
-
- public void testRightJoin()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table1.column", "table2.column", Criteria.RIGHT_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT table1.column1"
- + " FROM table1 RIGHT JOIN table2"
- + " ON table1.column=table2.column",
- query.toString());
- }
-
- public void testRightJoinWithExistingRightTable()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
- criteria.addJoin("table1.column1", "table2.column2", Criteria.RIGHT_JOIN);
- Query query = SqlBuilder.buildQuery(criteria);
- // right join must be converted to left join to satisfy
- // first join condition
- assertEquals(
- "SELECT table1.column1"
- + " FROM table2 INNER JOIN table3"
- + " ON table2.column2=table3.column3"
- + " LEFT JOIN table1"
- + " ON table1.column1=table2.column2",
- query.toString());
- }
-
- public void testInnerJoinImplicitWithAlias()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("alias.column1");
- criteria.addAlias("alias", "table1");
- criteria.addJoin("alias.column", "table2.column");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 FROM table1 alias, table2"
- + " WHERE alias.column=table2.column",
- query.toString());
- }
-
- public void testInnerJoinImplicitWithAliasAndAsColumn()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addAsColumn("x", "alias.column");
- criteria.addAlias("alias", "table1");
- criteria.addJoin("x", "table2.column");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column AS x FROM table2, table1 alias"
- + " WHERE x=table2.column",
- query.toString());
- }
-
-
- public void testInnerJoinImplicitWithDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("*");
- criteria.addJoin("table1.column", "table2.column");
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT *"
- + " FROM schema1.table1, schema1.table2"
- + " WHERE table1.column=table2.column",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testInnerJoinImplicitWithAliasAndDefaultSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("alias.column1");
- criteria.addAlias("alias", "table1");
- criteria.addJoin("alias.column", "table2.column");
- String oldSchema = database.getSchema();
- try
- {
- database.setSchema("schema1");
- criteria.setDbName(database.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 "
- + "FROM schema1.table1 alias, schema1.table2"
- + " WHERE alias.column=table2.column",
- query.toString());
- }
- finally
- {
- database.setSchema(oldSchema);
- }
- }
-
- public void testInnerJoinImplicitWithAliasAndSchema()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("schema1.alias.column1");
- criteria.addAlias("alias", "table1");
- criteria.addJoin("schema1.alias.column", "schema2.table2.column");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT alias.column1 FROM schema1.table1 alias, schema2.table2"
- + " WHERE alias.column=table2.column",
- query.toString());
- }
-
- public void testDistinct()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table1.column1");
- criteria.setDistinct();
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT DISTINCT table1.column1 FROM table1",
- query.toString());
- }
-
- public void testGroupBy()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addGroupByColumn(stringColumnMap);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals(
- "SELECT TABLE.COLUMN1 FROM TABLE GROUP BY TABLE.COLUMN1",
- query.toString());
- }
-
- public void testLimitPostgresql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setLimit(20);
- criteria.setDbName(databasePostgresql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "LIMIT 20",
- query.toString());
- }
-
- public void testOffsetPostgresql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setOffset(10);
- criteria.setDbName(databasePostgresql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "OFFSET 10",
- query.toString());
- }
-
- public void testLimitOffsetPostgresql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setLimit(20);
- criteria.setOffset(10);
- criteria.setDbName(databasePostgresql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "LIMIT 20 OFFSET 10",
- query.toString());
- }
-
- public void testLimitMysql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setLimit(20);
- criteria.setDbName(databaseMysql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "LIMIT 20",
- query.toString());
- }
-
- public void testOffsetMysql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setOffset(10);
- criteria.setDbName(databaseMysql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "LIMIT 18446744073709551615 OFFSET 10",
- query.toString());
- }
-
- public void testLimitOffsetMysql() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn(stringColumnMap);
- criteria.setLimit(20);
- criteria.setOffset(10);
- criteria.setDbName(databaseMysql.getName());
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
- + "LIMIT 20 OFFSET 10",
- query.toString());
- }
-
- public void testHaving() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addGroupByColumn(stringColumnMap);
- criteria.addAsColumn("count", "count(*)");
- criteria.addSelectColumn(stringColumnMap);
- criteria.addHaving(
- criteria.getNewCriterion("count", 10, Criteria.GREATER_EQUAL));
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT TABLE.COLUMN1, count(*) AS count FROM TABLE "
- + "GROUP BY TABLE.COLUMN1 HAVING count>=10",
- query.toString());
- }
-
- public void testSelectColumnWithoutTable()
- throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("*");
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT * FROM ", query.toString());
- }
-
-
- public void testCriterionCustom() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("A", (Object) "A = functionOf(B)", Criteria.CUSTOM);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table WHERE A = functionOf(B)",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionCurrentDate() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", Criteria.CURRENT_DATE);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column=CURRENT_DATE",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionCurrentTime() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", Criteria.CURRENT_TIME);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column=CURRENT_TIME",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionObjectKey() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", new NumberKey(11));
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column=?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- new BigDecimal(11),
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionNullValue() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", null);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column IS NULL",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionNullValueNotEqual() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", (Object) null, Criteria.NOT_EQUAL);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column IS NOT NULL",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionNullValueAltNotEqual() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", (Object) null, Criteria.ALT_NOT_EQUAL);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column IS NOT NULL",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionIsNull() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", (Object) "value ignored", Criteria.ISNULL);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column IS NULL",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionIsNotNull() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column");
- criteria.add("column", (Object) "value ignored", Criteria.ISNOTNULL);
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column FROM table "
- + "WHERE column IS NOT NULL",
- query.toString());
- assertEquals(0, query.getPreparedStatementReplacements().size());
- }
-
- public void testCriterionCriteria() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
-
- Criteria subquery = new Criteria();
- subquery.add("table.column2", "value2");
- subquery.addSelectColumn("table.column3");
- criteria.add("table.column3", subquery);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column3=("
- + "SELECT table.column3 FROM table "
- + "WHERE table.column2=?)",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals("value2", query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionLike() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 LIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%v%al_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- /**
- * Test whether LIKE clauses with Escapes are built correctly.
- */
- public void testCriterionLikeWithEscape() throws TorqueException
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
- Criteria.LIKE);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 LIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "*v\\%al\\_e\\\\%2?\\",
- query.getPreparedStatementReplacements().get(0));
- }
-
- /**
- * Test whether LIKE clauses with Escapes are built correctly in Oracle.
- * Oracle needs to have an ESCAPE clause
- */
- public void testCriterionLikeWithEscapeOracle() throws TorqueException
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
- Criteria.LIKE);
- criteria.setDbName(databaseOracle.getName());
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 LIKE ? ESCAPE '\\'",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "*v\\%al\\_e\\\\%2?\\",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionLikeIgnoreCase() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 ILIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%v%al_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionLikeIgnoreCaseNoWildcard() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "value\\\\2", Criteria.LIKE);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2)=UPPER(?)",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "value\\2",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionLikeInteger() throws TorqueException
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- 1,
- Criteria.LIKE);
-
- try
- {
- SqlBuilder.buildQuery(criteria);
- fail("Exception expected");
- }
- catch (TorqueException e)
- {
- assertEquals("rValue must be a String for the operator LIKE ",
- e.getMessage());
- }
- }
-
- public void testCriterionNotLike() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*val_e2?", Criteria.NOT_LIKE);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 NOT LIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%val_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionNotLikeIgnoreCase() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.NOT_LIKE);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 NOT ILIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%v%al_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionNotLikeIgnoreCaseNoWildcard() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "value\\\\2", Criteria.NOT_LIKE);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE UPPER(table.column2)<>UPPER(?)",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "value\\2",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionIlike() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*val_e2?", Criteria.ILIKE);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 ILIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%val_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionNotIlike() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add("table.column2", (Object) "*val_e2?", Criteria.NOT_ILIKE);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE table.column2 NOT ILIKE ?",
- query.toString());
- assertEquals(1, query.getPreparedStatementReplacements().size());
- assertEquals(
- "%val_e2_",
- query.getPreparedStatementReplacements().get(0));
- }
-
- public void testCriterionInArray() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- new String[] {"a", "b", null, null},
- Criteria.IN);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
- query.toString());
- assertEquals(2, query.getPreparedStatementReplacements().size());
- assertEquals("a", query.getPreparedStatementReplacements().get(0));
- assertEquals("b", query.getPreparedStatementReplacements().get(1));
- }
-
- public void testCriterionInArrayIgnoreCase() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- 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(?)) "
- + "OR table.column2 IS NULL)",
- query.toString());
- List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(2, replacements.size());
- assertEquals("a", replacements.get(0));
- assertEquals("b", replacements.get(1));
- }
-
- public void testCriterionInList() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- List<Integer> inList = new ArrayList<Integer>();
- inList.add(1);
- inList.add(2);
- inList.add(null);
- inList.add(null);
- criteria.add(
- "table.column2",
- inList,
- Criteria.IN);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE (table.column2 IN (?,?) "
- + "OR table.column2 IS NULL)",
- query.toString());
- assertEquals(2, query.getPreparedStatementReplacements().size());
- assertEquals(1, query.getPreparedStatementReplacements().get(0));
- assertEquals(2, query.getPreparedStatementReplacements().get(1));
- }
-
- public void testCriterionInListIgnoreCase() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- List<String> inList = new ArrayList<String>();
- inList.add("a");
- inList.add(null);
- inList.add("b");
- inList.add(null);
- criteria.add(
- "table.column2",
- inList,
- Criteria.IN);
- criteria.setIgnoreCase(true);
-
- Query query = SqlBuilder.buildQuery(criteria);
- assertEquals("SELECT table.column1 FROM table "
- + "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?)) "
- + "OR table.column2 IS NULL)",
- query.toString());
- List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(2, replacements.size());
- assertEquals("a", replacements.get(0));
- assertEquals("b", replacements.get(1));
- }
-
- public void testCriterionInLargeArray() throws TorqueException
- {
- int size = 10000;
- String[] values = new String[size];
- for (int i = 0; i < size; i++)
- {
- Array.set(values, i, String.valueOf(i));
- }
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- values,
- Criteria.IN);
- long start = System.currentTimeMillis();
- Query query = SqlBuilder.buildQuery(criteria);
- long end = System.currentTimeMillis();
- List<Object> replacements = query.getPreparedStatementReplacements();
- assertEquals(size, replacements.size());
- // time limit 100 ms
- assertTrue(end - start < 100L);
- }
-
- public void testCriterionInString() throws Exception
- {
- Criteria criteria = new Criteria();
- criteria.addSelectColumn("table.column1");
- criteria.add(
- "table.column2",
- (Object) "illegal in value",
- Criteria.IN);
-
- try
- {
- SqlBuilder.buildQuery(criteria);
- fail("Exception expected");
- }
- catch (IllegalArgumentException e)
- {
- assertEquals("Unknown rValue type java.lang.String. "
- + "rValue must be an instance of Iterable or Array",
- e.getMessage());
- }
- }
-}
+package org.apache.torque.sql;
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+import java.lang.reflect.Array;
+import java.math.BigDecimal;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.torque.BaseTestCase;
+import org.apache.torque.Column;
+import org.apache.torque.Database;
+import org.apache.torque.TorqueException;
+import org.apache.torque.om.NumberKey;
+import org.apache.torque.util.Criteria;
+
+/**
+ * Tests for SqlExpression
+ *
+ * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
+ * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
+ * @version $Id$
+ */
+public class SqlBuilderOldCriteriaTest extends BaseTestCase
+{
+ /**
+ * Test that unknown columns are treated case-insensitive if ignoreCase
+ * is set.
+ */
+ public void testignoreCaseUnknownColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("unknownTable.column1");
+ criteria.add("column1", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT unknownTable.column1 FROM unknownTable "
+ + "WHERE UPPER(column1)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testIgnoreCaseStringColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.add(stringColumnMap, "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 FROM TABLE "
+ + "WHERE UPPER(TABLE.COLUMN1)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testIgnoreCaseIntegerColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(integerColumnMap);
+ criteria.add(integerColumnMap, "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 FROM TABLE "
+ + "WHERE TABLE.COLUMN4=?",
+ query.toString());
+ }
+
+ public void testOrderByDesc() throws TorqueException
+ {
+ Criteria criteria = new Criteria();
+ criteria.addDescendingOrderByColumn("table.column1");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table.column1 FROM table ORDER BY table.column1 DESC",
+ query.toString());
+ }
+
+ public void testOrderByAsc() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn("table.column1");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table.column1 FROM table ORDER BY table.column1 ASC",
+ query.toString());
+ }
+
+ public void testOrderByNullColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ try
+ {
+ criteria.addAscendingOrderByColumn((Column) null);
+ fail("Exception expected");
+ }
+ catch (NullPointerException e)
+ {
+ assertEquals("column is null", e.getMessage());
+ }
+ }
+
+ public void testOrderByMultiple() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn("table.column1");
+ criteria.addDescendingOrderByColumn("table2.column2");
+ criteria.addAscendingOrderByColumn("table3.column1");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table.column1, table2.column2, table3.column1"
+ + " FROM table, table2, table3"
+ + " ORDER BY table.column1 ASC,"
+ + " table2.column2 DESC,"
+ + " table3.column1 ASC",
+ query.toString());
+ }
+
+ public void testOrderByWithDefaultSchema() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn("table.column1");
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table.column1 FROM schema1.table "
+ + "ORDER BY table.column1 ASC",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testOrderByAscendingIgnoreCaseString() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn(stringColumnMap, true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
+ + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
+ query.toString());
+ }
+
+ public void testOrderByAscendingIgnoreCaseInteger() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn(integerColumnMap, true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 FROM TABLE "
+ + "ORDER BY TABLE.COLUMN4 ASC",
+ query.toString());
+ }
+
+ public void testOrderByAscendingIgnoreCaseStringInCriteria()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn(stringColumnMap);
+ criteria.setIgnoreCase(true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
+ + "ORDER BY UPPER(TABLE.COLUMN1) ASC",
+ query.toString());
+ }
+
+ public void testOrderByAscendingIgnoreCaseIntegerInCriteria()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAscendingOrderByColumn(integerColumnMap);
+ criteria.setIgnoreCase(true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 FROM TABLE "
+ + "ORDER BY TABLE.COLUMN4 ASC",
+ query.toString());
+ }
+
+ public void testOrderByDescendingIgnoreCaseString() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addDescendingOrderByColumn(stringColumnMap, true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
+ + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
+ query.toString());
+ }
+
+ public void testOrderByDescendingIgnoreCaseInteger() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addDescendingOrderByColumn(integerColumnMap, true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 FROM TABLE "
+ + "ORDER BY TABLE.COLUMN4 DESC",
+ query.toString());
+ }
+
+ public void testOrderByDescendingIgnoreCaseStringInCriteria()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addDescendingOrderByColumn(stringColumnMap);
+ criteria.setIgnoreCase(true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT UPPER(TABLE.COLUMN1) FROM TABLE "
+ + "ORDER BY UPPER(TABLE.COLUMN1) DESC",
+ query.toString());
+ }
+
+ public void testOrderByDescendingIgnoreCaseIntegerInCriteria()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addDescendingOrderByColumn(integerColumnMap);
+ criteria.setIgnoreCase(true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 FROM TABLE "
+ + "ORDER BY TABLE.COLUMN4 DESC",
+ query.toString());
+ }
+
+ public void testAlias() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", "table");
+ criteria.addSelectColumn("alias.column1");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 FROM table alias",
+ query.toString());
+ }
+
+ public void testAliasWithDefaultSchema() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", "table");
+ criteria.addSelectColumn("alias.column1");
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 FROM schema1.table alias",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testAliasWithIgnoreCaseUnknownColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", "table");
+ criteria.addSelectColumn("alias.column1");
+ criteria.add("alias.column1", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 FROM table alias "
+ + "WHERE UPPER(alias.column1)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testAliasWithIgnoreCaseStringColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", tableMap.getName());
+ criteria.addSelectColumn("alias.COLUMN1");
+ criteria.add("alias.COLUMN1", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.COLUMN1 FROM TABLE alias "
+ + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testAliasWithIgnoreCaseIntegerColumnType() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", tableMap.getName());
+ criteria.addSelectColumn("alias.COLUMN4");
+ criteria.add("alias.COLUMN4", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.COLUMN4 FROM TABLE alias "
+ + "WHERE alias.COLUMN4=?",
+ query.toString());
+ }
+
+ public void testAliasWithIgnoreCaseStringColumnTypeAndDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", tableMap.getName());
+ criteria.addSelectColumn("alias.COLUMN1");
+ criteria.add("alias.COLUMN1", "1");
+ criteria.setIgnoreCase(true);
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.COLUMN1 FROM schema1.TABLE alias "
+ + "WHERE UPPER(alias.COLUMN1)=UPPER(?)",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testAliasWithIgnoreCaseIntegerColumnTypeAndDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAlias("alias", tableMap.getName());
+ criteria.addSelectColumn("alias.COLUMN4");
+ criteria.add("alias.COLUMN4", "1");
+ criteria.setIgnoreCase(true);
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.COLUMN4 FROM schema1.TABLE alias "
+ + "WHERE alias.COLUMN4=?",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testAsColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", stringColumnMap);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE",
+ query.toString());
+ }
+
+ public void testAsColumnWithIgnoreCaseUnknownColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", "table.column");
+ criteria.add("columnAlias", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table.column AS columnAlias FROM table"
+ + " WHERE UPPER(columnAlias)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testAsColumnWithIgnoreCaseStringColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", stringColumnMap);
+ criteria.add("columnAlias", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 AS columnAlias FROM TABLE"
+ + " WHERE UPPER(columnAlias)=UPPER(?)",
+ query.toString());
+ }
+
+ public void testAsColumnWithIgnoreCaseIntegerColumn() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", integerColumnMap);
+ criteria.add("columnAlias", "1");
+ criteria.setIgnoreCase(true);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 AS columnAlias FROM TABLE"
+ + " WHERE columnAlias=?",
+ query.toString());
+ }
+
+ public void testAsColumnWithIgnoreCaseStringColumnAndDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", stringColumnMap);
+ criteria.addSelectColumn("columnAlias");
+ criteria.add("columnAlias", "1");
+ criteria.setIgnoreCase(true);
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 AS columnAlias FROM schema1.TABLE"
+ + " WHERE UPPER(columnAlias)=UPPER(?)",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testAsColumnWithIgnoreCaseIntegerColumnAndDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("columnAlias", integerColumnMap);
+ criteria.addSelectColumn("columnAlias");
+ criteria.add("columnAlias", "1");
+ criteria.setIgnoreCase(true);
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN4 AS columnAlias FROM schema1.TABLE"
+ + " WHERE columnAlias=?",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testInnerJoinImplicit()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column");
+ criteria.addJoin("table1.column1", "table2.column2");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table1.column FROM table1, table2"
+ + " WHERE table1.column1=table2.column2",
+ query.toString());
+ }
+
+
+ public void testInnerJoinExplicit()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin(
+ "table1.column1",
+ "table2.column2",
+ Criteria.INNER_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table1.column1 FROM table1 INNER JOIN table2"
+ + " ON table1.column1=table2.column2",
+ query.toString());
+ }
+
+ public void testInnerJoinWithExcplicitExistingRightTable()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
+ criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ // second join condition must be changed in order to satisfy
+ // first join condition
+ assertEquals(
+ "SELECT table1.column1"
+ + " FROM table2 INNER JOIN table3"
+ + " ON table2.column2=table3.column3"
+ + " INNER JOIN table1"
+ + " ON table1.column1=table2.column2",
+ query.toString());
+ }
+
+ public void testInnerJoinExcplicitWithExistingRightAndLeftTable()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
+ criteria.addJoin("table1.column1", "table4.column4", Criteria.INNER_JOIN);
+ criteria.addJoin("table1.column1", "table2.column2", Criteria.INNER_JOIN);
+ try
+ {
+ SqlBuilder.buildQuery(criteria);
+ fail("Exception expected");
+ }
+ catch (TorqueException e)
+ {
+ assertEquals("Unable to create a INNER JOIN "
+ + "because both expressions table1 and table2 "
+ + "are already in use. Try to create an(other) alias.",
+ e.getMessage());
+ }
+ }
+
+ public void testLeftJoin()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table1.column", "table2.column", Criteria.LEFT_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table1.column1"
+ + " FROM table1 LEFT JOIN table2"
+ + " ON table1.column=table2.column",
+ query.toString());
+ }
+
+ public void testLeftJoinWithExistingRightTable()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
+ criteria.addJoin("table1.column1", "table2.column2", Criteria.LEFT_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ // left join must be converted to right join to satisfy
+ // first join condition
+ assertEquals(
+ "SELECT table1.column1"
+ + " FROM table2 INNER JOIN table3"
+ + " ON table2.column2=table3.column3"
+ + " RIGHT JOIN table1"
+ + " ON table1.column1=table2.column2",
+ query.toString());
+ }
+
+ public void testRightJoin()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table1.column", "table2.column", Criteria.RIGHT_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT table1.column1"
+ + " FROM table1 RIGHT JOIN table2"
+ + " ON table1.column=table2.column",
+ query.toString());
+ }
+
+ public void testRightJoinWithExistingRightTable()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.addJoin("table2.column2", "table3.column3", Criteria.INNER_JOIN);
+ criteria.addJoin("table1.column1", "table2.column2", Criteria.RIGHT_JOIN);
+ Query query = SqlBuilder.buildQuery(criteria);
+ // right join must be converted to left join to satisfy
+ // first join condition
+ assertEquals(
+ "SELECT table1.column1"
+ + " FROM table2 INNER JOIN table3"
+ + " ON table2.column2=table3.column3"
+ + " LEFT JOIN table1"
+ + " ON table1.column1=table2.column2",
+ query.toString());
+ }
+
+ public void testInnerJoinImplicitWithAlias()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("alias.column1");
+ criteria.addAlias("alias", "table1");
+ criteria.addJoin("alias.column", "table2.column");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 FROM table1 alias, table2"
+ + " WHERE alias.column=table2.column",
+ query.toString());
+ }
+
+ public void testInnerJoinImplicitWithAliasAndAsColumn()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addAsColumn("x", "alias.column");
+ criteria.addAlias("alias", "table1");
+ criteria.addJoin("x", "table2.column");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column AS x FROM table2, table1 alias"
+ + " WHERE x=table2.column",
+ query.toString());
+ }
+
+
+ public void testInnerJoinImplicitWithDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("*");
+ criteria.addJoin("table1.column", "table2.column");
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT *"
+ + " FROM schema1.table1, schema1.table2"
+ + " WHERE table1.column=table2.column",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testInnerJoinImplicitWithAliasAndDefaultSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("alias.column1");
+ criteria.addAlias("alias", "table1");
+ criteria.addJoin("alias.column", "table2.column");
+ String oldSchema = database.getSchema();
+ try
+ {
+ database.setSchema("schema1");
+ criteria.setDbName(database.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 "
+ + "FROM schema1.table1 alias, schema1.table2"
+ + " WHERE alias.column=table2.column",
+ query.toString());
+ }
+ finally
+ {
+ database.setSchema(oldSchema);
+ }
+ }
+
+ public void testInnerJoinImplicitWithAliasAndSchema()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("schema1.alias.column1");
+ criteria.addAlias("alias", "table1");
+ criteria.addJoin("schema1.alias.column", "schema2.table2.column");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT alias.column1 FROM schema1.table1 alias, schema2.table2"
+ + " WHERE alias.column=table2.column",
+ query.toString());
+ }
+
+ public void testDistinct()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table1.column1");
+ criteria.setDistinct();
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT DISTINCT table1.column1 FROM table1",
+ query.toString());
+ }
+
+ public void testGroupBy()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addGroupByColumn(stringColumnMap);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals(
+ "SELECT TABLE.COLUMN1 FROM TABLE GROUP BY TABLE.COLUMN1",
+ query.toString());
+ }
+
+ public void testLimitPostgresql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setLimit(20);
+ criteria.setDbName(databasePostgresql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "LIMIT 20",
+ query.toString());
+ }
+
+ public void testOffsetPostgresql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setOffset(10);
+ criteria.setDbName(databasePostgresql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "OFFSET 10",
+ query.toString());
+ }
+
+ public void testLimitOffsetPostgresql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setLimit(20);
+ criteria.setOffset(10);
+ criteria.setDbName(databasePostgresql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "LIMIT 20 OFFSET 10",
+ query.toString());
+ }
+
+ public void testLimitMysql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setLimit(20);
+ criteria.setDbName(databaseMysql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "LIMIT 20",
+ query.toString());
+ }
+
+ public void testOffsetMysql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setOffset(10);
+ criteria.setDbName(databaseMysql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "LIMIT 18446744073709551615 OFFSET 10",
+ query.toString());
+ }
+
+ public void testLimitOffsetMysql() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.setLimit(20);
+ criteria.setOffset(10);
+ criteria.setDbName(databaseMysql.getName());
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1 FROM TABLE "
+ + "LIMIT 20 OFFSET 10",
+ query.toString());
+ }
+
+ public void testHaving() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addGroupByColumn(stringColumnMap);
+ criteria.addAsColumn("count", "count(*)");
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.addHaving(
+ criteria.getNewCriterion("count", 10, Criteria.GREATER_EQUAL));
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT TABLE.COLUMN1, count(*) AS count FROM TABLE "
+ + "GROUP BY TABLE.COLUMN1 HAVING count>=10",
+ query.toString());
+ }
+
+ public void testSelectColumnWithoutTable()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("*");
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT * FROM ", query.toString());
+ }
+
+
+ public void testCriterionCustom() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("A", (Object) "A = functionOf(B)", Criteria.CUSTOM);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table WHERE A = functionOf(B)",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionCurrentDate() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", Criteria.CURRENT_DATE);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column=CURRENT_DATE",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionCurrentTime() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", Criteria.CURRENT_TIME);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column=CURRENT_TIME",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionObjectKey() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", new NumberKey(11));
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column=?",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals(
+ new BigDecimal(11),
+ query.getPreparedStatementReplacements().get(0));
+ }
+
+ public void testCriterionNullValue() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", null);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column IS NULL",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionNullValueNotEqual() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", (Object) null, Criteria.NOT_EQUAL);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column IS NOT NULL",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionNullValueAltNotEqual() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", (Object) null, Criteria.ALT_NOT_EQUAL);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column IS NOT NULL",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionIsNull() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", (Object) "value ignored", Criteria.ISNULL);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column IS NULL",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionIsNotNull() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column");
+ criteria.add("column", (Object) "value ignored", Criteria.ISNOTNULL);
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column FROM table "
+ + "WHERE column IS NOT NULL",
+ query.toString());
+ assertEquals(0, query.getPreparedStatementReplacements().size());
+ }
+
+ public void testCriterionCriteria() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+
+ Criteria subquery = new Criteria();
+ subquery.add("table.column2", "value2");
+ subquery.addSelectColumn("table.column3");
+ criteria.add("table.column3", subquery);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE table.column3=("
+ + "SELECT table.column3 FROM table "
+ + "WHERE table.column2=?)",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals("value2", query.getPreparedStatementReplacements().get(0));
+ }
+
+ public void testCriterionLike() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+ criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE table.column2 LIKE ?",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals(
+ "%v%al_e2_",
+ query.getPreparedStatementReplacements().get(0));
+ }
+
+ /**
+ * Test whether LIKE clauses with Escapes are built correctly.
+ */
+ public void testCriterionLikeWithEscape() throws TorqueException
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+ criteria.add(
+ "table.column2",
+ (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
+ Criteria.LIKE);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE table.column2 LIKE ?",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals(
+ "*v\\%al\\_e\\\\%2?\\",
+ query.getPreparedStatementReplacements().get(0));
+ }
+
+ /**
+ * Test whether LIKE clauses with Escapes are built correctly in Oracle.
+ * Oracle needs to have an ESCAPE clause
+ */
+ public void testCriterionLikeWithEscapeOracle() throws TorqueException
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+ criteria.add(
+ "table.column2",
+ (Object) "\\*v\\%al\\_e\\\\*2\\?\\",
+ Criteria.LIKE);
+ criteria.setDbName(databaseOracle.getName());
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE table.column2 LIKE ? ESCAPE '\\'",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals(
+ "*v\\%al\\_e\\\\%2?\\",
+ query.getPreparedStatementReplacements().get(0));
+ }
+
+ public void testCriterionLikeIgnoreCase() throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn("table.column1");
+ criteria.add("table.column2", (Object) "*v%al_e2?", Criteria.LIKE);
+ criteria.setIgnoreCase(true);
+
+ Query query = SqlBuilder.buildQuery(criteria);
+ assertEquals("SELECT table.column1 FROM table "
+ + "WHERE table.column2 ILIKE ?",
+ query.toString());
+ assertEquals(1, query.getPreparedStatementReplacements().size());
+ assertEquals(
+ "%v%al_e2_",
[... 258 lines stripped ...]
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org