Java tutorial
package org.apache.torque.util; /* * 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.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.List; import java.util.Map; import org.apache.commons.lang.SerializationUtils; import org.apache.torque.BaseTestCase; import org.apache.torque.ColumnImpl; import org.apache.torque.Torque; import org.apache.torque.TorqueException; import org.apache.torque.criteria.Join; import org.apache.torque.criteria.SqlEnum; import org.apache.torque.map.ColumnMap; import org.apache.torque.map.DatabaseMap; import org.apache.torque.map.TableMap; import org.apache.torque.sql.OrderBy; import org.apache.torque.sql.Query; import org.apache.torque.sql.SqlBuilder; import org.apache.torque.util.Criteria.Criterion; /** * Test class for Criteria. * * @author <a href="mailto:celkins@scardini.com">Christopher Elkins</a> * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a> * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a> * @version $Id: CriteriaTest.java 1377464 2012-08-26 17:24:59Z tv $ */ public class CriteriaTest extends BaseTestCase { /** The criteria to use in the test. */ private Criteria c; /** * Initializes the criteria. */ public void setUp() throws Exception { super.setUp(); c = new Criteria(); DatabaseMap databaseMap = Torque.getDatabaseMap("postgresql"); if (!databaseMap.containsTable("TABLE")) { TableMap tableMap = databaseMap.addTable("TABLE"); { ColumnMap columnMap1 = new ColumnMap("COLUMN1", tableMap); columnMap1.setType(new String("")); columnMap1.setJavaType("String"); tableMap.addColumn(columnMap1); } { ColumnMap columnMap2 = new ColumnMap("COLUMN2", tableMap); columnMap2.setType(new String("")); columnMap2.setJavaType("String"); tableMap.addColumn(columnMap2); } { ColumnMap columnMap3 = new ColumnMap("COLUMN3", tableMap); columnMap3.setType(new String("")); columnMap3.setJavaType("String"); tableMap.addColumn(columnMap3); } { ColumnMap columnMap4 = new ColumnMap("COLUMN4", tableMap); columnMap4.setType(new Integer(0)); columnMap4.setJavaType("Integer"); tableMap.addColumn(columnMap4); } } } /** * Test basic adding of strings. */ public void testAddString() { final String table = "myTable"; final String column = "myColumn"; final String value = "myValue"; // Add the string c.add(table, column, value); // Verify that the key exists assertTrue(c.containsKey(table, column)); // Verify that what we get out is what we put in assertTrue(c.getString(table, column).equals(value)); } /** * test various properties of Criterion and nested criterion */ public void testNestedCriterion() throws TorqueException { final String table2 = "myTable2"; final String column2 = "myColumn2"; final String value2 = "myValue2"; final String table3 = "myTable3"; final String column3 = "myColumn3"; final String value3 = "myValue3"; final String table4 = "myTable4"; final String column4 = "myColumn4"; final String value4 = "myValue4"; final String table5 = "myTable5"; final String column5 = "myColumn5"; final String value5 = "myValue5"; Criteria.Criterion crit2 = c.getNewCriterion(table2, column2, value2, Criteria.EQUAL); Criteria.Criterion crit3 = c.getNewCriterion(table3, column3, value3, Criteria.EQUAL); Criteria.Criterion crit4 = c.getNewCriterion(table4, column4, value4, Criteria.EQUAL); Criteria.Criterion crit5 = c.getNewCriterion(table5, column5, value5, Criteria.EQUAL); crit2.and(crit3).or(crit4.and(crit5)); c.add(crit2); c.addSelectColumn(new ColumnImpl(null, "myTable2", null, "*")); String expect = "SELECT * FROM myTable2, myTable3, myTable4, myTable5 WHERE " + "((myTable2.myColumn2=? " + "AND myTable3.myColumn3=?) " + "OR (myTable4.myColumn4=? " + "AND myTable5.myColumn5=?))"; Query result = SqlBuilder.buildQuery(c); assertEquals(expect, result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(4, preparedStatementReplacements.size()); assertEquals("myValue2", preparedStatementReplacements.get(0)); assertEquals("myValue3", preparedStatementReplacements.get(1)); assertEquals("myValue4", preparedStatementReplacements.get(2)); assertEquals("myValue5", preparedStatementReplacements.get(3)); c.clear(); Criteria.Criterion crit6 = c.getNewCriterion(table2, column2, value2, Criteria.LESS_THAN); Criteria.Criterion crit7 = c.getNewCriterion(table3, column3, value3, Criteria.LESS_EQUAL); Criteria.Criterion crit8 = c.getNewCriterion(table4, column4, value4, Criteria.GREATER_THAN); Criteria.Criterion crit9 = c.getNewCriterion(table5, column5, value5, Criteria.GREATER_EQUAL); crit6.and(crit7).or(crit8).and(crit9); c.add(crit6); c.addSelectColumn(new ColumnImpl(null, "myTable2", null, "*")); expect = "SELECT * FROM myTable2, myTable3, myTable4, myTable5 WHERE " + "(((myTable2.myColumn2<? " + "AND myTable3.myColumn3<=?) " + "OR myTable4.myColumn4>?) " + "AND myTable5.myColumn5>=?)"; result = SqlBuilder.buildQuery(c); assertEquals(expect, result.toString()); preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(4, preparedStatementReplacements.size()); assertEquals("myValue2", preparedStatementReplacements.get(0)); assertEquals("myValue3", preparedStatementReplacements.get(1)); assertEquals("myValue4", preparedStatementReplacements.get(2)); assertEquals("myValue5", preparedStatementReplacements.get(3)); // simple confirmations that equality operations work Criteria.Criterion compareToCriterion = c.getNewCriterion(table5, column5, value5, Criteria.GREATER_EQUAL); assertEquals(compareToCriterion.hashCode(), crit9.hashCode()); assertEquals("myTable5.myColumn5>=myValue5", crit9.toString()); } /** * Tests <= and =>. */ public void testBetweenCriterion() throws TorqueException { Criteria.Criterion cn1 = c.getNewCriterion(new ColumnImpl("INVOICE", "COST"), 1000, Criteria.GREATER_EQUAL); Criteria.Criterion cn2 = c.getNewCriterion(new ColumnImpl("INVOICE", "COST"), 5000, Criteria.LESS_EQUAL); c.add(cn1.and(cn2)); String expect = "SELECT FROM INVOICE WHERE " + "(INVOICE.COST>=? AND INVOICE.COST<=?)"; Query result = SqlBuilder.buildQuery(c); assertEquals(expect, result.toString()); assertEquals(2, result.getPreparedStatementReplacements().size()); assertEquals(1000, result.getPreparedStatementReplacements().get(0)); assertEquals(5000, result.getPreparedStatementReplacements().get(1)); } /** * Test Criterion.setIgnoreCase(). */ public void testCriterionIgnoreCase() throws TorqueException { Criteria.Criterion criterion1 = c.getNewCriterion(new ColumnImpl("TABLE", "COLUMN1"), "FoObAr1", Criteria.LIKE); criterion1.setIgnoreCase(true); Criteria.Criterion criterion2 = c.getNewCriterion(new ColumnImpl("TABLE", "COLUMN2"), "FoObAr2", Criteria.EQUAL); criterion2.setIgnoreCase(true); Criteria.Criterion criterion3 = c.getNewCriterion(new ColumnImpl("TABLE", "COLUMN3"), "FoObAr3", Criteria.EQUAL); Criteria.Criterion criterion4 = c.getNewCriterion(new ColumnImpl("TABLE", "COLUMN4"), new Integer(1), Criteria.EQUAL); criterion4.setIgnoreCase(true); c.add(criterion1.and(criterion2).and(criterion3).and(criterion4)); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "*")); c.setDbName("postgresql"); Query result = SqlBuilder.buildQuery(c); String expect = "SELECT * FROM TABLE WHERE " + "(((UPPER(TABLE.COLUMN1)=UPPER(?)" + " AND UPPER(TABLE.COLUMN2)=UPPER(?))" + " AND TABLE.COLUMN3=?) AND TABLE.COLUMN4=?)"; assertEquals(expect, result.toString()); List<Object> replacements = result.getPreparedStatementReplacements(); assertEquals(4, replacements.size()); assertEquals("FoObAr1", replacements.get(0)); assertEquals("FoObAr2", replacements.get(1)); assertEquals("FoObAr3", replacements.get(2)); assertEquals(new Integer(1), replacements.get(3)); } /** * Test that true is evaluated correctly. */ public void testBoolean() throws TorqueException { c.add(new ColumnImpl("TABLE", "COLUMN"), true); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE TABLE.COLUMN=?", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals(Boolean.TRUE, preparedStatementReplacements.get(0)); // test the postgresql variation c = new Criteria(); c.add(new ColumnImpl("TABLE", "COLUMN"), true); c.setDbName("postgresql"); result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE TABLE.COLUMN=?", result.toString()); preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals(Boolean.TRUE, preparedStatementReplacements.get(0)); } /** * testcase for addDate() */ public void testAddDate() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE TABLE.DATE_COLUMN=?", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals(new GregorianCalendar(2003, 0, 22).getTime(), preparedStatementReplacements.get(0)); } /** * testcase for andDate() * issue TORQUE-42 */ public void testAndDate() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22, Criteria.GREATER_THAN); c.andDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2004, 2, 24, Criteria.LESS_THAN); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE " + "(TABLE.DATE_COLUMN>? AND TABLE.DATE_COLUMN<?)", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(2, preparedStatementReplacements.size()); assertEquals(new GregorianCalendar(2003, 0, 22).getTime(), preparedStatementReplacements.get(0)); assertEquals(new GregorianCalendar(2004, 2, 24).getTime(), preparedStatementReplacements.get(1)); } /** * testcase for add(Date) */ public void testDateAdd() throws TorqueException { Calendar cal = new GregorianCalendar(2003, 0, 22); Date date = cal.getTime(); c.add(new ColumnImpl("TABLE", "DATE_COLUMN"), date); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE " + "TABLE.DATE_COLUMN=?", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals(new GregorianCalendar(2003, 0, 22).getTime(), preparedStatementReplacements.get(0)); } public void testCurrentDate() throws TorqueException { c.add(new ColumnImpl("TABLE", "DATE_COLUMN"), Criteria.CURRENT_DATE); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT COUNT(*) FROM TABLE WHERE " + "TABLE.DATE_COLUMN=CURRENT_DATE", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(0, preparedStatementReplacements.size()); } public void testCurrentTime() throws TorqueException { c.add(new ColumnImpl("TABLE", "TIME_COLUMN"), Criteria.CURRENT_TIME); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT COUNT(*) FROM TABLE WHERE " + "TABLE.TIME_COLUMN=CURRENT_TIME", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(0, preparedStatementReplacements.size()); } public void testCriteriaOffsetLimit() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22); c.setOffset(3).setLimit(5); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); Query result = SqlBuilder.buildQuery(c); String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.DATE_COLUMN=?" + " LIMIT 5 OFFSET 3"; assertEquals(expect, result.toString()); } public void testCriteriaWithOffsetNoLimitPostgresql() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22); c.setOffset(3); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); Query result = SqlBuilder.buildQuery(c); String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.DATE_COLUMN=?" + " OFFSET 3"; assertEquals(expect, result.toString()); } /** * TORQUE-87 */ public void testCriteriaWithOffsetNoLimitMysql() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22); c.setOffset(3); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); c.setDbName("mysql"); Query result = SqlBuilder.buildQuery(c); String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.DATE_COLUMN=?" + " LIMIT 18446744073709551615 OFFSET 3"; assertEquals(expect, result.toString()); } public void testCriteriaToStringLimit() throws TorqueException { c.addDate(new ColumnImpl("TABLE", "DATE_COLUMN"), 2003, 0, 22); c.setLimit(5); c.addSelectColumn(new ColumnImpl(null, "TABLE", null, "COUNT(*)")); Query result = SqlBuilder.buildQuery(c); String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.DATE_COLUMN=?" + " LIMIT 5"; assertEquals(expect, result.toString()); } /** * This test case verifies if the Criteria.LIKE comparison type will * get replaced through Criteria.EQUAL if there are no SQL wildcards * in the given value. */ public void testLikeWithoutWildcards() throws TorqueException { c.add(new ColumnImpl("TABLE", "COLUMN"), "no wildcards", Criteria.LIKE); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT FROM TABLE WHERE " + "TABLE.COLUMN=?", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals("no wildcards", preparedStatementReplacements.get(0)); } /** * This test case verifies if the Criteria.NOT_LIKE comparison type will * get replaced through Criteria.NOT_EQUAL if there are no SQL wildcards * in the given value. */ public void testNotLikeWithoutWildcards() { c.add(new ColumnImpl("TABLE", "COLUMN"), "no wildcards", Criteria.NOT_LIKE); String firstExpect = "SELECT FROM TABLE WHERE TABLE.COLUMN!=?"; String secondExpect = "SELECT FROM TABLE WHERE TABLE.COLUMN<>?"; Query result = null; try { result = SqlBuilder.buildQuery(c); } catch (TorqueException e) { e.printStackTrace(); fail("TorqueException thrown in SqlBuilder.buildQuery()"); } assertTrue(result.toString().equals(firstExpect) || result.toString().equals(secondExpect)); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(1, preparedStatementReplacements.size()); assertEquals("no wildcards", preparedStatementReplacements.get(0)); } /** * Test that serialization works. */ public void testSerialization() { c.setOffset(10); c.setLimit(11); c.setIgnoreCase(true); c.setSingleRecord(true); c.setDbName("myDB"); c.setAll(); c.setDistinct(); c.addSelectColumn(new ColumnImpl("Author", "NAME")); c.addSelectColumn(new ColumnImpl("Author", "AUTHOR_ID")); c.addDescendingOrderByColumn(new ColumnImpl("Author", "NAME")); c.addAscendingOrderByColumn(new ColumnImpl("Author", "AUTHOR_ID")); c.addAlias("Writer", "Author"); c.addAsColumn("AUTHOR_NAME", new ColumnImpl("Author", "NAME")); c.addJoin(new ColumnImpl("Author", "AUTHOR_ID"), new ColumnImpl("Book", "AUTHOR_ID"), Criteria.INNER_JOIN); c.add(new ColumnImpl("Author", "NAME"), "author%", Criteria.LIKE); // Some direct Criterion checks Criterion cn = c.getCriterion(new ColumnImpl("Author", "NAME")); cn.setIgnoreCase(true); assertEquals("author%", cn.getValue()); assertEquals(Criteria.LIKE, cn.getComparison()); Criterion cnDirectClone = (Criterion) SerializationUtils.clone(cn); assertEquals(cn, cnDirectClone); // Clone the object Criteria cClone = (Criteria) SerializationUtils.clone(c); // Check the clone assertEquals(c.size(), cClone.size()); assertEquals(10, cClone.getOffset()); assertEquals(c.getOffset(), cClone.getOffset()); assertEquals(11, cClone.getLimit()); assertEquals(c.getLimit(), cClone.getLimit()); assertEquals(true, cClone.isIgnoreCase()); assertEquals(c.isIgnoreCase(), cClone.isIgnoreCase()); assertEquals(true, cClone.isSingleRecord()); assertEquals(c.isSingleRecord(), cClone.isSingleRecord()); assertEquals("myDB", cClone.getDbName()); assertEquals(c.getDbName(), cClone.getDbName()); List selectModifiersClone = cClone.getSelectModifiers(); assertTrue(selectModifiersClone.contains(Criteria.ALL.toString())); assertTrue(selectModifiersClone.contains(Criteria.DISTINCT.toString())); assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers()); List selectColumnsClone = cClone.getSelectColumns(); assertTrue(selectColumnsClone.contains(new ColumnImpl("Author", "NAME"))); assertTrue(selectColumnsClone.contains(new ColumnImpl("Author", "AUTHOR_ID"))); assertEquals(c.getSelectColumns(), cClone.getSelectColumns()); List orderByColumnsClone = cClone.getOrderByColumns(); assertTrue(orderByColumnsClone.contains(new OrderBy(new ColumnImpl("Author.NAME"), SqlEnum.DESC, false))); assertTrue( orderByColumnsClone.contains(new OrderBy(new ColumnImpl("Author.AUTHOR_ID"), SqlEnum.ASC, false))); assertEquals(c.getOrderByColumns(), cClone.getOrderByColumns()); Map aliasesClone = cClone.getAliases(); assertTrue(aliasesClone.containsKey("Writer")); assertEquals("Author", aliasesClone.get("Writer")); assertEquals(c.getAliases(), cClone.getAliases()); Map asColumnsClone = cClone.getAsColumns(); assertTrue(asColumnsClone.containsKey("AUTHOR_NAME")); assertEquals(new ColumnImpl("Author", "NAME"), asColumnsClone.get("AUTHOR_NAME")); assertEquals(c.getAsColumns(), cClone.getAsColumns()); // Check Joins List joinsClone = cClone.getJoins(); Join joinClone = (Join) joinsClone.get(0); assertEquals(new ColumnImpl("Author", "AUTHOR_ID"), joinClone.getJoinCondition().getLValue()); assertEquals(new ColumnImpl("Book", "AUTHOR_ID"), joinClone.getJoinCondition().getRValue()); assertEquals(Criteria.INNER_JOIN, joinClone.getJoinType()); assertEquals(c.getJoins(), cClone.getJoins()); // Some Criterion checks Criterion cnClone = cClone.getCriterion(new ColumnImpl("Author", "NAME")); assertEquals("author%", cnClone.getValue()); assertEquals(Criteria.LIKE, cnClone.getComparison()); assertEquals(cn.isIgnoreCase(), cnClone.isIgnoreCase()); // Confirm that equals() checks all of the above. assertEquals(c, cClone); // Check hashCode() too. assertEquals(c.hashCode(), cClone.hashCode()); } /** * Test that {@link Criteria#equals(Object)} works correctly for a simple * Criteria object. * @throws TorqueException */ public void testEquals() throws TorqueException { c.addSelectColumn(new ColumnImpl("Author", "NAME")); c.addSelectColumn(new ColumnImpl("Author", "AUTHOR_ID")); c.add(new ColumnImpl("Author", "NAME"), "foobar"); Criteria cClone = (Criteria) SerializationUtils.clone(c); assertTrue(c.equals(cClone)); } /** * Checks whether orderBy works. */ public void testOrderBy() throws TorqueException { // we need a rudimentary databaseMap for this test case to work DatabaseMap dbMap = Torque.getDatabaseMap(Torque.getDefaultDB()); TableMap tableMap = dbMap.addTable("AUTHOR"); ColumnMap columnMap = new ColumnMap("NAME", tableMap); columnMap.setType(""); tableMap.addColumn(columnMap); columnMap = new ColumnMap("AUTHOR_ID", tableMap); columnMap.setType(new Integer(0)); tableMap.addColumn(columnMap); // check that alias'ed tables are referenced by their alias // name when added to the select clause. c.addSelectColumn(new ColumnImpl("AUTHOR", "NAME")); c.addAlias("a", "AUTHOR"); c.addJoin(new ColumnImpl("AUTHOR", "AUTHOR_ID"), new ColumnImpl("a", "AUTHOR_ID")); c.addAscendingOrderByColumn(new ColumnImpl("a", "NAME")); Query result = SqlBuilder.buildQuery(c); assertEquals("SELECT AUTHOR.NAME, a.NAME " + "FROM AUTHOR, AUTHOR a " + "WHERE AUTHOR.AUTHOR_ID=a.AUTHOR_ID " + "ORDER BY a.NAME ASC", result.toString()); List<Object> preparedStatementReplacements = result.getPreparedStatementReplacements(); assertEquals(0, preparedStatementReplacements.size()); } }