001: /*
002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com>
003: * Distributed under the terms of either:
004: * - the common development and distribution license (CDDL), v1.0; or
005: * - the GNU Lesser General Public License, v2.1 or later
006: * $Id: TestDeleteDerby.java 3695 2007-03-16 09:26:50Z gbevin $
007: */
008: package com.uwyn.rife.database.queries;
009:
010: import com.uwyn.rife.database.BeanImpl;
011: import com.uwyn.rife.database.BeanImplConstrained;
012: import com.uwyn.rife.database.DbPreparedStatement;
013: import com.uwyn.rife.database.DbPreparedStatementHandler;
014: import com.uwyn.rife.database.exceptions.TableNameRequiredException;
015: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
016: import java.math.BigDecimal;
017: import java.sql.Time;
018: import java.sql.Timestamp;
019: import java.util.Arrays;
020: import java.util.Calendar;
021:
022: public class TestDeleteDerby extends TestDelete {
023: public TestDeleteDerby(String name) {
024: super (name);
025: }
026:
027: public void testInstantiationDerby() {
028: Delete query = new Delete(mDerby);
029: assertNotNull(query);
030: try {
031: query.getSql();
032: fail();
033: } catch (TableNameRequiredException e) {
034: assertEquals(e.getQueryName(), "Delete");
035: }
036: }
037:
038: public void testIncompleteQueryDerby() {
039: Delete query = new Delete(mDerby);
040: try {
041: query.getSql();
042: fail();
043: } catch (TableNameRequiredException e) {
044: assertEquals(e.getQueryName(), "Delete");
045: }
046: query.where("this = that");
047: try {
048: query.getSql();
049: fail();
050: } catch (TableNameRequiredException e) {
051: assertEquals(e.getQueryName(), "Delete");
052: }
053: query.from("tablename");
054: assertNotNull(query.getSql());
055: }
056:
057: public void testClearDerby() {
058: Delete query = new Delete(mDerby);
059: query.from("tablename").where("this = that");
060: assertNotNull(query.getSql());
061: query.clear();
062: try {
063: query.getSql();
064: fail();
065: } catch (TableNameRequiredException e) {
066: assertEquals(e.getQueryName(), "Delete");
067: }
068: }
069:
070: public void testHintDerby() {
071: Delete query = new Delete(mDerby).hint("NO_INDEX").from(
072: "tablename");
073: try {
074: query.getSql();
075: fail();
076: } catch (UnsupportedSqlFeatureException e) {
077: assertTrue(true);
078: }
079: }
080:
081: public void testFromDerby() {
082: Delete query = new Delete(mDerby);
083: query.from("tablename");
084: assertEquals(query.getSql(), "DELETE FROM tablename");
085: assertTrue(execute(query));
086: }
087:
088: public void testWhereDerby() {
089: Delete query = new Delete(mDerby);
090: query.from("tablename").where("propertyByte = 89");
091: assertEquals(query.getSql(),
092: "DELETE FROM tablename WHERE propertyByte = 89");
093: assertTrue(execute(query));
094: }
095:
096: public void testWhereTypedDerby() {
097: Delete query = new Delete(mDerby);
098: query.from("tablename");
099:
100: Calendar cal = Calendar.getInstance();
101: cal.set(2003, 2, 3, 10, 1, 28);
102: cal.set(Calendar.MILLISECOND, 154);
103:
104: query.where("propertyBigDecimal", ">=",
105: new BigDecimal("53443433.9784567")).whereAnd(
106: "propertyBoolean", "=", false).whereOr("propertyByte",
107: "=", (byte) 54).whereAnd("propertyCalendar", "<=", cal)
108: .whereOr("propertyChar", "=", 'f').whereAnd(
109: "propertyDate", "=", cal.getTime()).whereAnd(
110: "propertyDouble", "!=", 73453.71d).whereOr(
111: "propertyFloat", ">=", 1987.14f).whereAnd(
112: "propertyInt", "=", 973).whereAnd(
113: "propertyLong", "<", 347678L).whereAnd(
114: "propertyShort", "=", (short) 78).whereOr(
115: "propertySqlDate", "=",
116: new java.sql.Date(cal.getTime().getTime()))
117: .whereAnd("propertyString", "LIKE", "someotherstring%")
118: .whereAnd("propertyStringbuffer", "=",
119: new StringBuffer("someotherstringbuff"))
120: .whereOr("propertyTime", "=",
121: new Time(cal.getTime().getTime())).whereAnd(
122: "propertyTimestamp", "<=",
123: new Timestamp(cal.getTime().getTime()));
124:
125: assertEquals(
126: query.getSql(),
127: "DELETE FROM tablename WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = 0 OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = '2003-03-03' AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
128: assertFalse(execute(query));
129: }
130:
131: public void testWhereTypedMixedDerby() {
132: Delete query = new Delete(mDerby);
133: query.from("tablename");
134:
135: final Calendar cal = Calendar.getInstance();
136: cal.set(2003, 2, 3, 10, 1, 28);
137: cal.set(Calendar.MILLISECOND, 154);
138:
139: query.where("propertyBigDecimal", ">=",
140: new BigDecimal("53443433.9784567")).whereAnd(
141: "propertyBoolean", "=", false).whereOr(
142: "propertyByte = 54").whereAnd("propertyCalendar", "<=",
143: cal).whereOr("propertyChar", "=", 'f').whereAnd(
144: "propertyDate", "=", cal.getTime()).whereAnd(
145: "propertyDouble", "!=", 73453.71d).whereOr(
146: "propertyFloat >= 1987.14").whereAnd("propertyInt",
147: "=", 973).whereAnd("propertyLong", "<", 347678L)
148: .whereAnd("propertyShort", "=", (short) 78)
149: .whereParameterOr("propertySqlDate", "=").whereAnd(
150: "propertyString", "LIKE", "someotherstring%")
151: .whereAnd("propertyStringbuffer", "=",
152: new StringBuffer("someotherstringbuff"))
153: .whereOr("propertyTime", "=",
154: new Time(cal.getTime().getTime())).whereAnd(
155: "propertyTimestamp", "<=",
156: new Timestamp(cal.getTime().getTime()));
157:
158: assertEquals(
159: query.getSql(),
160: "DELETE FROM tablename WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = 0 OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = ? AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
161:
162: assertFalse(execute(query, new DbPreparedStatementHandler() {
163: public void setParameters(DbPreparedStatement statement) {
164: statement.setDate("propertySqlDate", new java.sql.Date(
165: cal.getTime().getTime()));
166: }
167: }));
168: }
169:
170: public void testWhereParametersDerby() {
171: Delete query = new Delete(mDerby);
172: query.from("tablename");
173:
174: assertNull(query.getParameters());
175:
176: query.whereParameter("propertyInt", "=").whereParameterAnd(
177: "propertyLong", "<").whereParameterOr("propertyChar",
178: "=");
179:
180: assertEquals(query.getParameters().getOrderedNames().size(), 3);
181: assertEquals(query.getParameters().getOrderedNames().get(0),
182: "propertyInt");
183: assertEquals(query.getParameters().getOrderedNames().get(1),
184: "propertyLong");
185: assertEquals(query.getParameters().getOrderedNames().get(2),
186: "propertyChar");
187: assertTrue(Arrays.equals(query.getParameters()
188: .getOrderedNamesArray(), new String[] { "propertyInt",
189: "propertyLong", "propertyChar" }));
190:
191: assertEquals(
192: query.getSql(),
193: "DELETE FROM tablename WHERE propertyInt = ? AND propertyLong < ? OR propertyChar = ?");
194: assertTrue(execute(query, new DbPreparedStatementHandler() {
195: public void setParameters(DbPreparedStatement statement) {
196: statement.setInt(1, 545).setLong(2, 50000).setString(3,
197: "v");
198: }
199: }));
200:
201: query.where("propertyInt = 545");
202:
203: assertNull(query.getParameters());
204: assertEquals(query.getSql(),
205: "DELETE FROM tablename WHERE propertyInt = 545");
206: }
207:
208: public void testWhereParametersMixedDerby() {
209: Delete query = new Delete(mDerby);
210: query.from("tablename").where("propertyInt = 545")
211: .whereParameterAnd("propertyLong", "<")
212: .whereParameterOr("propertyChar", "=");
213:
214: assertEquals(query.getParameters().getOrderedNames().get(0),
215: "propertyLong");
216: assertEquals(query.getParameters().getOrderedNames().get(1),
217: "propertyChar");
218: assertTrue(Arrays.equals(query.getParameters()
219: .getOrderedNamesArray(), new String[] { "propertyLong",
220: "propertyChar" }));
221:
222: assertEquals(
223: query.getSql(),
224: "DELETE FROM tablename WHERE propertyInt = 545 AND propertyLong < ? OR propertyChar = ?");
225: assertTrue(execute(query, new DbPreparedStatementHandler() {
226: public void setParameters(DbPreparedStatement statement) {
227: statement.setLong(1, 50000).setString(2, "v");
228: }
229: }));
230:
231: query.where("propertyInt = 545");
232:
233: assertNull(query.getParameters());
234: assertEquals(query.getSql(),
235: "DELETE FROM tablename WHERE propertyInt = 545");
236: }
237:
238: public void testWhereConstructionDerby() {
239: Delete query = new Delete(mDerby);
240: query.from("tablename").where("propertyInt = 545").whereAnd(
241: "propertyLong < 50000").whereOr("propertyChar = 'v'");
242: assertEquals(
243: query.getSql(),
244: "DELETE FROM tablename WHERE propertyInt = 545 AND propertyLong < 50000 OR propertyChar = 'v'");
245: assertTrue(execute(query));
246: }
247:
248: public void testWhereConstructionGroupDerby() {
249: Delete query = new Delete(mDerby);
250: query.from("tablename").where("propertyInt = 545").whereAnd(
251: "propertyLong < 50000").startWhereOr().whereParameter(
252: "propertyString", "=").whereAnd("propertyByte", "<=",
253: (byte) 0).startWhereAnd().where("propertyBoolean",
254: "!=", true).whereParameterOr("propertyStringbuffer",
255: "LIKE").end().end().whereOr("propertyChar = 'v'");
256:
257: assertEquals(query.getParameters().getOrderedNames().get(0),
258: "propertyString");
259: assertEquals(query.getParameters().getOrderedNames().get(1),
260: "propertyStringbuffer");
261: assertTrue(Arrays.equals(query.getParameters()
262: .getOrderedNamesArray(), new String[] {
263: "propertyString", "propertyStringbuffer" }));
264:
265: assertEquals(
266: query.getSql(),
267: "DELETE FROM tablename WHERE propertyInt = 545 AND propertyLong < 50000 OR (propertyString = ? AND propertyByte <= 0 AND (propertyBoolean != 1 OR propertyStringbuffer LIKE ?)) OR propertyChar = 'v'");
268:
269: assertTrue(execute(query, new DbPreparedStatementHandler() {
270: public void setParameters(DbPreparedStatement statement) {
271: statement
272: .setString("propertyString", "someotherstring")
273: .setString("propertyStringbuffer", "stringbuff");
274: }
275: }));
276: }
277:
278: public void testWhereBeanDerby() {
279: Delete query = new Delete(mDerby);
280: query.from("tablename").where(BeanImpl.getPopulatedBean());
281: assertEquals(
282: query.getSql(),
283: "DELETE FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
284: assertTrue(execute(query));
285: }
286:
287: public void testWhereBeanConstrainedDerby() {
288: Delete query = new Delete(mDerby);
289: query.from("tablename").where(
290: BeanImplConstrained.getPopulatedBean());
291: assertEquals(
292: query.getSql(),
293: "DELETE FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
294: assertTrue(execute(query));
295: }
296:
297: public void testWhereBeanNullValuesDerby() {
298: Delete query = new Delete(mDerby);
299: query.from("tablename").where(BeanImpl.getNullBean());
300: assertEquals(
301: query.getSql(),
302: "DELETE FROM tablename WHERE propertyBoolean = 0 AND propertyBooleanObject = 0 AND propertyByte = 0 AND propertyByteObject = 0 AND propertyDouble = 0.0 AND propertyDoubleObject = 0.0 AND propertyFloat = 0.0 AND propertyFloatObject = 0.0 AND propertyInt = 0 AND propertyIntegerObject = 0 AND propertyLong = 0 AND propertyLongObject = 0 AND propertyShort = 0 AND propertyShortObject = 0");
303: assertTrue(execute(query));
304: }
305:
306: public void testWhereBeanIncludedDerby() {
307: Delete query = new Delete(mDerby);
308: query.from("tablename").whereIncluded(
309: BeanImpl.getPopulatedBean(),
310: new String[] { "propertyByte", "propertyDouble",
311: "propertyShort", "propertyStringbuffer",
312: "propertyTime" });
313: assertEquals(
314: query.getSql(),
315: "DELETE FROM tablename WHERE propertyByte = 89 AND propertyDouble = 53348.34 AND propertyShort = 43 AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14'");
316: assertTrue(execute(query));
317: }
318:
319: public void testWhereBeanExcludedDerby() {
320: Delete query = new Delete(mDerby);
321: query.from("tablename").whereExcluded(
322: BeanImpl.getPopulatedBean(),
323: new String[] { "propertyByte", "propertyDouble",
324: "propertyShort", "propertyStringbuffer",
325: "propertyTime" });
326: assertEquals(
327: query.getSql(),
328: "DELETE FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
329: assertTrue(execute(query));
330: }
331:
332: public void testWhereBeanFilteredDerby() {
333: Delete query = new Delete(mDerby);
334: query.from("tablename").whereFiltered(
335: BeanImpl.getPopulatedBean(),
336: new String[] { "propertyByte", "propertyDouble",
337: "propertyShort", "propertyStringbuffer",
338: "propertyTime" },
339: new String[] { "propertyByte", "propertyShort",
340: "propertyTime" });
341: assertEquals(
342: query.getSql(),
343: "DELETE FROM tablename WHERE propertyDouble = 53348.34 AND propertyStringbuffer = 'someotherstringbuff'");
344: assertTrue(execute(query));
345: }
346:
347: public void testWhereParametersBeanDerby() {
348: Delete query = new Delete(mDerby);
349: query.from("tablename").whereParameters(BeanImpl.class);
350: assertEquals(
351: query.getSql(),
352: "DELETE FROM tablename WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLong = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
353:
354: assertEquals(query.getParameters().getOrderedNames().size(), 25);
355: assertEquals(query.getParameters().getOrderedNames().get(0),
356: "propertyBigDecimal");
357: assertEquals(query.getParameters().getOrderedNames().get(1),
358: "propertyBoolean");
359: assertEquals(query.getParameters().getOrderedNames().get(2),
360: "propertyBooleanObject");
361: assertEquals(query.getParameters().getOrderedNames().get(3),
362: "propertyByte");
363: assertEquals(query.getParameters().getOrderedNames().get(4),
364: "propertyByteObject");
365: assertEquals(query.getParameters().getOrderedNames().get(5),
366: "propertyCalendar");
367: assertEquals(query.getParameters().getOrderedNames().get(6),
368: "propertyChar");
369: assertEquals(query.getParameters().getOrderedNames().get(7),
370: "propertyCharacterObject");
371: assertEquals(query.getParameters().getOrderedNames().get(8),
372: "propertyDate");
373: assertEquals(query.getParameters().getOrderedNames().get(9),
374: "propertyDouble");
375: assertEquals(query.getParameters().getOrderedNames().get(10),
376: "propertyDoubleObject");
377: assertEquals(query.getParameters().getOrderedNames().get(11),
378: "propertyEnum");
379: assertEquals(query.getParameters().getOrderedNames().get(12),
380: "propertyFloat");
381: assertEquals(query.getParameters().getOrderedNames().get(13),
382: "propertyFloatObject");
383: assertEquals(query.getParameters().getOrderedNames().get(14),
384: "propertyInt");
385: assertEquals(query.getParameters().getOrderedNames().get(15),
386: "propertyIntegerObject");
387: assertEquals(query.getParameters().getOrderedNames().get(16),
388: "propertyLong");
389: assertEquals(query.getParameters().getOrderedNames().get(17),
390: "propertyLongObject");
391: assertEquals(query.getParameters().getOrderedNames().get(18),
392: "propertyShort");
393: assertEquals(query.getParameters().getOrderedNames().get(19),
394: "propertyShortObject");
395: assertEquals(query.getParameters().getOrderedNames().get(20),
396: "propertySqlDate");
397: assertEquals(query.getParameters().getOrderedNames().get(21),
398: "propertyString");
399: assertEquals(query.getParameters().getOrderedNames().get(22),
400: "propertyStringbuffer");
401: assertEquals(query.getParameters().getOrderedNames().get(23),
402: "propertyTime");
403: assertEquals(query.getParameters().getOrderedNames().get(24),
404: "propertyTimestamp");
405: assertTrue(Arrays.equals(query.getParameters()
406: .getOrderedNamesArray(), new String[] {
407: "propertyBigDecimal", "propertyBoolean",
408: "propertyBooleanObject", "propertyByte",
409: "propertyByteObject", "propertyCalendar",
410: "propertyChar", "propertyCharacterObject",
411: "propertyDate", "propertyDouble",
412: "propertyDoubleObject", "propertyEnum",
413: "propertyFloat", "propertyFloatObject", "propertyInt",
414: "propertyIntegerObject", "propertyLong",
415: "propertyLongObject", "propertyShort",
416: "propertyShortObject", "propertySqlDate",
417: "propertyString", "propertyStringbuffer",
418: "propertyTime", "propertyTimestamp" }));
419:
420: assertTrue(execute(query, new DbPreparedStatementHandler() {
421: public void setParameters(DbPreparedStatement statement) {
422: Calendar cal = Calendar.getInstance();
423: cal.set(2002, 5, 18, 15, 26, 14);
424: cal.set(Calendar.MILLISECOND, 764);
425: statement.setBigDecimal(1,
426: new BigDecimal("219038743.392874")).setBoolean(
427: 2, true).setBoolean(3, false).setByte(4,
428: (byte) 89).setByte(5, (byte) 34)
429: .setTimestamp(
430: 6,
431: new java.sql.Timestamp(cal.getTime()
432: .getTime())).setString(7, "v")
433: .setString(8, "r").setTimestamp(
434: 9,
435: new java.sql.Timestamp(cal.getTime()
436: .getTime())).setDouble(10,
437: 53348.34d).setDouble(11, 143298.692d)
438: .setString(12, "VALUE_THREE").setDouble(13,
439: 98634.2d).setDouble(14, 8734.7d)
440: .setInt(15, 545).setInt(16, 968).setLong(17,
441: 34563L).setLong(18, 66875L).setShort(
442: 19, (short) 43)
443: .setShort(20, (short) 68).setDate(
444: 21,
445: new java.sql.Date(cal.getTime()
446: .getTime())).setString(22,
447: "someotherstring").setString(23,
448: "someotherstringbuff").setTime(24,
449: new Time(15, 26, 14)).setTimestamp(25,
450: new Timestamp(cal.getTime().getTime()));
451: }
452: }));
453: }
454:
455: public void testWhereParametersBeanConstrainedDerby() {
456: Delete query = new Delete(mDerby);
457: query.from("tablename").whereParameters(
458: BeanImplConstrained.class);
459: assertEquals(
460: query.getSql(),
461: "DELETE FROM tablename WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
462:
463: assertEquals(query.getParameters().getOrderedNames().size(), 22);
464: assertEquals(query.getParameters().getOrderedNames().get(0),
465: "propertyBigDecimal");
466: assertEquals(query.getParameters().getOrderedNames().get(1),
467: "propertyBoolean");
468: assertEquals(query.getParameters().getOrderedNames().get(2),
469: "propertyBooleanObject");
470: assertEquals(query.getParameters().getOrderedNames().get(3),
471: "propertyByte");
472: assertEquals(query.getParameters().getOrderedNames().get(4),
473: "propertyByteObject");
474: assertEquals(query.getParameters().getOrderedNames().get(5),
475: "propertyCalendar");
476: assertEquals(query.getParameters().getOrderedNames().get(6),
477: "propertyChar");
478: assertEquals(query.getParameters().getOrderedNames().get(7),
479: "propertyCharacterObject");
480: assertEquals(query.getParameters().getOrderedNames().get(8),
481: "propertyDate");
482: assertEquals(query.getParameters().getOrderedNames().get(9),
483: "propertyDouble");
484: assertEquals(query.getParameters().getOrderedNames().get(10),
485: "propertyDoubleObject");
486: assertEquals(query.getParameters().getOrderedNames().get(11),
487: "propertyFloat");
488: assertEquals(query.getParameters().getOrderedNames().get(12),
489: "propertyFloatObject");
490: assertEquals(query.getParameters().getOrderedNames().get(13),
491: "propertyInt");
492: assertEquals(query.getParameters().getOrderedNames().get(14),
493: "propertyIntegerObject");
494: assertEquals(query.getParameters().getOrderedNames().get(15),
495: "propertyLongObject");
496: assertEquals(query.getParameters().getOrderedNames().get(16),
497: "propertyShort");
498: assertEquals(query.getParameters().getOrderedNames().get(17),
499: "propertySqlDate");
500: assertEquals(query.getParameters().getOrderedNames().get(18),
501: "propertyString");
502: assertEquals(query.getParameters().getOrderedNames().get(19),
503: "propertyStringbuffer");
504: assertEquals(query.getParameters().getOrderedNames().get(20),
505: "propertyTime");
506: assertEquals(query.getParameters().getOrderedNames().get(21),
507: "propertyTimestamp");
508: assertTrue(Arrays.equals(query.getParameters()
509: .getOrderedNamesArray(), new String[] {
510: "propertyBigDecimal", "propertyBoolean",
511: "propertyBooleanObject", "propertyByte",
512: "propertyByteObject", "propertyCalendar",
513: "propertyChar", "propertyCharacterObject",
514: "propertyDate", "propertyDouble",
515: "propertyDoubleObject", "propertyFloat",
516: "propertyFloatObject", "propertyInt",
517: "propertyIntegerObject", "propertyLongObject",
518: "propertyShort", "propertySqlDate", "propertyString",
519: "propertyStringbuffer", "propertyTime",
520: "propertyTimestamp" }));
521:
522: // don't check if actual rows were returned, since Derby doesn't
523: // match on the float
524: execute(query, new DbPreparedStatementHandler() {
525: public void setParameters(DbPreparedStatement statement) {
526: Calendar cal = Calendar.getInstance();
527: cal.set(2002, 5, 18, 15, 26, 14);
528: cal.set(Calendar.MILLISECOND, 764);
529: statement.setBigDecimal(1,
530: new BigDecimal("219038743.392874")).setBoolean(
531: 2, true).setBoolean(3, false).setByte(4,
532: (byte) 89).setByte(5, (byte) 34)
533: .setTimestamp(
534: 6,
535: new java.sql.Timestamp(cal.getTime()
536: .getTime())).setString(7, "v")
537: .setString(8, "r").setTimestamp(
538: 9,
539: new java.sql.Timestamp(cal.getTime()
540: .getTime())).setDouble(10,
541: 53348.34d).setDouble(11, 143298.692d)
542: .setFloat(12, 98634.2f).setFloat(13, 8734.7f)
543: .setInt(14, 545).setInt(15, 968).setLong(16,
544: 66875L).setShort(17, (short) 43)
545: .setDate(
546: 18,
547: new java.sql.Date(cal.getTime()
548: .getTime())).setString(19,
549: "someotherstring").setString(20,
550: "someotherstringbuff").setTime(21,
551: new Time(cal.getTime().getTime()))
552: .setTimestamp(22,
553: new Timestamp(cal.getTime().getTime()));
554: }
555: });
556: }
557:
558: public void testWhereParametersBeanExcludedDerby() {
559: Delete query = new Delete(mDerby);
560: query.from("tablename").whereParametersExcluded(
561: BeanImpl.class,
562: new String[] { "propertyBoolean", "propertyByte",
563: "propertyChar", "propertyDouble",
564: "propertyInt", "propertyLong",
565: "propertySqlDate", "propertyStringbuffer",
566: "propertyTimestamp" });
567: assertEquals(
568: query.getSql(),
569: "DELETE FROM tablename WHERE propertyBigDecimal = ? AND propertyBooleanObject = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertyString = ? AND propertyTime = ?");
570:
571: assertEquals(query.getParameters().getOrderedNames().size(), 16);
572: assertEquals(query.getParameters().getOrderedNames().get(0),
573: "propertyBigDecimal");
574: assertEquals(query.getParameters().getOrderedNames().get(1),
575: "propertyBooleanObject");
576: assertEquals(query.getParameters().getOrderedNames().get(2),
577: "propertyByteObject");
578: assertEquals(query.getParameters().getOrderedNames().get(3),
579: "propertyCalendar");
580: assertEquals(query.getParameters().getOrderedNames().get(4),
581: "propertyCharacterObject");
582: assertEquals(query.getParameters().getOrderedNames().get(5),
583: "propertyDate");
584: assertEquals(query.getParameters().getOrderedNames().get(6),
585: "propertyDoubleObject");
586: assertEquals(query.getParameters().getOrderedNames().get(7),
587: "propertyEnum");
588: assertEquals(query.getParameters().getOrderedNames().get(8),
589: "propertyFloat");
590: assertEquals(query.getParameters().getOrderedNames().get(9),
591: "propertyFloatObject");
592: assertEquals(query.getParameters().getOrderedNames().get(10),
593: "propertyIntegerObject");
594: assertEquals(query.getParameters().getOrderedNames().get(11),
595: "propertyLongObject");
596: assertEquals(query.getParameters().getOrderedNames().get(12),
597: "propertyShort");
598: assertEquals(query.getParameters().getOrderedNames().get(13),
599: "propertyShortObject");
600: assertEquals(query.getParameters().getOrderedNames().get(14),
601: "propertyString");
602: assertEquals(query.getParameters().getOrderedNames().get(15),
603: "propertyTime");
604: assertTrue(Arrays.equals(query.getParameters()
605: .getOrderedNamesArray(), new String[] {
606: "propertyBigDecimal", "propertyBooleanObject",
607: "propertyByteObject", "propertyCalendar",
608: "propertyCharacterObject", "propertyDate",
609: "propertyDoubleObject", "propertyEnum",
610: "propertyFloat", "propertyFloatObject",
611: "propertyIntegerObject", "propertyLongObject",
612: "propertyShort", "propertyShortObject",
613: "propertyString", "propertyTime" }));
614: assertTrue(execute(query, new DbPreparedStatementHandler() {
615: public void setParameters(DbPreparedStatement statement) {
616: Calendar cal = Calendar.getInstance();
617: cal.set(2002, 5, 18, 15, 26, 14);
618: cal.set(Calendar.MILLISECOND, 764);
619: statement.setBigDecimal(1,
620: new BigDecimal("219038743.392874")).setBoolean(
621: 2, false).setByte(3, (byte) 34)
622: .setTimestamp(
623: 4,
624: new java.sql.Timestamp(cal.getTime()
625: .getTime())).setString(5, "r")
626: .setTimestamp(
627: 6,
628: new java.sql.Timestamp(cal.getTime()
629: .getTime())).setDouble(7,
630: 143298.692d)
631: .setString(8, "VALUE_THREE").setDouble(9,
632: 98634.2d).setDouble(10, 8734.7d)
633: .setInt(11, 968).setLong(12, 66875L).setShort(
634: 13, (short) 43)
635: .setShort(14, (short) 68).setString(15,
636: "someotherstring").setTime(16,
637: new Time(15, 26, 14));
638: }
639: }));
640: }
641:
642: public void testDeleteSubselectParamsDerby() {
643: Select wherequery = new Select(mDerby);
644: wherequery.from("table2").field("max(propertyShort)")
645: .whereParameter("propertyShort", "!=");
646:
647: // Manual subselect creation
648: Delete query = new Delete(mDerby);
649: query.where("propertyShort >= (" + wherequery + ")")
650: .whereSubselect(wherequery).whereParameterOr(
651: "propertyString", "=").from("tablename");
652: assertEquals(
653: query.getSql(),
654: "DELETE FROM tablename WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ?");
655: String[] parameters = query.getParameters()
656: .getOrderedNamesArray();
657: assertEquals(2, parameters.length);
658: assertEquals(parameters[0], "propertyShort");
659: assertEquals(parameters[1], "propertyString");
660: assertTrue(execute(query, new DbPreparedStatementHandler() {
661: public void setParameters(DbPreparedStatement statement) {
662: statement.setShort("propertyShort", (short) 5)
663: .setString("propertyString", "thestring");
664: }
665: }));
666:
667: // Automated subselect creation
668: query = new Delete(mDerby);
669: query.where("propertyShort", ">=", wherequery)
670: .whereParameterOr("propertyString", "=").whereOr(
671: "tablename.propertyFloat",
672: ">",
673: new Select(mDerby).from("table2").field(
674: "max(propertyLong)").whereParameter(
675: "propertyLong", "!=")).whereAnd(
676: "tablename.propertyDouble",
677: "<=",
678: new Select(mDerby).from("table2").field(
679: "max(propertyFloat)").whereParameter(
680: "propertyFloat", "!=")).from(
681: "tablename");
682: assertEquals(
683: query.getSql(),
684: "DELETE FROM tablename WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ? OR tablename.propertyFloat > (SELECT max(propertyLong) FROM table2 WHERE propertyLong != ?) AND tablename.propertyDouble <= (SELECT max(propertyFloat) FROM table2 WHERE propertyFloat != ?)");
685: parameters = query.getParameters().getOrderedNamesArray();
686: assertEquals(4, parameters.length);
687: assertEquals(parameters[0], "propertyShort");
688: assertEquals(parameters[1], "propertyString");
689: assertEquals(parameters[2], "propertyLong");
690: assertEquals(parameters[3], "propertyFloat");
691: assertTrue(execute(query, new DbPreparedStatementHandler() {
692: public void setParameters(DbPreparedStatement statement) {
693: statement.setShort("propertyShort", (short) 5)
694: .setString("propertyString", "thestring")
695: .setLong("propertyLong", 99999999).setFloat(
696: "propertyFloat", -1f);
697: }
698: }));
699: }
700:
701: public void testCloneDerby() {
702: Select wherequery = new Select(mDerby);
703: wherequery.from("table2").field("max(propertyShort)")
704: .whereParameter("propertyShort", "!=");
705:
706: Delete query = new Delete(mDerby);
707: query.from("tablename").where(
708: "propertyShort >= (" + wherequery + ")")
709: .whereSubselect(wherequery).whereParameterOr(
710: "propertyString", "=").whereOr("propertyByte",
711: "=", (byte) 54).whereAnd("propertyDouble",
712: "!=", 73453.71d).whereParameterOr(
713: "propertyInt", "=").whereParameterAnd(
714: "propertyLong", "<").whereParameterOr(
715: "propertyChar", "=");
716:
717: Delete query_clone = query.clone();
718: assertEquals(query.getSql(), query_clone.getSql());
719: assertTrue(query != query_clone);
720: execute(query, new DbPreparedStatementHandler() {
721: public void setParameters(DbPreparedStatement statement) {
722: statement.setString("propertyChar", "M").setInt(
723: "propertyInt", 34).setString("propertyString",
724: "string'value").setLong("propertyLong", 34543)
725: .setShort("propertyShort", (short) 4);
726: }
727: });
728: }
729: }
|