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: TestDeleteMckoi.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 TestDeleteMckoi extends TestDelete {
023: public TestDeleteMckoi(String name) {
024: super (name);
025: }
026:
027: public void testInstantiationMckoi() {
028: Delete query = new Delete(mMckoi);
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 testIncompleteQueryMckoi() {
039: Delete query = new Delete(mMckoi);
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 testClearMckoi() {
058: Delete query = new Delete(mMckoi);
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 testHintMckoi() {
071: Delete query = new Delete(mMckoi).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 testFromMckoi() {
082: Delete query = new Delete(mMckoi);
083: query.from("tablename");
084: assertEquals(query.getSql(), "DELETE FROM tablename");
085: assertTrue(execute(query));
086: }
087:
088: public void testWhereMckoi() {
089: Delete query = new Delete(mMckoi);
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 testWhereTypedMckoi() {
097: Delete query = new Delete(mMckoi);
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 <= TIMESTAMP '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = TIMESTAMP '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 = DATE '2003-03-03' AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = TIME '10:01:28' AND propertyTimestamp <= TIMESTAMP '2003-03-03 10:01:28.154'");
128: assertFalse(execute(query));
129: }
130:
131: public void testWhereTypedMixedMckoi() {
132: Delete query = new Delete(mMckoi);
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 <= TIMESTAMP '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = TIMESTAMP '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 = TIME '10:01:28' AND propertyTimestamp <= TIMESTAMP '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 testWhereParametersMckoi() {
171: Delete query = new Delete(mMckoi);
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 testWhereParametersMixedMckoi() {
209: Delete query = new Delete(mMckoi);
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 testWhereConstructionMckoi() {
239: Delete query = new Delete(mMckoi);
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 testWhereBeanMckoi() {
249: Delete query = new Delete(mMckoi);
250: query.from("tablename").where(BeanImpl.getPopulatedBean());
251: assertEquals(
252: query.getSql(),
253: "DELETE FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = true AND propertyBooleanObject = false AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = TIMESTAMP '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = TIMESTAMP '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 = DATE '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = TIME '15:26:14' AND propertyTimestamp = TIMESTAMP '2002-06-18 15:26:14.764'");
254: assertTrue(execute(query));
255: }
256:
257: public void testWhereBeanConstrainedMckoi() {
258: Delete query = new Delete(mMckoi);
259: query.from("tablename").where(
260: BeanImplConstrained.getPopulatedBean());
261: assertEquals(
262: query.getSql(),
263: "DELETE FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = true AND propertyBooleanObject = false AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = TIMESTAMP '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = TIMESTAMP '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 = DATE '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = TIME '15:26:14' AND propertyTimestamp = TIMESTAMP '2002-06-18 15:26:14.764'");
264: assertTrue(execute(query));
265: }
266:
267: public void testWhereConstructionGroupMckoi() {
268: Delete query = new Delete(mMckoi);
269: query.from("tablename").where("propertyInt = 545").whereAnd(
270: "propertyLong < 50000").startWhereOr().whereParameter(
271: "propertyString", "=").whereAnd("propertyByte", "<=",
272: (byte) 0).startWhereAnd().where("propertyBoolean",
273: "!=", true).whereParameterOr("propertyStringbuffer",
274: "LIKE").end().end().whereOr("propertyChar = 'v'");
275:
276: assertEquals(query.getParameters().getOrderedNames().get(0),
277: "propertyString");
278: assertEquals(query.getParameters().getOrderedNames().get(1),
279: "propertyStringbuffer");
280: assertTrue(Arrays.equals(query.getParameters()
281: .getOrderedNamesArray(), new String[] {
282: "propertyString", "propertyStringbuffer" }));
283:
284: assertEquals(
285: query.getSql(),
286: "DELETE FROM tablename WHERE propertyInt = 545 AND propertyLong < 50000 OR (propertyString = ? AND propertyByte <= 0 AND (propertyBoolean != true OR propertyStringbuffer LIKE ?)) OR propertyChar = 'v'");
287:
288: assertTrue(execute(query, new DbPreparedStatementHandler() {
289: public void setParameters(DbPreparedStatement statement) {
290: statement
291: .setString("propertyString", "someotherstring")
292: .setString("propertyStringbuffer", "stringbuff");
293: }
294: }));
295: }
296:
297: public void testWhereBeanNullValuesMckoi() {
298: Delete query = new Delete(mMckoi);
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 testWhereBeanIncludedMckoi() {
307: Delete query = new Delete(mMckoi);
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 = TIME '15:26:14'");
316: assertTrue(execute(query));
317: }
318:
319: public void testWhereBeanExcludedMckoi() {
320: Delete query = new Delete(mMckoi);
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 = TIMESTAMP '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = TIMESTAMP '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 = DATE '2002-06-18' AND propertyString = 'someotherstring' AND propertyTimestamp = TIMESTAMP '2002-06-18 15:26:14.764'");
329: assertTrue(execute(query));
330: }
331:
332: public void testWhereBeanFilteredMckoi() {
333: Delete query = new Delete(mMckoi);
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 testWhereParametersBeanMckoi() {
348: Delete query = new Delete(mMckoi);
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: java.sql.Date date = new java.sql.Date(102, 5, 18);
426: Time time = new Time(15, 26, 14);
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(21, date)
446: .setString(22, "someotherstring").setString(23,
447: "someotherstringbuff")
448: .setTime(24, time).setTimestamp(25,
449: new Timestamp(cal.getTime().getTime()));
450: }
451: }));
452: }
453:
454: public void testWhereParametersBeanConstrainedMckoi() {
455: Delete query = new Delete(mMckoi);
456: query.from("tablename").whereParameters(
457: BeanImplConstrained.class);
458: assertEquals(
459: query.getSql(),
460: "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 = ?");
461:
462: assertEquals(query.getParameters().getOrderedNames().size(), 22);
463: assertEquals(query.getParameters().getOrderedNames().get(0),
464: "propertyBigDecimal");
465: assertEquals(query.getParameters().getOrderedNames().get(1),
466: "propertyBoolean");
467: assertEquals(query.getParameters().getOrderedNames().get(2),
468: "propertyBooleanObject");
469: assertEquals(query.getParameters().getOrderedNames().get(3),
470: "propertyByte");
471: assertEquals(query.getParameters().getOrderedNames().get(4),
472: "propertyByteObject");
473: assertEquals(query.getParameters().getOrderedNames().get(5),
474: "propertyCalendar");
475: assertEquals(query.getParameters().getOrderedNames().get(6),
476: "propertyChar");
477: assertEquals(query.getParameters().getOrderedNames().get(7),
478: "propertyCharacterObject");
479: assertEquals(query.getParameters().getOrderedNames().get(8),
480: "propertyDate");
481: assertEquals(query.getParameters().getOrderedNames().get(9),
482: "propertyDouble");
483: assertEquals(query.getParameters().getOrderedNames().get(10),
484: "propertyDoubleObject");
485: assertEquals(query.getParameters().getOrderedNames().get(11),
486: "propertyFloat");
487: assertEquals(query.getParameters().getOrderedNames().get(12),
488: "propertyFloatObject");
489: assertEquals(query.getParameters().getOrderedNames().get(13),
490: "propertyInt");
491: assertEquals(query.getParameters().getOrderedNames().get(14),
492: "propertyIntegerObject");
493: assertEquals(query.getParameters().getOrderedNames().get(15),
494: "propertyLongObject");
495: assertEquals(query.getParameters().getOrderedNames().get(16),
496: "propertyShort");
497: assertEquals(query.getParameters().getOrderedNames().get(17),
498: "propertySqlDate");
499: assertEquals(query.getParameters().getOrderedNames().get(18),
500: "propertyString");
501: assertEquals(query.getParameters().getOrderedNames().get(19),
502: "propertyStringbuffer");
503: assertEquals(query.getParameters().getOrderedNames().get(20),
504: "propertyTime");
505: assertEquals(query.getParameters().getOrderedNames().get(21),
506: "propertyTimestamp");
507: assertTrue(Arrays.equals(query.getParameters()
508: .getOrderedNamesArray(), new String[] {
509: "propertyBigDecimal", "propertyBoolean",
510: "propertyBooleanObject", "propertyByte",
511: "propertyByteObject", "propertyCalendar",
512: "propertyChar", "propertyCharacterObject",
513: "propertyDate", "propertyDouble",
514: "propertyDoubleObject", "propertyFloat",
515: "propertyFloatObject", "propertyInt",
516: "propertyIntegerObject", "propertyLongObject",
517: "propertyShort", "propertySqlDate", "propertyString",
518: "propertyStringbuffer", "propertyTime",
519: "propertyTimestamp" }));
520:
521: assertTrue(execute(query, new DbPreparedStatementHandler() {
522: public void setParameters(DbPreparedStatement statement) {
523: Calendar cal = Calendar.getInstance();
524: cal.set(2002, 5, 18, 15, 26, 14);
525: cal.set(Calendar.MILLISECOND, 764);
526: java.sql.Date date = new java.sql.Date(102, 5, 18);
527: Time time = new Time(15, 26, 14);
528: statement.setBigDecimal(1,
529: new BigDecimal("219038743.392874")).setBoolean(
530: 2, true).setBoolean(3, false).setByte(4,
531: (byte) 89).setByte(5, (byte) 34)
532: .setTimestamp(
533: 6,
534: new java.sql.Timestamp(cal.getTime()
535: .getTime())).setString(7, "v")
536: .setString(8, "r").setTimestamp(
537: 9,
538: new java.sql.Timestamp(cal.getTime()
539: .getTime())).setDouble(10,
540: 53348.34d).setDouble(11, 143298.692d)
541: .setFloat(12, 98634.2f).setFloat(13, 8734.7f)
542: .setInt(14, 545).setInt(15, 968).setLong(16,
543: 66875L).setShort(17, (short) 43)
544: .setDate(18, date).setString(19,
545: "someotherstring").setString(20,
546: "someotherstringbuff")
547: .setTime(21, time).setTimestamp(22,
548: new Timestamp(cal.getTime().getTime()));
549: }
550: }));
551: }
552:
553: public void testWhereParametersBeanExcludedMckoi() {
554: Delete query = new Delete(mMckoi);
555: query.from("tablename").whereParametersExcluded(
556: BeanImpl.class,
557: new String[] { "propertyBoolean", "propertyByte",
558: "propertyChar", "propertyDouble",
559: "propertyInt", "propertyLong",
560: "propertySqlDate", "propertyStringbuffer",
561: "propertyTimestamp" });
562: assertEquals(
563: query.getSql(),
564: "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 = ?");
565:
566: assertEquals(query.getParameters().getOrderedNames().size(), 16);
567: assertEquals(query.getParameters().getOrderedNames().get(0),
568: "propertyBigDecimal");
569: assertEquals(query.getParameters().getOrderedNames().get(1),
570: "propertyBooleanObject");
571: assertEquals(query.getParameters().getOrderedNames().get(2),
572: "propertyByteObject");
573: assertEquals(query.getParameters().getOrderedNames().get(3),
574: "propertyCalendar");
575: assertEquals(query.getParameters().getOrderedNames().get(4),
576: "propertyCharacterObject");
577: assertEquals(query.getParameters().getOrderedNames().get(5),
578: "propertyDate");
579: assertEquals(query.getParameters().getOrderedNames().get(6),
580: "propertyDoubleObject");
581: assertEquals(query.getParameters().getOrderedNames().get(7),
582: "propertyEnum");
583: assertEquals(query.getParameters().getOrderedNames().get(8),
584: "propertyFloat");
585: assertEquals(query.getParameters().getOrderedNames().get(9),
586: "propertyFloatObject");
587: assertEquals(query.getParameters().getOrderedNames().get(10),
588: "propertyIntegerObject");
589: assertEquals(query.getParameters().getOrderedNames().get(11),
590: "propertyLongObject");
591: assertEquals(query.getParameters().getOrderedNames().get(12),
592: "propertyShort");
593: assertEquals(query.getParameters().getOrderedNames().get(13),
594: "propertyShortObject");
595: assertEquals(query.getParameters().getOrderedNames().get(14),
596: "propertyString");
597: assertEquals(query.getParameters().getOrderedNames().get(15),
598: "propertyTime");
599: assertTrue(Arrays.equals(query.getParameters()
600: .getOrderedNamesArray(), new String[] {
601: "propertyBigDecimal", "propertyBooleanObject",
602: "propertyByteObject", "propertyCalendar",
603: "propertyCharacterObject", "propertyDate",
604: "propertyDoubleObject", "propertyEnum",
605: "propertyFloat", "propertyFloatObject",
606: "propertyIntegerObject", "propertyLongObject",
607: "propertyShort", "propertyShortObject",
608: "propertyString", "propertyTime" }));
609:
610: assertTrue(execute(query, new DbPreparedStatementHandler() {
611: public void setParameters(DbPreparedStatement statement) {
612: Calendar cal = Calendar.getInstance();
613: cal.set(2002, 5, 18, 15, 26, 14);
614: cal.set(Calendar.MILLISECOND, 764);
615: Time time = new Time(15, 26, 14);
616: statement.setBigDecimal(1,
617: new BigDecimal("219038743.392874")).setBoolean(
618: 2, false).setByte(3, (byte) 34)
619: .setTimestamp(
620: 4,
621: new java.sql.Timestamp(cal.getTime()
622: .getTime())).setString(5, "r")
623: .setTimestamp(
624: 6,
625: new java.sql.Timestamp(cal.getTime()
626: .getTime())).setDouble(7,
627: 143298.692d)
628: .setString(8, "VALUE_THREE").setFloat(9,
629: 98634.2f).setFloat(10, 8734.7f).setInt(
630: 11, 968).setLong(12, 66875L).setShort(
631: 13, (short) 43)
632: .setShort(14, (short) 68).setString(15,
633: "someotherstring").setTime(16, time);
634: }
635: }));
636: }
637:
638: public void testDeleteSubselectParamsMckoi() {
639: Select wherequery = new Select(mMckoi);
640: wherequery.from("table2").field("max(propertyShort)")
641: .whereParameter("propertyShort", "!=");
642:
643: // Manual subselect creation
644: Delete query = new Delete(mMckoi);
645: query.where("propertyShort >= (" + wherequery + ")")
646: .whereSubselect(wherequery).whereParameterOr(
647: "propertyString", "=").from("tablename");
648: assertEquals(
649: query.getSql(),
650: "DELETE FROM tablename WHERE propertyShort >= (SELECT max(propertyShort) FROM table2 WHERE propertyShort != ?) OR propertyString = ?");
651: String[] parameters = query.getParameters()
652: .getOrderedNamesArray();
653: assertEquals(2, parameters.length);
654: assertEquals(parameters[0], "propertyShort");
655: assertEquals(parameters[1], "propertyString");
656: assertTrue(execute(query, new DbPreparedStatementHandler() {
657: public void setParameters(DbPreparedStatement statement) {
658: statement.setShort("propertyShort", (short) 5)
659: .setString("propertyString", "thestring");
660: }
661: }));
662:
663: // Automated subselect creation
664: query = new Delete(mMckoi);
665: query.where("propertyShort", ">=", wherequery)
666: .whereParameterOr("propertyString", "=").whereOr(
667: "tablename.propertyFloat",
668: ">",
669: new Select(mMckoi).from("table2").field(
670: "max(propertyLong)").whereParameter(
671: "propertyLong", "!=")).whereAnd(
672: "tablename.propertyDouble",
673: "<=",
674: new Select(mMckoi).from("table2").field(
675: "max(propertyFloat)").whereParameter(
676: "propertyFloat", "!=")).from(
677: "tablename");
678: assertEquals(
679: query.getSql(),
680: "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 != ?)");
681: parameters = query.getParameters().getOrderedNamesArray();
682: assertEquals(4, parameters.length);
683: assertEquals(parameters[0], "propertyShort");
684: assertEquals(parameters[1], "propertyString");
685: assertEquals(parameters[2], "propertyLong");
686: assertEquals(parameters[3], "propertyFloat");
687: assertTrue(execute(query, new DbPreparedStatementHandler() {
688: public void setParameters(DbPreparedStatement statement) {
689: statement.setShort("propertyShort", (short) 5)
690: .setString("propertyString", "thestring")
691: .setLong("propertyLong", 99999999).setFloat(
692: "propertyFloat", -1f);
693: }
694: }));
695: }
696:
697: public void testCloneMckoi() {
698: Select wherequery = new Select(mMckoi);
699: wherequery.from("table2").field("max(propertyShort)")
700: .whereParameter("propertyShort", "!=");
701:
702: Delete query = new Delete(mMckoi);
703: query.from("tablename").where(
704: "propertyShort >= (" + wherequery + ")")
705: .whereSubselect(wherequery).whereParameterOr(
706: "propertyString", "=").whereOr("propertyByte",
707: "=", (byte) 54).whereAnd("propertyDouble",
708: "!=", 73453.71d).whereParameterOr(
709: "propertyInt", "=").whereParameterAnd(
710: "propertyLong", "<").whereParameterOr(
711: "propertyChar", "=");
712:
713: Delete query_clone = query.clone();
714: assertEquals(query.getSql(), query_clone.getSql());
715: assertTrue(query != query_clone);
716: execute(query, new DbPreparedStatementHandler() {
717: public void setParameters(DbPreparedStatement statement) {
718: statement.setString("propertyChar", "M").setInt(
719: "propertyInt", 34).setString("propertyString",
720: "string'value").setLong("propertyLong", 34543)
721: .setShort("propertyShort", (short) 4);
722: }
723: });
724: }
725: }
|