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: TestInsertH2.java 3726 2007-04-30 18:20:33Z gbevin $
007: */
008: package com.uwyn.rife.database.queries;
009:
010: import java.math.BigDecimal;
011: import java.sql.Time;
012: import java.sql.Timestamp;
013: import java.util.Arrays;
014: import java.util.Calendar;
015:
016: import com.uwyn.rife.database.BeanImpl;
017: import com.uwyn.rife.database.BeanImplConstrained;
018: import com.uwyn.rife.database.DbPreparedStatement;
019: import com.uwyn.rife.database.DbPreparedStatementHandler;
020: import com.uwyn.rife.database.exceptions.FieldsRequiredException;
021: import com.uwyn.rife.database.exceptions.TableNameRequiredException;
022: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
023: import com.uwyn.rife.database.types.SqlNull;
024:
025: public class TestInsertH2 extends TestInsert {
026: public TestInsertH2(String name) {
027: super (name);
028: }
029:
030: public void testInstantiationH2() {
031: Insert query = new Insert(mH2);
032: assertNotNull(query);
033: try {
034: query.getSql();
035: fail();
036: } catch (TableNameRequiredException e) {
037: assertEquals(e.getQueryName(), "Insert");
038: }
039: }
040:
041: public void testIncompleteQueryH2() {
042: Insert query = new Insert(mH2);
043: try {
044: query.getSql();
045: fail();
046: } catch (TableNameRequiredException e) {
047: assertEquals(e.getQueryName(), "Insert");
048: }
049: query.into("tablename");
050: try {
051: query.getSql();
052: fail();
053: } catch (FieldsRequiredException e) {
054: assertEquals(e.getQueryName(), "Insert");
055: }
056: query.field("col1", "val1");
057: assertNotNull(query.getSql());
058: }
059:
060: public void testClearH2() {
061: Insert query = new Insert(mH2);
062: query.into("tablename").field("col1", "val1");
063: assertNotNull(query.getSql());
064: query.clear();
065: try {
066: query.getSql();
067: fail();
068: } catch (TableNameRequiredException e) {
069: assertEquals(e.getQueryName(), "Insert");
070: }
071: }
072:
073: public void testHintH2() {
074: Insert query = new Insert(mH2).hint("NO_INDEX").into(
075: "tablename").fieldParameter("col1");
076: try {
077: query.getSql();
078: fail();
079: } catch (UnsupportedSqlFeatureException e) {
080: assertTrue(true);
081: }
082: }
083:
084: public void testParameterH2() {
085: Insert query = new Insert(mH2);
086: query.into("tablename").fieldParameter("col1");
087: assertEquals(query.getSql(),
088: "INSERT INTO tablename (col1) VALUES (?)");
089: }
090:
091: public void testFieldH2() {
092: Calendar cal = Calendar.getInstance();
093: cal.set(2002, 7, 19, 12, 17, 52);
094: cal.set(Calendar.MILLISECOND, 462);
095: Insert query = new Insert(mH2);
096: query.into("tablename").field("nullColumn", SqlNull.NULL)
097: .field("propertyBigDecimal",
098: new BigDecimal("98347.876438637")).field(
099: "propertyBoolean", true).field("propertyByte",
100: (byte) 16).field("propertyCalendar",
101: cal.getTime()).field("propertyChar", 'M')
102: .field("propertyDate", cal).field("propertyDouble",
103: 12.3d).field("propertyFloat", 13.4f).field(
104: "propertyInt", 34).field("propertyLong", 45L)
105: .field("propertyShort", (short) 12).field(
106: "propertySqlDate",
107: new java.sql.Date(cal.getTime().getTime()))
108: .field("propertyString", "string'value").field(
109: "propertyStringbuffer",
110: new StringBuffer("stringbuffer'value")).field(
111: "propertyTime",
112: new Time(cal.getTime().getTime())).field(
113: "propertyTimestamp",
114: new Timestamp(cal.getTime().getTime()));
115: assertEquals(
116: query.getSql(),
117: "INSERT INTO tablename (nullColumn, propertyBigDecimal, propertyBoolean, propertyByte, propertyCalendar, propertyChar, propertyDate, propertyDouble, propertyFloat, propertyInt, propertyLong, propertyShort, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp) VALUES (NULL, 98347.876438637, true, 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')");
118: assertTrue(execute(query));
119: }
120:
121: public void testFieldCustomH2() {
122: Insert query = new Insert(mH2);
123: query.into("tablename").fieldCustom("propertySqlDate", "now()");
124: assertEquals(query.getSql(),
125: "INSERT INTO tablename (propertySqlDate) VALUES (now())");
126: assertTrue(execute(query));
127: }
128:
129: public void testFieldsH2() {
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(mH2);
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, true, 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 testFieldParametersH2() {
163: Insert query = new Insert(mH2);
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 testFieldParametersMixedH2() {
261: Insert query = new Insert(mH2);
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 testFieldsBeanH2() {
328: Insert query = new Insert(mH2);
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, true, false, 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 testFieldsBeanConstrainedH2() {
337: Insert query = new Insert(mH2);
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, true, false, 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 testFieldsBeanNullValuesH2() {
347: Insert query = new Insert(mH2);
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 (false, false, 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 testFieldsBeanIncludedH2() {
356: Insert query = new Insert(mH2);
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 testFieldsBeanExcludedH2() {
369: Insert query = new Insert(mH2);
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, true, false, 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 testFieldsBeanFilteredH2() {
382: Insert query = new Insert(mH2);
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 testMultipleRowsH2() {
397: Insert query = new Insert(mH2);
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 testFieldsParametersBeanH2() {
413: Insert query = new Insert(mH2);
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 testFieldsParametersBeanConstrainedH2() {
521: Insert query = new Insert(mH2);
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 testFieldsParametersBeanExcludedH2() {
619: Insert query = new Insert(mH2);
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 testInsertSubselectParamsH2() {
703: Select fieldquery = new Select(mH2);
704: fieldquery.from("table2").field("max(propertyLong)")
705: .whereParameter("propertyInt", ">");
706:
707: // Manual subselect creation
708: Insert query = new Insert(mH2);
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(mH2);
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 testCloneH2() {
749: Select fieldquery = new Select(mH2);
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(mH2);
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: }
|