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: TestInsertFirebird.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 TestInsertFirebird extends TestInsert {
025: public TestInsertFirebird(String name) {
026: super (name);
027: }
028:
029: public void testInstantiationFirebird() {
030: Insert query = new Insert(mFirebird);
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 testIncompleteQueryFirebird() {
041: Insert query = new Insert(mFirebird);
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 testClearFirebird() {
060: Insert query = new Insert(mFirebird);
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 testHintFirebird() {
073: Insert query = new Insert(mFirebird).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 testParameterFirebird() {
084: Insert query = new Insert(mFirebird);
085: query.into("tablename").fieldParameter("col1");
086: assertEquals(query.getSql(),
087: "INSERT INTO tablename (col1) VALUES (?)");
088: }
089:
090: public void testFieldFirebird() {
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(mFirebird);
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 testFieldCustomFirebird() {
121: Insert query = new Insert(mFirebird);
122: query.into("tablename").fieldCustom("propertySqlDate", "'now'");
123: assertEquals(query.getSql(),
124: "INSERT INTO tablename (propertySqlDate) VALUES ('now')");
125: assertTrue(execute(query));
126: }
127:
128: public void testFieldsFirebird() {
129: Calendar cal = Calendar.getInstance();
130: cal.set(2002, 7, 19, 12, 17, 52);
131: cal.set(Calendar.MILLISECOND, 462);
132: Insert query = new Insert(mFirebird);
133: query.into("tablename").fields(
134: new Object[] { "nullColumn", SqlNull.NULL,
135: "propertyBigDecimal",
136: new BigDecimal("98347.876438637"),
137: "propertyBoolean", new Boolean(true),
138: "propertyByte", new Byte((byte) 16),
139: "propertyCalendar", cal.getTime(),
140: "propertyChar", new Character('M'),
141: "propertyDate", cal, "propertyDouble",
142: new Double(12.3d), "propertyFloat",
143: new Float(13.4f), "propertyInt",
144: new Integer(34), "propertyLong", new Long(45L),
145: "propertyShort", new Short((short) 12),
146: "propertySqlDate",
147: new java.sql.Date(cal.getTime().getTime()),
148: "propertyString", new String("string'value"),
149: "propertyStringbuffer",
150: new StringBuffer("stringbuffer'value"),
151: "propertyTime",
152: new Time(cal.getTime().getTime()),
153: "propertyTimestamp",
154: new Timestamp(cal.getTime().getTime()) });
155: assertEquals(
156: query.getSql(),
157: "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')");
158: assertTrue(execute(query));
159: }
160:
161: public void testFieldParametersFirebird() {
162: Insert query = new Insert(mFirebird);
163: query.into("tablename");
164:
165: assertNull(query.getParameters());
166:
167: query.fieldParameter("nullColumn").fieldParameter(
168: "propertyBigDecimal").fieldParameter("propertyBoolean")
169: .fieldParameter("propertyByte").fieldParameter(
170: "propertyCalendar").fieldParameter(
171: "propertyChar").fieldParameter("propertyDate")
172: .fieldParameter("propertyDouble").fieldParameter(
173: "propertyFloat").fieldParameter("propertyInt")
174: .fieldParameter("propertyLong").fieldParameter(
175: "propertyShort").fieldParameter(
176: "propertySqlDate").fieldParameter(
177: "propertyString").fieldParameter(
178: "propertyStringbuffer").fieldParameter(
179: "propertyTime").fieldParameter(
180: "propertyTimestamp");
181:
182: assertEquals(query.getParameters().getOrderedNames().size(), 17);
183: assertEquals(query.getParameters().getOrderedNames().get(0),
184: "nullColumn");
185: assertEquals(query.getParameters().getOrderedNames().get(1),
186: "propertyBigDecimal");
187: assertEquals(query.getParameters().getOrderedNames().get(2),
188: "propertyBoolean");
189: assertEquals(query.getParameters().getOrderedNames().get(3),
190: "propertyByte");
191: assertEquals(query.getParameters().getOrderedNames().get(4),
192: "propertyCalendar");
193: assertEquals(query.getParameters().getOrderedNames().get(5),
194: "propertyChar");
195: assertEquals(query.getParameters().getOrderedNames().get(6),
196: "propertyDate");
197: assertEquals(query.getParameters().getOrderedNames().get(7),
198: "propertyDouble");
199: assertEquals(query.getParameters().getOrderedNames().get(8),
200: "propertyFloat");
201: assertEquals(query.getParameters().getOrderedNames().get(9),
202: "propertyInt");
203: assertEquals(query.getParameters().getOrderedNames().get(10),
204: "propertyLong");
205: assertEquals(query.getParameters().getOrderedNames().get(11),
206: "propertyShort");
207: assertEquals(query.getParameters().getOrderedNames().get(12),
208: "propertySqlDate");
209: assertEquals(query.getParameters().getOrderedNames().get(13),
210: "propertyString");
211: assertEquals(query.getParameters().getOrderedNames().get(14),
212: "propertyStringbuffer");
213: assertEquals(query.getParameters().getOrderedNames().get(15),
214: "propertyTime");
215: assertEquals(query.getParameters().getOrderedNames().get(16),
216: "propertyTimestamp");
217: assertTrue(Arrays.equals(query.getParameters()
218: .getOrderedNamesArray(), new String[] { "nullColumn",
219: "propertyBigDecimal", "propertyBoolean",
220: "propertyByte", "propertyCalendar", "propertyChar",
221: "propertyDate", "propertyDouble", "propertyFloat",
222: "propertyInt", "propertyLong", "propertyShort",
223: "propertySqlDate", "propertyString",
224: "propertyStringbuffer", "propertyTime",
225: "propertyTimestamp" }));
226:
227: assertEquals(
228: query.getSql(),
229: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
230:
231: assertTrue(execute(query, new DbPreparedStatementHandler() {
232: public void setParameters(DbPreparedStatement statement) {
233: Calendar cal = Calendar.getInstance();
234: cal.set(2002, 7, 19, 12, 17, 52);
235: cal.set(Calendar.MILLISECOND, 462);
236: statement.setString(1, null).setBigDecimal(2,
237: new BigDecimal("98347.876438637")).setBoolean(
238: 3, true).setByte(4, (byte) 16).setDate(5,
239: new java.sql.Date(cal.getTime().getTime()))
240: .setString(6, "M").setDate(
241: 7,
242: new java.sql.Date(cal.getTime()
243: .getTime()))
244: .setDouble(8, 12.3d).setFloat(9, 13.4f).setInt(
245: 10, 34).setLong(11, 45L).setShort(12,
246: (short) 12).setDate(
247: 13,
248: new java.sql.Date(cal.getTime()
249: .getTime())).setString(14,
250: "string'value").setString(15,
251: "string'value2").setTime(16,
252: new Time(cal.getTime().getTime()))
253: .setTimestamp(17,
254: new Timestamp(cal.getTime().getTime()));
255: }
256: }));
257: }
258:
259: public void testFieldParametersMixedFirebird() {
260: Insert query = new Insert(mFirebird);
261: query.into("tablename");
262:
263: assertNull(query.getParameters());
264:
265: final Calendar cal = Calendar.getInstance();
266: cal.set(2002, 7, 19, 12, 17, 52);
267: cal.set(Calendar.MILLISECOND, 462);
268: query.fieldParameter("nullColumn").field("propertyBigDecimal",
269: new BigDecimal("98347.876438637")).fieldParameter(
270: "propertyBoolean").fieldParameter("propertyByte")
271: .field("propertyCalendar", cal.getTime())
272: .fieldParameter("propertyChar").field("propertyDate",
273: cal).field("propertyDouble", 12.3d)
274: .fieldParameter("propertyFloat").fieldParameter(
275: "propertyInt").field("propertyLong", 45L)
276: .field("propertyShort", (short) 12).fieldParameter(
277: "propertySqlDate").fieldParameter(
278: "propertyString").field("propertyStringbuffer",
279: new StringBuffer("stringbuffer'value")).field(
280: "propertyTime",
281: new Time(cal.getTime().getTime()))
282: .fieldParameter("propertyTimestamp");
283:
284: assertEquals(query.getParameters().getOrderedNames().size(), 9);
285: assertEquals(query.getParameters().getOrderedNames().get(0),
286: "nullColumn");
287: assertEquals(query.getParameters().getOrderedNames().get(1),
288: "propertyBoolean");
289: assertEquals(query.getParameters().getOrderedNames().get(2),
290: "propertyByte");
291: assertEquals(query.getParameters().getOrderedNames().get(3),
292: "propertyChar");
293: assertEquals(query.getParameters().getOrderedNames().get(4),
294: "propertyFloat");
295: assertEquals(query.getParameters().getOrderedNames().get(5),
296: "propertyInt");
297: assertEquals(query.getParameters().getOrderedNames().get(6),
298: "propertySqlDate");
299: assertEquals(query.getParameters().getOrderedNames().get(7),
300: "propertyString");
301: assertEquals(query.getParameters().getOrderedNames().get(8),
302: "propertyTimestamp");
303: assertTrue(Arrays.equals(query.getParameters()
304: .getOrderedNamesArray(), new String[] { "nullColumn",
305: "propertyBoolean", "propertyByte", "propertyChar",
306: "propertyFloat", "propertyInt", "propertySqlDate",
307: "propertyString", "propertyTimestamp" }));
308:
309: assertEquals(
310: query.getSql(),
311: "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', ?)");
312:
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 testFieldsBeanFirebird() {
328: Insert query = new Insert(mFirebird);
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 testFieldsBeanConstrainedFirebird() {
337: Insert query = new Insert(mFirebird);
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 testFieldsBeanNullValuesFirebird() {
347: Insert query = new Insert(mFirebird);
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 testFieldsBeanIncludedFirebird() {
356: Insert query = new Insert(mFirebird);
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 testFieldsBeanExcludedFirebird() {
369: Insert query = new Insert(mFirebird);
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 testFieldsBeanFilteredFirebird() {
382: Insert query = new Insert(mFirebird);
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 testMultipleRowsFirebird() {
397: Insert query = new Insert(mFirebird);
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 testFieldsParametersBeanFirebird() {
413: Insert query = new Insert(mPgsql);
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 testFieldsParametersBeanConstrainedFirebird() {
521: Insert query = new Insert(mFirebird);
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 testFieldsParametersBeanExcludedFirebird() {
619: Insert query = new Insert(mPgsql);
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 testInsertSubselectParamsFirebird() {
703: Select fieldquery = new Select(mFirebird);
704: fieldquery.from("table2").field("max(propertyLong)")
705: .whereParameter("propertyInt", ">");
706:
707: // Manual subselect creation
708: Insert query = new Insert(mFirebird);
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(mFirebird);
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 testCloneFirebird() {
749: Select fieldquery = new Select(mFirebird);
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(mFirebird);
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: }
|