You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by tf...@apache.org on 2014/09/16 23:05:33 UTC
svn commit: r1625391 - in /db/torque/torque4/trunk:
torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
Author: tfischer
Date: Tue Sep 16 21:05:32 2014
New Revision: 1625391
URL: http://svn.apache.org/r1625391
Log:
TORQUE-322 docs and test for set operations
Added:
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
Modified:
db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
Modified: db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml?rev=1625391&r1=1625390&r2=1625391&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml (original)
+++ db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/orm-reference/read-from-db.xml Tue Sep 16 21:05:32 2014
@@ -921,6 +921,42 @@ SELECT BOOK.BOOK_ID, BOOK.TITLE FROM BOO
</section>
+ <section name="Set operations (UNION, EXCEPT, INTERSECT)">
+ <p>
+ Torque supports the set operations UNION, EXCEPT and INTERSECT.
+ MINUS is the Oracle variant of EXCEPT and is also implicitly supported;
+ Torque knows that for oracle, EXCEPT must be converted to MINUS in SQL.
+ </p>
+
+ <p>
+ Queries using the set operators can be created using the methods
+ union, unionAll, except, exceptAll, intersect and intersectAll
+ in Criteria.
+ For example:
+ </p>
+
+<source><![CDATA[
+Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID, authorList.get(1).getAuthorId(), Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+criteria.union(otherCriteria);
+
+List<Integer> result = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+]]></source>
+ <p>
+ Note that the resulting Criteria from a set operation results in
+ a so-called composite Criteria, for which some operations (e.g. group by)
+ cannot be performed (because the resulting SQL would make no sense).
+ Instead, these operations must be performed on the parts of which
+ the Criteria consists.
+
+ </p>
+ </section>
+
<section name="Locking">
<subsection name="Pessimistically lock table rows within a transaction">
<p>
Added: db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java?rev=1625391&view=auto
==============================================================================
--- db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java (added)
+++ db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/SelectSetOperationTest.java Tue Sep 16 21:05:32 2014
@@ -0,0 +1,467 @@
+package org.apache.torque.generated.peer;
+
+/*
+ * 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.ArrayList;
+import java.util.Collection;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.torque.BaseDatabaseTestCase;
+import org.apache.torque.ColumnImpl;
+import org.apache.torque.TorqueException;
+import org.apache.torque.adapter.MssqlAdapter;
+import org.apache.torque.adapter.MysqlAdapter;
+import org.apache.torque.adapter.OracleAdapter;
+import org.apache.torque.criteria.Criteria;
+import org.apache.torque.om.mapper.IntegerMapper;
+import org.apache.torque.test.dbobject.Author;
+import org.apache.torque.test.dbobject.Book;
+import org.apache.torque.test.peer.AuthorPeer;
+import org.apache.torque.test.peer.BookPeer;
+
+/**
+ * Tests simple selects.
+ *
+ * @version $Id: SelectTest.java 1448403 2013-02-20 20:55:15Z tfischer $
+ */
+public class SelectSetOperationTest extends BaseDatabaseTestCase
+{
+ private static Log log = LogFactory.getLog(SelectSetOperationTest.class);
+
+ private List<Author> authorList;
+
+ @Override
+ public void setUp() throws Exception
+ {
+ super.setUp();
+ cleanBookstore();
+ authorList = insertBookstoreData();
+ }
+
+ /**
+ * Tests a select with UNION
+ *
+ * @throws Exception if the test fails
+ */
+ public void testUnion() throws Exception
+ {
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.union(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(20, result.size());
+
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(0), expected);
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+ }
+
+ /**
+ * Tests a select with UNION ALL
+ *
+ * @throws Exception if the test fails
+ */
+ public void testUnionAll() throws Exception
+ {
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(30, result.size());
+
+ // check unique contents of list
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(0), expected);
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+
+ // check that books of first author occur multiple times (in fact twice)
+ // in the list
+ for (int i = 0; i < 10; ++i)
+ {
+ assertFalse(result.indexOf(authorList.get(0).getBooks().get(i).getBookId())
+ == result.lastIndexOf(authorList.get(0).getBooks().get(i).getBookId()));
+ }
+ }
+
+ /**
+ * Tests a select with EXCEPT
+ *
+ * @throws Exception if the test fails
+ */
+ public void testExcept() throws Exception
+ {
+ if (defaultAdapter instanceof MysqlAdapter)
+ {
+ log.error("testExcept(): "
+ + "MySQL does not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.except(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(10, result.size());
+
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+ }
+
+ /**
+ * Tests a select with EXCEPT ALL.
+ *
+ * @throws Exception if the test fails
+ */
+ public void testExceptAll() throws Exception
+ {
+ if (defaultAdapter instanceof OracleAdapter
+ || defaultAdapter instanceof MysqlAdapter
+ || defaultAdapter instanceof MssqlAdapter)
+ {
+ log.error("testExceptAll(): "
+ + "Oracle, MySQL and MSSQL do not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.exceptAll(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(20, result.size());
+
+ // check unique result
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+
+ // check that books of the author occur multiple times (in fact twice)
+ // in the list
+ for (int i = 0; i < 10; ++i)
+ {
+ assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+ == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+ }
+ }
+
+ /**
+ * Tests a select with EXCEPT ALL. The Set from which the Except statement
+ * is executed contains the removable numbers twice,
+ * Except all only removes one occurance so the other remains.
+ *
+ * @throws Exception if the test fails
+ */
+ public void testExceptAllMultipleOccurances() throws Exception
+ {
+ if (defaultAdapter instanceof OracleAdapter
+ || defaultAdapter instanceof MysqlAdapter
+ || defaultAdapter instanceof MssqlAdapter)
+ {
+ log.error("testExceptAllMultipleOccurances(): "
+ + "Oracle, MySQL and MSSQL do not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.exceptAll(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(30, result.size());
+
+ // check unique result
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(0), expected);
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+
+ // check that books of the second author occur multiple times (in fact twice)
+ // in the list
+ for (int i = 0; i < 10; ++i)
+ {
+ assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+ == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+ }
+ }
+
+ /**
+ * Tests a select with INTERSECT
+ *
+ * @throws Exception if the test fails
+ */
+ public void testIntersect() throws Exception
+ {
+ if (defaultAdapter instanceof MysqlAdapter)
+ {
+ log.error("testIntersect(): "
+ + "MySQL does not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ Criteria intersectCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ intersectCriteria.unionAll(otherCriteria);
+ criteria.intersect(intersectCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(10, result.size());
+
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(1), expected);
+
+ assertEquals(expected, new HashSet<Integer>(result));
+ }
+
+ /**
+ * Tests a select with INTERSECT ALL. The common set is contained twice
+ * in the intersected sets, so the intersection contains the numbers twice.
+ *
+ * @throws Exception if the test fails
+ */
+ public void testIntersectAllMultipleOccurances() throws Exception
+ {
+ if (defaultAdapter instanceof OracleAdapter
+ || defaultAdapter instanceof MysqlAdapter
+ || defaultAdapter instanceof MssqlAdapter)
+ {
+ log.error("testIntersectAllMultipleOccurances(): "
+ + "Oracle, MySQL and MSSQL do not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ Criteria intersectCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ intersectCriteria.unionAll(otherCriteria);
+ criteria.intersectAll(intersectCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(20, result.size());
+
+ // check unique result
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+
+ // check that books of the author occur multiple times (in fact twice)
+ // in the list
+ for (int i = 0; i < 10; ++i)
+ {
+ assertFalse(result.indexOf(authorList.get(1).getBooks().get(i).getBookId())
+ == result.lastIndexOf(authorList.get(1).getBooks().get(i).getBookId()));
+ }
+ }
+
+ /**
+ * Tests a select with INTERSECT ALL.
+ *
+ * @throws Exception if the test fails
+ */
+ public void testIntersectAll() throws Exception
+ {
+ if (defaultAdapter instanceof OracleAdapter
+ || defaultAdapter instanceof MysqlAdapter
+ || defaultAdapter instanceof MssqlAdapter)
+ {
+ log.error("testIntersectAll(): "
+ + "Oracle, MySQL and MSSQL do not support "
+ + "the EXCEPT ALL operator");
+ return;
+ }
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId(),
+ Criteria.LESS_EQUAL)
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria);
+ otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(1).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.intersectAll(otherCriteria);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(10, result.size());
+
+ // check unique result
+ Set<Integer> expected = new HashSet<Integer>();
+ addBookIds(authorList.get(1), expected);
+ assertEquals(expected, new HashSet<Integer>(result));
+ }
+
+ private void addBookIds(
+ final Author author,
+ final Collection<Integer> expected)
+ throws TorqueException
+ {
+ for (Book book : author.getBooks())
+ {
+ expected.add(book.getBookId());
+ }
+ }
+
+ /**
+ * Tests a select with UNION ALL, order by, limit and offset
+ *
+ * @throws Exception if the test fails
+ */
+ public void testUnionAllOrderByLimitOffset() throws Exception
+ {
+ Criteria criteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ Criteria otherCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID,
+ authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.BOOK_ID);
+ criteria.unionAll(otherCriteria)
+ .addAscendingOrderByColumn(new ColumnImpl("1"))
+ .setOffset(2)
+ .setLimit(4);
+
+ List<Integer> result
+ = AuthorPeer.doSelect(criteria, new IntegerMapper());
+
+ assertEquals(4, result.size());
+
+ // check unique contents of list
+ List<Integer> expected = new ArrayList<Integer>();
+ expected.add(authorList.get(0).getBooks().get(1).getBookId());
+ expected.add(authorList.get(0).getBooks().get(1).getBookId());
+ expected.add(authorList.get(0).getBooks().get(2).getBookId());
+ expected.add(authorList.get(0).getBooks().get(2).getBookId());
+ assertEquals(expected, result);
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org