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