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