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: TestInsertOracle.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.FieldsRequiredException;
015: import com.uwyn.rife.database.exceptions.TableNameRequiredException;
016: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
017: import com.uwyn.rife.database.types.SqlNull;
018: import java.math.BigDecimal;
019: import java.sql.Time;
020: import java.sql.Timestamp;
021: import java.util.Arrays;
022: import java.util.Calendar;
023:
024: public class TestInsertOracle extends TestInsert {
025: public TestInsertOracle(String name) {
026: super (name);
027: }
028:
029: public void testInstantiationOracle() {
030: Insert query = new Insert(mOracle);
031: assertNotNull(query);
032: try {
033: query.getSql();
034: fail();
035: } catch (TableNameRequiredException e) {
036: assertEquals(e.getQueryName(), "Insert");
037: }
038: }
039:
040: public void testIncompleteQueryOracle() {
041: Insert query = new Insert(mOracle);
042: try {
043: query.getSql();
044: fail();
045: } catch (TableNameRequiredException e) {
046: assertEquals(e.getQueryName(), "Insert");
047: }
048: query.into("tablename");
049: try {
050: query.getSql();
051: fail();
052: } catch (FieldsRequiredException e) {
053: assertEquals(e.getQueryName(), "Insert");
054: }
055: query.field("col1", "val1");
056: assertNotNull(query.getSql());
057: }
058:
059: public void testClearOracle() {
060: Insert query = new Insert(mOracle);
061: query.into("tablename").field("col1", "val1");
062: assertNotNull(query.getSql());
063: query.clear();
064: try {
065: query.getSql();
066: fail();
067: } catch (TableNameRequiredException e) {
068: assertEquals(e.getQueryName(), "Insert");
069: }
070: }
071:
072: public void testHintOracle() {
073: Insert query = new Insert(mOracle).hint("APPEND").into(
074: "tablename").field("propertyDouble", 12.3d);
075: assertEquals(query.getSql(),
076: "INSERT /*+ APPEND */ INTO tablename (propertyDouble) VALUES (12.3)");
077: assertTrue(execute(query));
078: }
079:
080: public void testParameterOracle() {
081: Insert query = new Insert(mOracle);
082: query.into("tablename").fieldParameter("col1");
083: assertEquals(query.getSql(),
084: "INSERT INTO tablename (col1) VALUES (?)");
085: }
086:
087: public void testFieldOracle() {
088: Calendar cal = Calendar.getInstance();
089: cal.set(2002, 7, 19, 12, 17, 52);
090: Insert query = new Insert(mOracle);
091: query.into("tablename").field("nullColumn", SqlNull.NULL)
092: .field("propertyBigDecimal",
093: new BigDecimal("98347.876438637")).field(
094: "propertyBoolean", true).field("propertyByte",
095: (byte) 16).field("propertyCalendar",
096: cal.getTime()).field("propertyChar", 'M')
097: .field("propertyDate", cal).field("propertyDouble",
098: 12.3d).field("propertyFloat", 13.4f).field(
099: "propertyInt", 34).field("propertyLong", 45L)
100: .field("propertyShort", (short) 12).field(
101: "propertySqlDate",
102: new java.sql.Date(cal.getTime().getTime()))
103: .field("propertyString", "string'value").field(
104: "propertyStringbuffer",
105: new StringBuffer("stringbuffer'value")).field(
106: "propertyTime",
107: new Time(cal.getTime().getTime())).field(
108: "propertyTimestamp",
109: new Timestamp(cal.getTime().getTime()));
110: assertEquals(
111: query.getSql(),
112: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (NULL, 98347.876438637, 1, 16, TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), 'M', TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), 12.3, 13.4, 34, 45, 12, TO_DATE('2002/08/19 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'string''value', 'stringbuffer''value', TO_DATE('12:17:52', 'HH24:MI:SS'), TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'))");
113: assertTrue(execute(query));
114: }
115:
116: public void testFieldCustomOracle() {
117: Insert query = new Insert(mOracle);
118: query.into("tablename").fieldCustom("propertySqlDate",
119: "(SELECT sysdate FROM dual)");
120: assertEquals(query.getSql(),
121: "INSERT INTO tablename (propertySqlDate) VALUES ((SELECT sysdate FROM dual))");
122: assertTrue(execute(query));
123: }
124:
125: public void testFieldsOracle() {
126: Calendar cal = Calendar.getInstance();
127: cal.set(2002, 7, 19, 12, 17, 52);
128: Insert query = new Insert(mOracle);
129: query.into("tablename").fields(
130: new Object[] { "nullColumn", SqlNull.NULL,
131: "propertyBigDecimal",
132: new BigDecimal("98347.876438637"),
133: "propertyBoolean", new Boolean(true),
134: "propertyByte", new Byte((byte) 16),
135: "propertyCalendar", cal.getTime(),
136: "propertyChar", new Character('M'),
137: "propertyDate", cal, "propertyDouble",
138: new Double(12.3d), "propertyFloat",
139: new Float(13.4f), "propertyInt",
140: new Integer(34), "propertyLong", new Long(45L),
141: "propertyShort", new Short((short) 12),
142: "propertySqlDate",
143: new java.sql.Date(cal.getTime().getTime()),
144: "propertyString", new String("string'value"),
145: "propertyStringbuffer",
146: new StringBuffer("stringbuffer'value"),
147: "propertyTime",
148: new Time(cal.getTime().getTime()),
149: "propertyTimestamp",
150: new Timestamp(cal.getTime().getTime()) });
151: assertEquals(
152: query.getSql(),
153: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (NULL, 98347.876438637, 1, 16, TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), 'M', TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), 12.3, 13.4, 34, 45, 12, TO_DATE('2002/08/19 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'string''value', 'stringbuffer''value', TO_DATE('12:17:52', 'HH24:MI:SS'), TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'))");
154: assertTrue(execute(query));
155: }
156:
157: public void testFieldParametersOracle() {
158: Insert query = new Insert(mOracle);
159: query.into("tablename");
160:
161: assertNull(query.getParameters());
162:
163: query.fieldParameter("nullColumn").fieldParameter(
164: "propertyBigDecimal").fieldParameter("propertyBoolean")
165: .fieldParameter("propertyByte").fieldParameter(
166: "propertyCalendar").fieldParameter(
167: "propertyChar").fieldParameter("propertyDate")
168: .fieldParameter("propertyDouble").fieldParameter(
169: "propertyFloat").fieldParameter("propertyInt")
170: .fieldParameter("propertyLong").fieldParameter(
171: "propertyShort").fieldParameter(
172: "propertySqlDate").fieldParameter(
173: "propertyString").fieldParameter(
174: "propertyStringbuffer").fieldParameter(
175: "propertyTime").fieldParameter(
176: "propertyTimestamp");
177:
178: assertEquals(query.getParameters().getOrderedNames().size(), 17);
179: assertEquals(query.getParameters().getOrderedNames().get(0),
180: "nullColumn");
181: assertEquals(query.getParameters().getOrderedNames().get(1),
182: "propertyBigDecimal");
183: assertEquals(query.getParameters().getOrderedNames().get(2),
184: "propertyBoolean");
185: assertEquals(query.getParameters().getOrderedNames().get(3),
186: "propertyByte");
187: assertEquals(query.getParameters().getOrderedNames().get(4),
188: "propertyCalendar");
189: assertEquals(query.getParameters().getOrderedNames().get(5),
190: "propertyChar");
191: assertEquals(query.getParameters().getOrderedNames().get(6),
192: "propertyDate");
193: assertEquals(query.getParameters().getOrderedNames().get(7),
194: "propertyDouble");
195: assertEquals(query.getParameters().getOrderedNames().get(8),
196: "propertyFloat");
197: assertEquals(query.getParameters().getOrderedNames().get(9),
198: "propertyInt");
199: assertEquals(query.getParameters().getOrderedNames().get(10),
200: "propertyLong");
201: assertEquals(query.getParameters().getOrderedNames().get(11),
202: "propertyShort");
203: assertEquals(query.getParameters().getOrderedNames().get(12),
204: "propertySqlDate");
205: assertEquals(query.getParameters().getOrderedNames().get(13),
206: "propertyString");
207: assertEquals(query.getParameters().getOrderedNames().get(14),
208: "propertyStringbuffer");
209: assertEquals(query.getParameters().getOrderedNames().get(15),
210: "propertyTime");
211: assertEquals(query.getParameters().getOrderedNames().get(16),
212: "propertyTimestamp");
213: assertTrue(Arrays.equals(query.getParameters()
214: .getOrderedNamesArray(), new String[] { "nullColumn",
215: "propertyBigDecimal", "propertyBoolean",
216: "propertyByte", "propertyCalendar", "propertyChar",
217: "propertyDate", "propertyDouble", "propertyFloat",
218: "propertyInt", "propertyLong", "propertyShort",
219: "propertySqlDate", "propertyString",
220: "propertyStringbuffer", "propertyTime",
221: "propertyTimestamp" }));
222:
223: assertEquals(
224: query.getSql(),
225: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
226:
227: assertTrue(execute(query, new DbPreparedStatementHandler() {
228: public void setParameters(DbPreparedStatement statement) {
229: Calendar cal = Calendar.getInstance();
230: cal.set(2002, 7, 19, 12, 17, 52);
231: cal.set(Calendar.MILLISECOND, 462);
232: statement.setString(1, null).setBigDecimal(2,
233: new BigDecimal("98347.876438637")).setBoolean(
234: 3, true).setByte(4, (byte) 16).setDate(5,
235: new java.sql.Date(cal.getTime().getTime()))
236: .setString(6, "M").setDate(
237: 7,
238: new java.sql.Date(cal.getTime()
239: .getTime()))
240: .setDouble(8, 12.3d).setFloat(9, 13.4f).setInt(
241: 10, 34).setLong(11, 45L).setShort(12,
242: (short) 12).setDate(
243: 13,
244: new java.sql.Date(cal.getTime()
245: .getTime())).setString(14,
246: "string'value").setString(15,
247: "string'value2").setTime(16,
248: new Time(cal.getTime().getTime()))
249: .setTimestamp(17,
250: new Timestamp(cal.getTime().getTime()));
251: }
252: }));
253: }
254:
255: public void testFieldParametersMixedOracle() {
256: Insert query = new Insert(mOracle);
257: query.into("tablename");
258:
259: assertNull(query.getParameters());
260:
261: final Calendar cal = Calendar.getInstance();
262: cal.set(2002, 7, 19, 12, 17, 52);
263: cal.set(Calendar.MILLISECOND, 462);
264: query.fieldParameter("nullColumn").field("propertyBigDecimal",
265: new BigDecimal("98347.876438637")).fieldParameter(
266: "propertyBoolean").fieldParameter("propertyByte")
267: .field("propertyCalendar", cal.getTime())
268: .fieldParameter("propertyChar").field("propertyDate",
269: cal).field("propertyDouble", 12.3d)
270: .fieldParameter("propertyFloat").fieldParameter(
271: "propertyInt").field("propertyLong", 45L)
272: .field("propertyShort", (short) 12).fieldParameter(
273: "propertySqlDate").fieldParameter(
274: "propertyString").field("propertyStringbuffer",
275: new StringBuffer("stringbuffer'value")).field(
276: "propertyTime",
277: new Time(cal.getTime().getTime()))
278: .fieldParameter("propertyTimestamp");
279:
280: assertEquals(query.getParameters().getOrderedNames().size(), 9);
281: assertEquals(query.getParameters().getOrderedNames().get(0),
282: "nullColumn");
283: assertEquals(query.getParameters().getOrderedNames().get(1),
284: "propertyBoolean");
285: assertEquals(query.getParameters().getOrderedNames().get(2),
286: "propertyByte");
287: assertEquals(query.getParameters().getOrderedNames().get(3),
288: "propertyChar");
289: assertEquals(query.getParameters().getOrderedNames().get(4),
290: "propertyFloat");
291: assertEquals(query.getParameters().getOrderedNames().get(5),
292: "propertyInt");
293: assertEquals(query.getParameters().getOrderedNames().get(6),
294: "propertySqlDate");
295: assertEquals(query.getParameters().getOrderedNames().get(7),
296: "propertyString");
297: assertEquals(query.getParameters().getOrderedNames().get(8),
298: "propertyTimestamp");
299: assertTrue(Arrays.equals(query.getParameters()
300: .getOrderedNamesArray(), new String[] { "nullColumn",
301: "propertyBoolean", "propertyByte", "propertyChar",
302: "propertyFloat", "propertyInt", "propertySqlDate",
303: "propertyString", "propertyTimestamp" }));
304:
305: assertEquals(
306: query.getSql(),
307: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (?, 98347.876438637, ?, ?, TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), ?, TO_DATE('2002/08/19 12:17:52', 'YYYY/MM/DD HH24:MI:SS'), 12.3, ?, ?, 45, 12, ?, ?, 'stringbuffer''value', TO_DATE('12:17:52', 'HH24:MI:SS'), ?)");
308:
309: assertTrue(execute(query, new DbPreparedStatementHandler() {
310: public void setParameters(DbPreparedStatement statement) {
311: statement.setString(1, null).setBoolean(2, true)
312: .setByte(3, (byte) 16).setString(4, "M")
313: .setFloat(5, 13.4f).setInt(6, 34).setDate(
314: 7,
315: new java.sql.Date(cal.getTime()
316: .getTime())).setString(8,
317: "string'value").setTimestamp(9,
318: new Timestamp(cal.getTime().getTime()));
319: }
320: }));
321: }
322:
323: public void testFieldsBeanOracle() {
324: Insert query = new Insert(mOracle);
325: query.into("tablename").fields(BeanImpl.getPopulatedBean());
326: assertEquals(
327: query.getSql(),
328: "INSERT INTO tablename (propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyCalendar, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyEnum, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShort, propertyShortObject, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (219038743.392874, 1, 0, 89, 34, TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 'v', 'r', TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 53348.34, 143298.692, 'VALUE_THREE', 98634.2, 8734.7, 545, 968, 34563, 66875, 43, 68, TO_DATE('2002/06/18 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'someotherstring', 'someotherstringbuff', TO_DATE('15:26:14', 'HH24:MI:SS'), TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'))");
329: assertTrue(execute(query));
330: }
331:
332: public void testFieldsBeanConstrainedOracle() {
333: Insert query = new Insert(mOracle);
334: query.into("tablename").fields(
335: BeanImplConstrained.getPopulatedBean());
336: assertEquals(
337: query.getSql(),
338: "INSERT INTO tablename (propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByteObject, propertyCalendar, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLongObject, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (219038743.392874, 1, 0, 34, TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 'v', 'r', TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 53348.34, 143298.692, 98634.2, 8734.7, 545, 968, 66875, 43, TO_DATE('2002/06/18 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'someotherstring', 'someotherstringbuff', TO_DATE('15:26:14', 'HH24:MI:SS'), TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'))");
339: assertTrue(execute(query));
340: }
341:
342: public void testFieldsBeanNullValuesOracle() {
343: Insert query = new Insert(mOracle);
344: query.into("tablename").fields(BeanImpl.getNullBean());
345: assertEquals(
346: query.getSql(),
347: "INSERT INTO tablename (propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyDouble, propertyDoubleObject, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShort, propertyShortObject) VALUES (0, 0, 0, 0, 0.0, 0.0, 0.0, 0.0, 0, 0, 0, 0, 0, 0)");
348: assertTrue(execute(query));
349: }
350:
351: public void testFieldsBeanIncludedOracle() {
352: Insert query = new Insert(mOracle);
353: query.into("tablename").fieldsIncluded(
354: BeanImpl.getPopulatedBean(),
355: new String[] { "propertyByte", "propertyDouble",
356: "propertyShort", "propertyStringbuffer",
357: "propertyTime" });
358: assertEquals(
359: query.getSql(),
360: "INSERT INTO tablename (propertyByte, propertyDouble, propertyShort, propertyStringbuffer, propertyTime) VALUES (89, 53348.34, 43, 'someotherstringbuff', TO_DATE('15:26:14', 'HH24:MI:SS'))");
361: assertTrue(execute(query));
362: }
363:
364: public void testFieldsBeanExcludedOracle() {
365: Insert query = new Insert(mOracle);
366: query.into("tablename").fieldsExcluded(
367: BeanImpl.getPopulatedBean(),
368: new String[] { "propertyByte", "propertyDouble",
369: "propertyShort", "propertyStringbuffer",
370: "propertyTime" });
371: assertEquals(
372: query.getSql(),
373: "INSERT INTO tablename (propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByteObject, propertyCalendar, propertyChar, propertyCharacterObject, propertyDate, propertyDoubleObject, propertyEnum, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShortObject, propertySqlDate, propertyString, propertyTimestamp) VALUES (219038743.392874, 1, 0, 34, TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 'v', 'r', TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'), 143298.692, 'VALUE_THREE', 98634.2, 8734.7, 545, 968, 34563, 66875, 68, TO_DATE('2002/06/18 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'someotherstring', TO_DATE('2002/06/18 15:26:14', 'YYYY/MM/DD HH24:MI:SS'))");
374: assertTrue(execute(query));
375: }
376:
377: public void testFieldsBeanFilteredOracle() {
378: Insert query = new Insert(mOracle);
379: query.into("tablename").fieldsFiltered(
380: BeanImpl.getPopulatedBean(),
381: new String[] { "propertyByte", "propertyDouble",
382: "propertyShort", "propertyStringbuffer",
383: "propertyTime" },
384: new String[] { "propertyByte", "propertyShort",
385: "propertyTime" });
386: assertEquals(
387: query.getSql(),
388: "INSERT INTO tablename (propertyDouble, propertyStringbuffer) VALUES (53348.34, 'someotherstringbuff')");
389: assertTrue(execute(query));
390: }
391:
392: public void testMultipleRowsOracle() {
393: Insert query = new Insert(mOracle);
394: query.into("tablename").field("propertyChar", 'M').field(
395: "propertyDouble", 12.3d).field("propertyFloat", 13.4f)
396: .field("propertyInt", 34);
397: query.field("propertyChar", 'S').field("propertyDouble", 45.1d)
398: .field("propertyFloat", 27.9f);
399: query.field("propertyChar", 'T');
400: try {
401: query.getSql();
402: fail();
403: } catch (UnsupportedSqlFeatureException e) {
404: assertTrue(true);
405: }
406: }
407:
408: public void testFieldsParametersBeanOracle() {
409: Insert query = new Insert(mPgsql);
410: query.into("tablename").fieldsParameters(BeanImpl.class);
411: assertEquals(
412: query.getSql(),
413: "INSERT INTO tablename (propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyCalendar, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyEnum, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShort, propertyShortObject, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
414:
415: assertEquals(query.getParameters().getOrderedNames().size(), 25);
416: assertEquals(query.getParameters().getOrderedNames().get(0),
417: "propertyBigDecimal");
418: assertEquals(query.getParameters().getOrderedNames().get(1),
419: "propertyBoolean");
420: assertEquals(query.getParameters().getOrderedNames().get(2),
421: "propertyBooleanObject");
422: assertEquals(query.getParameters().getOrderedNames().get(3),
423: "propertyByte");
424: assertEquals(query.getParameters().getOrderedNames().get(4),
425: "propertyByteObject");
426: assertEquals(query.getParameters().getOrderedNames().get(5),
427: "propertyCalendar");
428: assertEquals(query.getParameters().getOrderedNames().get(6),
429: "propertyChar");
430: assertEquals(query.getParameters().getOrderedNames().get(7),
431: "propertyCharacterObject");
432: assertEquals(query.getParameters().getOrderedNames().get(8),
433: "propertyDate");
434: assertEquals(query.getParameters().getOrderedNames().get(9),
435: "propertyDouble");
436: assertEquals(query.getParameters().getOrderedNames().get(10),
437: "propertyDoubleObject");
438: assertEquals(query.getParameters().getOrderedNames().get(11),
439: "propertyEnum");
440: assertEquals(query.getParameters().getOrderedNames().get(12),
441: "propertyFloat");
442: assertEquals(query.getParameters().getOrderedNames().get(13),
443: "propertyFloatObject");
444: assertEquals(query.getParameters().getOrderedNames().get(14),
445: "propertyInt");
446: assertEquals(query.getParameters().getOrderedNames().get(15),
447: "propertyIntegerObject");
448: assertEquals(query.getParameters().getOrderedNames().get(16),
449: "propertyLong");
450: assertEquals(query.getParameters().getOrderedNames().get(17),
451: "propertyLongObject");
452: assertEquals(query.getParameters().getOrderedNames().get(18),
453: "propertyShort");
454: assertEquals(query.getParameters().getOrderedNames().get(19),
455: "propertyShortObject");
456: assertEquals(query.getParameters().getOrderedNames().get(20),
457: "propertySqlDate");
458: assertEquals(query.getParameters().getOrderedNames().get(21),
459: "propertyString");
460: assertEquals(query.getParameters().getOrderedNames().get(22),
461: "propertyStringbuffer");
462: assertEquals(query.getParameters().getOrderedNames().get(23),
463: "propertyTime");
464: assertEquals(query.getParameters().getOrderedNames().get(24),
465: "propertyTimestamp");
466: assertTrue(Arrays.equals(query.getParameters()
467: .getOrderedNamesArray(), new String[] {
468: "propertyBigDecimal", "propertyBoolean",
469: "propertyBooleanObject", "propertyByte",
470: "propertyByteObject", "propertyCalendar",
471: "propertyChar", "propertyCharacterObject",
472: "propertyDate", "propertyDouble",
473: "propertyDoubleObject", "propertyEnum",
474: "propertyFloat", "propertyFloatObject", "propertyInt",
475: "propertyIntegerObject", "propertyLong",
476: "propertyLongObject", "propertyShort",
477: "propertyShortObject", "propertySqlDate",
478: "propertyString", "propertyStringbuffer",
479: "propertyTime", "propertyTimestamp" }));
480:
481: assertTrue(execute(query, new DbPreparedStatementHandler() {
482: public void setParameters(DbPreparedStatement statement) {
483: Calendar cal = Calendar.getInstance();
484: cal.set(2002, 7, 19, 12, 17, 52);
485: cal.set(Calendar.MILLISECOND, 462);
486: statement.setBigDecimal(1,
487: new BigDecimal("98347.876438637")).setBoolean(
488: 2, false).setBoolean(3, true).setByte(4,
489: (byte) 16).setByte(5, (byte) 72)
490: .setTimestamp(
491: 6,
492: new java.sql.Timestamp(cal.getTime()
493: .getTime())).setString(7, "M")
494: .setString(8, "p").setTimestamp(
495: 9,
496: new java.sql.Timestamp(cal.getTime()
497: .getTime())).setDouble(10,
498: 12.3d).setDouble(11, 68.7d).setString(
499: 12, "VALUE_THREE").setFloat(13, 13.4f)
500: .setFloat(14, 42.1f).setInt(15, 92).setInt(16,
501: 34).setLong(17, 687L).setLong(18, 92)
502: .setShort(19, (short) 7).setShort(20,
503: (short) 12).setDate(
504: 21,
505: new java.sql.Date(cal.getTime()
506: .getTime())).setString(22,
507: "string'value").setString(23,
508: "string'value2").setTime(24,
509: new Time(cal.getTime().getTime()))
510: .setTimestamp(25,
511: new Timestamp(cal.getTime().getTime()));
512: }
513: }));
514: }
515:
516: public void testFieldsParametersBeanConstrainedOracle() {
517: Insert query = new Insert(mOracle);
518: query.into("tablename").fieldsParameters(
519: BeanImplConstrained.class);
520: assertEquals(
521: query.getSql(),
522: "INSERT INTO tablename (propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByteObject, propertyCalendar, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyFloat, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLongObject, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
523:
524: assertEquals(query.getParameters().getOrderedNames().size(), 21);
525: assertEquals(query.getParameters().getOrderedNames().get(0),
526: "propertyBigDecimal");
527: assertEquals(query.getParameters().getOrderedNames().get(1),
528: "propertyBoolean");
529: assertEquals(query.getParameters().getOrderedNames().get(2),
530: "propertyBooleanObject");
531: assertEquals(query.getParameters().getOrderedNames().get(3),
532: "propertyByteObject");
533: assertEquals(query.getParameters().getOrderedNames().get(4),
534: "propertyCalendar");
535: assertEquals(query.getParameters().getOrderedNames().get(5),
536: "propertyChar");
537: assertEquals(query.getParameters().getOrderedNames().get(6),
538: "propertyCharacterObject");
539: assertEquals(query.getParameters().getOrderedNames().get(7),
540: "propertyDate");
541: assertEquals(query.getParameters().getOrderedNames().get(8),
542: "propertyDouble");
543: assertEquals(query.getParameters().getOrderedNames().get(9),
544: "propertyDoubleObject");
545: assertEquals(query.getParameters().getOrderedNames().get(10),
546: "propertyFloat");
547: assertEquals(query.getParameters().getOrderedNames().get(11),
548: "propertyFloatObject");
549: assertEquals(query.getParameters().getOrderedNames().get(12),
550: "propertyInt");
551: assertEquals(query.getParameters().getOrderedNames().get(13),
552: "propertyIntegerObject");
553: assertEquals(query.getParameters().getOrderedNames().get(14),
554: "propertyLongObject");
555: assertEquals(query.getParameters().getOrderedNames().get(15),
556: "propertyShort");
557: assertEquals(query.getParameters().getOrderedNames().get(16),
558: "propertySqlDate");
559: assertEquals(query.getParameters().getOrderedNames().get(17),
560: "propertyString");
561: assertEquals(query.getParameters().getOrderedNames().get(18),
562: "propertyStringbuffer");
563: assertEquals(query.getParameters().getOrderedNames().get(19),
564: "propertyTime");
565: assertEquals(query.getParameters().getOrderedNames().get(20),
566: "propertyTimestamp");
567: assertTrue(Arrays.equals(query.getParameters()
568: .getOrderedNamesArray(), new String[] {
569: "propertyBigDecimal", "propertyBoolean",
570: "propertyBooleanObject", "propertyByteObject",
571: "propertyCalendar", "propertyChar",
572: "propertyCharacterObject", "propertyDate",
573: "propertyDouble", "propertyDoubleObject",
574: "propertyFloat", "propertyFloatObject", "propertyInt",
575: "propertyIntegerObject", "propertyLongObject",
576: "propertyShort", "propertySqlDate", "propertyString",
577: "propertyStringbuffer", "propertyTime",
578: "propertyTimestamp" }));
579:
580: assertTrue(execute(query, new DbPreparedStatementHandler() {
581: public void setParameters(DbPreparedStatement statement) {
582: Calendar cal = Calendar.getInstance();
583: cal.set(2002, 7, 19, 12, 17, 52);
584: cal.set(Calendar.MILLISECOND, 462);
585: statement.setBigDecimal(1,
586: new BigDecimal("98347.876438637")).setBoolean(
587: 2, false).setBoolean(3, true).setByte(4,
588: (byte) 72)
589: .setTimestamp(
590: 5,
591: new java.sql.Timestamp(cal.getTime()
592: .getTime())).setString(6, "M")
593: .setString(7, "p").setTimestamp(
594: 8,
595: new java.sql.Timestamp(cal.getTime()
596: .getTime()))
597: .setDouble(9, 12.3d).setDouble(10, 68.7d)
598: .setFloat(11, 13.4f).setFloat(12, 42.1f)
599: .setInt(13, 92).setInt(14, 34).setLong(15, 92)
600: .setShort(16, (short) 7).setDate(
601: 17,
602: new java.sql.Date(cal.getTime()
603: .getTime())).setString(18,
604: "string'value").setString(19,
605: "string'value2").setTime(20,
606: new Time(cal.getTime().getTime()))
607: .setTimestamp(21,
608: new Timestamp(cal.getTime().getTime()));
609: }
610: }));
611: }
612:
613: public void testFieldsParametersBeanExcludedOracle() {
614: Insert query = new Insert(mPgsql);
615: query.into("tablename").fieldsParametersExcluded(
616: BeanImpl.class,
617: new String[] { "propertyBoolean", "propertyByte",
618: "propertyChar", "propertyDouble",
619: "propertyInt", "propertyLong",
620: "propertySqlDate", "propertyStringbuffer",
621: "propertyTimestamp" });
622: assertEquals(
623: query.getSql(),
624: "INSERT INTO tablename (propertyBigDecimal, propertyBooleanObject, propertyByteObject, propertyCalendar, propertyCharacterObject, propertyDate, propertyDoubleObject, propertyEnum, propertyFloat, propertyFloatObject, propertyIntegerObject, propertyLongObject, propertyShort, propertyShortObject, propertyString, propertyTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
625:
626: assertEquals(query.getParameters().getOrderedNames().size(), 16);
627: assertEquals(query.getParameters().getOrderedNames().get(0),
628: "propertyBigDecimal");
629: assertEquals(query.getParameters().getOrderedNames().get(1),
630: "propertyBooleanObject");
631: assertEquals(query.getParameters().getOrderedNames().get(2),
632: "propertyByteObject");
633: assertEquals(query.getParameters().getOrderedNames().get(3),
634: "propertyCalendar");
635: assertEquals(query.getParameters().getOrderedNames().get(4),
636: "propertyCharacterObject");
637: assertEquals(query.getParameters().getOrderedNames().get(5),
638: "propertyDate");
639: assertEquals(query.getParameters().getOrderedNames().get(6),
640: "propertyDoubleObject");
641: assertEquals(query.getParameters().getOrderedNames().get(7),
642: "propertyEnum");
643: assertEquals(query.getParameters().getOrderedNames().get(8),
644: "propertyFloat");
645: assertEquals(query.getParameters().getOrderedNames().get(9),
646: "propertyFloatObject");
647: assertEquals(query.getParameters().getOrderedNames().get(10),
648: "propertyIntegerObject");
649: assertEquals(query.getParameters().getOrderedNames().get(11),
650: "propertyLongObject");
651: assertEquals(query.getParameters().getOrderedNames().get(12),
652: "propertyShort");
653: assertEquals(query.getParameters().getOrderedNames().get(13),
654: "propertyShortObject");
655: assertEquals(query.getParameters().getOrderedNames().get(14),
656: "propertyString");
657: assertEquals(query.getParameters().getOrderedNames().get(15),
658: "propertyTime");
659: assertTrue(Arrays.equals(query.getParameters()
660: .getOrderedNamesArray(), new String[] {
661: "propertyBigDecimal", "propertyBooleanObject",
662: "propertyByteObject", "propertyCalendar",
663: "propertyCharacterObject", "propertyDate",
664: "propertyDoubleObject", "propertyEnum",
665: "propertyFloat", "propertyFloatObject",
666: "propertyIntegerObject", "propertyLongObject",
667: "propertyShort", "propertyShortObject",
668: "propertyString", "propertyTime" }));
669:
670: assertTrue(execute(query, new DbPreparedStatementHandler() {
671: public void setParameters(DbPreparedStatement statement) {
672: Calendar cal = Calendar.getInstance();
673: cal.set(2002, 7, 19, 12, 17, 52);
674: cal.set(Calendar.MILLISECOND, 462);
675: statement.setBigDecimal(1,
676: new BigDecimal("98347.876438637")).setBoolean(
677: 2, true).setByte(3, (byte) 72)
678: .setTimestamp(
679: 4,
680: new java.sql.Timestamp(cal.getTime()
681: .getTime())).setString(5, "o")
682: .setTimestamp(
683: 6,
684: new java.sql.Timestamp(cal.getTime()
685: .getTime()))
686: .setDouble(7, 86.7d)
687: .setString(8, "VALUE_THREE").setFloat(9, 13.4f)
688: .setFloat(10, 32.8f).setInt(11, 358).setLong(
689: 12, 9680L).setShort(13, (short) 12)
690: .setShort(14, (short) 78).setString(15,
691: "string'value").setTime(16,
692: new Time(cal.getTime().getTime()));
693: }
694: }));
695: }
696:
697: public void testInsertSubselectParamsOracle() {
698: Select fieldquery = new Select(mOracle);
699: fieldquery.from("table2").field("max(propertyLong)")
700: .whereParameter("propertyInt", ">");
701:
702: // Manual subselect creation
703: Insert query = new Insert(mOracle);
704: // shuffled the structure around a bit to test the correct order usage
705: query.into("tablename").fieldParameter("propertyString")
706: .fieldCustom("propertyLong", "(" + fieldquery + ")")
707: .fieldSubselect(fieldquery);
708: assertEquals(
709: query.getSql(),
710: "INSERT INTO tablename (propertyString, propertyLong) VALUES (?, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?))");
711: String[] parameters = query.getParameters()
712: .getOrderedNamesArray();
713: assertEquals(2, parameters.length);
714: assertEquals(parameters[0], "propertyString");
715: assertEquals(parameters[1], "propertyInt");
716: assertTrue(execute(query, new DbPreparedStatementHandler() {
717: public void setParameters(DbPreparedStatement statement) {
718: statement.setString("propertyString", "thestring")
719: .setLong("propertyInt", 90);
720: }
721: }));
722:
723: // Automated subselect creation
724: query = new Insert(mOracle);
725: // shuffled the structure around a bit to test the correct order usage
726: query.into("tablename").fieldParameter("propertyString").field(
727: "propertyLong", fieldquery);
728: assertEquals(
729: query.getSql(),
730: "INSERT INTO tablename (propertyString, propertyLong) VALUES (?, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?))");
731: parameters = query.getParameters().getOrderedNamesArray();
732: assertEquals(2, parameters.length);
733: assertEquals(parameters[0], "propertyString");
734: assertEquals(parameters[1], "propertyInt");
735: assertTrue(execute(query, new DbPreparedStatementHandler() {
736: public void setParameters(DbPreparedStatement statement) {
737: statement.setString("propertyString", "thestring")
738: .setLong("propertyInt", 90);
739: }
740: }));
741: }
742:
743: public void testCloneOracle() {
744: Select fieldquery = new Select(mOracle);
745: fieldquery.from("table2").field("max(propertyLong)")
746: .whereParameter("propertyInt", ">");
747:
748: final Calendar cal = Calendar.getInstance();
749: cal.set(2002, 7, 19, 12, 17, 52);
750: cal.set(Calendar.MILLISECOND, 462);
751: Insert query = new Insert(mOracle);
752: query.hint("APPEND").into("tablename").fieldParameter(
753: "nullColumn").field("propertyBigDecimal",
754: new BigDecimal("98347.876438637")).fieldParameter(
755: "propertyBoolean").fieldParameter("propertyByte")
756: .field("propertyCalendar", cal.getTime())
757: .fieldParameter("propertyChar").field("propertyDate",
758: cal).field("propertyDouble", 12.3d)
759: .fieldParameter("propertyFloat").fieldParameter(
760: "propertyInt").field("propertyShort",
761: (short) 12).fieldParameter("propertySqlDate")
762: .fieldParameter("propertyString").field(
763: "propertyStringbuffer",
764: new StringBuffer("stringbuffer'value")).field(
765: "propertyTime",
766: new Time(cal.getTime().getTime()))
767: .fieldParameter("propertyTimestamp").fieldCustom(
768: "propertyLong", "(" + fieldquery + ")")
769: .fieldSubselect(fieldquery);
770:
771: Insert query_clone = query.clone();
772: assertEquals(query.getSql(), query_clone.getSql());
773: assertTrue(query != query_clone);
774: assertTrue(execute(query, new DbPreparedStatementHandler() {
775: public void setParameters(DbPreparedStatement statement) {
776: statement.setString("nullColumn", null).setBoolean(
777: "propertyBoolean", true).setByte(
778: "propertyByte", (byte) 16).setString(
779: "propertyChar", "M").setFloat("propertyFloat",
780: 13.4f).setInt("propertyInt", 34).setDate(
781: "propertySqlDate",
782: new java.sql.Date(cal.getTime().getTime()))
783: .setString("propertyString", "string'value")
784: .setTimestamp("propertyTimestamp",
785: new Timestamp(cal.getTime().getTime()));
786: }
787: }));
788: }
789: }
|