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: TestDeleteFirebird.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 TestDeleteFirebird extends TestDelete {
023: public TestDeleteFirebird(String name) {
024: super (name);
025: }
026:
027: public void testInstantiationFirebird() {
028: Delete query = new Delete(mFirebird);
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 testIncompleteQueryFirebird() {
039: Delete query = new Delete(mFirebird);
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 testClearFirebird() {
058: Delete query = new Delete(mFirebird);
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 testHintFirebird() {
071: Delete query = new Delete(mFirebird).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 testFromFirebird() {
082: Delete query = new Delete(mFirebird);
083: query.from("tablename");
084: assertEquals(query.getSql(), "DELETE FROM tablename");
085: assertTrue(execute(query));
086: }
087:
088: public void testWhereFirebird() {
089: Delete query = new Delete(mFirebird);
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 testWhereTypedFirebird() {
097: Delete query = new Delete(mFirebird);
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 testWhereTypedMixedFirebird() {
132: Delete query = new Delete(mFirebird);
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 testWhereParametersFirebird() {
171: Delete query = new Delete(mFirebird);
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 testWhereParametersMixedFirebird() {
209: Delete query = new Delete(mFirebird);
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 testWhereConstructionFirebird() {
239: Delete query = new Delete(mFirebird);
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 testWhereConstructionGroupFirebird() {
249: Delete query = new Delete(mFirebird);
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 testWhereBeanFirebird() {
279: Delete query = new Delete(mFirebird);
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 testWhereBeanConstrainedFirebird() {
288: Delete query = new Delete(mFirebird);
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 testWhereBeanNullValuesFirebird() {
298: Delete query = new Delete(mFirebird);
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 testWhereBeanIncludedFirebird() {
307: Delete query = new Delete(mFirebird);
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 testWhereBeanExcludedFirebird() {
320: Delete query = new Delete(mFirebird);
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 testWhereBeanFilteredFirebird() {
333: Delete query = new Delete(mFirebird);
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 testWhereParametersBeanFirebird() {
348: Delete query = new Delete(mFirebird);
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: // don't check the result since Firebird doesn't match exactly on float
421: execute(query, new DbPreparedStatementHandler() {
422: public void setParameters(DbPreparedStatement statement) {
423: Calendar cal = Calendar.getInstance();
424: cal.set(2002, 5, 18, 15, 26, 14);
425: cal.set(Calendar.MILLISECOND, 764);
426: statement.setBigDecimal(1,
427: new BigDecimal("219038743.392874")).setBoolean(
428: 2, true).setBoolean(3, false).setByte(4,
429: (byte) 89).setByte(5, (byte) 34)
430: .setTimestamp(
431: 6,
432: new java.sql.Timestamp(cal.getTime()
433: .getTime())).setString(7, "v")
434: .setString(8, "r").setTimestamp(
435: 9,
436: new java.sql.Timestamp(cal.getTime()
437: .getTime())).setDouble(10,
438: 53348.34d).setDouble(11, 143298.692d)
439: .setString(12, "VALUE_THREE").setFloat(13,
440: 98634.2f).setFloat(14, 8734.7f).setInt(
441: 15, 545).setInt(16, 968).setLong(17,
442: 34563L).setLong(18, 66875L).setShort(
443: 19, (short) 43)
444: .setShort(20, (short) 68).setDate(
445: 21,
446: new java.sql.Date(cal.getTime()
447: .getTime())).setString(22,
448: "someotherstring").setString(23,
449: "someotherstringbuff").setTime(24,
450: new Time(cal.getTime().getTime()))
451: .setTimestamp(25,
452: new Timestamp(cal.getTime().getTime()));
453: }
454: });
455: }
456:
457: public void testWhereParametersBeanConstrainedFirebird() {
458: Delete query = new Delete(mFirebird);
459: query.from("tablename").whereParameters(
460: BeanImplConstrained.class);
461: assertEquals(
462: query.getSql(),
463: "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 = ?");
464:
465: assertEquals(query.getParameters().getOrderedNames().size(), 22);
466: assertEquals(query.getParameters().getOrderedNames().get(0),
467: "propertyBigDecimal");
468: assertEquals(query.getParameters().getOrderedNames().get(1),
469: "propertyBoolean");
470: assertEquals(query.getParameters().getOrderedNames().get(2),
471: "propertyBooleanObject");
472: assertEquals(query.getParameters().getOrderedNames().get(3),
473: "propertyByte");
474: assertEquals(query.getParameters().getOrderedNames().get(4),
475: "propertyByteObject");
476: assertEquals(query.getParameters().getOrderedNames().get(5),
477: "propertyCalendar");
478: assertEquals(query.getParameters().getOrderedNames().get(6),
479: "propertyChar");
480: assertEquals(query.getParameters().getOrderedNames().get(7),
481: "propertyCharacterObject");
482: assertEquals(query.getParameters().getOrderedNames().get(8),
483: "propertyDate");
484: assertEquals(query.getParameters().getOrderedNames().get(9),
485: "propertyDouble");
486: assertEquals(query.getParameters().getOrderedNames().get(10),
487: "propertyDoubleObject");
488: assertEquals(query.getParameters().getOrderedNames().get(11),
489: "propertyFloat");
490: assertEquals(query.getParameters().getOrderedNames().get(12),
491: "propertyFloatObject");
492: assertEquals(query.getParameters().getOrderedNames().get(13),
493: "propertyInt");
494: assertEquals(query.getParameters().getOrderedNames().get(14),
495: "propertyIntegerObject");
496: assertEquals(query.getParameters().getOrderedNames().get(15),
497: "propertyLongObject");
498: assertEquals(query.getParameters().getOrderedNames().get(16),
499: "propertyShort");
500: assertEquals(query.getParameters().getOrderedNames().get(17),
501: "propertySqlDate");
502: assertEquals(query.getParameters().getOrderedNames().get(18),
503: "propertyString");
504: assertEquals(query.getParameters().getOrderedNames().get(19),
505: "propertyStringbuffer");
506: assertEquals(query.getParameters().getOrderedNames().get(20),
507: "propertyTime");
508: assertEquals(query.getParameters().getOrderedNames().get(21),
509: "propertyTimestamp");
510: assertTrue(Arrays.equals(query.getParameters()
511: .getOrderedNamesArray(), new String[] {
512: "propertyBigDecimal", "propertyBoolean",
513: "propertyBooleanObject", "propertyByte",
514: "propertyByteObject", "propertyCalendar",
515: "propertyChar", "propertyCharacterObject",
516: "propertyDate", "propertyDouble",
517: "propertyDoubleObject", "propertyFloat",
518: "propertyFloatObject", "propertyInt",
519: "propertyIntegerObject", "propertyLongObject",
520: "propertyShort", "propertySqlDate", "propertyString",
521: "propertyStringbuffer", "propertyTime",
522: "propertyTimestamp" }));
523:
524: // don't check the result since Firebird doesn't match exactly on float
525: execute(query, new DbPreparedStatementHandler() {
526: public void setParameters(DbPreparedStatement statement) {
527: Calendar cal = Calendar.getInstance();
528: cal.set(2002, 5, 18, 15, 26, 14);
529: cal.set(Calendar.MILLISECOND, 764);
530: statement.setBigDecimal(1,
531: new BigDecimal("219038743.392874")).setBoolean(
532: 2, true).setBoolean(3, false).setByte(4,
533: (byte) 89).setByte(5, (byte) 34)
534: .setTimestamp(
535: 6,
536: new java.sql.Timestamp(cal.getTime()
537: .getTime())).setString(7, "v")
538: .setString(8, "r").setTimestamp(
539: 9,
540: new java.sql.Timestamp(cal.getTime()
541: .getTime())).setDouble(10,
542: 53348.34d).setDouble(11, 143298.692d)
543: .setFloat(12, 98634.2f).setFloat(13, 8734.7f)
544: .setInt(14, 545).setInt(15, 968).setLong(16,
545: 66875L).setShort(17, (short) 43)
546: .setDate(
547: 18,
548: new java.sql.Date(cal.getTime()
549: .getTime())).setString(19,
550: "someotherstring").setString(20,
551: "someotherstringbuff").setTime(21,
552: new Time(cal.getTime().getTime()))
553: .setTimestamp(22,
554: new Timestamp(cal.getTime().getTime()));
555: }
556: });
557: }
558:
559: public void testWhereParametersBeanExcludedFirebird() {
560: Delete query = new Delete(mFirebird);
561: query.from("tablename").whereParametersExcluded(
562: BeanImpl.class,
563: new String[] { "propertyBoolean", "propertyByte",
564: "propertyChar", "propertyDouble",
565: "propertyInt", "propertyLong",
566: "propertySqlDate", "propertyStringbuffer",
567: "propertyTimestamp" });
568: assertEquals(
569: query.getSql(),
570: "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 = ?");
571:
572: assertEquals(query.getParameters().getOrderedNames().size(), 16);
573: assertEquals(query.getParameters().getOrderedNames().get(0),
574: "propertyBigDecimal");
575: assertEquals(query.getParameters().getOrderedNames().get(1),
576: "propertyBooleanObject");
577: assertEquals(query.getParameters().getOrderedNames().get(2),
578: "propertyByteObject");
579: assertEquals(query.getParameters().getOrderedNames().get(3),
580: "propertyCalendar");
581: assertEquals(query.getParameters().getOrderedNames().get(4),
582: "propertyCharacterObject");
583: assertEquals(query.getParameters().getOrderedNames().get(5),
584: "propertyDate");
585: assertEquals(query.getParameters().getOrderedNames().get(6),
586: "propertyDoubleObject");
587: assertEquals(query.getParameters().getOrderedNames().get(7),
588: "propertyEnum");
589: assertEquals(query.getParameters().getOrderedNames().get(8),
590: "propertyFloat");
591: assertEquals(query.getParameters().getOrderedNames().get(9),
592: "propertyFloatObject");
593: assertEquals(query.getParameters().getOrderedNames().get(10),
594: "propertyIntegerObject");
595: assertEquals(query.getParameters().getOrderedNames().get(11),
596: "propertyLongObject");
597: assertEquals(query.getParameters().getOrderedNames().get(12),
598: "propertyShort");
599: assertEquals(query.getParameters().getOrderedNames().get(13),
600: "propertyShortObject");
601: assertEquals(query.getParameters().getOrderedNames().get(14),
602: "propertyString");
603: assertEquals(query.getParameters().getOrderedNames().get(15),
604: "propertyTime");
605: assertTrue(Arrays.equals(query.getParameters()
606: .getOrderedNamesArray(), new String[] {
607: "propertyBigDecimal", "propertyBooleanObject",
608: "propertyByteObject", "propertyCalendar",
609: "propertyCharacterObject", "propertyDate",
610: "propertyDoubleObject", "propertyEnum",
611: "propertyFloat", "propertyFloatObject",
612: "propertyIntegerObject", "propertyLongObject",
613: "propertyShort", "propertyShortObject",
614: "propertyString", "propertyTime" }));
615:
616: // don't check the result since Firebird doesn't match exactly on float
617: execute(query, new DbPreparedStatementHandler() {
618: public void setParameters(DbPreparedStatement statement) {
619: Calendar cal = Calendar.getInstance();
620: cal.set(2002, 5, 18, 15, 26, 14);
621: cal.set(Calendar.MILLISECOND, 764);
622: statement.setBigDecimal(1,
623: new BigDecimal("219038743.392874")).setBoolean(
624: 2, false).setByte(3, (byte) 34)
625: .setTimestamp(
626: 4,
627: new java.sql.Timestamp(cal.getTime()
628: .getTime())).setString(5, "r")
629: .setTimestamp(
630: 6,
631: new java.sql.Timestamp(cal.getTime()
632: .getTime())).setDouble(7,
633: 143298.692d)
634: .setString(8, "VALUE_THREE").setFloat(9,
635: 98634.2f).setFloat(10, 8734.7f).setInt(
636: 11, 968).setLong(12, 66875L).setShort(
637: 13, (short) 43)
638: .setShort(14, (short) 68).setString(15,
639: "someotherstring").setTime(16,
640: new Time(cal.getTime().getTime()));
641: }
642: });
643: }
644:
645: public void testDeleteSubselectParamsFirebird() {
646: Select wherequery = new Select(mFirebird);
647: wherequery.from("table2").field("max(propertyShort)")
648: .whereParameter("propertyShort", "!=");
649:
650: // Manual subselect creation
651: Delete query = new Delete(mFirebird);
652: query.where("propertyShort >= (" + wherequery + ")")
653: .whereSubselect(wherequery).whereParameterOr(
654: "propertyString", "=").from("tablename");
655: assertEquals(
656: query.getSql(),
657: "DELETE FROM tablename WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ?");
658: String[] parameters = query.getParameters()
659: .getOrderedNamesArray();
660: assertEquals(2, parameters.length);
661: assertEquals(parameters[0], "propertyShort");
662: assertEquals(parameters[1], "propertyString");
663: assertTrue(execute(query, new DbPreparedStatementHandler() {
664: public void setParameters(DbPreparedStatement statement) {
665: statement.setShort("propertyShort", (short) 5)
666: .setString("propertyString", "thestring");
667: }
668: }));
669:
670: // Automated subselect creation
671: query = new Delete(mFirebird);
672: query.where("propertyShort", ">=", wherequery)
673: .whereParameterOr("propertyString", "=").whereOr(
674: "tablename.propertyFloat",
675: ">",
676: new Select(mFirebird).from("table2").field(
677: "max(propertyLong)").whereParameter(
678: "propertyLong", "!=")).whereAnd(
679: "tablename.propertyDouble",
680: "<=",
681: new Select(mFirebird).from("table2").field(
682: "max(propertyFloat)").whereParameter(
683: "propertyFloat", "!=")).from(
684: "tablename");
685: assertEquals(
686: query.getSql(),
687: "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 != ?)");
688: parameters = query.getParameters().getOrderedNamesArray();
689: assertEquals(4, parameters.length);
690: assertEquals(parameters[0], "propertyShort");
691: assertEquals(parameters[1], "propertyString");
692: assertEquals(parameters[2], "propertyLong");
693: assertEquals(parameters[3], "propertyFloat");
694: assertTrue(execute(query, new DbPreparedStatementHandler() {
695: public void setParameters(DbPreparedStatement statement) {
696: statement.setShort("propertyShort", (short) 5)
697: .setString("propertyString", "thestring")
698: .setLong("propertyLong", 99999999).setFloat(
699: "propertyFloat", -1f);
700: }
701: }));
702: }
703:
704: public void testCloneFirebird() {
705: Select wherequery = new Select(mFirebird);
706: wherequery.from("table2").field("max(propertyShort)")
707: .whereParameter("propertyShort", "!=");
708:
709: Delete query = new Delete(mFirebird);
710: query.from("tablename").where(
711: "propertyShort >= (" + wherequery + ")")
712: .whereSubselect(wherequery).whereParameterOr(
713: "propertyString", "=").whereOr("propertyByte",
714: "=", (byte) 54).whereAnd("propertyDouble",
715: "!=", 73453.71d).whereParameterOr(
716: "propertyInt", "=").whereParameterAnd(
717: "propertyLong", "<").whereParameterOr(
718: "propertyChar", "=");
719:
720: Delete query_clone = query.clone();
721: assertEquals(query.getSql(), query_clone.getSql());
722: assertTrue(query != query_clone);
723: execute(query, new DbPreparedStatementHandler() {
724: public void setParameters(DbPreparedStatement statement) {
725: statement.setString("propertyChar", "M").setInt(
726: "propertyInt", 34).setString("propertyString",
727: "string'value").setLong("propertyLong", 34543)
728: .setShort("propertyShort", (short) 4);
729: }
730: });
731: }
732: }
|