0001: /*
0002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com>
0003: * Distributed under the terms of either:
0004: * - the common development and distribution license (CDDL), v1.0; or
0005: * - the GNU Lesser General Public License, v2.1 or later
0006: * $Id: TestSelectFirebird.java 3695 2007-03-16 09:26:50Z gbevin $
0007: */
0008: package com.uwyn.rife.database.queries;
0009:
0010: import java.math.BigDecimal;
0011: import java.sql.Time;
0012: import java.sql.Timestamp;
0013: import java.util.Arrays;
0014: import java.util.Calendar;
0015:
0016: import com.uwyn.rife.database.BeanImpl;
0017: import com.uwyn.rife.database.BeanImplConstrained;
0018: import com.uwyn.rife.database.DbPreparedStatement;
0019: import com.uwyn.rife.database.DbPreparedStatementHandler;
0020: import com.uwyn.rife.database.exceptions.TableNameOrFieldsRequiredException;
0021: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
0022:
0023: public class TestSelectFirebird extends TestSelect {
0024: public TestSelectFirebird(String name) {
0025: super (name);
0026: }
0027:
0028: public void testInstantiationFirebird() {
0029: Select query = new Select(mFirebird);
0030: assertNotNull(query);
0031: try {
0032: query.getSql();
0033: fail();
0034: } catch (TableNameOrFieldsRequiredException e) {
0035: assertEquals(e.getQueryName(), "Select");
0036: }
0037: }
0038:
0039: public void testIncompleteQueryFirebird() {
0040: Select query = new Select(mFirebird);
0041: try {
0042: query.getSql();
0043: fail();
0044: } catch (TableNameOrFieldsRequiredException e) {
0045: assertEquals(e.getQueryName(), "Select");
0046: }
0047: query.from("tablename");
0048: assertNotNull(query.getSql());
0049:
0050: query = new Select(mFirebird);
0051: try {
0052: query.getSql();
0053: fail();
0054: } catch (TableNameOrFieldsRequiredException e) {
0055: assertEquals(e.getQueryName(), "Select");
0056: }
0057: query.field("field");
0058: assertNotNull(query.getSql());
0059: }
0060:
0061: public void testClearFirebird() {
0062: Select query = new Select(mFirebird);
0063: query.from("tablename");
0064: assertNotNull(query.getSql());
0065: query.clear();
0066: try {
0067: query.getSql();
0068: fail();
0069: } catch (TableNameOrFieldsRequiredException e) {
0070: assertEquals(e.getQueryName(), "Select");
0071: }
0072: }
0073:
0074: public void testBasicFirebird() {
0075: Select query = new Select(mFirebird);
0076: query.from("tablename");
0077: assertEquals(query.getSql(), "SELECT * FROM tablename");
0078: assertTrue(execute(query));
0079: }
0080:
0081: public void testHintFirebird() {
0082: Select query = new Select(mFirebird);
0083: query.hint("NO_INDEX").from("tablename");
0084: try {
0085: query.getSql();
0086: fail();
0087: } catch (UnsupportedSqlFeatureException e) {
0088: assertTrue(true);
0089: }
0090: }
0091:
0092: public void testOrderByAscendingFirebird() {
0093: Select query = new Select(mFirebird);
0094: query.from("tablename").orderBy("propertyInt", Select.ASC);
0095: assertEquals(query.getSql(),
0096: "SELECT * FROM tablename ORDER BY propertyInt ASC");
0097: assertTrue(execute(query));
0098: }
0099:
0100: public void testOrderByDescendingFirebird() {
0101: Select query = new Select(mFirebird);
0102: query.from("tablename").orderBy("propertyInt", Select.DESC);
0103: assertEquals(query.getSql(),
0104: "SELECT * FROM tablename ORDER BY propertyInt DESC");
0105: assertTrue(execute(query));
0106: }
0107:
0108: public void testBeanFirebird() {
0109: Select query = new Select(mFirebird);
0110: query.from("tablename").fields(BeanImpl.class);
0111: assertEquals(
0112: query.getSql(),
0113: "SELECT 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 FROM tablename");
0114: assertTrue(execute(query));
0115: }
0116:
0117: public void testBeanConstrainedFirebird() {
0118: Select query = new Select(mFirebird, BeanImplConstrained.class);
0119: query.from("tablename");
0120: assertEquals(query.getSql(),
0121: "SELECT * FROM tablename ORDER BY propertyString ASC, propertyInt DESC");
0122: assertTrue(execute(query));
0123:
0124: query = new Select(mFirebird, BeanImplConstrained.class);
0125: query.from("tablename").orderBy("propertyByte");
0126: assertEquals(query.getSql(),
0127: "SELECT * FROM tablename ORDER BY propertyByte ASC");
0128: assertTrue(execute(query));
0129: }
0130:
0131: public void testBeanExcludedFirebird() {
0132: Select query = new Select(mFirebird);
0133: query.from("tablename").fieldsExcluded(
0134: BeanImpl.class,
0135: new String[] { "propertyCalendar", "propertyFloat",
0136: "propertyShort" });
0137: assertEquals(
0138: query.getSql(),
0139: "SELECT propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyEnum, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShortObject, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp FROM tablename");
0140: assertTrue(execute(query));
0141: }
0142:
0143: public void testBeanTableFirebird() {
0144: Select query = new Select(mFirebird);
0145: query.from("tablename").fields("tablename", BeanImpl.class);
0146: assertEquals(
0147: query.getSql(),
0148: "SELECT tablename.propertyBigDecimal, tablename.propertyBoolean, tablename.propertyBooleanObject, tablename.propertyByte, tablename.propertyByteObject, tablename.propertyCalendar, tablename.propertyChar, tablename.propertyCharacterObject, tablename.propertyDate, tablename.propertyDouble, tablename.propertyDoubleObject, tablename.propertyEnum, tablename.propertyFloat, tablename.propertyFloatObject, tablename.propertyInt, tablename.propertyIntegerObject, tablename.propertyLong, tablename.propertyLongObject, tablename.propertyShort, tablename.propertyShortObject, tablename.propertySqlDate, tablename.propertyString, tablename.propertyStringbuffer, tablename.propertyTime, tablename.propertyTimestamp FROM tablename");
0149: assertTrue(execute(query));
0150: }
0151:
0152: public void testBeanExcludedTableFirebird() {
0153: Select query = new Select(mFirebird);
0154: query.from("tablename").fieldsExcluded("tablename",
0155: BeanImpl.class, "propertyCalendar", "propertyFloat",
0156: "propertyShort");
0157: assertEquals(
0158: query.getSql(),
0159: "SELECT tablename.propertyBigDecimal, tablename.propertyBoolean, tablename.propertyBooleanObject, tablename.propertyByte, tablename.propertyByteObject, tablename.propertyChar, tablename.propertyCharacterObject, tablename.propertyDate, tablename.propertyDouble, tablename.propertyDoubleObject, tablename.propertyEnum, tablename.propertyFloatObject, tablename.propertyInt, tablename.propertyIntegerObject, tablename.propertyLong, tablename.propertyLongObject, tablename.propertyShortObject, tablename.propertySqlDate, tablename.propertyString, tablename.propertyStringbuffer, tablename.propertyTime, tablename.propertyTimestamp FROM tablename");
0160: assertTrue(execute(query));
0161: }
0162:
0163: public void testWhereTypedFirebird() {
0164: Select query = new Select(mFirebird);
0165: query.from("tablename");
0166:
0167: Calendar cal = Calendar.getInstance();
0168: cal.set(2003, 2, 3, 10, 1, 28);
0169: cal.set(Calendar.MILLISECOND, 154);
0170:
0171: query.where("propertyBigDecimal", ">=",
0172: new BigDecimal("53443433.9784567")).whereAnd(
0173: "propertyBoolean", "=", false).whereOr("propertyByte",
0174: "=", (byte) 54).whereAnd("propertyCalendar", "<=", cal)
0175: .whereOr("propertyChar", "=", 'f').whereAnd(
0176: "propertyDate", "=", cal.getTime()).whereAnd(
0177: "propertyDouble", "!=", 73453.71d).whereOr(
0178: "propertyFloat", ">=", 1987.14f).whereAnd(
0179: "propertyInt", "=", 973).whereAnd(
0180: "propertyLong", "<", 347678L).whereAnd(
0181: "propertyShort", "=", (short) 78).whereOr(
0182: "propertySqlDate", "=",
0183: new java.sql.Date(cal.getTime().getTime()))
0184: .whereAnd("propertyString", "LIKE", "someotherstring%")
0185: .whereAnd("propertyStringbuffer", "=",
0186: new StringBuffer("someotherstringbuff"))
0187: .whereOr("propertyTime", "=",
0188: new Time(cal.getTime().getTime())).whereAnd(
0189: "propertyTimestamp", "<=",
0190: new Timestamp(cal.getTime().getTime()));
0191:
0192: assertEquals(
0193: query.getSql(),
0194: "SELECT * FROM tablename WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = 0 OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = '2003-03-03' AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
0195: assertFalse(execute(query));
0196: }
0197:
0198: public void testWhereTypedMixedFirebird() {
0199: Select query = new Select(mFirebird);
0200: query.from("tablename");
0201:
0202: final Calendar cal = Calendar.getInstance();
0203: cal.set(2003, 2, 3, 10, 1, 28);
0204: cal.set(Calendar.MILLISECOND, 154);
0205:
0206: query.where("propertyBigDecimal", ">=",
0207: new BigDecimal("53443433.9784567")).whereAnd(
0208: "propertyBoolean", "=", false).whereOr(
0209: "propertyByte = 54").whereAnd("propertyCalendar", "<=",
0210: cal).whereOr("propertyChar", "=", 'f').whereAnd(
0211: "propertyDate", "=", cal.getTime()).whereAnd(
0212: "propertyDouble", "!=", 73453.71d).whereOr(
0213: "propertyFloat >= 1987.14").whereAnd("propertyInt",
0214: "=", 973).whereAnd("propertyLong", "<", 347678L)
0215: .whereAnd("propertyShort", "=", (short) 78)
0216: .whereParameterOr("propertySqlDate", "=").whereAnd(
0217: "propertyString", "LIKE", "someotherstring%")
0218: .whereAnd("propertyStringbuffer", "=",
0219: new StringBuffer("someotherstringbuff"))
0220: .whereOr("propertyTime", "=",
0221: new Time(cal.getTime().getTime())).whereAnd(
0222: "propertyTimestamp", "<=",
0223: new Timestamp(cal.getTime().getTime()));
0224:
0225: assertEquals(
0226: query.getSql(),
0227: "SELECT * FROM tablename WHERE propertyBigDecimal >= 53443433.9784567 AND propertyBoolean = 0 OR propertyByte = 54 AND propertyCalendar <= '2003-03-03 10:01:28.154' OR propertyChar = 'f' AND propertyDate = '2003-03-03 10:01:28.154' AND propertyDouble != 73453.71 OR propertyFloat >= 1987.14 AND propertyInt = 973 AND propertyLong < 347678 AND propertyShort = 78 OR propertySqlDate = ? AND propertyString LIKE 'someotherstring%' AND propertyStringbuffer = 'someotherstringbuff' OR propertyTime = '10:01:28' AND propertyTimestamp <= '2003-03-03 10:01:28.154'");
0228:
0229: assertFalse(execute(query, new DbPreparedStatementHandler() {
0230: public void setParameters(DbPreparedStatement statement) {
0231: statement.setDate("propertySqlDate", new java.sql.Date(
0232: cal.getTime().getTime()));
0233: }
0234: }));
0235: }
0236:
0237: public void testWhereParametersFirebird() {
0238: Select query = new Select(mFirebird);
0239: query.from("tablename");
0240:
0241: assertNull(query.getParameters());
0242:
0243: query.whereParameter("propertyInt", "=").whereParameterAnd(
0244: "propertyLong", "<").whereParameterOr("propertyChar",
0245: "=");
0246:
0247: assertEquals(query.getParameters().getOrderedNames().size(), 3);
0248: assertEquals(query.getParameters().getOrderedNames().get(0),
0249: "propertyInt");
0250: assertEquals(query.getParameters().getOrderedNames().get(1),
0251: "propertyLong");
0252: assertEquals(query.getParameters().getOrderedNames().get(2),
0253: "propertyChar");
0254: assertTrue(Arrays.equals(query.getParameters()
0255: .getOrderedNamesArray(), new String[] { "propertyInt",
0256: "propertyLong", "propertyChar" }));
0257:
0258: assertEquals(
0259: query.getSql(),
0260: "SELECT * FROM tablename WHERE propertyInt = ? AND propertyLong < ? OR propertyChar = ?");
0261: assertTrue(execute(query, new DbPreparedStatementHandler() {
0262: public void setParameters(DbPreparedStatement statement) {
0263: statement.setInt(1, 545).setLong(2, 50000).setString(3,
0264: "v");
0265: }
0266: }));
0267:
0268: query.where("propertyInt = 545");
0269:
0270: assertNull(query.getParameters());
0271: assertEquals(query.getSql(),
0272: "SELECT * FROM tablename WHERE propertyInt = 545");
0273: }
0274:
0275: public void testWhereParametersMixedFirebird() {
0276: Select query = new Select(mFirebird);
0277: query.from("tablename").where("propertyInt = 545")
0278: .whereParameterAnd("propertyLong", "<")
0279: .whereParameterOr("propertyChar", "=");
0280:
0281: assertEquals(query.getParameters().getOrderedNames().get(0),
0282: "propertyLong");
0283: assertEquals(query.getParameters().getOrderedNames().get(1),
0284: "propertyChar");
0285: assertTrue(Arrays.equals(query.getParameters()
0286: .getOrderedNamesArray(), new String[] { "propertyLong",
0287: "propertyChar" }));
0288:
0289: assertEquals(
0290: query.getSql(),
0291: "SELECT * FROM tablename WHERE propertyInt = 545 AND propertyLong < ? OR propertyChar = ?");
0292: assertTrue(execute(query, new DbPreparedStatementHandler() {
0293: public void setParameters(DbPreparedStatement statement) {
0294: statement.setLong(1, 50000).setString(2, "v");
0295: }
0296: }));
0297: }
0298:
0299: public void testWhereConstructionFirebird() {
0300: Select query = new Select(mFirebird);
0301: query.from("tablename").where("propertyInt = 545").whereAnd(
0302: "propertyLong < 50000").whereOr("propertyChar = 'v'");
0303: assertEquals(
0304: query.getSql(),
0305: "SELECT * FROM tablename WHERE propertyInt = 545 AND propertyLong < 50000 OR propertyChar = 'v'");
0306: assertTrue(execute(query));
0307: }
0308:
0309: public void testWhereConstructionGroupFirebird() {
0310: Select query = new Select(mFirebird);
0311: query.from("tablename").startWhere().where("propertyInt", "=",
0312: 545).whereAnd("propertyByte", "=", 89).end().whereAnd(
0313: "propertyLong < 50000").startWhereOr().whereParameter(
0314: "propertyString", "=").whereAnd("propertyByte", "<=",
0315: (byte) 0).startWhereAnd().where("propertyBoolean",
0316: "!=", true).whereParameterOr("propertyStringbuffer",
0317: "LIKE").end().end().whereOr("propertyChar = 'v'");
0318:
0319: assertEquals(query.getParameters().getOrderedNames().get(0),
0320: "propertyString");
0321: assertEquals(query.getParameters().getOrderedNames().get(1),
0322: "propertyStringbuffer");
0323: assertTrue(Arrays.equals(query.getParameters()
0324: .getOrderedNamesArray(), new String[] {
0325: "propertyString", "propertyStringbuffer" }));
0326:
0327: assertEquals(
0328: query.getSql(),
0329: "SELECT * FROM tablename WHERE (propertyInt = 545 AND propertyByte = 89) AND propertyLong < 50000 OR (propertyString = ? AND propertyByte <= 0 AND (propertyBoolean != 1 OR propertyStringbuffer LIKE ?)) OR propertyChar = 'v'");
0330:
0331: assertTrue(execute(query, new DbPreparedStatementHandler() {
0332: public void setParameters(DbPreparedStatement statement) {
0333: statement
0334: .setString("propertyString", "someotherstring")
0335: .setString("propertyStringbuffer", "stringbuff");
0336: }
0337: }));
0338: }
0339:
0340: public void testWhereBeanFirebird() {
0341: Select query = new Select(mFirebird);
0342: query.from("tablename").where(BeanImpl.getPopulatedBean());
0343: assertEquals(
0344: query.getSql(),
0345: "SELECT * FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
0346: assertTrue(execute(query));
0347: }
0348:
0349: public void testWhereBeanConstrainedFirebird() {
0350: Select query = new Select(mFirebird);
0351: query.from("tablename").where(
0352: BeanImplConstrained.getPopulatedBean());
0353: assertEquals(
0354: query.getSql(),
0355: "SELECT * FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByte = 89 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDouble = 53348.34 AND propertyDoubleObject = 143298.692 AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLongObject = 66875 AND propertyShort = 43 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
0356: assertTrue(execute(query));
0357: }
0358:
0359: public void testWhereBeanNullValuesFirebird() {
0360: Select query = new Select(mFirebird);
0361: query.from("tablename").where(BeanImpl.getNullBean());
0362: assertEquals(
0363: query.getSql(),
0364: "SELECT * FROM tablename WHERE propertyBoolean = 0 AND propertyBooleanObject = 0 AND propertyByte = 0 AND propertyByteObject = 0 AND propertyDouble = 0.0 AND propertyDoubleObject = 0.0 AND propertyFloat = 0.0 AND propertyFloatObject = 0.0 AND propertyInt = 0 AND propertyIntegerObject = 0 AND propertyLong = 0 AND propertyLongObject = 0 AND propertyShort = 0 AND propertyShortObject = 0");
0365: assertTrue(execute(query));
0366: }
0367:
0368: public void testWhereBeanIncludedFirebird() {
0369: Select query = new Select(mFirebird);
0370: query.from("tablename").whereIncluded(
0371: BeanImpl.getPopulatedBean(),
0372: new String[] { "propertyByte", "propertyDouble",
0373: "propertyShort", "propertyStringbuffer",
0374: "propertyTime" });
0375: assertEquals(
0376: query.getSql(),
0377: "SELECT * FROM tablename WHERE propertyByte = 89 AND propertyDouble = 53348.34 AND propertyShort = 43 AND propertyStringbuffer = 'someotherstringbuff' AND propertyTime = '15:26:14'");
0378: assertTrue(execute(query));
0379: }
0380:
0381: public void testWhereBeanExcludedFirebird() {
0382: Select query = new Select(mFirebird);
0383: query.from("tablename").whereExcluded(
0384: BeanImpl.getPopulatedBean(),
0385: new String[] { "propertyByte", "propertyDouble",
0386: "propertyShort", "propertyStringbuffer",
0387: "propertyTime" });
0388: assertEquals(
0389: query.getSql(),
0390: "SELECT * FROM tablename WHERE propertyBigDecimal = 219038743.392874 AND propertyBoolean = 1 AND propertyBooleanObject = 0 AND propertyByteObject = 34 AND propertyCalendar = '2002-06-18 15:26:14.764' AND propertyChar = 'v' AND propertyCharacterObject = 'r' AND propertyDate = '2002-06-18 15:26:14.764' AND propertyDoubleObject = 143298.692 AND propertyEnum = 'VALUE_THREE' AND propertyFloat = 98634.2 AND propertyFloatObject = 8734.7 AND propertyInt = 545 AND propertyIntegerObject = 968 AND propertyLong = 34563 AND propertyLongObject = 66875 AND propertyShortObject = 68 AND propertySqlDate = '2002-06-18' AND propertyString = 'someotherstring' AND propertyTimestamp = '2002-06-18 15:26:14.764'");
0391: assertTrue(execute(query));
0392: }
0393:
0394: public void testWhereBeanFilteredFirebird() {
0395: Select query = new Select(mFirebird);
0396: query.from("tablename").whereFiltered(
0397: BeanImpl.getPopulatedBean(),
0398: new String[] { "propertyByte", "propertyDouble",
0399: "propertyShort", "propertyStringbuffer",
0400: "propertyTime" },
0401: new String[] { "propertyByte", "propertyShort",
0402: "propertyTime" });
0403: assertEquals(
0404: query.getSql(),
0405: "SELECT * FROM tablename WHERE propertyDouble = 53348.34 AND propertyStringbuffer = 'someotherstringbuff'");
0406: assertTrue(execute(query));
0407: }
0408:
0409: public void testWhereParametersBeanFirebird() {
0410: Select query = new Select(mFirebird);
0411: query.from("tablename").whereParameters(BeanImpl.class);
0412: assertEquals(
0413: query.getSql(),
0414: "SELECT * FROM tablename WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLong = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
0415:
0416: assertEquals(query.getParameters().getOrderedNames().size(), 25);
0417: assertEquals(query.getParameters().getOrderedNames().get(0),
0418: "propertyBigDecimal");
0419: assertEquals(query.getParameters().getOrderedNames().get(1),
0420: "propertyBoolean");
0421: assertEquals(query.getParameters().getOrderedNames().get(2),
0422: "propertyBooleanObject");
0423: assertEquals(query.getParameters().getOrderedNames().get(3),
0424: "propertyByte");
0425: assertEquals(query.getParameters().getOrderedNames().get(4),
0426: "propertyByteObject");
0427: assertEquals(query.getParameters().getOrderedNames().get(5),
0428: "propertyCalendar");
0429: assertEquals(query.getParameters().getOrderedNames().get(6),
0430: "propertyChar");
0431: assertEquals(query.getParameters().getOrderedNames().get(7),
0432: "propertyCharacterObject");
0433: assertEquals(query.getParameters().getOrderedNames().get(8),
0434: "propertyDate");
0435: assertEquals(query.getParameters().getOrderedNames().get(9),
0436: "propertyDouble");
0437: assertEquals(query.getParameters().getOrderedNames().get(10),
0438: "propertyDoubleObject");
0439: assertEquals(query.getParameters().getOrderedNames().get(11),
0440: "propertyEnum");
0441: assertEquals(query.getParameters().getOrderedNames().get(12),
0442: "propertyFloat");
0443: assertEquals(query.getParameters().getOrderedNames().get(13),
0444: "propertyFloatObject");
0445: assertEquals(query.getParameters().getOrderedNames().get(14),
0446: "propertyInt");
0447: assertEquals(query.getParameters().getOrderedNames().get(15),
0448: "propertyIntegerObject");
0449: assertEquals(query.getParameters().getOrderedNames().get(16),
0450: "propertyLong");
0451: assertEquals(query.getParameters().getOrderedNames().get(17),
0452: "propertyLongObject");
0453: assertEquals(query.getParameters().getOrderedNames().get(18),
0454: "propertyShort");
0455: assertEquals(query.getParameters().getOrderedNames().get(19),
0456: "propertyShortObject");
0457: assertEquals(query.getParameters().getOrderedNames().get(20),
0458: "propertySqlDate");
0459: assertEquals(query.getParameters().getOrderedNames().get(21),
0460: "propertyString");
0461: assertEquals(query.getParameters().getOrderedNames().get(22),
0462: "propertyStringbuffer");
0463: assertEquals(query.getParameters().getOrderedNames().get(23),
0464: "propertyTime");
0465: assertEquals(query.getParameters().getOrderedNames().get(24),
0466: "propertyTimestamp");
0467: assertTrue(Arrays.equals(query.getParameters()
0468: .getOrderedNamesArray(), new String[] {
0469: "propertyBigDecimal", "propertyBoolean",
0470: "propertyBooleanObject", "propertyByte",
0471: "propertyByteObject", "propertyCalendar",
0472: "propertyChar", "propertyCharacterObject",
0473: "propertyDate", "propertyDouble",
0474: "propertyDoubleObject", "propertyEnum",
0475: "propertyFloat", "propertyFloatObject", "propertyInt",
0476: "propertyIntegerObject", "propertyLong",
0477: "propertyLongObject", "propertyShort",
0478: "propertyShortObject", "propertySqlDate",
0479: "propertyString", "propertyStringbuffer",
0480: "propertyTime", "propertyTimestamp" }));
0481:
0482: // don't check the result since Firebird doesn't match exactly on float
0483: execute(query, new DbPreparedStatementHandler() {
0484: public void setParameters(DbPreparedStatement statement) {
0485: Calendar cal = Calendar.getInstance();
0486: cal.set(2002, 5, 18, 15, 26, 14);
0487: cal.set(Calendar.MILLISECOND, 764);
0488: statement.setBigDecimal(1,
0489: new BigDecimal("219038743.392874")).setBoolean(
0490: 2, true).setBoolean(3, false).setByte(4,
0491: (byte) 89).setByte(5, (byte) 34)
0492: .setTimestamp(
0493: 6,
0494: new java.sql.Timestamp(cal.getTime()
0495: .getTime())).setString(7, "v")
0496: .setString(8, "r").setTimestamp(
0497: 9,
0498: new java.sql.Timestamp(cal.getTime()
0499: .getTime())).setDouble(10,
0500: 53348.34d).setDouble(11, 143298.692d)
0501: .setString(12, "VALUE_THREE").setFloat(13,
0502: 98634.2f).setFloat(14, 8734.7f).setInt(
0503: 15, 545).setInt(16, 968).setLong(17,
0504: 34563L).setLong(18, 66875L).setShort(
0505: 19, (short) 43)
0506: .setShort(20, (short) 68).setDate(
0507: 21,
0508: new java.sql.Date(cal.getTime()
0509: .getTime())).setString(22,
0510: "someotherstring").setString(23,
0511: "someotherstringbuff").setTime(24,
0512: new Time(cal.getTime().getTime()))
0513: .setTimestamp(25,
0514: new Timestamp(cal.getTime().getTime()));
0515: }
0516: });
0517: }
0518:
0519: public void testWhereParametersBeanConstrainedFirebird() {
0520: Select query = new Select(mFirebird);
0521: query.from("tablename").whereParameters(
0522: BeanImplConstrained.class);
0523: assertEquals(
0524: query.getSql(),
0525: "SELECT * FROM tablename WHERE propertyBigDecimal = ? AND propertyBoolean = ? AND propertyBooleanObject = ? AND propertyByte = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyChar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDouble = ? AND propertyDoubleObject = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyInt = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertySqlDate = ? AND propertyString = ? AND propertyStringbuffer = ? AND propertyTime = ? AND propertyTimestamp = ?");
0526:
0527: assertEquals(query.getParameters().getOrderedNames().size(), 22);
0528: assertEquals(query.getParameters().getOrderedNames().get(0),
0529: "propertyBigDecimal");
0530: assertEquals(query.getParameters().getOrderedNames().get(1),
0531: "propertyBoolean");
0532: assertEquals(query.getParameters().getOrderedNames().get(2),
0533: "propertyBooleanObject");
0534: assertEquals(query.getParameters().getOrderedNames().get(3),
0535: "propertyByte");
0536: assertEquals(query.getParameters().getOrderedNames().get(4),
0537: "propertyByteObject");
0538: assertEquals(query.getParameters().getOrderedNames().get(5),
0539: "propertyCalendar");
0540: assertEquals(query.getParameters().getOrderedNames().get(6),
0541: "propertyChar");
0542: assertEquals(query.getParameters().getOrderedNames().get(7),
0543: "propertyCharacterObject");
0544: assertEquals(query.getParameters().getOrderedNames().get(8),
0545: "propertyDate");
0546: assertEquals(query.getParameters().getOrderedNames().get(9),
0547: "propertyDouble");
0548: assertEquals(query.getParameters().getOrderedNames().get(10),
0549: "propertyDoubleObject");
0550: assertEquals(query.getParameters().getOrderedNames().get(11),
0551: "propertyFloat");
0552: assertEquals(query.getParameters().getOrderedNames().get(12),
0553: "propertyFloatObject");
0554: assertEquals(query.getParameters().getOrderedNames().get(13),
0555: "propertyInt");
0556: assertEquals(query.getParameters().getOrderedNames().get(14),
0557: "propertyIntegerObject");
0558: assertEquals(query.getParameters().getOrderedNames().get(15),
0559: "propertyLongObject");
0560: assertEquals(query.getParameters().getOrderedNames().get(16),
0561: "propertyShort");
0562: assertEquals(query.getParameters().getOrderedNames().get(17),
0563: "propertySqlDate");
0564: assertEquals(query.getParameters().getOrderedNames().get(18),
0565: "propertyString");
0566: assertEquals(query.getParameters().getOrderedNames().get(19),
0567: "propertyStringbuffer");
0568: assertEquals(query.getParameters().getOrderedNames().get(20),
0569: "propertyTime");
0570: assertEquals(query.getParameters().getOrderedNames().get(21),
0571: "propertyTimestamp");
0572: assertTrue(Arrays.equals(query.getParameters()
0573: .getOrderedNamesArray(), new String[] {
0574: "propertyBigDecimal", "propertyBoolean",
0575: "propertyBooleanObject", "propertyByte",
0576: "propertyByteObject", "propertyCalendar",
0577: "propertyChar", "propertyCharacterObject",
0578: "propertyDate", "propertyDouble",
0579: "propertyDoubleObject", "propertyFloat",
0580: "propertyFloatObject", "propertyInt",
0581: "propertyIntegerObject", "propertyLongObject",
0582: "propertyShort", "propertySqlDate", "propertyString",
0583: "propertyStringbuffer", "propertyTime",
0584: "propertyTimestamp" }));
0585:
0586: // don't check the result since Firebird doesn't match exactly on float
0587: execute(query, new DbPreparedStatementHandler() {
0588: public void setParameters(DbPreparedStatement statement) {
0589: Calendar cal = Calendar.getInstance();
0590: cal.set(2002, 5, 18, 15, 26, 14);
0591: cal.set(Calendar.MILLISECOND, 764);
0592: statement.setBigDecimal(1,
0593: new BigDecimal("219038743.392874")).setBoolean(
0594: 2, true).setBoolean(3, false).setByte(4,
0595: (byte) 89).setByte(5, (byte) 34)
0596: .setTimestamp(
0597: 6,
0598: new java.sql.Timestamp(cal.getTime()
0599: .getTime())).setString(7, "v")
0600: .setString(8, "r").setTimestamp(
0601: 9,
0602: new java.sql.Timestamp(cal.getTime()
0603: .getTime())).setDouble(10,
0604: 53348.34d).setDouble(11, 143298.692d)
0605: .setFloat(12, 98634.2f).setFloat(13, 8734.7f)
0606: .setInt(14, 545).setInt(15, 968).setLong(16,
0607: 66875L).setShort(17, (short) 43)
0608: .setDate(
0609: 18,
0610: new java.sql.Date(cal.getTime()
0611: .getTime())).setString(19,
0612: "someotherstring").setString(20,
0613: "someotherstringbuff").setTime(21,
0614: new Time(cal.getTime().getTime()))
0615: .setTimestamp(22,
0616: new Timestamp(cal.getTime().getTime()));
0617: }
0618: });
0619: }
0620:
0621: public void testWhereParametersBeanExcludedFirebird() {
0622: Select query = new Select(mFirebird);
0623: query.from("tablename").whereParametersExcluded(
0624: BeanImpl.class,
0625: new String[] { "propertyBoolean", "propertyByte",
0626: "propertyChar", "propertyDouble",
0627: "propertyInt", "propertyLong",
0628: "propertySqlDate", "propertyStringbuffer",
0629: "propertyTimestamp" });
0630: assertEquals(
0631: query.getSql(),
0632: "SELECT * FROM tablename WHERE propertyBigDecimal = ? AND propertyBooleanObject = ? AND propertyByteObject = ? AND propertyCalendar = ? AND propertyCharacterObject = ? AND propertyDate = ? AND propertyDoubleObject = ? AND propertyEnum = ? AND propertyFloat = ? AND propertyFloatObject = ? AND propertyIntegerObject = ? AND propertyLongObject = ? AND propertyShort = ? AND propertyShortObject = ? AND propertyString = ? AND propertyTime = ?");
0633:
0634: assertEquals(query.getParameters().getOrderedNames().size(), 16);
0635: assertEquals(query.getParameters().getOrderedNames().get(0),
0636: "propertyBigDecimal");
0637: assertEquals(query.getParameters().getOrderedNames().get(1),
0638: "propertyBooleanObject");
0639: assertEquals(query.getParameters().getOrderedNames().get(2),
0640: "propertyByteObject");
0641: assertEquals(query.getParameters().getOrderedNames().get(3),
0642: "propertyCalendar");
0643: assertEquals(query.getParameters().getOrderedNames().get(4),
0644: "propertyCharacterObject");
0645: assertEquals(query.getParameters().getOrderedNames().get(5),
0646: "propertyDate");
0647: assertEquals(query.getParameters().getOrderedNames().get(6),
0648: "propertyDoubleObject");
0649: assertEquals(query.getParameters().getOrderedNames().get(7),
0650: "propertyEnum");
0651: assertEquals(query.getParameters().getOrderedNames().get(8),
0652: "propertyFloat");
0653: assertEquals(query.getParameters().getOrderedNames().get(9),
0654: "propertyFloatObject");
0655: assertEquals(query.getParameters().getOrderedNames().get(10),
0656: "propertyIntegerObject");
0657: assertEquals(query.getParameters().getOrderedNames().get(11),
0658: "propertyLongObject");
0659: assertEquals(query.getParameters().getOrderedNames().get(12),
0660: "propertyShort");
0661: assertEquals(query.getParameters().getOrderedNames().get(13),
0662: "propertyShortObject");
0663: assertEquals(query.getParameters().getOrderedNames().get(14),
0664: "propertyString");
0665: assertEquals(query.getParameters().getOrderedNames().get(15),
0666: "propertyTime");
0667: assertTrue(Arrays.equals(query.getParameters()
0668: .getOrderedNamesArray(), new String[] {
0669: "propertyBigDecimal", "propertyBooleanObject",
0670: "propertyByteObject", "propertyCalendar",
0671: "propertyCharacterObject", "propertyDate",
0672: "propertyDoubleObject", "propertyEnum",
0673: "propertyFloat", "propertyFloatObject",
0674: "propertyIntegerObject", "propertyLongObject",
0675: "propertyShort", "propertyShortObject",
0676: "propertyString", "propertyTime" }));
0677:
0678: // don't check the result since Firebird doesn't match exactly on float
0679: execute(query, new DbPreparedStatementHandler() {
0680: public void setParameters(DbPreparedStatement statement) {
0681: Calendar cal = Calendar.getInstance();
0682: cal.set(2002, 5, 18, 15, 26, 14);
0683: cal.set(Calendar.MILLISECOND, 764);
0684: statement.setBigDecimal(1,
0685: new BigDecimal("219038743.392874")).setBoolean(
0686: 2, false).setByte(3, (byte) 34)
0687: .setTimestamp(
0688: 4,
0689: new java.sql.Timestamp(cal.getTime()
0690: .getTime())).setString(5, "r")
0691: .setTimestamp(
0692: 6,
0693: new java.sql.Timestamp(cal.getTime()
0694: .getTime())).setDouble(7,
0695: 143298.692d)
0696: .setString(8, "VALUE_THREE").setFloat(9,
0697: 98634.2f).setFloat(10, 8734.7f).setInt(
0698: 11, 968).setLong(12, 66875L).setShort(
0699: 13, (short) 43)
0700: .setShort(14, (short) 68).setString(15,
0701: "someotherstring").setTime(16,
0702: new Time(cal.getTime().getTime()));
0703: }
0704: });
0705: }
0706:
0707: public void testDistinctFirebird() {
0708: Select query = new Select(mFirebird);
0709: query.from("tablename").distinct().where("propertyByte = 89")
0710: .orderBy("propertyDouble").orderBy("propertyShort")
0711: .orderBy("propertyTime");
0712: assertEquals(
0713: query.getSql(),
0714: "SELECT DISTINCT * FROM tablename WHERE propertyByte = 89 ORDER BY propertyDouble ASC, propertyShort ASC, propertyTime ASC");
0715: assertTrue(execute(query));
0716: }
0717:
0718: public void testDistinctOnFirebird() {
0719: Select query = new Select(mFirebird);
0720: query.from("tablename").distinctOn("propertyDouble")
0721: .distinctOn("propertyShort").distinctOn("propertyTime")
0722: .where("propertyByte = 89").orderBy("propertyDouble")
0723: .orderBy("propertyShort").orderBy("propertyTime");
0724: try {
0725: query.getSql();
0726: fail();
0727: } catch (UnsupportedSqlFeatureException e) {
0728: assertEquals("DISTINCT ON", e.getFeature());
0729: }
0730: }
0731:
0732: public void testComplexFirebird() {
0733: Select query = new Select(mFirebird);
0734: query.from("tablename").field("field1").field("field2").field(
0735: "field3").join("table2").joinInner("table3", Select.ON,
0736: "table2.field1 = table3.field1").where("this = that")
0737: .groupBy("gexpr1").groupBy("gexpr2").having("hexpr1")
0738: .having("hexpr2").distinct().unionAll("uexpr1").union(
0739: "uexpr2").limit(3).offset(1);
0740: assertEquals(
0741: query.getSql(),
0742: "SELECT FIRST 3 SKIP 1 DISTINCT field1, field2, field3 FROM tablename, table2 INNER JOIN table3 ON (table2.field1 = table3.field1) WHERE this = that GROUP BY gexpr1, gexpr2 HAVING hexpr1, hexpr2 UNION ALL uexpr1 UNION uexpr2");
0743: }
0744:
0745: public void testGroupByBeanFirebird() {
0746: Select query = new Select(mFirebird);
0747: query.from("tablename").fields(BeanImpl.class).groupBy(
0748: BeanImpl.class);
0749: assertEquals(
0750: query.getSql(),
0751: "SELECT 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 FROM tablename GROUP BY 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");
0752: assertTrue(execute(query));
0753: }
0754:
0755: public void testGroupByBeanExcludedFirebird() {
0756: Select query = new Select(mFirebird);
0757: query.from("tablename").fieldsExcluded(
0758: BeanImpl.class,
0759: new String[] { "propertyCalendar", "propertyFloat",
0760: "propertyShort" }).groupByExcluded(
0761: BeanImpl.class,
0762: new String[] { "propertyCalendar", "propertyFloat",
0763: "propertyShort" });
0764: assertEquals(
0765: query.getSql(),
0766: "SELECT propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyEnum, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShortObject, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp FROM tablename GROUP BY propertyBigDecimal, propertyBoolean, propertyBooleanObject, propertyByte, propertyByteObject, propertyChar, propertyCharacterObject, propertyDate, propertyDouble, propertyDoubleObject, propertyEnum, propertyFloatObject, propertyInt, propertyIntegerObject, propertyLong, propertyLongObject, propertyShortObject, propertySqlDate, propertyString, propertyStringbuffer, propertyTime, propertyTimestamp");
0767: assertTrue(execute(query));
0768: }
0769:
0770: public void testJoinFirebird() {
0771: Select query = new Select(mFirebird);
0772: query.from("tablename").join("table2").join("table3");
0773: assertEquals(query.getSql(),
0774: "SELECT * FROM tablename, table2, table3");
0775: assertTrue(execute(query));
0776: }
0777:
0778: public void testJoinCustomFirebird() {
0779: Select query = new Select(mFirebird);
0780: query
0781: .from("tablename")
0782: .joinCustom(
0783: "INNER JOIN table3 ON (tablename.propertyInt = table3.propertyInt)")
0784: .joinCustom(
0785: "INNER JOIN table2 ON (table3.propertyInt = table2.propertyInt)");
0786: assertEquals(
0787: query.getSql(),
0788: "SELECT * FROM tablename INNER JOIN table3 ON (tablename.propertyInt = table3.propertyInt) INNER JOIN table2 ON (table3.propertyInt = table2.propertyInt)");
0789: assertTrue(execute(query));
0790: }
0791:
0792: public void testJoinCrossFirebird() {
0793: Select query = new Select(mFirebird);
0794: query.from("tablename").joinCross("table2").joinCross("table3");
0795: try {
0796: query.getSql();
0797: fail();
0798: } catch (UnsupportedSqlFeatureException e) {
0799: assertEquals("CROSS JOIN", e.getFeature());
0800: }
0801:
0802: try {
0803: query.getSql();
0804: fail();
0805: } catch (UnsupportedSqlFeatureException e) {
0806: assertEquals("CROSS JOIN", e.getFeature());
0807: }
0808: }
0809:
0810: public void testJoinInnerFirebird() {
0811: Select query = new Select(mFirebird);
0812: query.from("tablename").joinInner("table2", Select.NATURAL,
0813: null);
0814: try {
0815: query.getSql();
0816: fail();
0817: } catch (UnsupportedSqlFeatureException e) {
0818: assertEquals("NATURAL for INNER JOIN", e.getFeature());
0819: }
0820:
0821: query.clear();
0822: query.from("tablename").joinInner("table2", Select.ON,
0823: "tablename.propertyInt = table2.propertyInt");
0824: assertEquals(
0825: query.getSql(),
0826: "SELECT * FROM tablename INNER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0827: assertTrue(execute(query));
0828: query.clear();
0829:
0830: query.from("tablename").joinInner("table2", Select.USING,
0831: "propertyInt");
0832: try {
0833: query.getSql();
0834: fail();
0835: } catch (UnsupportedSqlFeatureException e) {
0836: assertEquals("USING for INNER JOIN", e.getFeature());
0837: }
0838: }
0839:
0840: public void testJoinOuterFirebird() {
0841: Select query = new Select(mFirebird);
0842:
0843: query.from("tablename").joinOuter("table2", Select.FULL,
0844: Select.NATURAL, null);
0845: try {
0846: query.getSql();
0847: fail();
0848: } catch (UnsupportedSqlFeatureException e) {
0849: assertEquals("NATURAL for OUTER JOIN", e.getFeature());
0850: }
0851:
0852: query.clear();
0853: query.from("tablename").joinOuter("table2", Select.LEFT,
0854: Select.NATURAL, null);
0855: try {
0856: query.getSql();
0857: fail();
0858: } catch (UnsupportedSqlFeatureException e) {
0859: assertEquals("NATURAL for OUTER JOIN", e.getFeature());
0860: }
0861:
0862: query.clear();
0863: query.from("tablename").joinOuter("table2", Select.RIGHT,
0864: Select.NATURAL, null);
0865: try {
0866: query.getSql();
0867: fail();
0868: } catch (UnsupportedSqlFeatureException e) {
0869: assertEquals("NATURAL for OUTER JOIN", e.getFeature());
0870: }
0871:
0872: query.clear();
0873: query.from("tablename")
0874: .joinOuter("table2", Select.FULL, Select.ON,
0875: "tablename.propertyInt = table2.propertyInt");
0876: assertEquals(
0877: query.getSql(),
0878: "SELECT * FROM tablename FULL OUTER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0879: assertTrue(execute(query));
0880: query.clear();
0881: query.from("tablename")
0882: .joinOuter("table2", Select.LEFT, Select.ON,
0883: "tablename.propertyInt = table2.propertyInt");
0884: assertEquals(
0885: query.getSql(),
0886: "SELECT * FROM tablename LEFT OUTER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0887: assertTrue(execute(query));
0888: query.clear();
0889: query.from("tablename")
0890: .joinOuter("table2", Select.RIGHT, Select.ON,
0891: "tablename.propertyInt = table2.propertyInt");
0892: assertEquals(
0893: query.getSql(),
0894: "SELECT * FROM tablename RIGHT OUTER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0895: assertTrue(execute(query));
0896: query.clear();
0897:
0898: query.from("tablename").joinOuter("table2", Select.FULL,
0899: Select.USING, "propertyInt");
0900: try {
0901: query.getSql();
0902: fail();
0903: } catch (UnsupportedSqlFeatureException e) {
0904: assertEquals("USING for OUTER JOIN", e.getFeature());
0905: }
0906:
0907: query.clear();
0908: query.from("tablename").joinOuter("table2", Select.LEFT,
0909: Select.USING, "propertyInt");
0910: try {
0911: query.getSql();
0912: fail();
0913: } catch (UnsupportedSqlFeatureException e) {
0914: assertEquals("USING for OUTER JOIN", e.getFeature());
0915: }
0916:
0917: query.clear();
0918: query.from("tablename").joinOuter("table2", Select.RIGHT,
0919: Select.USING, "propertyInt");
0920: try {
0921: query.getSql();
0922: fail();
0923: } catch (UnsupportedSqlFeatureException e) {
0924: assertEquals("USING for OUTER JOIN", e.getFeature());
0925: }
0926: query.clear();
0927: }
0928:
0929: public void testLimitFirebird() {
0930: Select query = new Select(mFirebird);
0931: query.from("tablename").limit(3);
0932: assertEquals(query.getSql(), "SELECT FIRST 3 * FROM tablename");
0933: assertTrue(execute(query));
0934: query.offset(1);
0935: assertEquals(query.getSql(),
0936: "SELECT FIRST 3 SKIP 1 * FROM tablename");
0937: assertTrue(execute(query));
0938: query.clear();
0939: query.from("tablename").offset(10);
0940: assertEquals(query.getSql(), "SELECT * FROM tablename");
0941: assertTrue(execute(query));
0942: }
0943:
0944: public void testLimitParameterFirebird() {
0945: Select query = new Select(mFirebird);
0946: query.from("tablename").limitParameter("limit");
0947: assertEquals(query.getSql(), "SELECT FIRST ? * FROM tablename");
0948: assertTrue(execute(query, new DbPreparedStatementHandler() {
0949: public void setParameters(DbPreparedStatement statement) {
0950: statement.setInt("limit", 3);
0951: }
0952: }));
0953:
0954: query.offsetParameter("offset");
0955: assertEquals(query.getSql(),
0956: "SELECT FIRST ? SKIP ? * FROM tablename");
0957: assertTrue(execute(query, new DbPreparedStatementHandler() {
0958: public void setParameters(DbPreparedStatement statement) {
0959: statement.setInt("limit", 3).setInt("offset", 1);
0960: }
0961: }));
0962:
0963: query.clear();
0964: query.from("tablename").offsetParameter("offset");
0965: assertEquals(query.getSql(), "SELECT * FROM tablename");
0966: assertTrue(execute(query));
0967: }
0968:
0969: public void testSubselectParamsFirebird() {
0970: Select fieldquery = new Select(mFirebird);
0971: fieldquery.from("table2").field("propertyLong").whereParameter(
0972: "propertyInt", ">").limit(1);
0973: Select wherequery = new Select(mFirebird);
0974: wherequery.from("table3").field("max(propertyShort)")
0975: .whereParameter("propertyShort", "!=");
0976: // Select unionquery1 = new Select(mFirebird);
0977: // unionquery1
0978: // .from("table2")
0979: // .field("propertyString")
0980: // .field("max(propertyByte)")
0981: // .whereParameter("propertyByte", "=");
0982: // Select unionquery2 = new Select(mFirebird);
0983: // unionquery2
0984: // .from("table2")
0985: // .field("propertyStringbuffer")
0986: // .field("min(propertyByte)")
0987: // .whereParameter("propertyByte", ">")
0988: // .groupBy("propertyStringbuffer");
0989:
0990: // Manual subselect creation
0991: Select query = new Select(mFirebird);
0992: // shuffled the structure around a bit to test the correct order usage
0993: query
0994: // .union(unionquery1)
0995: // .union(unionquery2)
0996: .where(
0997: "tablename.propertyShort >= (" + wherequery
0998: + ")").whereSubselect(wherequery)
0999: .whereParameterOr("tablename.propertyString",
1000: "propertyString", "=").from("tablename")
1001: // firebird doesn't support table sub-selects
1002: .field("tablename.propertyString").field(
1003: "(" + fieldquery + ") AS propertyLong")
1004: .fieldSubselect(fieldquery);
1005: assertEquals(
1006: query.getSql(),
1007: "SELECT tablename.propertyString, (SELECT FIRST 1 propertyLong FROM table2 WHERE propertyInt > ?) AS propertyLong FROM tablename WHERE tablename.propertyShort >= (SELECT max(propertyShort) FROM table3 WHERE propertyShort != ?) OR tablename.propertyString = ?");
1008: String[] parameters = query.getParameters()
1009: .getOrderedNamesArray();
1010: assertEquals(3, parameters.length);
1011: assertEquals(parameters[0], "propertyInt");
1012: assertEquals(parameters[1], "propertyShort");
1013: assertEquals(parameters[2], "propertyString");
1014: // assertEquals(parameters[3], "propertyByte");
1015: // assertEquals(parameters[3], "propertyByte");
1016:
1017: assertTrue(execute(query, new DbPreparedStatementHandler() {
1018: public void setParameters(DbPreparedStatement statement) {
1019: statement.setInt("propertyInt", 1).setShort(
1020: "propertyShort", (short) 5).setString(
1021: "propertyString", "thestring");
1022: // .setByte("propertyByte", (byte)4);
1023: }
1024: }));
1025:
1026: //Automated subselect creation
1027: query = new Select(mFirebird);
1028: query
1029: // .union(unionquery1)
1030: // .union(unionquery2)
1031: .where("tablename.propertyShort", ">=", wherequery)
1032: .whereParameterOr("tablename.propertyString",
1033: "propertyString", "=").whereOr(
1034: "tablename.propertyFloat",
1035: ">",
1036: new Select(mFirebird).from("table3").field(
1037: "max(propertyLong)").whereParameter(
1038: "propertyLong", "!="))
1039: .from("tablename")
1040: // .join("tablesubselect", tablequery)
1041: .whereAnd(
1042: "tablename.propertyDouble",
1043: "<=",
1044: new Select(mFirebird).from("table2").field(
1045: "max(propertyFloat)").whereParameter(
1046: "propertyFloat", "!=")).field(
1047: "tablename.propertyString").field(
1048: "propertyLong", fieldquery);
1049: assertEquals(
1050: query.getSql(),
1051: "SELECT tablename.propertyString, (SELECT FIRST 1 propertyLong FROM table2 WHERE propertyInt > ?) AS propertyLong FROM tablename WHERE tablename.propertyShort >= (SELECT max(propertyShort) FROM table3 WHERE propertyShort != ?) OR tablename.propertyString = ? OR tablename.propertyFloat > (SELECT max(propertyLong) FROM table3 WHERE propertyLong != ?) AND tablename.propertyDouble <= (SELECT max(propertyFloat) FROM table2 WHERE propertyFloat != ?)");
1052: parameters = query.getParameters().getOrderedNamesArray();
1053: assertEquals(5, parameters.length);
1054: assertEquals(parameters[0], "propertyInt");
1055: // assertEquals(parameters[1], "propertyLong");
1056: assertEquals(parameters[1], "propertyShort");
1057: assertEquals(parameters[2], "propertyString");
1058: assertEquals(parameters[3], "propertyLong");
1059: assertEquals(parameters[4], "propertyFloat");
1060: // assertEquals(parameters[6], "propertyByte");
1061: // assertEquals(parameters[7], "propertyByte");
1062:
1063: assertTrue(execute(query, new DbPreparedStatementHandler() {
1064: public void setParameters(DbPreparedStatement statement) {
1065: statement.setInt("propertyInt", 1).setLong(
1066: "propertyLong", 99999999).setShort(
1067: "propertyShort", (short) 5).setString(
1068: "propertyString", "thestring").setFloat(
1069: "propertyFloat", -1f);
1070: // .setByte("propertyByte", (byte)4);
1071: }
1072: }));
1073: }
1074:
1075: public void testCloneFirebird() {
1076: Select fieldquery = new Select(mFirebird);
1077: fieldquery.from("table2").field("max(propertyLong)")
1078: .whereParameter("propertyInt", ">");
1079: Select wherequery = new Select(mFirebird);
1080: wherequery.from("table3").field("max(propertyShort)")
1081: .whereParameter("propertyShort", "!=");
1082: // Select unionquery1 = new Select(mFirebird);
1083: // unionquery1
1084: // .from("table2")
1085: // .field("propertyString")
1086: // .field("max(propertyByte)")
1087: // .whereParameter("propertyByte", "=")
1088: // .groupBy("propertyString");
1089: // Select unionquery2 = new Select(mFirebird);
1090: // unionquery2
1091: // .from("table2")
1092: // .field("propertyStringbuffer")
1093: // .field("min(propertyByte)")
1094: // .whereParameter("propertyByte", ">")
1095: // .groupBy("propertyStringbuffer");
1096: Select query = new Select(mFirebird);
1097: query.from("tablename")
1098: .joinOuter("table2", Select.RIGHT, Select.ON,
1099: "tablename.propertyInt = table2.propertyInt")
1100: .distinct().field("tablename.propertyString").field(
1101: "(" + fieldquery + ") AS propertyLong")
1102: .fieldSubselect(fieldquery).where(
1103: "tablename.propertyShort >= (" + wherequery
1104: + ")").whereSubselect(wherequery)
1105: .whereParameterOr("tablename.propertyString",
1106: "propertyString", "=").whereOr(
1107: "tablename.propertyByte", "=", (byte) 54)
1108: .whereAnd("tablename.propertyDouble", "!=", 73453.71d)
1109: .whereParameterOr("tablename.propertyInt",
1110: "propertyInt", "=").whereParameterAnd(
1111: "tablename.propertyLong", "propertyLong", "<")
1112: .whereParameterOr("tablename.propertyChar",
1113: "propertyChar", "=").groupBy(
1114: "tablename.propertyShort").groupBy(
1115: "tablename.propertyLong").groupBy(
1116: "tablename.propertyString").having(
1117: "tablename.propertyLong = 1")
1118: // .union(unionquery1)
1119: // .union(unionquery2)
1120: .limit(3).offset(1);
1121: Select query_clone = query.clone();
1122: assertEquals(query.getSql(), query_clone.getSql());
1123: assertTrue(query != query_clone);
1124: execute(query, new DbPreparedStatementHandler() {
1125: public void setParameters(DbPreparedStatement statement) {
1126: statement.setInt("propertyInt", 1).setLong(
1127: "propertyLong", 99999999).setShort(
1128: "propertyShort", (short) 5).setString(
1129: "propertyString", "thestring")
1130: // .setByte("propertyByte", (byte)4)
1131: .setString("propertyChar", "c");
1132: }
1133: });
1134: }
1135: }
|