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: TestSelectDerby.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 TestSelectDerby extends TestSelect {
0024: public TestSelectDerby(String name) {
0025: super (name);
0026: }
0027:
0028: public void testInstantiationDerby() {
0029: Select query = new Select(mDerby);
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 testIncompleteQueryDerby() {
0040: Select query = new Select(mDerby);
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(mDerby);
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 testClearDerby() {
0062: Select query = new Select(mDerby);
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 testBasicDerby() {
0075: Select query = new Select(mDerby);
0076: query.from("tablename");
0077: assertEquals(query.getSql(), "SELECT * FROM tablename");
0078: assertTrue(execute(query));
0079: }
0080:
0081: public void testHintDerby() {
0082: Select query = new Select(mDerby);
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 testOrderByAscendingDerby() {
0093: Select query = new Select(mDerby);
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 testOrderByDescendingDerby() {
0101: Select query = new Select(mDerby);
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 testBeanDerby() {
0109: Select query = new Select(mDerby);
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 testBeanConstrainedDerby() {
0118: Select query = new Select(mDerby, 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(mDerby, 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 testBeanExcludedDerby() {
0132: Select query = new Select(mDerby);
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 testBeanTableDerby() {
0144: Select query = new Select(mDerby);
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 testBeanExcludedTableDerby() {
0153: Select query = new Select(mDerby);
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 testWhereTypedDerby() {
0164: Select query = new Select(mDerby);
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 testWhereTypedMixedDerby() {
0199: Select query = new Select(mDerby);
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 testWhereParametersDerby() {
0238: Select query = new Select(mDerby);
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 testWhereParametersMixedDerby() {
0276: Select query = new Select(mDerby);
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 testWhereConstructionDerby() {
0300: Select query = new Select(mDerby);
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 testWhereConstructionGroupDerby() {
0310: Select query = new Select(mDerby);
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 testWhereBeanDerby() {
0341: Select query = new Select(mDerby);
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 testWhereBeanConstrainedDerby() {
0350: Select query = new Select(mDerby);
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 testWhereBeanNullValuesDerby() {
0360: Select query = new Select(mDerby);
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 testWhereBeanIncludedDerby() {
0369: Select query = new Select(mDerby);
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 testWhereBeanExcludedDerby() {
0382: Select query = new Select(mDerby);
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 testWhereBeanFilteredDerby() {
0395: Select query = new Select(mDerby);
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 testWhereParametersBeanDerby() {
0410: Select query = new Select(mDerby);
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: assertTrue(execute(query, new DbPreparedStatementHandler() {
0483: public void setParameters(DbPreparedStatement statement) {
0484: Calendar cal = Calendar.getInstance();
0485: cal.set(2002, 5, 18, 15, 26, 14);
0486: cal.set(Calendar.MILLISECOND, 764);
0487: statement.setBigDecimal(1,
0488: new BigDecimal("219038743.392874")).setBoolean(
0489: 2, true).setBoolean(3, false).setByte(4,
0490: (byte) 89).setByte(5, (byte) 34)
0491: .setTimestamp(
0492: 6,
0493: new java.sql.Timestamp(cal.getTime()
0494: .getTime())).setString(7, "v")
0495: .setString(8, "r").setTimestamp(
0496: 9,
0497: new java.sql.Timestamp(cal.getTime()
0498: .getTime())).setDouble(10,
0499: 53348.34d).setDouble(11, 143298.692d)
0500: .setString(12, "VALUE_THREE").setDouble(13,
0501: 98634.2d).setDouble(14, 8734.7d)
0502: .setInt(15, 545).setInt(16, 968).setLong(17,
0503: 34563L).setLong(18, 66875L).setShort(
0504: 19, (short) 43)
0505: .setShort(20, (short) 68).setDate(
0506: 21,
0507: new java.sql.Date(cal.getTime()
0508: .getTime())).setString(22,
0509: "someotherstring").setString(23,
0510: "someotherstringbuff").setTime(24,
0511: new Time(15, 26, 14)).setTimestamp(25,
0512: new Timestamp(cal.getTime().getTime()));
0513: }
0514: }));
0515: }
0516:
0517: public void testWhereParametersBeanConstrainedDerby() {
0518: Select query = new Select(mDerby);
0519: query.from("tablename").whereParameters(
0520: BeanImplConstrained.class);
0521: assertEquals(
0522: query.getSql(),
0523: "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 = ?");
0524:
0525: assertEquals(query.getParameters().getOrderedNames().size(), 22);
0526: assertEquals(query.getParameters().getOrderedNames().get(0),
0527: "propertyBigDecimal");
0528: assertEquals(query.getParameters().getOrderedNames().get(1),
0529: "propertyBoolean");
0530: assertEquals(query.getParameters().getOrderedNames().get(2),
0531: "propertyBooleanObject");
0532: assertEquals(query.getParameters().getOrderedNames().get(3),
0533: "propertyByte");
0534: assertEquals(query.getParameters().getOrderedNames().get(4),
0535: "propertyByteObject");
0536: assertEquals(query.getParameters().getOrderedNames().get(5),
0537: "propertyCalendar");
0538: assertEquals(query.getParameters().getOrderedNames().get(6),
0539: "propertyChar");
0540: assertEquals(query.getParameters().getOrderedNames().get(7),
0541: "propertyCharacterObject");
0542: assertEquals(query.getParameters().getOrderedNames().get(8),
0543: "propertyDate");
0544: assertEquals(query.getParameters().getOrderedNames().get(9),
0545: "propertyDouble");
0546: assertEquals(query.getParameters().getOrderedNames().get(10),
0547: "propertyDoubleObject");
0548: assertEquals(query.getParameters().getOrderedNames().get(11),
0549: "propertyFloat");
0550: assertEquals(query.getParameters().getOrderedNames().get(12),
0551: "propertyFloatObject");
0552: assertEquals(query.getParameters().getOrderedNames().get(13),
0553: "propertyInt");
0554: assertEquals(query.getParameters().getOrderedNames().get(14),
0555: "propertyIntegerObject");
0556: assertEquals(query.getParameters().getOrderedNames().get(15),
0557: "propertyLongObject");
0558: assertEquals(query.getParameters().getOrderedNames().get(16),
0559: "propertyShort");
0560: assertEquals(query.getParameters().getOrderedNames().get(17),
0561: "propertySqlDate");
0562: assertEquals(query.getParameters().getOrderedNames().get(18),
0563: "propertyString");
0564: assertEquals(query.getParameters().getOrderedNames().get(19),
0565: "propertyStringbuffer");
0566: assertEquals(query.getParameters().getOrderedNames().get(20),
0567: "propertyTime");
0568: assertEquals(query.getParameters().getOrderedNames().get(21),
0569: "propertyTimestamp");
0570: assertTrue(Arrays.equals(query.getParameters()
0571: .getOrderedNamesArray(), new String[] {
0572: "propertyBigDecimal", "propertyBoolean",
0573: "propertyBooleanObject", "propertyByte",
0574: "propertyByteObject", "propertyCalendar",
0575: "propertyChar", "propertyCharacterObject",
0576: "propertyDate", "propertyDouble",
0577: "propertyDoubleObject", "propertyFloat",
0578: "propertyFloatObject", "propertyInt",
0579: "propertyIntegerObject", "propertyLongObject",
0580: "propertyShort", "propertySqlDate", "propertyString",
0581: "propertyStringbuffer", "propertyTime",
0582: "propertyTimestamp" }));
0583:
0584: // don't check if actual rows were returned, since Derby doesn't
0585: // match on the float
0586: execute(query, new DbPreparedStatementHandler() {
0587: public void setParameters(DbPreparedStatement statement) {
0588: Calendar cal = Calendar.getInstance();
0589: cal.set(2002, 5, 18, 15, 26, 14);
0590: cal.set(Calendar.MILLISECOND, 764);
0591: statement.setBigDecimal(1,
0592: new BigDecimal("219038743.392874")).setBoolean(
0593: 2, true).setBoolean(3, false).setByte(4,
0594: (byte) 89).setByte(5, (byte) 34)
0595: .setTimestamp(
0596: 6,
0597: new java.sql.Timestamp(cal.getTime()
0598: .getTime())).setString(7, "v")
0599: .setString(8, "r").setTimestamp(
0600: 9,
0601: new java.sql.Timestamp(cal.getTime()
0602: .getTime())).setDouble(10,
0603: 53348.34d).setDouble(11, 143298.692d)
0604: .setFloat(12, 98634.2f).setFloat(13, 8734.7f)
0605: .setInt(14, 545).setInt(15, 968).setLong(16,
0606: 66875L).setShort(17, (short) 43)
0607: .setDate(
0608: 18,
0609: new java.sql.Date(cal.getTime()
0610: .getTime())).setString(19,
0611: "someotherstring").setString(20,
0612: "someotherstringbuff").setTime(21,
0613: new Time(cal.getTime().getTime()))
0614: .setTimestamp(22,
0615: new Timestamp(cal.getTime().getTime()));
0616: }
0617: });
0618: }
0619:
0620: public void testWhereParametersBeanExcludedDerby() {
0621: Select query = new Select(mDerby);
0622: query.from("tablename").whereParametersExcluded(
0623: BeanImpl.class,
0624: new String[] { "propertyBoolean", "propertyByte",
0625: "propertyChar", "propertyDouble",
0626: "propertyInt", "propertyLong",
0627: "propertySqlDate", "propertyStringbuffer",
0628: "propertyTimestamp" });
0629: assertEquals(
0630: query.getSql(),
0631: "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 = ?");
0632:
0633: assertEquals(query.getParameters().getOrderedNames().size(), 16);
0634: assertEquals(query.getParameters().getOrderedNames().get(0),
0635: "propertyBigDecimal");
0636: assertEquals(query.getParameters().getOrderedNames().get(1),
0637: "propertyBooleanObject");
0638: assertEquals(query.getParameters().getOrderedNames().get(2),
0639: "propertyByteObject");
0640: assertEquals(query.getParameters().getOrderedNames().get(3),
0641: "propertyCalendar");
0642: assertEquals(query.getParameters().getOrderedNames().get(4),
0643: "propertyCharacterObject");
0644: assertEquals(query.getParameters().getOrderedNames().get(5),
0645: "propertyDate");
0646: assertEquals(query.getParameters().getOrderedNames().get(6),
0647: "propertyDoubleObject");
0648: assertEquals(query.getParameters().getOrderedNames().get(7),
0649: "propertyEnum");
0650: assertEquals(query.getParameters().getOrderedNames().get(8),
0651: "propertyFloat");
0652: assertEquals(query.getParameters().getOrderedNames().get(9),
0653: "propertyFloatObject");
0654: assertEquals(query.getParameters().getOrderedNames().get(10),
0655: "propertyIntegerObject");
0656: assertEquals(query.getParameters().getOrderedNames().get(11),
0657: "propertyLongObject");
0658: assertEquals(query.getParameters().getOrderedNames().get(12),
0659: "propertyShort");
0660: assertEquals(query.getParameters().getOrderedNames().get(13),
0661: "propertyShortObject");
0662: assertEquals(query.getParameters().getOrderedNames().get(14),
0663: "propertyString");
0664: assertEquals(query.getParameters().getOrderedNames().get(15),
0665: "propertyTime");
0666: assertTrue(Arrays.equals(query.getParameters()
0667: .getOrderedNamesArray(), new String[] {
0668: "propertyBigDecimal", "propertyBooleanObject",
0669: "propertyByteObject", "propertyCalendar",
0670: "propertyCharacterObject", "propertyDate",
0671: "propertyDoubleObject", "propertyEnum",
0672: "propertyFloat", "propertyFloatObject",
0673: "propertyIntegerObject", "propertyLongObject",
0674: "propertyShort", "propertyShortObject",
0675: "propertyString", "propertyTime" }));
0676: assertTrue(execute(query, new DbPreparedStatementHandler() {
0677: public void setParameters(DbPreparedStatement statement) {
0678: Calendar cal = Calendar.getInstance();
0679: cal.set(2002, 5, 18, 15, 26, 14);
0680: cal.set(Calendar.MILLISECOND, 764);
0681: statement.setBigDecimal(1,
0682: new BigDecimal("219038743.392874")).setBoolean(
0683: 2, false).setByte(3, (byte) 34)
0684: .setTimestamp(
0685: 4,
0686: new java.sql.Timestamp(cal.getTime()
0687: .getTime())).setString(5, "r")
0688: .setTimestamp(
0689: 6,
0690: new java.sql.Timestamp(cal.getTime()
0691: .getTime())).setDouble(7,
0692: 143298.692d)
0693: .setString(8, "VALUE_THREE").setDouble(9,
0694: 98634.2d).setDouble(10, 8734.7d)
0695: .setInt(11, 968).setLong(12, 66875L).setShort(
0696: 13, (short) 43)
0697: .setShort(14, (short) 68).setString(15,
0698: "someotherstring").setTime(16,
0699: new Time(15, 26, 14));
0700: }
0701: }));
0702: }
0703:
0704: public void testDistinctDerby() {
0705: Select query = new Select(mDerby);
0706: query.from("tablename").distinct().where("propertyByte = 89")
0707: .orderBy("propertyDouble").orderBy("propertyShort")
0708: .orderBy("propertyTime");
0709: assertEquals(
0710: query.getSql(),
0711: "SELECT DISTINCT * FROM tablename WHERE propertyByte = 89 ORDER BY propertyDouble ASC, propertyShort ASC, propertyTime ASC");
0712: assertTrue(execute(query));
0713: }
0714:
0715: public void testDistinctOnDerby() {
0716: Select query = new Select(mDerby);
0717: query.from("tablename").distinctOn("propertyDouble")
0718: .distinctOn("propertyShort").distinctOn("propertyTime")
0719: .where("propertyByte = 89").orderBy("propertyDouble")
0720: .orderBy("propertyShort").orderBy("propertyTime");
0721: try {
0722: query.getSql();
0723: fail();
0724: } catch (UnsupportedSqlFeatureException e) {
0725: assertTrue(true);
0726: }
0727: }
0728:
0729: public void testComplexDerby() {
0730: Select query = new Select(mDerby);
0731: query.from("tablename").field("field1").field("field2").field(
0732: "field3").join("table2").join("table3").where(
0733: "this = that").groupBy("gexpr1").groupBy("gexpr2")
0734: .having("hexpr1").having("hexpr2").distinct().unionAll(
0735: "uexpr1").union("uexpr2");
0736: // .limit(3)
0737: // .offset(1);
0738: assertEquals(
0739: query.getSql(),
0740: "SELECT DISTINCT field1, field2, field3 FROM tablename, table2, table3 WHERE this = that GROUP BY gexpr1, gexpr2 HAVING hexpr1, hexpr2 UNION ALL uexpr1 UNION uexpr2");
0741: }
0742:
0743: public void testGroupByBeanDerby() {
0744: Select query = new Select(mDerby);
0745: query.from("tablename").fields(BeanImpl.class).groupBy(
0746: BeanImpl.class);
0747: assertEquals(
0748: query.getSql(),
0749: "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");
0750: assertTrue(execute(query));
0751: }
0752:
0753: public void testGroupByBeanExcludedDerby() {
0754: Select query = new Select(mDerby);
0755: query.from("tablename").fieldsExcluded(
0756: BeanImpl.class,
0757: new String[] { "propertyCalendar", "propertyFloat",
0758: "propertyShort" }).groupByExcluded(
0759: BeanImpl.class,
0760: new String[] { "propertyCalendar", "propertyFloat",
0761: "propertyShort" });
0762: assertEquals(
0763: query.getSql(),
0764: "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");
0765: assertTrue(execute(query));
0766: }
0767:
0768: public void testJoinDerby() {
0769: Select query = new Select(mDerby);
0770: query.from("tablename").join("table2").join("table3");
0771: assertEquals(query.getSql(),
0772: "SELECT * FROM tablename, table2, table3");
0773: assertTrue(execute(query));
0774: }
0775:
0776: public void testJoinCustomDerby() {
0777: Select query = new Select(mDerby);
0778: query
0779: .from("tablename")
0780: .joinCustom(
0781: "INNER JOIN table3 ON (tablename.propertyInt = table3.propertyInt)")
0782: .joinCustom(
0783: "LEFT OUTER JOIN table2 ON (table3.propertyInt = table2.propertyInt)");
0784: assertEquals(
0785: query.getSql(),
0786: "SELECT * FROM tablename INNER JOIN table3 ON (tablename.propertyInt = table3.propertyInt) LEFT OUTER JOIN table2 ON (table3.propertyInt = table2.propertyInt)");
0787: assertTrue(execute(query));
0788: }
0789:
0790: public void testJoinCrossDerby() {
0791: Select query = new Select(mDerby);
0792: query.from("tablename").joinCross("table2").joinCross("table3");
0793: try {
0794: query.getSql();
0795: fail();
0796: } catch (UnsupportedSqlFeatureException e) {
0797: assertTrue(true);
0798: }
0799: }
0800:
0801: public void testJoinInnerDerby() {
0802: Select query = new Select(mDerby);
0803: query.from("tablename").joinInner("table2", Select.NATURAL,
0804: null);
0805: try {
0806: query.getSql();
0807: fail();
0808: } catch (UnsupportedSqlFeatureException e) {
0809: assertTrue(true);
0810: }
0811: query.clear();
0812: query.from("tablename").joinInner("table2", Select.ON,
0813: "tablename.propertyInt = table2.propertyInt");
0814: assertEquals(
0815: query.getSql(),
0816: "SELECT * FROM tablename INNER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0817: assertTrue(execute(query));
0818: query.clear();
0819: query.from("tablename").joinInner("table2", Select.USING,
0820: "propertyInt");
0821: try {
0822: query.getSql();
0823: fail();
0824: } catch (UnsupportedSqlFeatureException e) {
0825: assertTrue(true);
0826: }
0827: }
0828:
0829: public void testJoinOuterDerby() {
0830: Select query = new Select(mDerby);
0831:
0832: query.from("tablename").joinOuter("table2", Select.FULL,
0833: Select.NATURAL, null);
0834: try {
0835: query.getSql();
0836: fail();
0837: } catch (UnsupportedSqlFeatureException e) {
0838: assertTrue(true);
0839: }
0840: query.clear();
0841: query.from("tablename").joinOuter("table2", Select.LEFT,
0842: Select.NATURAL, null);
0843: try {
0844: query.getSql();
0845: fail();
0846: } catch (UnsupportedSqlFeatureException e) {
0847: assertTrue(true);
0848: }
0849: query.clear();
0850: query.from("tablename").joinOuter("table2", Select.RIGHT,
0851: Select.NATURAL, null);
0852: try {
0853: query.getSql();
0854: fail();
0855: } catch (UnsupportedSqlFeatureException e) {
0856: assertTrue(true);
0857: }
0858: query.clear();
0859:
0860: query.from("tablename")
0861: .joinOuter("table2", Select.FULL, Select.ON,
0862: "tablename.propertyInt = table2.propertyInt");
0863: try {
0864: query.getSql();
0865: fail();
0866: } catch (UnsupportedSqlFeatureException e) {
0867: assertTrue(true);
0868: }
0869: query.clear();
0870: query.from("tablename")
0871: .joinOuter("table2", Select.LEFT, Select.ON,
0872: "tablename.propertyInt = table2.propertyInt");
0873: assertEquals(
0874: query.getSql(),
0875: "SELECT * FROM tablename LEFT OUTER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0876: assertTrue(execute(query));
0877: query.clear();
0878: query.from("tablename")
0879: .joinOuter("table2", Select.RIGHT, Select.ON,
0880: "tablename.propertyInt = table2.propertyInt");
0881: assertEquals(
0882: query.getSql(),
0883: "SELECT * FROM tablename RIGHT OUTER JOIN table2 ON (tablename.propertyInt = table2.propertyInt)");
0884: assertTrue(execute(query));
0885: query.clear();
0886:
0887: query.from("tablename").joinOuter("table2", Select.FULL,
0888: Select.USING, "propertyInt");
0889: try {
0890: query.getSql();
0891: fail();
0892: } catch (UnsupportedSqlFeatureException e) {
0893: assertTrue(true);
0894: }
0895: query.clear();
0896: query.from("tablename").joinOuter("table2", Select.LEFT,
0897: Select.USING, "propertyInt");
0898: try {
0899: query.getSql();
0900: fail();
0901: } catch (UnsupportedSqlFeatureException e) {
0902: assertTrue(true);
0903: }
0904: query.clear();
0905:
0906: query.from("tablename").joinOuter("table2", Select.RIGHT,
0907: Select.USING, "propertyInt");
0908: try {
0909: query.getSql();
0910: fail();
0911: } catch (UnsupportedSqlFeatureException e) {
0912: assertTrue(true);
0913: }
0914: query.clear();
0915: }
0916:
0917: public void testLimitDerby() {
0918: Select query = new Select(mDerby);
0919: query.from("tablename").limit(3);
0920: try {
0921: query.getSql();
0922: fail();
0923: } catch (UnsupportedSqlFeatureException e) {
0924: assertTrue(true);
0925: }
0926: query.offset(1);
0927: try {
0928: query.getSql();
0929: fail();
0930: } catch (UnsupportedSqlFeatureException e) {
0931: assertTrue(true);
0932: }
0933: query.clear();
0934: query.from("tablename").offset(10);
0935: assertEquals(query.getSql(), "SELECT * FROM tablename");
0936: assertTrue(execute(query));
0937: }
0938:
0939: public void testLimitParameterDerby() {
0940: Select query = new Select(mDerby);
0941: query.from("tablename").limitParameter("limit");
0942: try {
0943: query.getSql();
0944: fail();
0945: } catch (UnsupportedSqlFeatureException e) {
0946: assertTrue(true);
0947: }
0948:
0949: query.offsetParameter("offset");
0950: try {
0951: query.getSql();
0952: fail();
0953: } catch (UnsupportedSqlFeatureException e) {
0954: assertTrue(true);
0955: }
0956:
0957: query.clear();
0958: query.from("tablename").offsetParameter("offset");
0959: assertEquals(query.getSql(), "SELECT * FROM tablename");
0960: assertTrue(execute(query));
0961: }
0962:
0963: public void testSubselectParamsDerby() {
0964: Select fieldquery = new Select(mDerby);
0965: fieldquery.from("table2").field("max(propertyLong)")
0966: .whereParameter("propertyInt", ">");
0967: Select tablequery = new Select(mDerby);
0968: tablequery.from("table2").whereParameter("propertyLong", "<");
0969: Select wherequery = new Select(mDerby);
0970: wherequery.from("table3").field("max(propertyShort)")
0971: .whereParameter("propertyShort", "!=");
0972: Select unionquery1 = new Select(mDerby);
0973: unionquery1.from("table2").field("propertyString").field(
0974: "max(propertyByte)")
0975: .whereParameter("propertyByte", "=").groupBy(
0976: "propertyString");
0977: Select unionquery2 = new Select(mDerby);
0978: unionquery2.from("table2").field("propertyStringbuffer").field(
0979: "min(propertyByte)")
0980: .whereParameter("propertyByte", ">").groupBy(
0981: "propertyStringbuffer");
0982:
0983: // Manual subselect creation
0984: Select query = new Select(mDerby);
0985: // shuffled the structure around a bit to test the correct order usage
0986: query.unionAll(unionquery1).union(unionquery2).where(
0987: "tablename.propertyShort >= (" + wherequery + ")")
0988: .whereSubselect(wherequery).whereParameterOr(
0989: "tablename.propertyString", "propertyString",
0990: "=").from("tablename").join(
0991: "(" + tablequery + ") AS tablesubselect")
0992: .tableSubselect(tablequery).field(
0993: "tablename.propertyString").field(
0994: "(" + fieldquery + ") AS propertyLong")
0995: .fieldSubselect(fieldquery);
0996: assertEquals(
0997: query.getSql(),
0998: "SELECT tablename.propertyString, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?) AS propertyLong FROM tablename, (SELECT * FROM table2 WHERE propertyLong < ?) AS tablesubselect WHERE tablename.propertyShort >= (SELECT max(propertyShort) FROM table3 WHERE propertyShort != ?) OR tablename.propertyString = ? UNION ALL SELECT propertyString, max(propertyByte) FROM table2 WHERE propertyByte = ? GROUP BY propertyString UNION SELECT propertyStringbuffer, min(propertyByte) FROM table2 WHERE propertyByte > ? GROUP BY propertyStringbuffer");
0999: String[] parameters = query.getParameters()
1000: .getOrderedNamesArray();
1001: assertEquals(6, parameters.length);
1002: assertEquals(parameters[0], "propertyInt");
1003: assertEquals(parameters[1], "propertyLong");
1004: assertEquals(parameters[2], "propertyShort");
1005: assertEquals(parameters[3], "propertyString");
1006: assertEquals(parameters[4], "propertyByte");
1007: assertEquals(parameters[5], "propertyByte");
1008:
1009: assertTrue(execute(query, new DbPreparedStatementHandler() {
1010: public void setParameters(DbPreparedStatement statement) {
1011: statement.setInt("propertyInt", 1).setLong(
1012: "propertyLong", 99999999).setShort(
1013: "propertyShort", (short) 5).setString(
1014: "propertyString", "thestring").setByte(
1015: "propertyByte", (byte) 4);
1016: }
1017: }));
1018:
1019: //Automated subselect creation
1020: query = new Select(mDerby);
1021: query.union(unionquery1).union(unionquery2).where(
1022: "tablename.propertyShort", ">=", wherequery)
1023: .whereParameterOr("tablename.propertyString",
1024: "propertyString", "=").whereOr(
1025: "tablename.propertyFloat",
1026: ">",
1027: new Select(mDerby).from("table3").field(
1028: "max(propertyLong)").whereParameter(
1029: "propertyLong", "!=")).from(
1030: "tablename",
1031: new Select(mDerby).from("tablename")).join(
1032: "tablesubselect", tablequery).whereAnd(
1033: "tablename.propertyDouble",
1034: "<=",
1035: new Select(mDerby).from("table2").field(
1036: "max(propertyFloat)").whereParameter(
1037: "propertyFloat", "!=")).field(
1038: "tablename.propertyString").field(
1039: "propertyLong", fieldquery);
1040: assertEquals(
1041: query.getSql(),
1042: "SELECT tablename.propertyString, (SELECT max(propertyLong) FROM table2 WHERE propertyInt > ?) AS propertyLong FROM (SELECT * FROM tablename) tablename, (SELECT * FROM table2 WHERE propertyLong < ?) tablesubselect 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 != ?) UNION SELECT propertyString, max(propertyByte) FROM table2 WHERE propertyByte = ? GROUP BY propertyString UNION SELECT propertyStringbuffer, min(propertyByte) FROM table2 WHERE propertyByte > ? GROUP BY propertyStringbuffer");
1043: parameters = query.getParameters().getOrderedNamesArray();
1044: assertEquals(8, parameters.length);
1045: assertEquals(parameters[0], "propertyInt");
1046: assertEquals(parameters[1], "propertyLong");
1047: assertEquals(parameters[2], "propertyShort");
1048: assertEquals(parameters[3], "propertyString");
1049: assertEquals(parameters[4], "propertyLong");
1050: assertEquals(parameters[5], "propertyFloat");
1051: assertEquals(parameters[6], "propertyByte");
1052: assertEquals(parameters[7], "propertyByte");
1053:
1054: assertTrue(execute(query, new DbPreparedStatementHandler() {
1055: public void setParameters(DbPreparedStatement statement) {
1056: statement.setInt("propertyInt", 1).setLong(
1057: "propertyLong", 99999999).setShort(
1058: "propertyShort", (short) 5).setString(
1059: "propertyString", "thestring").setFloat(
1060: "propertyFloat", -1f).setByte("propertyByte",
1061: (byte) 4);
1062: }
1063: }));
1064: }
1065:
1066: public void testCloneDerby() {
1067: Select fieldquery = new Select(mDerby);
1068: fieldquery.from("table2").field("max(propertyLong)")
1069: .whereParameter("propertyInt", ">");
1070: Select tablequery = new Select(mDerby);
1071: tablequery.from("table2").whereParameter("propertyLong", "<");
1072: Select wherequery = new Select(mDerby);
1073: wherequery.from("table3").field("max(propertyShort)")
1074: .whereParameter("propertyShort", "!=");
1075: Select unionquery1 = new Select(mDerby);
1076: unionquery1.from("table2").field("propertyString").field(
1077: "max(propertyByte)")
1078: .whereParameter("propertyByte", "=").groupBy(
1079: "propertyString");
1080: Select unionquery2 = new Select(mDerby);
1081: unionquery2.from("table2").field("propertyStringbuffer").field(
1082: "min(propertyByte)")
1083: .whereParameter("propertyByte", ">").groupBy(
1084: "propertyStringbuffer");
1085: Select query = new Select(mDerby);
1086: query
1087: .from("tablename")
1088: .join("(" + tablequery + ") AS tablesubselect")
1089: .tableSubselect(tablequery)
1090: .join("table3")
1091: .joinOuter("table2", Select.RIGHT, Select.ON,
1092: "table3.propertyInt = table2.propertyInt")
1093: .distinct()
1094: // .distinctOn("tablename.propertyShort")
1095: .field("tablename.propertyString").field(
1096: "(" + fieldquery + ") AS propertyLong")
1097: .fieldSubselect(fieldquery).where(
1098: "tablename.propertyShort >= (" + wherequery
1099: + ")").whereSubselect(wherequery)
1100: .whereParameterOr("tablename.propertyString",
1101: "propertyString", "=").whereOr(
1102: "tablename.propertyByte", "=", (byte) 54)
1103: .whereAnd("tablename.propertyDouble", "!=", 73453.71d)
1104: .whereParameterOr("tablename.propertyInt",
1105: "propertyInt", "=").whereParameterAnd(
1106: "tablename.propertyLong", "propertyLong", "<")
1107: .whereParameterOr("tablename.propertyChar",
1108: "propertyChar", "=")
1109: // .groupBy("tablename.propertyShort")
1110: // .groupBy("tablename.propertyLong")
1111: // .groupBy("tablename.propertyString")
1112: // .having("tablename.propertyLong = 1")
1113: .unionAll(unionquery1).union(unionquery2);
1114: // .limit(3)
1115: // .offset(1);
1116: Select query_clone = query.clone();
1117: assertEquals(query.getSql(), query_clone.getSql());
1118: assertTrue(query != query_clone);
1119: execute(query, new DbPreparedStatementHandler() {
1120: public void setParameters(DbPreparedStatement statement) {
1121: statement.setInt("propertyInt", 1).setLong(
1122: "propertyLong", 99999999).setShort(
1123: "propertyShort", (short) 5).setString(
1124: "propertyString", "thestring").setByte(
1125: "propertyByte", (byte) 4).setString(
1126: "propertyChar", "c");
1127: }
1128: });
1129: }
1130: }
|