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: TestInsertPgsql.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 TestInsertPgsql extends TestInsert {
025: public TestInsertPgsql(String name) {
026: super (name);
027: }
028:
029: public void testInstantiationPgsql() {
030: Insert query = new Insert(mPgsql);
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 testIncompleteQueryPgsql() {
041: Insert query = new Insert(mPgsql);
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 testClearPgsql() {
060: Insert query = new Insert(mPgsql);
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 testParameterPgsql() {
073: Insert query = new Insert(mPgsql);
074: query.into("tablename").fieldParameter("col1");
075: assertEquals(query.getSql(),
076: "INSERT INTO tablename (col1) VALUES (?)");
077: }
078:
079: public void testHintPgsql() {
080: Insert query = new Insert(mPgsql).hint("NO_INDEX").into(
081: "tablename").fieldParameter("col1");
082: try {
083: query.getSql();
084: fail();
085: } catch (UnsupportedSqlFeatureException e) {
086: assertTrue(true);
087: }
088: }
089:
090: public void testFieldPgsql() {
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(mPgsql);
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, 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')");
117: assertTrue(execute(query));
118: }
119:
120: public void testFieldCustomPgsql() {
121: Insert query = new Insert(mPgsql);
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 testFieldsPgsql() {
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(mPgsql);
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, 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')");
158: assertTrue(execute(query));
159: }
160:
161: public void testFieldParametersPgsql() {
162: Insert query = new Insert(mPgsql);
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 testFieldParametersMixedPgsql() {
260: Insert query = new Insert(mPgsql);
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: assertTrue(execute(query, new DbPreparedStatementHandler() {
313: public void setParameters(DbPreparedStatement statement) {
314: statement.setString(1, null).setBoolean(2, true)
315: .setByte(3, (byte) 16).setString(4, "M")
316: .setFloat(5, 13.4f).setInt(6, 34).setDate(
317: 7,
318: new java.sql.Date(cal.getTime()
319: .getTime())).setString(8,
320: "string'value").setTimestamp(9,
321: new Timestamp(cal.getTime().getTime()));
322: }
323: }));
324: }
325:
326: public void testFieldsBeanPgsql() {
327: Insert query = new Insert(mPgsql);
328: query.into("tablename").fields(BeanImpl.getPopulatedBean());
329: assertEquals(
330: query.getSql(),
331: "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')");
332: assertTrue(execute(query));
333: }
334:
335: public void testFieldsBeanConstrainedPgsql() {
336: Insert query = new Insert(mPgsql);
337: query.into("tablename").fields(
338: BeanImplConstrained.getPopulatedBean());
339: assertEquals(
340: query.getSql(),
341: "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')");
342: assertTrue(execute(query));
343: }
344:
345: public void testFieldsBeanNullValuesPgsql() {
346: Insert query = new Insert(mPgsql);
347: query.into("tablename").fields(BeanImpl.getNullBean());
348: assertEquals(
349: query.getSql(),
350: "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)");
351: assertTrue(execute(query));
352: }
353:
354: public void testFieldsBeanIncludedPgsql() {
355: Insert query = new Insert(mPgsql);
356: query.into("tablename").fieldsIncluded(
357: BeanImpl.getPopulatedBean(),
358: new String[] { "propertyByte", "propertyDouble",
359: "propertyShort", "propertyStringbuffer",
360: "propertyTime" });
361: assertEquals(
362: query.getSql(),
363: "INSERT INTO tablename (propertyByte, propertyDouble, propertyShort, propertyStringbuffer, propertyTime) VALUES (89, 53348.34, 43, 'someotherstringbuff', '15:26:14')");
364: assertTrue(execute(query));
365: }
366:
367: public void testFieldsBeanExcludedPgsql() {
368: Insert query = new Insert(mPgsql);
369: query.into("tablename").fieldsExcluded(
370: BeanImpl.getPopulatedBean(),
371: new String[] { "propertyByte", "propertyDouble",
372: "propertyShort", "propertyStringbuffer",
373: "propertyTime" });
374: assertEquals(
375: query.getSql(),
376: "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')");
377: assertTrue(execute(query));
378: }
379:
380: public void testFieldsBeanFilteredPgsql() {
381: Insert query = new Insert(mPgsql);
382: query.into("tablename").fieldsFiltered(
383: BeanImpl.getPopulatedBean(),
384: new String[] { "propertyByte", "propertyDouble",
385: "propertyShort", "propertyStringbuffer",
386: "propertyTime" },
387: new String[] { "propertyByte", "propertyShort",
388: "propertyTime" });
389: assertEquals(
390: query.getSql(),
391: "INSERT INTO tablename (propertyDouble, propertyStringbuffer) VALUES (53348.34, 'someotherstringbuff')");
392: assertTrue(execute(query));
393: }
394:
395: public void testMultipleRowsPgsql() {
396: Insert query = new Insert(mPgsql);
397: query.into("tablename").field("propertyChar", 'M').field(
398: "propertyDouble", 12.3d).field("propertyFloat", 13.4f)
399: .field("propertyInt", 34);
400: query.field("propertyChar", 'S').field("propertyDouble", 45.1d)
401: .field("propertyFloat", 27.9f);
402: query.field("propertyChar", 'T');
403: try {
404: query.getSql();
405: fail();
406: } catch (UnsupportedSqlFeatureException e) {
407: assertTrue(true);
408: }
409: }
410:
411: public void testFieldsParametersBeanPgsql() {
412: Insert query = new Insert(mPgsql);
413: query.into("tablename").fieldsParameters(BeanImpl.class);
414: assertEquals(
415: query.getSql(),
416: "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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
417:
418: assertEquals(query.getParameters().getOrderedNames().size(), 25);
419: assertEquals(query.getParameters().getOrderedNames().get(0),
420: "propertyBigDecimal");
421: assertEquals(query.getParameters().getOrderedNames().get(1),
422: "propertyBoolean");
423: assertEquals(query.getParameters().getOrderedNames().get(2),
424: "propertyBooleanObject");
425: assertEquals(query.getParameters().getOrderedNames().get(3),
426: "propertyByte");
427: assertEquals(query.getParameters().getOrderedNames().get(4),
428: "propertyByteObject");
429: assertEquals(query.getParameters().getOrderedNames().get(5),
430: "propertyCalendar");
431: assertEquals(query.getParameters().getOrderedNames().get(6),
432: "propertyChar");
433: assertEquals(query.getParameters().getOrderedNames().get(7),
434: "propertyCharacterObject");
435: assertEquals(query.getParameters().getOrderedNames().get(8),
436: "propertyDate");
437: assertEquals(query.getParameters().getOrderedNames().get(9),
438: "propertyDouble");
439: assertEquals(query.getParameters().getOrderedNames().get(10),
440: "propertyDoubleObject");
441: assertEquals(query.getParameters().getOrderedNames().get(11),
442: "propertyEnum");
443: assertEquals(query.getParameters().getOrderedNames().get(12),
444: "propertyFloat");
445: assertEquals(query.getParameters().getOrderedNames().get(13),
446: "propertyFloatObject");
447: assertEquals(query.getParameters().getOrderedNames().get(14),
448: "propertyInt");
449: assertEquals(query.getParameters().getOrderedNames().get(15),
450: "propertyIntegerObject");
451: assertEquals(query.getParameters().getOrderedNames().get(16),
452: "propertyLong");
453: assertEquals(query.getParameters().getOrderedNames().get(17),
454: "propertyLongObject");
455: assertEquals(query.getParameters().getOrderedNames().get(18),
456: "propertyShort");
457: assertEquals(query.getParameters().getOrderedNames().get(19),
458: "propertyShortObject");
459: assertEquals(query.getParameters().getOrderedNames().get(20),
460: "propertySqlDate");
461: assertEquals(query.getParameters().getOrderedNames().get(21),
462: "propertyString");
463: assertEquals(query.getParameters().getOrderedNames().get(22),
464: "propertyStringbuffer");
465: assertEquals(query.getParameters().getOrderedNames().get(23),
466: "propertyTime");
467: assertEquals(query.getParameters().getOrderedNames().get(24),
468: "propertyTimestamp");
469: assertTrue(Arrays.equals(query.getParameters()
470: .getOrderedNamesArray(), new String[] {
471: "propertyBigDecimal", "propertyBoolean",
472: "propertyBooleanObject", "propertyByte",
473: "propertyByteObject", "propertyCalendar",
474: "propertyChar", "propertyCharacterObject",
475: "propertyDate", "propertyDouble",
476: "propertyDoubleObject", "propertyEnum",
477: "propertyFloat", "propertyFloatObject", "propertyInt",
478: "propertyIntegerObject", "propertyLong",
479: "propertyLongObject", "propertyShort",
480: "propertyShortObject", "propertySqlDate",
481: "propertyString", "propertyStringbuffer",
482: "propertyTime", "propertyTimestamp" }));
483:
484: assertTrue(execute(query, new DbPreparedStatementHandler() {
485: public void setParameters(DbPreparedStatement statement) {
486: Calendar cal = Calendar.getInstance();
487: cal.set(2002, 7, 19, 12, 17, 52);
488: cal.set(Calendar.MILLISECOND, 462);
489: statement.setBigDecimal(1,
490: new BigDecimal("98347.876438637")).setBoolean(
491: 2, false).setBoolean(3, true).setByte(4,
492: (byte) 16).setByte(5, (byte) 72)
493: .setTimestamp(
494: 6,
495: new java.sql.Timestamp(cal.getTime()
496: .getTime())).setString(7, "M")
497: .setString(8, "p").setTimestamp(
498: 9,
499: new java.sql.Timestamp(cal.getTime()
500: .getTime())).setDouble(10,
501: 12.3d).setDouble(11, 68.7d).setString(
502: 12, "VALUE_THREE").setFloat(13, 13.4f)
503: .setFloat(14, 42.1f).setInt(15, 92).setInt(16,
504: 34).setLong(17, 687L).setLong(18, 92)
505: .setShort(19, (short) 7).setShort(20,
506: (short) 12).setDate(
507: 21,
508: new java.sql.Date(cal.getTime()
509: .getTime())).setString(22,
510: "string'value").setString(23,
511: "string'value2").setTime(24,
512: new Time(cal.getTime().getTime()))
513: .setTimestamp(25,
514: new Timestamp(cal.getTime().getTime()));
515: }
516: }));
517: }
518:
519: public void testFieldsParametersBeanConstrainedPgsql() {
520: Insert query = new Insert(mPgsql);
521: query.into("tablename").fieldsParameters(
522: BeanImplConstrained.class);
523: assertEquals(
524: query.getSql(),
525: "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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
526:
527: assertEquals(query.getParameters().getOrderedNames().size(), 21);
528: assertEquals(query.getParameters().getOrderedNames().get(0),
529: "propertyBigDecimal");
530: assertEquals(query.getParameters().getOrderedNames().get(1),
531: "propertyBoolean");
532: assertEquals(query.getParameters().getOrderedNames().get(2),
533: "propertyBooleanObject");
534: assertEquals(query.getParameters().getOrderedNames().get(3),
535: "propertyByteObject");
536: assertEquals(query.getParameters().getOrderedNames().get(4),
537: "propertyCalendar");
538: assertEquals(query.getParameters().getOrderedNames().get(5),
539: "propertyChar");
540: assertEquals(query.getParameters().getOrderedNames().get(6),
541: "propertyCharacterObject");
542: assertEquals(query.getParameters().getOrderedNames().get(7),
543: "propertyDate");
544: assertEquals(query.getParameters().getOrderedNames().get(8),
545: "propertyDouble");
546: assertEquals(query.getParameters().getOrderedNames().get(9),
547: "propertyDoubleObject");
548: assertEquals(query.getParameters().getOrderedNames().get(10),
549: "propertyFloat");
550: assertEquals(query.getParameters().getOrderedNames().get(11),
551: "propertyFloatObject");
552: assertEquals(query.getParameters().getOrderedNames().get(12),
553: "propertyInt");
554: assertEquals(query.getParameters().getOrderedNames().get(13),
555: "propertyIntegerObject");
556: assertEquals(query.getParameters().getOrderedNames().get(14),
557: "propertyLongObject");
558: assertEquals(query.getParameters().getOrderedNames().get(15),
559: "propertyShort");
560: assertEquals(query.getParameters().getOrderedNames().get(16),
561: "propertySqlDate");
562: assertEquals(query.getParameters().getOrderedNames().get(17),
563: "propertyString");
564: assertEquals(query.getParameters().getOrderedNames().get(18),
565: "propertyStringbuffer");
566: assertEquals(query.getParameters().getOrderedNames().get(19),
567: "propertyTime");
568: assertEquals(query.getParameters().getOrderedNames().get(20),
569: "propertyTimestamp");
570: assertTrue(Arrays.equals(query.getParameters()
571: .getOrderedNamesArray(), new String[] {
572: "propertyBigDecimal", "propertyBoolean",
573: "propertyBooleanObject", "propertyByteObject",
574: "propertyCalendar", "propertyChar",
575: "propertyCharacterObject", "propertyDate",
576: "propertyDouble", "propertyDoubleObject",
577: "propertyFloat", "propertyFloatObject", "propertyInt",
578: "propertyIntegerObject", "propertyLongObject",
579: "propertyShort", "propertySqlDate", "propertyString",
580: "propertyStringbuffer", "propertyTime",
581: "propertyTimestamp" }));
582:
583: assertTrue(execute(query, new DbPreparedStatementHandler() {
584: public void setParameters(DbPreparedStatement statement) {
585: Calendar cal = Calendar.getInstance();
586: cal.set(2002, 5, 18, 15, 26, 14);
587: cal.set(Calendar.MILLISECOND, 764);
588: statement.setBigDecimal(1,
589: new BigDecimal("219038743.392874")).setBoolean(
590: 2, true).setBoolean(3, false).setByte(4,
591: (byte) 34)
592: .setTimestamp(
593: 5,
594: new java.sql.Timestamp(cal.getTime()
595: .getTime())).setString(6, "v")
596: .setString(7, "r").setTimestamp(
597: 8,
598: new java.sql.Timestamp(cal.getTime()
599: .getTime())).setDouble(9,
600: 53348.34d).setDouble(10, 143298.692d)
601: .setFloat(11, 98634.2f).setFloat(12, 8734.7f)
602: .setInt(13, 545).setInt(14, 968).setLong(15,
603: 66875L).setShort(16, (short) 43)
604: .setDate(
605: 17,
606: new java.sql.Date(cal.getTime()
607: .getTime())).setString(18,
608: "someotherstring").setString(19,
609: "someotherstringbuff").setTime(20,
610: new Time(cal.getTime().getTime()))
611: .setTimestamp(21,
612: new Timestamp(cal.getTime().getTime()));
613: }
614: }));
615: }
616:
617: public void testFieldsParametersBeanExcludedPgsql() {
618: Insert query = new Insert(mPgsql);
619: query.into("tablename").fieldsParametersExcluded(
620: BeanImpl.class,
621: new String[] { "propertyBoolean", "propertyByte",
622: "propertyChar", "propertyDouble",
623: "propertyInt", "propertyLong",
624: "propertySqlDate", "propertyStringbuffer",
625: "propertyTimestamp" });
626: assertEquals(
627: query.getSql(),
628: "INSERT INTO tablename (propertyBigDecimal, propertyBooleanObject, propertyByteObject, propertyCalendar, propertyCharacterObject, propertyDate, propertyDoubleObject, propertyEnum, propertyFloat, propertyFloatObject, propertyIntegerObject, propertyLongObject, propertyShort, propertyShortObject, propertyString, propertyTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
629:
630: assertEquals(query.getParameters().getOrderedNames().size(), 16);
631: assertEquals(query.getParameters().getOrderedNames().get(0),
632: "propertyBigDecimal");
633: assertEquals(query.getParameters().getOrderedNames().get(1),
634: "propertyBooleanObject");
635: assertEquals(query.getParameters().getOrderedNames().get(2),
636: "propertyByteObject");
637: assertEquals(query.getParameters().getOrderedNames().get(3),
638: "propertyCalendar");
639: assertEquals(query.getParameters().getOrderedNames().get(4),
640: "propertyCharacterObject");
641: assertEquals(query.getParameters().getOrderedNames().get(5),
642: "propertyDate");
643: assertEquals(query.getParameters().getOrderedNames().get(6),
644: "propertyDoubleObject");
645: assertEquals(query.getParameters().getOrderedNames().get(7),
646: "propertyEnum");
647: assertEquals(query.getParameters().getOrderedNames().get(8),
648: "propertyFloat");
649: assertEquals(query.getParameters().getOrderedNames().get(9),
650: "propertyFloatObject");
651: assertEquals(query.getParameters().getOrderedNames().get(10),
652: "propertyIntegerObject");
653: assertEquals(query.getParameters().getOrderedNames().get(11),
654: "propertyLongObject");
655: assertEquals(query.getParameters().getOrderedNames().get(12),
656: "propertyShort");
657: assertEquals(query.getParameters().getOrderedNames().get(13),
658: "propertyShortObject");
659: assertEquals(query.getParameters().getOrderedNames().get(14),
660: "propertyString");
661: assertEquals(query.getParameters().getOrderedNames().get(15),
662: "propertyTime");
663: assertTrue(Arrays.equals(query.getParameters()
664: .getOrderedNamesArray(), new String[] {
665: "propertyBigDecimal", "propertyBooleanObject",
666: "propertyByteObject", "propertyCalendar",
667: "propertyCharacterObject", "propertyDate",
668: "propertyDoubleObject", "propertyEnum",
669: "propertyFloat", "propertyFloatObject",
670: "propertyIntegerObject", "propertyLongObject",
671: "propertyShort", "propertyShortObject",
672: "propertyString", "propertyTime" }));
673:
674: assertTrue(execute(query, new DbPreparedStatementHandler() {
675: public void setParameters(DbPreparedStatement statement) {
676: Calendar cal = Calendar.getInstance();
677: cal.set(2002, 7, 19, 12, 17, 52);
678: cal.set(Calendar.MILLISECOND, 462);
679: statement.setBigDecimal(1,
680: new BigDecimal("98347.876438637")).setBoolean(
681: 2, true).setByte(3, (byte) 72)
682: .setTimestamp(
683: 4,
684: new java.sql.Timestamp(cal.getTime()
685: .getTime())).setString(5, "o")
686: .setTimestamp(
687: 6,
688: new java.sql.Timestamp(cal.getTime()
689: .getTime()))
690: .setDouble(7, 86.7d)
691: .setString(8, "VALUE_THREE").setFloat(9, 13.4f)
692: .setFloat(10, 32.8f).setInt(11, 358).setLong(
693: 12, 9680L).setShort(13, (short) 12)
694: .setShort(14, (short) 78).setString(15,
695: "string'value").setTime(16,
696: new Time(cal.getTime().getTime()));
697: }
698: }));
699: }
700:
701: public void testInsertSubselectParamsPgsql() {
702: Select fieldquery = new Select(mPgsql);
703: fieldquery.from("table2").field("max(propertyLong)")
704: .whereParameter("propertyInt", ">");
705:
706: // Manual subselect creation
707: Insert query = new Insert(mPgsql);
708: // shuffled the structure around a bit to test the correct order usage
709: query.into("tablename").fieldParameter("propertyString")
710: .fieldCustom("propertyLong", "(" + fieldquery + ")")
711: .fieldSubselect(fieldquery);
712: assertEquals(
713: query.getSql(),
714: "INSERT INTO tablename (propertyString, propertyLong) VALUES (?, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?))");
715: String[] parameters = query.getParameters()
716: .getOrderedNamesArray();
717: assertEquals(2, parameters.length);
718: assertEquals(parameters[0], "propertyString");
719: assertEquals(parameters[1], "propertyInt");
720: assertTrue(execute(query, new DbPreparedStatementHandler() {
721: public void setParameters(DbPreparedStatement statement) {
722: statement.setString("propertyString", "thestring")
723: .setLong("propertyInt", 90);
724: }
725: }));
726:
727: // Automated subselect creation
728: query = new Insert(mPgsql);
729: // shuffled the structure around a bit to test the correct order usage
730: query.into("tablename").fieldParameter("propertyString").field(
731: "propertyLong", fieldquery);
732: assertEquals(
733: query.getSql(),
734: "INSERT INTO tablename (propertyString, propertyLong) VALUES (?, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?))");
735: parameters = query.getParameters().getOrderedNamesArray();
736: assertEquals(2, parameters.length);
737: assertEquals(parameters[0], "propertyString");
738: assertEquals(parameters[1], "propertyInt");
739: assertTrue(execute(query, new DbPreparedStatementHandler() {
740: public void setParameters(DbPreparedStatement statement) {
741: statement.setString("propertyString", "thestring")
742: .setLong("propertyInt", 90);
743: }
744: }));
745: }
746:
747: public void testClonePgsql() {
748: Select fieldquery = new Select(mPgsql);
749: fieldquery.from("table2").field("max(propertyLong)")
750: .whereParameter("propertyInt", ">");
751:
752: final Calendar cal = Calendar.getInstance();
753: cal.set(2002, 7, 19, 12, 17, 52);
754: cal.set(Calendar.MILLISECOND, 462);
755: Insert query = new Insert(mPgsql);
756: query.into("tablename").fieldParameter("nullColumn")
757: .field("propertyBigDecimal",
758: new BigDecimal("98347.876438637"))
759: .fieldParameter("propertyBoolean").fieldParameter(
760: "propertyByte").field("propertyCalendar",
761: cal.getTime()).fieldParameter("propertyChar")
762: .field("propertyDate", cal).field("propertyDouble",
763: 12.3d).fieldParameter("propertyFloat")
764: .fieldParameter("propertyInt").field("propertyShort",
765: (short) 12).fieldParameter("propertySqlDate")
766: .fieldParameter("propertyString").field(
767: "propertyStringbuffer",
768: new StringBuffer("stringbuffer'value")).field(
769: "propertyTime",
770: new Time(cal.getTime().getTime()))
771: .fieldParameter("propertyTimestamp").fieldCustom(
772: "propertyLong", "(" + fieldquery + ")")
773: .fieldSubselect(fieldquery);
774:
775: Insert query_clone = query.clone();
776: assertEquals(query.getSql(), query_clone.getSql());
777: assertTrue(query != query_clone);
778: assertTrue(execute(query, new DbPreparedStatementHandler() {
779: public void setParameters(DbPreparedStatement statement) {
780: statement.setString("nullColumn", null).setBoolean(
781: "propertyBoolean", true).setByte(
782: "propertyByte", (byte) 16).setString(
783: "propertyChar", "M").setFloat("propertyFloat",
784: 13.4f).setInt("propertyInt", 34).setDate(
785: "propertySqlDate",
786: new java.sql.Date(cal.getTime().getTime()))
787: .setString("propertyString", "string'value")
788: .setTimestamp("propertyTimestamp",
789: new Timestamp(cal.getTime().getTime()));
790: }
791: }));
792: }
793: }
|