You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jaxme-dev@ws.apache.org by jo...@apache.org on 2004/02/18 15:15:51 UTC
cvs commit: ws-jaxme/src/js/org/apache/ws/jaxme/sqls SelectStatement.java
jochen 2004/02/18 06:15:51
Modified: src/js/org/apache/ws/jaxme/sqls/oracle
OraSQLGeneratorImpl.java
src/js/org/apache/ws/jaxme/sqls/impl SQLGeneratorImpl.java
SelectStatementImpl.java VirtualColumn.java
src/js/org/apache/ws/jaxme/sqls SelectStatement.java
Added: src/js/org/apache/ws/jaxme/sqls/junit JoinTest.java
Log:
Enhanced support for subselects and other advanced features.
Revision Changes Path
1.5 +1 -0 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/oracle/OraSQLGeneratorImpl.java
Index: OraSQLGeneratorImpl.java
===================================================================
RCS file: /home/cvs/ws-jaxme/src/js/org/apache/ws/jaxme/sqls/oracle/OraSQLGeneratorImpl.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- OraSQLGeneratorImpl.java 16 Feb 2004 23:39:54 -0000 1.4
+++ OraSQLGeneratorImpl.java 18 Feb 2004 14:15:50 -0000 1.5
@@ -112,6 +112,7 @@
sb.append(whereClauses.get(i));
}
+ sb.append(getOrderClause(pData, pQuery));
return sb.toString();
}
1.12 +69 -14 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/SQLGeneratorImpl.java
Index: SQLGeneratorImpl.java
===================================================================
RCS file: /home/cvs/ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/SQLGeneratorImpl.java,v
retrieving revision 1.11
retrieving revision 1.12
diff -u -r1.11 -r1.12
--- SQLGeneratorImpl.java 16 Feb 2004 23:39:49 -0000 1.11
+++ SQLGeneratorImpl.java 18 Feb 2004 14:15:50 -0000 1.12
@@ -461,17 +461,31 @@
}
Integer num = (Integer) pData.getColumnNames().get(pColumn.getColumn().getName().toString().toUpperCase());
if (num == null) {
- throw new IllegalStateException("Column not in map of column counts.");
+ throw new IllegalStateException("Column not in map of column counts: "
+ + pColumn.getColumn().getName());
}
return num.intValue() > 1;
}
+ protected String getFunction(SelectStatementMetaData pData, Function f) {
+ return f.getName() + '(' + getParts(pData, f.getParts()) + ')';
+ }
+
protected String getColumnAlias(SelectStatementMetaData pData, ColumnReference pColumn) {
Column col = pColumn.getColumn();
String s = col.getName().toString();
if (col.isVirtual()) {
VirtualColumn virtCol = (VirtualColumn) col;
- return virtCol.getValue() + " AS " + s;
+ Object o = virtCol.getValue();
+ if (o instanceof SelectStatement) {
+ return "(" + getSelectQuery((SelectStatement) o, pData) + ") AS " + s;
+ } else if (o instanceof Function) {
+ return getFunction(pData, (Function) o) + " AS " + s;
+ } else if (o instanceof String) {
+ return ((String) o) + " AS " + s;
+ } else {
+ throw new IllegalStateException("Invalid type of VirtualColumn: " + o);
+ }
} else {
if (isQualifiedColumn(pData, pColumn)) {
TableReference tableReference = pColumn.getTableReference();
@@ -565,12 +579,11 @@
} else if (o instanceof ColumnReference[]) {
return getColumnAlias(pData, (ColumnReference[]) o);
} else if (o instanceof SelectStatement) {
- return '(' + getQuery((SelectStatement) o) + ')';
+ return '(' + getSelectQuery((SelectStatement) o, pData) + ')';
} else if (o instanceof RawSQLCode) {
return ((RawSQLCode) o).getRawSQL();
} else if (o instanceof Function) {
- Function f = (Function) o;
- return f.getName() + '(' + getParts(pData, f.getParts()) + ')';
+ return getFunction(pData, (Function) o);
} else {
throw new IllegalArgumentException("Invalid part of a boolean constraint: " + o.getClass().getName());
}
@@ -739,6 +752,37 @@
}
}
addCombinedConstraint(pQuery.getWhere());
+ for (Iterator iter = pQuery.getResultColumns(); iter.hasNext(); ) {
+ addColumn((ColumnReference) iter.next());
+ }
+ }
+
+ protected void addColumn(ColumnReference pColumn) {
+ if (pColumn instanceof VirtualColumn) {
+ VirtualColumn vc = (VirtualColumn) pColumn;
+ Object o = vc.getValue();
+ if (o instanceof SelectStatement) {
+ addSelectStatement((SelectStatement) o);
+ } else if (o instanceof Function) {
+ addParts((Function) o);
+ } else if (o instanceof String) {
+ // Do nothing
+ } else {
+ throw new IllegalStateException("Invalid type of VirtualColumn: " + o);
+ }
+ addColumnName(vc.getName());
+ }
+ }
+
+ private void addColumnName(Column.Name pName) {
+ String key = pName.toString().toUpperCase();
+ Integer num = (Integer) columnNames.get(key);
+ if (num == null) {
+ num = new Integer(1);
+ } else {
+ num = new Integer(num.intValue() + 1);
+ }
+ columnNames.put(key, num);
}
protected void addCombinedConstraint(CombinedConstraint pConstraint) {
@@ -842,14 +886,7 @@
TableReference table = (TableReference) tables.get(i);
for (Iterator iter = table.getTable().getColumns(); iter.hasNext(); ) {
Column col = (Column) iter.next();
- String key = col.getName().toString().toUpperCase();
- Integer num = (Integer) columnNames.get(key);
- if (num == null) {
- num = new Integer(1);
- } else {
- num = new Integer(num.intValue() + 1);
- }
- columnNames.put(key, num);
+ addColumnName(col.getName());
}
}
}
@@ -904,8 +941,26 @@
sb.append(" WHERE ");
sb.append(whereClause);
}
-
+
+ sb.append(getOrderClause(pData, pQuery));
return sb.toString();
+ }
+
+ protected String getOrderClause(SelectStatementMetaData pData, SelectStatement pQuery) {
+ StringBuffer sb = new StringBuffer();
+ for (Iterator iter = pQuery.getOrderColumns(); iter.hasNext(); ) {
+ SelectStatement.OrderColumn col = (SelectStatement.OrderColumn) iter.next();
+ if (sb.length() == 0) {
+ sb.append(" ORDER BY ");
+ } else {
+ sb.append(", ");
+ }
+ sb.append(getColumnAlias(pData, col.getColumn()));
+ if (col.isDescending()) {
+ sb.append(" DESC");
+ }
+ }
+ return sb.toString();
}
public String getQuery(Statement pStatement) {
1.3 +15 -11 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/SelectStatementImpl.java
Index: SelectStatementImpl.java
===================================================================
RCS file: /home/cvs/ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/SelectStatementImpl.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- SelectStatementImpl.java 16 Feb 2004 23:39:49 -0000 1.2
+++ SelectStatementImpl.java 18 Feb 2004 14:15:50 -0000 1.3
@@ -67,17 +67,21 @@
addOrderColumn(new OrderColumnImpl(pColumn, pDescending));
}
- public void addOrderColumn(SelectStatement.OrderColumn pColumn) {
- for (Iterator iter = orderColumns.iterator(); iter.hasNext(); ) {
- SelectStatement.OrderColumn column = (SelectStatement.OrderColumn) iter.next();
- if (column.getColumn().equals(pColumn) &&
- pColumn.getColumn().getTableReference() == column.getColumn().getTableReference()) {
- throw new NullPointerException("The column " + pColumn.getColumn().getColumn().getName() +
- " is already used for sorting.");
- }
- }
- orderColumns.add(pColumn);
- }
+ public void addOrderColumn(SelectStatement.OrderColumn pColumn) {
+ for (Iterator iter = orderColumns.iterator(); iter.hasNext(); ) {
+ SelectStatement.OrderColumn column = (SelectStatement.OrderColumn) iter.next();
+ if (column.getColumn().equals(pColumn) &&
+ pColumn.getColumn().getTableReference() == column.getColumn().getTableReference()) {
+ throw new NullPointerException("The column " + pColumn.getColumn().getColumn().getName() +
+ " is already used for sorting.");
+ }
+ }
+ orderColumns.add(pColumn);
+ }
+
+ public Iterator getOrderColumns() {
+ return orderColumns.iterator();
+ }
public void addResultColumn(ColumnReference pColumn) {
resultColumns.add(pColumn);
1.2 +63 -37 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/VirtualColumn.java
Index: VirtualColumn.java
===================================================================
RCS file: /home/cvs/ws-jaxme/src/js/org/apache/ws/jaxme/sqls/impl/VirtualColumn.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- VirtualColumn.java 18 Nov 2003 06:19:30 -0000 1.1
+++ VirtualColumn.java 18 Feb 2004 14:15:50 -0000 1.2
@@ -1,7 +1,25 @@
+/*
+ * Copyright 2004 The Apache Software Foundation
+ *
+ * Licensed 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.
+
+ */
package org.apache.ws.jaxme.sqls.impl;
import org.apache.ws.jaxme.sqls.Column;
import org.apache.ws.jaxme.sqls.ColumnReference;
+import org.apache.ws.jaxme.sqls.Function;
+import org.apache.ws.jaxme.sqls.SelectStatement;
import org.apache.ws.jaxme.sqls.Table;
import org.apache.ws.jaxme.sqls.TableReference;
@@ -17,41 +35,49 @@
* @author <a href="mailto:jwi@softwareag.com">Jochen Wiedmann</a>
*/
public class VirtualColumn extends AbstractColumn implements ColumnReference {
- private Column.Name alias;
- private String value;
-
- public VirtualColumn(Column.Name pName, Column.Type pType) {
- super(pName, pType);
- }
-
- public VirtualColumn(String pName, Column.Type pType) {
- super(new ColumnImpl.NameImpl(pName), pType);
- }
-
- public Table getTable() { return null; }
- public String getQName() { return getName().toString(); }
- public boolean isPrimaryKeyPart() { return false; }
- public TableReference getTableReference() { return null; }
- public Column getColumn() { return this; }
- public boolean isVirtual() { return true; }
-
- public void setAlias(String pName) {
- setAlias(new ColumnImpl.NameImpl(pName));
- }
-
- public void setAlias(Name pName) {
- alias = pName;
- }
-
- public Name getAlias() {
- return alias;
- }
-
- public void setValue(String pValue) {
- value = pValue;
- }
-
- public String getValue() {
- return value;
- }
+ private Column.Name alias;
+ private Object value;
+
+ public VirtualColumn(Column.Name pName, Column.Type pType) {
+ super(pName, pType);
+ }
+
+ public VirtualColumn(String pName, Column.Type pType) {
+ super(new ColumnImpl.NameImpl(pName), pType);
+ }
+
+ public Table getTable() { return null; }
+ public String getQName() { return getName().toString(); }
+ public boolean isPrimaryKeyPart() { return false; }
+ public TableReference getTableReference() { return null; }
+ public Column getColumn() { return this; }
+ public boolean isVirtual() { return true; }
+
+ public void setAlias(String pName) {
+ setAlias(new ColumnImpl.NameImpl(pName));
+ }
+
+ public void setAlias(Name pName) {
+ alias = pName;
+ }
+
+ public Name getAlias() {
+ return alias;
+ }
+
+ public void setValue(String pValue) {
+ value = pValue;
+ }
+
+ public void setValue(SelectStatement pValue) {
+ value = pValue;
+ }
+
+ public void setValue(Function pValue) {
+ value = pValue;
+ }
+
+ public Object getValue() {
+ return value;
+ }
}
1.1 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/junit/JoinTest.java
Index: JoinTest.java
===================================================================
/*
* Copyright 2003, 2004 The Apache Software Foundation
*
* Licensed 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.
*/
package org.apache.ws.jaxme.sqls.junit;
import org.apache.ws.jaxme.sqls.BooleanConstraint;
import org.apache.ws.jaxme.sqls.Column;
import org.apache.ws.jaxme.sqls.ColumnReference;
import org.apache.ws.jaxme.sqls.CombinedConstraint;
import org.apache.ws.jaxme.sqls.Function;
import org.apache.ws.jaxme.sqls.JoinReference;
import org.apache.ws.jaxme.sqls.Schema;
import org.apache.ws.jaxme.sqls.SelectStatement;
import org.apache.ws.jaxme.sqls.SelectTableReference;
import org.apache.ws.jaxme.sqls.Table;
import org.apache.ws.jaxme.sqls.TableReference;
import org.apache.ws.jaxme.sqls.impl.VirtualColumn;
import org.apache.ws.jaxme.sqls.oracle.OraSQLFactory;
import org.apache.ws.jaxme.sqls.oracle.OraSQLFactoryImpl;
import org.apache.ws.jaxme.sqls.oracle.OraSQLGenerator;
import junit.framework.TestCase;
/**
* @author <a href="mailto:joe@ispsoft.de">Jochen Wiedmann</a>
*/
public class JoinTest extends TestCase {
private OraSQLFactory sqlFactory;
private OraSQLGenerator sqlGenerator;
private Schema schema;
private Table dbAkte, dbBeteiligte, dbAktenzeichen;
public JoinTest(String pName) {
super(pName);
}
public void setUp() {
sqlFactory = new OraSQLFactoryImpl();
sqlGenerator = (OraSQLGenerator) sqlFactory.newSQLGenerator();
sqlGenerator.setOracle8Compatibility(true);
schema = sqlFactory.getDefaultSchema();
dbAkte = schema.newTable("DBAkte");
dbAkte.newColumn("aId", Column.Type.BIGINT);
dbBeteiligte = schema.newTable("DBBeteiligte");
dbBeteiligte.newColumn("aAktenId", Column.Type.BIGINT);
dbBeteiligte.newColumn("aFilter", Column.Type.BIGINT);
dbBeteiligte.newColumn("aName", Column.Type.VARCHAR);
dbBeteiligte.newColumn("aId", Column.Type.BIGINT);
dbAktenzeichen = schema.newTable("DBAktenzeichen");
dbAktenzeichen.newColumn("aAktenId", Column.Type.BIGINT);
dbAktenzeichen.newColumn("aFilter", Column.Type.VARCHAR);
dbAktenzeichen.newColumn("aId", Column.Type.BIGINT);
}
/** <p>Creates the WHERE clause
* <pre>
* a.aID = DBBeteiligte.aAktenId
* </pre></p>
*/
private void addAktenId(CombinedConstraint pWhere,
TableReference pAkteReference, TableReference pBeteiligteReference) {
BooleanConstraint bc = pWhere.createEQ();
bc.addPart(pBeteiligteReference.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
bc.addPart(pAkteReference.newColumnReference(dbAkte.getColumn("aId")));
}
private void addEQ(TableReference pTableReference, CombinedConstraint pWhere,
Column pColumn, String pValue) {
BooleanConstraint bc = pWhere.createEQ();
bc.addPart(pTableReference.newColumnReference(pColumn));
bc.addPart(pValue);
}
/** <p>Creates the count statement
* <pre>
* (SELECT COUNT(*) AS pColumnName FROM DBBeteiligte WHERE aAktenId=a.aId AND aFilter=pFilter) AS pColumnName
* </pre></p>
*/
private ColumnReference getCountStatement(String pColumnName, String pFilter,
TableReference pAkteReference, String pTableAlias) {
// SELECT COUNT(*) FROM DBBeteiligte klc WHERE klc.aFilter='Klaeger' AND klc.aAktenId=a.aID
SelectStatement st = sqlFactory.newSelectStatement();
st.setTable(dbBeteiligte);
SelectTableReference tRef = st.getSelectTableReference();
tRef.setAlias(pTableAlias);
addEQ(tRef, st.getWhere(), dbBeteiligte.getColumn("aFilter"), pFilter);
addAktenId(st.getWhere(), pAkteReference, tRef);
VirtualColumn vc = new VirtualColumn(pColumnName, Column.Type.INTEGER);
vc.setValue("COUNT(*)");
st.addResultColumn(vc);
VirtualColumn result = new VirtualColumn(pColumnName, Column.Type.INTEGER);
result.setValue(st);
return result;
}
/** <p>Creates a statement fetching the first row matching the search criteria:
* <pre>
* LEFT OUTER JOIN DBBeteiligte pTableAlias
* ON a.aId=pTableAlias.aAktenId AND pTableAlias.aFilter=pFilter AND
* UPPER(pTableAlias.aName)=
* (SELECT MIN(UPPER(aName)) FROM DBBeteiligte min WHERE
* pTableAlias.aAktenId=min.aAktenId AND min.aAktenId=pFilter)
* </pre></p>
*/
private JoinReference getFirstRowStatement(String pFilter, TableReference pAkteReference,
SelectTableReference pJoinReference, String pTableAlias) {
JoinReference result = pJoinReference.leftOuterJoin(dbBeteiligte);
result.setAlias(pTableAlias);
addAktenId(result.getOn(), pAkteReference, result);
addEQ(result, result.getOn(), dbBeteiligte.getColumn("aFilter"), pFilter);
BooleanConstraint bc = result.getOn().createEQ();
Function f = pAkteReference.getStatement().createFunction("UPPER");
f.addPart(result.newColumnReference(dbBeteiligte.getColumn("aName")));
bc.addPart(f);
SelectStatement minStatement = sqlFactory.newSelectStatement();
minStatement.setTable(dbBeteiligte);
SelectTableReference minTableRef = minStatement.getSelectTableReference();
minTableRef.setAlias(pTableAlias + "min");
BooleanConstraint bc2 = minStatement.getWhere().createEQ();
bc2.addPart(result.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
bc2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
bc2 = minStatement.getWhere().createEQ();
bc2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aFilter")));
bc2.addPart(pFilter);
f = pAkteReference.getStatement().createFunction("MIN");
Function f2 = pAkteReference.getStatement().createFunction("UPPER");
f.addPart(f2);
f2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aName")));
VirtualColumn vc = new VirtualColumn("MIN", Column.Type.VARCHAR);
vc.setValue(f);
minStatement.addResultColumn(vc);
bc.addPart(minStatement);
return result;
}
public SelectStatement newStatement() {
SelectStatement st = sqlFactory.newSelectStatement();
st.setTable(dbAkte);
SelectTableReference akte = st.getSelectTableReference();
akte.setAlias("a");
//aktenzeichen joinen
JoinReference az = akte.join(dbAktenzeichen);
az.setAlias("az");
CombinedConstraint onClause = az.getOn();
BooleanConstraint bc = onClause.createEQ();
bc.addPart(akte.newColumnReference(dbAkte.getColumn("aId")));
bc.addPart(az.newColumnReference(dbAktenzeichen.getColumn("aAktenId")));
bc = onClause.createEQ();
bc.addPart(az.newColumnReference(dbAktenzeichen.getColumn("aFilter")));
bc.addPart("Hauptverfahren");
//beteiligte joinen
JoinReference kl = getFirstRowStatement("Klaeger", akte, az, "kl");
JoinReference be = getFirstRowStatement("Beklagter", akte, kl, "be");
JoinReference ber = be.leftOuterJoin(dbBeteiligte);
ber.setAlias("ber");
addAktenId(ber.getOn(), akte, ber);
addEQ(ber, ber.getOn(), dbBeteiligte.getColumn("aFilter"), "Beklagter");
st.addResultColumn(getCountStatement("anzahlKlaeger", "Klaeger", akte, "klc"));
st.addResultColumn(getCountStatement("anzahlBeklagte", "Beklagter", akte, "bec"));
return st;
}
public void testCreate() {
SelectStatement st = newStatement();
String got = sqlGenerator.getQuery(st);
System.out.println(got);
String expect = "";
assertEquals(expect, got);
}
}
1.3 +6 -0 ws-jaxme/src/js/org/apache/ws/jaxme/sqls/SelectStatement.java
Index: SelectStatement.java
===================================================================
RCS file: /home/cvs/ws-jaxme/src/js/org/apache/ws/jaxme/sqls/SelectStatement.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- SelectStatement.java 16 Feb 2004 23:39:53 -0000 1.2
+++ SelectStatement.java 18 Feb 2004 14:15:50 -0000 1.3
@@ -67,6 +67,12 @@
*/
public Iterator getResultColumns();
+ /** <p>Returns the list of order columns. The elements
+ * returned by the iterator are instances of
+ * {@link SelectStatment.OrderColumn}.</p>
+ */
+ public Iterator getOrderColumns();
+
/** <p>Sets whether the statement should have a DISTINCT clause. By
* default no DISTINCT clause is present.</p>
*/
---------------------------------------------------------------------
To unsubscribe, e-mail: jaxme-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: jaxme-dev-help@ws.apache.org