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: SqlBuilderOldCriteriaTest.java 1448414 2013-02-20 21:06:35Z tfischer $
*/
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());
}
}
}