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