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.ColumnImpl;
import org.apache.torque.TorqueException;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.criteria.Criterion;
import org.apache.torque.criteria.FromElement;
import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.om.NumberKey;
/**
* 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: SqlBuilderTest.java 1446412 2013-02-15 00:30:18Z tfischer $
*/
public class SqlBuilderTest extends BaseTestCase
{
/** Time limit in ms of builing a query from a large array. */
private static final long LARGE_ARRAY_TIME_LIMIT = 100L;
/**
* Test where condition with several ANDs compairing against Strings.
*/
public void testAndString() throws Exception
{
final Column column1 = new ColumnImpl("myTable", "myColumn1");
final Column column2 = new ColumnImpl("myTable", "myColumn2");
final String value1a = "1a";
final String value1b = "1b";
final String value2a = "2a";
final String value2b = "2b";
Criteria c = new Criteria().where(column1, value1a)
.and(column1, value1b)
.and(column2, value2a)
.and(column2, value2b);
Query query = SqlBuilder.buildQuery(c);
assertEquals(
"SELECT FROM myTable "
+ "WHERE (myTable.myColumn1=? "
+ "AND myTable.myColumn1=? "
+ "AND myTable.myColumn2=? "
+ "AND myTable.myColumn2=?)",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add(value1a);
expectedReplacements.add(value1b);
expectedReplacements.add(value2a);
expectedReplacements.add(value2b);
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test where condition with several ORs compairing against Strings.
*/
public void testOrString() throws Exception
{
final Column column1 = new ColumnImpl("myTable", "myColumn1");
final Column column2 = new ColumnImpl("myTable", "myColumn2");
final String value1a = "1a";
final String value1b = "1b";
final String value2a = "2a";
final String value2b = "2b";
Criteria c = new Criteria().where(column1, value1a)
.or(column1, value1b)
.or(column2, value2a)
.or(column2, value2b);
Query query = SqlBuilder.buildQuery(c);
assertEquals(
"SELECT FROM myTable "
+ "WHERE (myTable.myColumn1=? "
+ "OR myTable.myColumn1=? "
+ "OR myTable.myColumn2=? "
+ "OR myTable.myColumn2=?)",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add(value1a);
expectedReplacements.add(value1b);
expectedReplacements.add(value2a);
expectedReplacements.add(value2b);
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test where condition with several ANDs compairing against Strings.
*/
public void testAndCriterions() throws Exception
{
final Column column1 = new ColumnImpl("myTable", "myColumn1");
final Column column2 = new ColumnImpl("myTable", "myColumn2");
final String value1a = "1a";
final String value1b = "1b";
final String value2a = "2a";
Criterion criterion1 = new Criterion(column1, value1a);
Criterion criterion2 = new Criterion(column1, value1b);
Criterion criterion3 = new Criterion(column2, value2a);
criterion1.and(criterion2).and(criterion3);
Criteria c = new Criteria().where(criterion1);
Query query = SqlBuilder.buildQuery(c);
assertEquals(
"SELECT FROM myTable "
+ "WHERE (myTable.myColumn1=? "
+ "AND myTable.myColumn1=? "
+ "AND myTable.myColumn2=?)",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add(value1a);
expectedReplacements.add(value1b);
expectedReplacements.add(value2a);
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test where condition with several ORs compairing against Strings.
*/
public void testOrCriterions() throws Exception
{
final Column column1 = new ColumnImpl("myTable", "myColumn1");
final Column column2 = new ColumnImpl("myTable", "myColumn2");
final String value1a = "1a";
final String value1b = "1b";
final String value2a = "2a";
Criterion criterion1 = new Criterion(column1, value1a);
Criterion criterion2 = new Criterion(column1, value1b);
Criterion criterion3 = new Criterion(column2, value2a);
criterion1.or(criterion2).or(criterion3);
Criteria c = new Criteria().where(criterion1);
Query query = SqlBuilder.buildQuery(c);
assertEquals(
"SELECT FROM myTable "
+ "WHERE (myTable.myColumn1=? "
+ "OR myTable.myColumn1=? "
+ "OR myTable.myColumn2=?)",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add(value1a);
expectedReplacements.add(value1b);
expectedReplacements.add(value2a);
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the andVerbatimSql method with null replacements.
*/
public void testAndVerbatimSqlReplacementNull() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.andVerbatimSql(
"foo(table1.x) = bar(table2.y)",
null);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1 "
+ "WHERE (table1.a=? "
+ "AND foo(table1.x) = bar(table2.y))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the andVerbatimSql method with replacements.
*/
public void testAndVerbatimSqlWithReplacements() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.andVerbatimSql(
"foo(table1.x, ?) = bar(table2.y, ?)",
new Object[] {"y", "z"});
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1 "
+ "WHERE (table1.a=? "
+ "AND foo(table1.x, ?) = bar(table2.y, ?))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
expectedReplacements.add("y");
expectedReplacements.add("z");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the andVerbatimSql method with from Columns.
*/
public void testAndVerbatimSqlWithFromColumns() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.andVerbatimSql(
"foo(table1.x) = bar(table2.y)",
new Object[] {},
new ColumnImpl("table1.x"),
new ColumnImpl("table2.y"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1, table2 "
+ "WHERE (table1.a=? "
+ "AND foo(table1.x) = bar(table2.y))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the orVerbatimSql method with null replacements.
*/
public void testOrVerbatimSqlReplacementNull() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.orVerbatimSql(
"foo(table1.x) = bar(table2.y)",
null);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1 "
+ "WHERE (table1.a=? "
+ "OR foo(table1.x) = bar(table2.y))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the orVerbatimSql method with replacements.
*/
public void testOrVerbatimSqlWithReplacements() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.orVerbatimSql(
"foo(table1.x, ?) = bar(table2.y, ?)",
new Object[] {"y", "z"});
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1 "
+ "WHERE (table1.a=? "
+ "OR foo(table1.x, ?) = bar(table2.y, ?))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
expectedReplacements.add("y");
expectedReplacements.add("z");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the orVerbatimSql method with from Columns.
*/
public void testOrVerbatimSqlWithFromColumns() throws Exception
{
Criteria criteria = new Criteria()
.where(new ColumnImpl("table1.a"), "a")
.orVerbatimSql(
"foo(table1.x) = bar(table2.y)",
new Object[] {},
new ColumnImpl("table1.x"),
new ColumnImpl("table2.y"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1, table2 "
+ "WHERE (table1.a=? "
+ "OR foo(table1.x) = bar(table2.y))",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("a");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the whereVerbatimSql method with null replacements.
*/
public void testWhereVerbatimSqlReplacementNull() throws Exception
{
Criteria criteria = new Criteria()
.whereVerbatimSql(
"foo(table1.x) = bar(table2.y)",
null);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM "
+ "WHERE foo(table1.x) = bar(table2.y)",
query.toString());
assertEquals(
new ArrayList<String>(),
query.getPreparedStatementReplacements());
}
/**
* Test the whereVerbatimSql method with replacements.
*/
public void testWhereVerbatimSqlWithReplacements() throws Exception
{
Criteria criteria = new Criteria()
.whereVerbatimSql(
"foo(table1.x, ?) = bar(table2.y, ?)",
new Object[] {"y", "z"});
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM "
+ "WHERE foo(table1.x, ?) = bar(table2.y, ?)",
query.toString());
List<String> expectedReplacements = new ArrayList<String>();
expectedReplacements.add("y");
expectedReplacements.add("z");
assertEquals(
expectedReplacements,
query.getPreparedStatementReplacements());
}
/**
* Test the whereVerbatimSql method with from Columns.
*/
public void testWhereVerbatimSqlWithFromColumns() throws Exception
{
Criteria criteria = new Criteria()
.whereVerbatimSql(
"foo(table1.x) = bar(table2.y)",
new Object[] {},
new ColumnImpl("table1.x"),
new ColumnImpl("table2.y"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT FROM table1, table2 "
+ "WHERE foo(table1.x) = bar(table2.y)",
query.toString());
assertEquals(
new ArrayList<String>(),
query.getPreparedStatementReplacements());
}
/**
* Test that unknown columns are treated case-insensitive if ignoreCase
* is set.
*/
public void testignoreCaseUnknownColumnType() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("unknownTable.column1"));
criteria.where(new ColumnImpl("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.where(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.where(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(new ColumnImpl("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(new ColumnImpl("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(new ColumnImpl("table.column1"));
criteria.addDescendingOrderByColumn(new ColumnImpl("table2.column2"));
criteria.addAscendingOrderByColumn(new ColumnImpl("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(new ColumnImpl("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 testOrderByWithFunction() throws Exception
{
Criteria criteria = new Criteria();
criteria.addAscendingOrderByColumn(
new ColumnImpl("count(table.column1)"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT count(table.column1) FROM table "
+ "ORDER BY count(table.column1) ASC",
query.toString());
}
public void testOrderByWithAsColumn() throws Exception
{
Criteria criteria = new Criteria();
criteria.addAsColumn("asColumn", stringColumnMap);
criteria.addAscendingOrderByColumn(new ColumnImpl("asColumn"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT TABLE.COLUMN1 AS asColumn "
+ "FROM TABLE "
+ "ORDER BY asColumn ASC",
query.toString());
}
public void testOrderByWithAsColumnIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addAsColumn("asColumn", stringColumnMap);
criteria.addAscendingOrderByColumn(new ColumnImpl("asColumn"), true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT TABLE.COLUMN1 AS asColumn, UPPER(asColumn) "
+ "FROM TABLE "
+ "ORDER BY UPPER(asColumn) ASC",
query.toString());
}
public void testOrderByWithAsColumnAndAliasIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addAlias("alias", stringColumnMap.getTableName());
criteria.addAsColumn(
"asColumn",
new ColumnImpl("alias", stringColumnMap.getColumnName()));
criteria.addAscendingOrderByColumn(new ColumnImpl("asColumn"), true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT alias.COLUMN1 AS asColumn, UPPER(asColumn) "
+ "FROM TABLE alias "
+ "ORDER BY UPPER(asColumn) ASC",
query.toString());
}
public void testOrderByAscendingIgnoreCaseString() throws Exception
{
Criteria criteria = new Criteria();
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(new ColumnImpl("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(new ColumnImpl("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(new ColumnImpl("alias.column1"));
criteria.where(new ColumnImpl("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 testAliasWithSubquery() throws Exception
{
Criteria subquery = new Criteria()
.addSelectColumn(new ColumnImpl("table2.column1"))
.where(new ColumnImpl("table2.column2"),
new ColumnImpl("table2.column3"));
Criteria criteria = new Criteria();
criteria.addAlias("alias", subquery);
criteria.addSelectColumn(new ColumnImpl("alias.column1"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT alias.column1 FROM ("
+ "SELECT table2.column1 FROM table2 "
+ "WHERE table2.column2=table2.column3) "
+ "alias",
query.toString());
assertEquals(0, query.getPreparedStatementReplacements().size());
}
public void testAliasWithSubqueryAndReplacements() throws Exception
{
Criteria subquery = new Criteria()
.addSelectColumn(new ColumnImpl("table2.column1"))
.where(new ColumnImpl("table2.column2"), "x");
Criteria criteria = new Criteria();
criteria.addAlias("alias", subquery);
criteria.addSelectColumn(new ColumnImpl("alias.column1"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT alias.column1 FROM ("
+ "SELECT table2.column1 FROM table2 "
+ "WHERE table2.column2=?) "
+ "alias",
query.toString());
assertEquals(1, query.getPreparedStatementReplacements().size());
assertEquals("x", query.getPreparedStatementReplacements().get(0));
}
public void testAliasWithIgnoreCaseStringColumnType() throws Exception
{
Criteria criteria = new Criteria();
criteria.addAlias("alias", tableMap.getName());
criteria.addSelectColumn(new ColumnImpl("alias.COLUMN1"));
criteria.where(new ColumnImpl("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(new ColumnImpl("alias.COLUMN4"));
criteria.where(new ColumnImpl("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(new ColumnImpl("alias.COLUMN1"));
criteria.where(new ColumnImpl("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(new ColumnImpl("alias.COLUMN4"));
criteria.where(new ColumnImpl("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", new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("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.where(new ColumnImpl("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.where(new ColumnImpl("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(new ColumnImpl("columnAlias"));
criteria.where(new ColumnImpl("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(new ColumnImpl("columnAlias"));
criteria.where(new ColumnImpl("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(new ColumnImpl("table1.column"));
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("table2.column2"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT table1.column FROM table1, table2"
+ " WHERE table1.column1=table2.column2",
query.toString());
}
public void testInnerJoinImplicitWithComparison()
throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table1.column"));
criteria.addJoin(
"table1",
"table2",
new Criterion(
new ColumnImpl("table1.column1"),
new ColumnImpl("table2.column2"),
SqlEnum.NOT_EQUAL),
null);
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(new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("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(new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table2.column2"),
new ColumnImpl("table3.column3"),
Criteria.INNER_JOIN);
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("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 testInnerJoinWithExcplicitExistingRightTableAndOperator()
throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addJoin(
"table2",
"table3",
new Criterion(
new ColumnImpl("table2.column2"),
new ColumnImpl("table3.column3"),
Criteria.LESS_THAN),
Criteria.INNER_JOIN);
criteria.addJoin(
"table1",
"table2",
new Criterion(
new ColumnImpl("table1.column1"),
new ColumnImpl("table2.column2"),
Criteria.GREATER_THAN),
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(new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table2.column2"),
new ColumnImpl("table3.column3"),
Criteria.INNER_JOIN);
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("table4.column4"),
Criteria.INNER_JOIN);
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("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 testInnerJoinExplicitWithComplicatedCondition()
throws Exception
{
Criterion join1 = new Criterion(new ColumnImpl("table1.column2"), "x");
Criterion join2 = new Criterion(
"y",
new ColumnImpl("table2.column2"),
SqlEnum.NOT_EQUAL);
join2.setIgnoreCase(true);
Criterion join3 = new Criterion(
new ColumnImpl("table1.column2"),
new ColumnImpl("table2.column2"));
Criterion join = new Criterion(join1).and(join2).and(join3);
Criteria criteria = new Criteria();
criteria.setDbName(databasePostgresql.getName());
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addJoin("table1", "table2", join, Criteria.INNER_JOIN);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT table1.column1 FROM table1 INNER JOIN table2 "
+ "ON (table1.column2=? "
+ "AND UPPER(?)<>UPPER(table2.column2) "
+ "AND table1.column2=table2.column2)",
query.toString());
assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals("x", query.getPreparedStatementReplacements().get(0));
assertEquals("y", query.getPreparedStatementReplacements().get(1));
}
public void testLeftJoin()
throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table1.column"),
new ColumnImpl("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(
new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table2.column2"),
new ColumnImpl("table3.column3"),
Criteria.INNER_JOIN);
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("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(new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table1.column"),
new ColumnImpl("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(
new ColumnImpl("table1.column1"));
criteria.addJoin(
new ColumnImpl("table2.column2"),
new ColumnImpl("table3.column3"),
Criteria.INNER_JOIN);
criteria.addJoin(
new ColumnImpl("table1.column1"),
new ColumnImpl("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(new ColumnImpl("alias.column1"));
criteria.addAlias("alias", "table1");
criteria.addJoin(
new ColumnImpl("alias.column"),
new ColumnImpl("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", new ColumnImpl("alias.column"));
criteria.addAlias("alias", "table1");
criteria.addJoin(new ColumnImpl("x"), new ColumnImpl("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(new ColumnImpl("*"));
criteria.addJoin(
new ColumnImpl("table1.column"),
new ColumnImpl("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(new ColumnImpl("alias.column1"));
criteria.addAlias("alias", "table1");
criteria.addJoin(
new ColumnImpl("alias.column"),
new ColumnImpl("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(
new ColumnImpl("schema1.alias.column1"));
criteria.addAlias("alias", "table1");
criteria.addJoin(
new ColumnImpl("schema1.alias.column"),
new ColumnImpl("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 testInnerJoinImplicitWithSubqueryAndReplacements()
throws Exception
{
Criteria subquery = new Criteria()
.addSelectColumn(new ColumnImpl("table2.column2a"))
.where(new ColumnImpl("table2.column2b"), "x");
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("alias.column1"));
criteria.addAlias("alias", subquery);
criteria.addJoin(
new ColumnImpl("alias.column1"),
new ColumnImpl("table2.column2"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT alias.column1 FROM ("
+ "SELECT table2.column2a FROM table2 "
+ "WHERE table2.column2b=?) "
+ "alias, table2 "
+ "WHERE alias.column1=table2.column2",
query.toString());
assertEquals(1, query.getPreparedStatementReplacements().size());
assertEquals("x", query.getPreparedStatementReplacements().get(0));
}
public void testInnerJoinImplicitWithComplicatedCondition()
throws Exception
{
Criterion join1 = new Criterion(new ColumnImpl("table1.column2"), "x");
Criterion join2 = new Criterion(
"y",
new ColumnImpl("table2.column2"),
SqlEnum.NOT_EQUAL);
join2.setIgnoreCase(true);
Criterion join3 = new Criterion(
new ColumnImpl("table1.column2"),
new ColumnImpl("table2.column2"));
Criterion join = new Criterion(join1).and(join2).and(join3);
Criteria criteria = new Criteria();
criteria.setDbName(databasePostgresql.getName());
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addJoin("table1", "table2", join, null);
criteria.where(new ColumnImpl("table1.column3"), null, Criteria.ISNULL);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals(
"SELECT table1.column1 FROM table1, table2 "
+ "WHERE (table1.column2=? "
+ "AND UPPER(?)<>UPPER(table2.column2) "
+ "AND table1.column2=table2.column2) "
+ "AND table1.column3 IS NULL",
query.toString());
assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals("x", query.getPreparedStatementReplacements().get(0));
assertEquals("y", query.getPreparedStatementReplacements().get(1));
}
public void testDistinct()
throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("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 testLimitOracle() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(stringColumnMap);
criteria.setLimit(20);
criteria.setDbName(databaseOracle.getName());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT B.* FROM ("
+ " SELECT A.*, rownum AS TORQUE$ROWNUM FROM "
+ "( SELECT TABLE.COLUMN1 FROM TABLE ) A ) B"
+ " WHERE B.TORQUE$ROWNUM <= 20",
query.toString());
}
public void testOffsetOracle() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(stringColumnMap);
criteria.setOffset(10);
criteria.setDbName(databaseOracle.getName());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT B.* FROM ("
+ " SELECT A.*, rownum AS TORQUE$ROWNUM FROM "
+ "( SELECT TABLE.COLUMN1 FROM TABLE ) A ) B"
+ " WHERE B.TORQUE$ROWNUM > 10",
query.toString());
}
public void testLimitOffsetOracle() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(stringColumnMap);
criteria.setLimit(20);
criteria.setOffset(10);
criteria.setDbName(databaseOracle.getName());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT B.* FROM ("
+ " SELECT A.*, rownum AS TORQUE$ROWNUM FROM "
+ "( SELECT TABLE.COLUMN1 FROM TABLE ) A ) B"
+ " WHERE B.TORQUE$ROWNUM > 10 AND B.TORQUE$ROWNUM <= 30",
query.toString());
}
public void testSelectForUpdate() throws Exception
{
Criteria criteria = new Criteria()
.addSelectColumn(stringColumnMap)
.setLimit(20)
.setOffset(10)
.forUpdate();
criteria.setDbName(databaseOracle.getName());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT B.* FROM ("
+ " SELECT A.*, rownum AS TORQUE$ROWNUM FROM "
+ "( SELECT TABLE.COLUMN1 FROM TABLE ) A ) B"
+ " WHERE B.TORQUE$ROWNUM > 10 AND B.TORQUE$ROWNUM <= 30"
+ " FOR UPDATE",
query.toString());
}
public void testHaving() throws Exception
{
Criteria criteria = new Criteria();
criteria.addGroupByColumn(stringColumnMap);
criteria.addAsColumn("count", new ColumnImpl("count(*)"));
criteria.addSelectColumn(stringColumnMap);
criteria.addHaving(
new Criterion("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(new ColumnImpl("*"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT * FROM ", query.toString());
}
public void testCustom() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where("A", "A = functionOf(B)", SqlEnum.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 testCustomLvalueNull() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(null, "A = functionOf(B)", SqlEnum.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 testCriterionCustomSql() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
Criterion criterion
= new Criterion("A", null, null, "A = functionOf(B)", null);
criteria.where(criterion);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column FROM table WHERE A = functionOf(B)",
query.toString());
assertEquals(0, query.getPreparedStatementReplacements().size());
}
public void testCriterionCustomNoString() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
Criterion criterion
= new Criterion("A", 3, SqlEnum.CUSTOM);
criteria.where(criterion);
try
{
SqlBuilder.buildQuery(criteria);
fail("Exception expected");
}
catch (TorqueException e)
{
assertEquals(
"rValue must be a String for the operator CUSTOM",
e.getMessage());
}
}
public void testLvalueIsObject() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(1, 2);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column FROM table WHERE ?=?",
query.toString());
assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
assertEquals(2, query.getPreparedStatementReplacements().get(1));
}
public void testCurrentDate() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("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 testCurrentTime() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("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 testCurrentTimestamp() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("column"), Criteria.CURRENT_TIMESTAMP);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column FROM table "
+ "WHERE column=CURRENT_TIMESTAMP",
query.toString());
assertEquals(0, query.getPreparedStatementReplacements().size());
}
public void testObjectKey() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("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 testNullValue() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(new ColumnImpl("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 testNullValueNotEqual() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(
new ColumnImpl("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 testNullValueAltNotEqual() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(
new ColumnImpl("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 testIsNull() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(
new ColumnImpl("column"),
"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 testIsNotNull() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where(
new ColumnImpl("column"),
"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 testSubselect() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
Criteria subselect = new Criteria();
subselect.where(new ColumnImpl("table.column2"), "value2");
subselect.addSelectColumn(new ColumnImpl("table.column3"));
criteria.where(new ColumnImpl("table.column3"), subselect);
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 testLike() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testLikeWithEscape() throws TorqueException
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"\\*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 testLikeWithEscapeOracle() throws TorqueException
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"\\*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 testLikeIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testLikeIgnoreCaseNoWildcard() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"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 testLikeInteger() throws TorqueException
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(new ColumnImpl("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 testNotLike() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testNotLikeIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testNotLikeIgnoreCaseNoWildcard() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"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 testIlike() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testNotIlike() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
new ColumnImpl("table.column2"),
"*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 testLvalueString() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
criteria.where("X", "Y");
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column FROM table WHERE ?=?",
query.toString());
assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals(
"X",
query.getPreparedStatementReplacements().get(0));
assertEquals(
"Y",
query.getPreparedStatementReplacements().get(1));
}
public void testLvalueNull() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
try
{
criteria.where(null, new ColumnImpl("table.column"));
fail("Exception expected");
}
catch (IllegalArgumentException e)
{
assertEquals("Either the values(lValue, comparison) or "
+ "sql must be not null",
e.getMessage());
}
}
public void testLvalueCriteria() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column"));
Criteria subselect = new Criteria();
subselect.addSelectColumn(new ColumnImpl("table2.column2"));
criteria.where(subselect, new ColumnImpl("table.column"));
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column FROM table WHERE "
+ "(SELECT table2.column2 FROM table2)=table.column",
query.toString());
assertEquals(0, query.getPreparedStatementReplacements().size());
}
public void testInArray() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
String[] inValue = new String[] {"a", "b", null, null};
criteria.whereIn(new ColumnImpl("table.column2"), inValue.clone());
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
+ "WHERE (table.column2 IN (?,?) OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
assertEquals(2, replacements.size());
assertEquals(inValue[0], replacements.get(0));
assertEquals(inValue[1], replacements.get(1));
}
public void testInArrayIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
String[] inValue = new String[] {"a", "b", null, null};
criteria.whereIn(new ColumnImpl("table.column2"), inValue.clone());
criteria.setIgnoreCase(true);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
+ "WHERE (UPPER(table.column2) IN (UPPER(?),UPPER(?))"
+ " OR table.column2 IS NULL)",
query.toString());
List<Object> replacements = query.getPreparedStatementReplacements();
assertEquals(2, replacements.size());
assertEquals(inValue[0], replacements.get(0));
assertEquals(inValue[1], replacements.get(1));
}
public void testInList() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
List<Integer> inList = new ArrayList<Integer>();
inList.add(1);
inList.add(null);
inList.add(2);
inList.add(null);
criteria.whereIn(new ColumnImpl("table.column2"), inList);
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 testInListIgnoreCase() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
List<String> inList = new ArrayList<String>();
inList.add("a");
inList.add("b");
inList.add(null);
inList.add(null);
criteria.whereIn(new ColumnImpl("table.column2"), inList);
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 testNotInList() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
List<Integer> inList = new ArrayList<Integer>();
inList.add(1);
inList.add(null);
inList.add(2);
inList.add(null);
criteria.where(
new ColumnImpl("table.column2"),
inList,
Criteria.NOT_IN);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table.column1 FROM table "
+ "WHERE (table.column2 NOT IN (?,?) "
+ "AND table.column2 IS NOT NULL)",
query.toString());
assertEquals(2, query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
assertEquals(2, query.getPreparedStatementReplacements().get(1));
}
public void testInLargeArray() 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(new ColumnImpl("table.column1"));
criteria.whereIn(new ColumnImpl("table.column2"), values);
long start = System.currentTimeMillis();
Query query = SqlBuilder.buildQuery(criteria);
long end = System.currentTimeMillis();
List<Object> replacements = query.getPreparedStatementReplacements();
assertEquals(size, replacements.size());
// time limit 50 ms
assertTrue("Exceeded time limit of " + LARGE_ARRAY_TIME_LIMIT
+ " ms. Execution time was " + (end - start) + " ms",
end - start < LARGE_ARRAY_TIME_LIMIT);
}
public void testInString() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table.column1"));
criteria.where(
"table.column2",
"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());
}
}
public void testFromElementsSetExplicitly() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addFrom(new FromElement("table3"));
criteria.where(new ColumnImpl("table2.column2"), 1);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table1.column1 FROM table3 "
+ "WHERE table2.column2=?",
query.toString());
assertEquals(1, query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
}
public void testFromElementsSetExplicitlyAsString() throws Exception
{
Criteria criteria = new Criteria();
criteria.addSelectColumn(new ColumnImpl("table1.column1"));
criteria.addFrom("table3");
criteria.where(new ColumnImpl("table2.column2"), 1);
Query query = SqlBuilder.buildQuery(criteria);
assertEquals("SELECT table1.column1 FROM table3 "
+ "WHERE table2.column2=?",
query.toString());
assertEquals(1, query.getPreparedStatementReplacements().size());
assertEquals(1, query.getPreparedStatementReplacements().get(0));
}
}