Author: tfischer
Date: Mon May 7 13:41:10 2012
New Revision: 1335015
URL: http://svn.apache.org/viewvc?rev=1335015&view=rev
Log:
TORQUE-7 support other operator than equal in joins
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java
db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties
db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties
db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties
db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Criteria.java
Mon May 7 13:41:10 2012
@@ -457,6 +457,39 @@ public class Criteria
}
/**
+ * Adds a join to the criteria, E.g. to create the condition
+ * <p>
+ * PROJECT LEFT JOIN FOO ON PROJECT.PROJECT_ID=FOO.PROJECT_ID
+ * <p> use <p>
+ * <code>
+ * criteria.addJoin(
+ * ProjectPeer.PROJECT_ID,
+ * Criteria.NOT_EQUAL,
+ * FooPeer.PROJECT_ID,
+ * Criteria.LEFT_JOIN);
+ * </code>
+ *
+ * @param left A String with the left side of the join.
+ * @param comparison the comparison operator, not null.
+ * The operator CUSTOM is not supported.
+ * @param right A String with the right side of the join.
+ * @param joinType The operator used for the join. Must be one of null,
+ * Criteria.LEFT_JOIN, Criteria.RIGHT_JOIN, Criteria.INNER_JOIN
+ *
+ * @return A modified Criteria object.
+ */
+ public Criteria addJoin(
+ Column left,
+ SqlEnum comparison,
+ Column right,
+ JoinType joinType)
+ {
+ joins.add(new Join(left, comparison, right, joinType));
+
+ return this;
+ }
+
+ /**
* Get the List of Joins.
*
* @return a List which contains objects of type Join, not null.
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/criteria/Join.java
Mon May 7 13:41:10 2012
@@ -30,8 +30,7 @@ import org.apache.torque.Column;
* <pre>
* table_a LEFT JOIN table_b ON table_a.id = table_b.a_id
* </pre>
- * The class is immutable. Because the class is also used by
- * {@link org.apache.torque.util.BasePeer}, it is visible from the package.
+ * The class is immutable.
*/
public class Join implements Serializable
{
@@ -41,6 +40,9 @@ public class Join implements Serializabl
/** The left column of the join condition, not null. */
private Column leftColumn = null;
+ /** The comparison operator. The operator CUSTOM is not supported. */
+ private SqlEnum comparison = SqlEnum.EQUAL;
+
/** The right column of the join condition, not null. */
private Column rightColumn = null;
@@ -51,17 +53,54 @@ public class Join implements Serializabl
private JoinType joinType = null;
/**
- * Constructor
+ * Constructor.
+ *
+ * @param leftColumn the left column of the join condition;
+ * might contain an alias name, not null.
+ * @param rightColumn the right column of the join condition
+ * might contain an alias name, not null.
+ * @param joinType the type of the join. Valid join types are
+ * null (adding the join condition to the where clause),
+ * SqlEnum.LEFT_JOIN, SqlEnum.RIGHT_JOIN, and SqlEnum.INNER_JOIN
+ */
+ public Join(
+ final Column leftColumn,
+ final Column rightColumn,
+ final JoinType joinType)
+ {
+ if (leftColumn == null)
+ {
+ throw new NullPointerException("leftColumn is null");
+ }
+ if (rightColumn == null)
+ {
+ throw new NullPointerException("rightColumn is null");
+ }
+ this.leftColumn = leftColumn;
+ this.rightColumn = rightColumn;
+ this.joinType = joinType;
+ }
+
+ /**
+ * Constructor with the comparison operator.
+ *
* @param leftColumn the left column of the join condition;
* might contain an alias name, not null.
+ * @param comparison the comparison, not null.
+ * The operator CUSTOM is not supported.
* @param rightColumn the right column of the join condition
* might contain an alias name, not null.
* @param joinType the type of the join. Valid join types are
* null (adding the join condition to the where clause),
* SqlEnum.LEFT_JOIN, SqlEnum.RIGHT_JOIN, and SqlEnum.INNER_JOIN
+ *
+ * @throws NullPointerException if leftColumn, comparison or rightColumn
+ * are null.
+ * @throws IllegalArgumentException if comparison id SqlEnum.CUSTOM
*/
public Join(
final Column leftColumn,
+ final SqlEnum comparison,
final Column rightColumn,
final JoinType joinType)
{
@@ -73,7 +112,17 @@ public class Join implements Serializabl
{
throw new NullPointerException("rightColumn is null");
}
+ if (comparison == null)
+ {
+ throw new NullPointerException("comparison is null");
+ }
+ if (comparison == SqlEnum.CUSTOM)
+ {
+ throw new IllegalArgumentException(
+ "The comparison SqlEnum.CUSTOM is not supported for
Joins.");
+ }
this.leftColumn = leftColumn;
+ this.comparison = comparison;
this.rightColumn = rightColumn;
this.joinType = joinType;
}
@@ -88,7 +137,7 @@ public class Join implements Serializabl
}
/**
- * @return the left column of the join condition
+ * @return the left column of the join condition.
*/
public final Column getLeftColumn()
{
@@ -96,7 +145,15 @@ public class Join implements Serializabl
}
/**
- * @return the right column of the join condition
+ * @return the comaprsion operator between leftColumn and rightColumn,
+ */
+ public final SqlEnum getComparison()
+ {
+ return comparison;
+ }
+
+ /**
+ * @return the right column of the join condition.
*/
public final Column getRightColumn()
{
@@ -118,7 +175,7 @@ public class Join implements Serializabl
.append(" : ");
}
result.append(leftColumn.getSqlExpression())
- .append("=")
+ .append(comparison)
.append(rightColumn.getSqlExpression())
.append(" (ignoreCase not considered)");
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/JoinBuilder.java
Mon May 7 13:41:10 2012
@@ -28,6 +28,7 @@ import org.apache.torque.criteria.Criter
import org.apache.torque.criteria.FromElement;
import org.apache.torque.criteria.Join;
import org.apache.torque.criteria.JoinType;
+import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.util.UniqueList;
/**
@@ -78,6 +79,7 @@ public final class JoinBuilder
{
Join join = criteriaJoins.get(i);
Column leftColumn = join.getLeftColumn();
+ SqlEnum comparison = join.getComparison();
Column rightColumn = join.getRightColumn();
// Check the join type and add the join to the
@@ -99,7 +101,7 @@ public final class JoinBuilder
criteria,
query);
queryWhereClause.add(
- buildJoinCondition(leftColumn, rightColumn));
+ buildJoinCondition(leftColumn, comparison,
rightColumn));
}
else
{
@@ -140,7 +142,10 @@ public final class JoinBuilder
FromElement fromElement = new FromElement(
rightExpression.getSql().toString(),
joinType,
- buildJoinCondition(leftColumn, rightColumn),
+ buildJoinCondition(
+ leftColumn,
+ comparison,
+ rightColumn),
rightExpression.getPreparedStatementReplacements());
queryFromClause.add(fromElement);
}
@@ -167,7 +172,7 @@ public final class JoinBuilder
leftExpression.getSql().toString(),
reverseJoinType(joinType),
buildJoinCondition(
- rightColumn, leftColumn),
+ leftColumn, comparison, rightColumn),
leftExpression.getPreparedStatementReplacements());
queryFromClause.add(fromElement);
}
@@ -208,16 +213,18 @@ public final class JoinBuilder
* Used to specify a join on two columns.
*
* @param column A column in one of the tables to be joined.
+ * @param operator the join operator.
* @param relatedColumn The column in the other table to be joined.
* @return A join expression, e.g. table_a.column_a=table_b.column_b.
*/
private static String buildJoinCondition(
Column column,
+ SqlEnum operator,
Column relatedColumn)
{
return column.getSqlExpression()
- + '='
+ + operator
+ relatedColumn.getSqlExpression();
}
}
Modified:
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/sql/SqlBuilderTest.java
Mon May 7 13:41:10 2012
@@ -917,6 +917,22 @@ public class SqlBuilderTest extends Base
query.toString());
}
+ public void testInnerJoinImplicitWithComparison()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(new ColumnImpl("table1.column"));
+ criteria.addJoin(
+ new ColumnImpl("table1.column1"),
+ Criteria.NOT_EQUAL,
+ new ColumnImpl("table2.column2"),
+ 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
@@ -955,7 +971,34 @@ public class SqlBuilderTest extends Base
+ " FROM table2 INNER JOIN table3"
+ " ON table2.column2=table3.column3"
+ " INNER JOIN table1"
- + " ON table2.column2=table1.column1",
+ + " ON table1.column1=table2.column2",
+ query.toString());
+ }
+
+ public void testInnerJoinWithExcplicitExistingRightTableAndOperator()
+ throws Exception
+ {
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(new ColumnImpl("table1.column1"));
+ criteria.addJoin(
+ new ColumnImpl("table2.column2"),
+ Criteria.LESS_THAN,
+ new ColumnImpl("table3.column3"),
+ Criteria.INNER_JOIN);
+ criteria.addJoin(
+ new ColumnImpl("table1.column1"),
+ Criteria.GREATER_THAN,
+ 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());
}
@@ -1029,7 +1072,7 @@ public class SqlBuilderTest extends Base
+ " FROM table2 INNER JOIN table3"
+ " ON table2.column2=table3.column3"
+ " RIGHT JOIN table1"
- + " ON table2.column2=table1.column1",
+ + " ON table1.column1=table2.column2",
query.toString());
}
@@ -1072,7 +1115,7 @@ public class SqlBuilderTest extends Base
+ " FROM table2 INNER JOIN table3"
+ " ON table2.column2=table3.column3"
+ " LEFT JOIN table1"
- + " ON table2.column2=table1.column1",
+ + " ON table1.column1=table2.column2",
query.toString());
}
Modified:
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/JoinTest.java
Mon May 7 13:41:10 2012
@@ -153,6 +153,37 @@ public class JoinTest extends BaseDataba
}
/**
+ * Test a join with an operator which is not equal.
+ *
+ * @throws Exception if the test fails
+ */
+ public void testInnerJoinsOtherComparator() throws Exception
+ {
+ cleanBookstore();
+ insertTestData();
+ Criteria criteria = new Criteria();
+ criteria.addJoin(
+ BookPeer.AUTHOR_ID,
+ Criteria.NOT_EQUAL,
+ AuthorPeer.AUTHOR_ID,
+ null);
+
+ List<Author> authorList = AuthorPeer.doSelect(criteria);
+
+ // Here we get 8 authors:
+ // three times the author with one book,
+ // four times the author without book,
+ // and one time the author with three books
+ if (authorList.size() != 8)
+ {
+ fail("book join author on not equals"
+ + "incorrect numbers of authors found : "
+ + authorList.size()
+ + ", should be 8");
+ }
+ }
+
+ /**
* Test double join with aliases.
*
* @throws Exception if the test fails
Modified:
db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/profile/derbyEmbedded/Torque.properties
Mon May 7 13:41:10 2012
@@ -20,6 +20,7 @@
torque.database.default = bookstore
torque.database.bookstore.adapter = derby
+torque.transactionManager = org.apache.torque.util.TransactionManagerImpl
#Using commons-dbcp
torque.dsfactory.bookstore.factory =
org.apache.torque.dsfactory.SharedPoolDataSourceFactory
Modified:
db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/profile/mssql/Torque.properties
Mon May 7 13:41:10 2012
@@ -27,8 +27,12 @@ torque.database.bookstore.adapter = mssq
# Using commons-dbcp
torque.dsfactory.bookstore.factory =
org.apache.torque.dsfactory.SharedPoolDataSourceFactory
torque.dsfactory.bookstore.connection.driver = net.sourceforge.jtds.jdbc.Driver
-torque.dsfactory.bookstore.connection.url =
jdbc:jtds:sqlserver://fastnet.dukece.com:1433/bookstore
-torque.dsfactory.bookstore.connection.user = sa
-torque.dsfactory.bookstore.connection.password =
+torque.dsfactory.bookstore.connection.url =
jdbc:jtds:sqlserver://localhost:1433/torque
+torque.dsfactory.bookstore.connection.user = torque
+torque.dsfactory.bookstore.connection.password = torqueUserPassword
+torque.dsfactory.bookstore.pool.defaultAutoCommit = false
torque.dsfactory.bookstore.pool.validationQuery = SELECT 1
+
+# use Caching. This property is only used if managers are used by generators.
+torque.manager.useCache = true
Modified:
db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/profile/mysql/Torque.properties
Mon May 7 13:41:10 2012
@@ -29,7 +29,7 @@ torque.dsfactory.bookstore.factory = org
torque.dsfactory.bookstore.connection.driver = org.gjt.mm.mysql.Driver
torque.dsfactory.bookstore.connection.url =
jdbc:mysql://localhost:3306/bookstore
torque.dsfactory.bookstore.connection.user = root
-torque.dsfactory.bookstore.connection.password =
+torque.dsfactory.bookstore.connection.password = mysql
torque.dsfactory.bookstore.pool.validationQuery = SELECT 1
Modified:
db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties?rev=1335015&r1=1335014&r2=1335015&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/profile/postgresql/Torque.properties
Mon May 7 13:41:10 2012
@@ -28,7 +28,10 @@ torque.database.bookstore.adapter = post
torque.dsfactory.bookstore.factory =
org.apache.torque.dsfactory.SharedPoolDataSourceFactory
torque.dsfactory.bookstore.connection.driver = org.postgresql.Driver
torque.dsfactory.bookstore.connection.url =
jdbc:postgresql://localhost:5432/bookstore
-torque.dsfactory.bookstore.connection.user = torque
-torque.dsfactory.bookstore.connection.password = torque
+torque.dsfactory.bookstore.connection.user = postgres
+torque.dsfactory.bookstore.connection.password = postgresql
torque.dsfactory.bookstore.pool.validationQuery = SELECT 1
+
+# use Caching. This property is only used if managers are used by generators.
+torque.manager.useCache = true
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
