|
1
16 package org.apache.ws.jaxme.sqls.junit;
17
18 import org.apache.ws.jaxme.sqls.BooleanConstraint;
19 import org.apache.ws.jaxme.sqls.Column;
20 import org.apache.ws.jaxme.sqls.ColumnReference;
21 import org.apache.ws.jaxme.sqls.CombinedConstraint;
22 import org.apache.ws.jaxme.sqls.DeleteStatement;
23 import org.apache.ws.jaxme.sqls.Function;
24 import org.apache.ws.jaxme.sqls.JoinReference;
25 import org.apache.ws.jaxme.sqls.Schema;
26 import org.apache.ws.jaxme.sqls.SelectStatement;
27 import org.apache.ws.jaxme.sqls.SelectTableReference;
28 import org.apache.ws.jaxme.sqls.Table;
29 import org.apache.ws.jaxme.sqls.TableReference;
30 import org.apache.ws.jaxme.sqls.impl.VirtualColumn;
31 import org.apache.ws.jaxme.sqls.oracle.OraColumnReference;
32 import org.apache.ws.jaxme.sqls.oracle.OraSQLFactory;
33 import org.apache.ws.jaxme.sqls.oracle.OraSQLFactoryImpl;
34 import org.apache.ws.jaxme.sqls.oracle.OraSQLGenerator;
35 import org.apache.ws.jaxme.sqls.oracle.OraSelectStatement;
36
37 import junit.framework.TestCase;
38
39
42 public class JoinTest extends TestCase {
43 private OraSQLFactory sqlFactory;
44 private OraSQLGenerator sqlGenerator;
45 private Schema schema;
46 private Table dbAkte, dbBeteiligte, dbAktenzeichen;
47 private Table kettenElement, vertreterKette;
48
49
51 public JoinTest(String  pName) {
52 super(pName);
53 }
54
55 public void setUp() {
56 sqlFactory = new OraSQLFactoryImpl();
57 sqlGenerator = (OraSQLGenerator) sqlFactory.newSQLGenerator();
58 sqlGenerator.setOracle8Compatibility(true);
59 schema = sqlFactory.getDefaultSchema();
60 dbAkte = schema.newTable("DBAkte");
61 dbAkte.newColumn("aId", Column.Type.BIGINT);
62 dbBeteiligte = schema.newTable("DBBeteiligte");
63 dbBeteiligte.newColumn("aAktenId", Column.Type.BIGINT);
64 dbBeteiligte.newColumn("aFilter", Column.Type.BIGINT);
65 dbBeteiligte.newColumn("aName", Column.Type.VARCHAR);
66 dbBeteiligte.newColumn("aVorname", Column.Type.VARCHAR);
67 dbBeteiligte.newColumn("aId", Column.Type.BIGINT);
68 dbAktenzeichen = schema.newTable("DBAktenzeichen");
69 dbAktenzeichen.newColumn("aAktenId", Column.Type.BIGINT);
70 dbAktenzeichen.newColumn("aFilter", Column.Type.VARCHAR);
71 dbAktenzeichen.newColumn("aId", Column.Type.BIGINT);
72
73 kettenElement = schema.newTable("KettenElement");
74 kettenElement.newColumn("aId", Column.Type.BIGINT);
75 kettenElement.newColumn("aVertreterId", Column.Type.BIGINT);
76 kettenElement.newColumn("organisationsId", Column.Type.BIGINT);
77
78 vertreterKette = schema.newTable("VertreterKette");
79 vertreterKette.newColumn("wurzelElement", Column.Type.BIGINT);
80 }
81
82
87 private void addAktenId(CombinedConstraint pWhere,
88 TableReference pAkteReference, TableReference pBeteiligteReference) {
89 BooleanConstraint bc = pWhere.createEQ();
90 bc.addPart(pBeteiligteReference.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
91 bc.addPart(pAkteReference.newColumnReference(dbAkte.getColumn("aId")));
92 }
93
94 private void addEQ(TableReference pTableReference, CombinedConstraint pWhere,
95 Column pColumn, String pValue) {
96 BooleanConstraint bc = pWhere.createEQ();
97 bc.addPart(pTableReference.newColumnReference(pColumn));
98 bc.addPart(pValue);
99 }
100
101
106 private ColumnReference getCountStatement(String pColumnName, String pFilter,
107 TableReference pAkteReference, String pTableAlias) {
108 SelectStatement st = sqlFactory.newSelectStatement();
110 st.setTable(dbBeteiligte);
111 SelectTableReference tRef = st.getSelectTableReference();
112 tRef.setAlias(pTableAlias);
113 addEQ(tRef, st.getWhere(), dbBeteiligte.getColumn("aFilter"), pFilter);
114 addAktenId(st.getWhere(), pAkteReference, tRef);
115 VirtualColumn vc = new VirtualColumn(pColumnName, Column.Type.INTEGER);
116 vc.setValue("COUNT(*)");
117 st.addResultColumn(vc);
118
119 VirtualColumn result = new VirtualColumn(pColumnName, Column.Type.INTEGER);
120 result.setValue(st);
121 return result;
122 }
123
124
125
140 private JoinReference getFirstRowStatement2(String pFilter, TableReference pAkteReference,
141 SelectTableReference pJoinReference,
142 String pTableAlias) {
143 Column aName = dbBeteiligte.getColumn("aName");
144 Column aVorname = dbBeteiligte.getColumn("aVorname");
145 Column aAktenId = dbBeteiligte.getColumn("aAktenId");
146 Column aFilter = dbBeteiligte.getColumn("aFilter");
147
148 SelectStatement st = sqlFactory.newSelectStatement();
149 st.setTable(dbBeteiligte);
150 SelectTableReference ref = st.getSelectTableReference();
151 VirtualColumn num = new VirtualColumn("NUM", Column.Type.INTEGER);
152 num.setValue("COUNT(*) OVER (PARTITION BY " + aAktenId.getName() + ")");
153 st.addResultColumn(num);
154 VirtualColumn minAName = new VirtualColumn("MINANAME", Column.Type.VARCHAR);
155 minAName.setValue("MIN(UPPER(" + aName.getName() + ")) OVER (PARTITION BY " +
156 aAktenId.getName() + ")");
157 st.addResultColumn(minAName);
158 VirtualColumn minAVorname = new VirtualColumn("MINAVORNAME", Column.Type.VARCHAR);
159 minAVorname.setValue("MIN(UPPER(NVL(" + aVorname.getName() +
160 ", ' '))) OVER (PARTITION BY " +
161 aAktenId.getName() + ", UPPER(" + aName.getName() +
162 "))");
163 st.addResultColumn(minAVorname);
164 st.addResultColumn(ref.newColumnReference(aName));
165 st.addResultColumn(ref.newColumnReference(aVorname));
166 st.addResultColumn(ref.newColumnReference(aAktenId));
167 BooleanConstraint bc = st.getWhere().createEQ();
168 bc.addPart(st.getTableReference().newColumnReference(aFilter));
169 bc.addPart(pFilter);
170 Table t = st.createView((Table.Name) null);
171
172 SelectStatement st2 = sqlFactory.newSelectStatement();
173 st2.setTable(t);
174 SelectTableReference ref2 = st2.getSelectTableReference();
175 st2.addResultColumn(ref2.newColumnReference(t.getColumn("NUM")));
176 Column aName2 = t.getColumn(aName.getName());
177 st2.addResultColumn(ref2.newColumnReference(aName2));
178 Column aVorname2 = t.getColumn(aVorname.getName());
179 st2.addResultColumn(ref2.newColumnReference(aVorname2));
180 st2.addResultColumn(ref2.newColumnReference(t.getColumn(aAktenId.getName())));
181
182 bc = st2.getWhere().createEQ();
183 bc.addPart(ref2.newColumnReference(t.getColumn("MINAVORNAME")));
184 Function f = st2.createFunction("MIN");
185 Function f2 = st.createFunction("UPPER");
186 Function f3 = st.createFunction("NVL");
187 f3.addPart(ref2.newColumnReference(aVorname2));
188 f3.addPart(" ");
189 f2.addPart(f3);
190 bc.addPart(f);
191
192 bc = st2.getWhere().createEQ();
193 bc.addPart(ref2.newColumnReference(t.getColumn("MINANAME")));
194 f = st2.createFunction("MIN");
195 f2 = st.createFunction("UPPER");
196 f2.addPart(ref2.newColumnReference(aName2));
197 f.addPart(f2);
198 bc.addPart(f);
199 Table t2 = st2.createView(pTableAlias);
200
201 JoinReference result = pJoinReference.leftOuterJoin(t2);
202 bc = result.getOn().createEQ();
203 bc.addPart(pAkteReference.newColumnReference(dbAkte.getColumn("aId")));
204 bc.addPart(result.newColumnReference(t2.getColumn(aAktenId.getName())));
205 return result;
206 }
207
208
217 private JoinReference getFirstRowStatement(String pFilter, TableReference pAkteReference,
218 SelectTableReference pJoinReference,
219 String pTableAlias) {
220 JoinReference result = pJoinReference.leftOuterJoin(dbBeteiligte);
221 result.setAlias(pTableAlias);
222 addAktenId(result.getOn(), pAkteReference, result);
223 addEQ(result, result.getOn(), dbBeteiligte.getColumn("aFilter"), pFilter);
224 BooleanConstraint bc = result.getOn().createEQ();
225
226 Function f = pAkteReference.getStatement().createFunction("UPPER");
227 f.addPart(result.newColumnReference(dbBeteiligte.getColumn("aName")));
228 bc.addPart(f);
229
230 SelectStatement minStatement = sqlFactory.newSelectStatement();
231 minStatement.setTable(dbBeteiligte);
232 SelectTableReference minTableRef = minStatement.getSelectTableReference();
233 minTableRef.setAlias(pTableAlias + "min");
234 BooleanConstraint bc2 = minStatement.getWhere().createEQ();
235 bc2.addPart(result.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
236 bc2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aAktenId")));
237
238 bc2 = minStatement.getWhere().createEQ();
239 bc2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aFilter")));
240 bc2.addPart(pFilter);
241
242 f = pAkteReference.getStatement().createFunction("MIN");
243 Function f2 = pAkteReference.getStatement().createFunction("UPPER");
244 f.addPart(f2);
245 f2.addPart(minTableRef.newColumnReference(dbBeteiligte.getColumn("aName")));
246 VirtualColumn vc = new VirtualColumn("MIN", Column.Type.VARCHAR);
247 vc.setValue(f);
248 minStatement.addResultColumn(vc);
249
250 bc.addPart(minStatement);
251 return result;
252 }
253
254 private SelectStatement newStatement(boolean pUseView) {
255 SelectStatement st = sqlFactory.newSelectStatement();
256 st.setTable(dbAkte);
257 SelectTableReference akte = st.getSelectTableReference();
258 akte.setAlias("a");
259
260 JoinReference az = akte.join(dbAktenzeichen);
262 az.setAlias("az");
263 CombinedConstraint onClause = az.getOn();
264 BooleanConstraint bc = onClause.createEQ();
265 bc.addPart(akte.newColumnReference(dbAkte.getColumn("aId")));
266 bc.addPart(az.newColumnReference(dbAktenzeichen.getColumn("aAktenId")));
267 bc = onClause.createEQ();
268 bc.addPart(az.newColumnReference(dbAktenzeichen.getColumn("aFilter")));
269 bc.addPart("Hauptverfahren");
270
271 JoinReference kl, be;
273 if (pUseView) {
274 kl = getFirstRowStatement2("Klaeger", akte, az, "kl");
275 be = getFirstRowStatement2("Beklagter", akte, kl, "be");
276 } else {
277 kl = getFirstRowStatement("Klaeger", akte, az, "kl");
278 be = getFirstRowStatement("Beklagter", akte, kl, "be");
279 st.addResultColumn(getCountStatement("anzahlKlaeger", "Klaeger", akte, "klc"));
280 st.addResultColumn(getCountStatement("anzahlBeklagte", "Beklagter", akte, "bec"));
281 }
282 JoinReference ber = be.leftOuterJoin(dbBeteiligte);
283 ber.setAlias("ber");
284 addAktenId(ber.getOn(), akte, ber);
285 addEQ(ber, ber.getOn(), dbBeteiligte.getColumn("aFilter"), "Beklagter");
286
287 return st;
288 }
289
290
292 public void testCreate1() {
293 SelectStatement st = newStatement(false);
294 String got = sqlGenerator.getQuery(st);
295 String expect = "SELECT (SELECT COUNT(*) AS anzahlKlaeger FROM DBBeteiligte klc WHERE"
296 + " (klc.aFilter='Klaeger' AND klc.aAktenId=a.aId)) AS anzahlKlaeger,"
297 + " (SELECT COUNT(*) AS anzahlBeklagte FROM DBBeteiligte bec WHERE"
298 + " (bec.aFilter='Beklagter' AND bec.aAktenId=a.aId)) AS anzahlBeklagte"
299 + " FROM DBAkte a, DBAktenzeichen az, DBBeteiligte kl, DBBeteiligte be,"
300 + " DBBeteiligte ber WHERE (a.aId=az.aAktenId AND az.aFilter='Hauptverfahren')"
301 + " AND (kl.aAktenId(+)=a.aId AND kl.aFilter(+)='Klaeger' AND"
302 + " UPPER(kl.aName(+))=(SELECT MIN(UPPER(klmin.aName)) AS MIN FROM"
303 + " DBBeteiligte klmin WHERE (kl.aAktenId(+)=klmin.aAktenId AND"
304 + " klmin.aFilter='Klaeger'))) AND (be.aAktenId(+)=a.aId AND"
305 + " be.aFilter(+)='Beklagter' AND UPPER(be.aName(+))=(SELECT"
306 + " MIN(UPPER(bemin.aName)) AS MIN FROM DBBeteiligte bemin WHERE"
307 + " (be.aAktenId(+)=bemin.aAktenId AND bemin.aFilter='Beklagter'))) AND"
308 + " (ber.aAktenId(+)=a.aId AND ber.aFilter(+)='Beklagter')";
309 assertEquals(expect, got);
310 }
311
312
314 public void testCreate2() {
315 SelectStatement st = newStatement(true);
316 String got = sqlGenerator.getQuery(st);
317 String expect = "SELECT * FROM DBAkte a, DBAktenzeichen az,"
318 + " (SELECT DBBeteiligte.NUM, DBBeteiligte.aName, DBBeteiligte.aVorname,"
319 + " DBBeteiligte.aAktenId FROM (SELECT COUNT(*) OVER (PARTITION BY aAktenId) AS NUM,"
320 + " MIN(UPPER(aName)) OVER (PARTITION BY aAktenId) AS MINANAME,"
321 + " MIN(UPPER(NVL(aVorname, ' '))) OVER (PARTITION BY aAktenId, UPPER(aName)) AS MINAVORNAME,"
322 + " DBBeteiligte0.aName, DBBeteiligte0.aVorname, DBBeteiligte0.aAktenId FROM"
323 + " DBBeteiligte DBBeteiligte0 WHERE DBBeteiligte0.aFilter='Klaeger') WHERE"
324 + " (DBBeteiligte.MINAVORNAME=MIN() AND"
325 + " DBBeteiligte.MINANAME=MIN(UPPER(DBBeteiligte.aName)))),"
326 + " (SELECT DBBeteiligte1.NUM, DBBeteiligte1.aName, DBBeteiligte1.aVorname,"
327 + " DBBeteiligte1.aAktenId FROM (SELECT COUNT(*) OVER (PARTITION BY aAktenId)"
328 + " AS NUM, MIN(UPPER(aName)) OVER (PARTITION BY aAktenId) AS MINANAME,"
329 + " MIN(UPPER(NVL(aVorname, ' '))) OVER (PARTITION BY aAktenId, UPPER(aName)) AS MINAVORNAME,"
330 + " DBBeteiligte2.aName, DBBeteiligte2.aVorname, DBBeteiligte2.aAktenId"
331 + " FROM DBBeteiligte DBBeteiligte2 WHERE DBBeteiligte2.aFilter='Beklagter')"
332 + " DBBeteiligte1 WHERE (DBBeteiligte1.MINAVORNAME=MIN() AND"
333 + " DBBeteiligte1.MINANAME=MIN(UPPER(DBBeteiligte1.aName)))),"
334 + " DBBeteiligte ber WHERE (a.aId=az.aAktenId AND az.aFilter='Hauptverfahren')"
335 + " AND a.aId=kl.aAktenId(+) AND a.aId=be.aAktenId(+) AND"
336 + " (ber.aAktenId(+)=a.aId AND ber.aFilter(+)='Beklagter')";
337 assertEquals(expect, got);
338 }
339
340 private void addEQ(TableReference pRef1, TableReference pRef2,
341 CombinedConstraint pConstraint,
342 Column pCol1, Column pCol2) {
343 BooleanConstraint bc = pConstraint.createEQ();
344 bc.addPart(pRef1.newColumnReference(pCol1));
345 bc.addPart(pRef2.newColumnReference(pCol2));
346 }
347
348 private void addEQ(TableReference pRef1, CombinedConstraint pConstraint,
349 Column pCol1) {
350 BooleanConstraint bc = pConstraint.createEQ();
351 bc.addPart(pRef1.newColumnReference(pCol1));
352 bc.addPlaceholder();
353 }
354
355
363 private SelectStatement getSelectAidByOrganisationsId() {
364 SelectStatement stmt = sqlFactory.newSelectStatement();
365 stmt.setTable(kettenElement);
366 SelectTableReference kettenElementRef = stmt.getSelectTableReference();
367 JoinReference vertreterKetteRef = kettenElementRef.join(vertreterKette);
368 addEQ(kettenElementRef, vertreterKetteRef, vertreterKetteRef.getOn(),
369 kettenElement.getColumn("aId"),
370 vertreterKette.getColumn("wurzelElement"));
371 addEQ(kettenElementRef, stmt.getWhere(), kettenElement.getColumn("organisationsId"));
372 stmt.addResultColumn(kettenElementRef.newColumnReference(kettenElement.getColumn("aId")));
373 return stmt;
374 }
375
376
388 private SelectStatement getSelectAllChildsByOrganisationsId() {
389 OraSelectStatement stmt = (OraSelectStatement) sqlFactory.newSelectStatement();
390 stmt.setTable(kettenElement);
391 TableReference tRef = stmt.getTableReference();
392
393 BooleanConstraint in = stmt.getStartWith().createIN();
394 in.addPart(tRef.newColumnReference(kettenElement.getColumn("aId")));
395 in.addPart(getSelectAidByOrganisationsId());
396
397 BooleanConstraint bc = stmt.getConnectBy().createEQ();
398 OraColumnReference ref1 = (OraColumnReference) tRef.newColumnReference(kettenElement.getColumn("aId"));
399 ref1.setPrior(true);
400 bc.addPart(ref1);
401 bc.addPart(tRef.newColumnReference(kettenElement.getColumn("aVertreterId")));
402
403 return stmt;
404 }
405
406
419 private DeleteStatement getDeleteAllChildsByOrganisationsId() {
420 DeleteStatement dstmt = sqlFactory.newDeleteStatement();
421 dstmt.setTable(kettenElement);
422 TableReference tRef = dstmt.getTableReference();
423 CombinedConstraint whereClause = dstmt.getWhere();
424 BooleanConstraint bc = whereClause.createIN();
425 bc.addPart(tRef.newColumnReference(kettenElement.getColumn("aId")));
426 bc.addPart(getSelectAllChildsByOrganisationsId());
427 return dstmt;
428 }
429
430
435 public void testDelete1() {
436 DeleteStatement ds = getDeleteAllChildsByOrganisationsId();
437 String expect = "DELETE FROM KettenElement WHERE KettenElement.aId IN" +
438 " ((SELECT * FROM KettenElement KettenElement0 START WITH KettenElement0.aId IN ((SELECT KettenElement1.aId FROM KettenElement KettenElement1, VertreterKette WHERE KettenElement1.aId=wurzelElement AND KettenElement1.organisationsId=?)) CONNECT BY PRIOR KettenElement0.aId=KettenElement0.aVertreterId))";
439 String got = sqlGenerator.getQuery(ds);
440 assertEquals(expect, got);
441 }
442 }
443 |
Popular Tags |
Java API By Example, From Geeks To Geeks.