0001: /*
0002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com> and
0003: * JR Boyens <gnu-jrb[remove] at gmx dot net>
0004: * Distributed under the terms of either:
0005: * - the common development and distribution license (CDDL), v1.0; or
0006: * - the GNU Lesser General Public License, v2.1 or later
0007: * $Id: TestRestoreQuery.java 3634 2007-01-08 21:42:24Z gbevin $
0008: */
0009: package com.uwyn.rife.database.querymanagers.generic;
0010:
0011: import com.uwyn.rife.database.Datasource;
0012: import com.uwyn.rife.database.exceptions.DatabaseException;
0013: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
0014: import com.uwyn.rife.database.queries.Select;
0015: import com.uwyn.rife.database.querymanagers.generic.beans.BeanImpl;
0016: import com.uwyn.rife.database.querymanagers.generic.beans.LinkBean;
0017: import com.uwyn.rife.database.querymanagers.generic.beans.SimpleBean;
0018: import java.math.BigDecimal;
0019: import java.sql.Time;
0020: import java.sql.Timestamp;
0021: import java.util.Calendar;
0022: import java.util.List;
0023: import junit.framework.TestCase;
0024:
0025: public class TestRestoreQuery extends TestCase {
0026: private Datasource mDatasource = null;
0027: private GenericQueryManager<SimpleBean> mManager = null;
0028: private GenericQueryManager<LinkBean> mLinkManager = null;
0029: private GenericQueryManager<BeanImpl> mBigBeanManager = null;
0030:
0031: public TestRestoreQuery(Datasource datasource,
0032: String datasourceName, String name) {
0033: super (name);
0034: mDatasource = datasource;
0035: }
0036:
0037: protected void setUp() {
0038: mManager = GenericQueryManagerFactory.getInstance(mDatasource,
0039: SimpleBean.class);
0040: mLinkManager = GenericQueryManagerFactory.getInstance(
0041: mDatasource, LinkBean.class);
0042: mBigBeanManager = GenericQueryManagerFactory.getInstance(
0043: mDatasource, BeanImpl.class);
0044:
0045: int poolsize = mDatasource.getPoolsize();
0046: // disabling pool for firebird
0047: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
0048: .getAliasedDriver())) {
0049: mDatasource.setPoolsize(0);
0050: }
0051: try {
0052: mManager.install();
0053: mLinkManager.install();
0054: mBigBeanManager.install();
0055: } finally {
0056: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
0057: .getAliasedDriver())) {
0058: mDatasource.setPoolsize(poolsize);
0059: }
0060: }
0061: }
0062:
0063: protected void tearDown() {
0064: int poolsize = mDatasource.getPoolsize();
0065: // disabling pool for firebird
0066: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
0067: .getAliasedDriver())) {
0068: mDatasource.setPoolsize(0);
0069: }
0070: try {
0071: mManager.remove();
0072: mLinkManager.remove();
0073: mBigBeanManager.remove();
0074: } finally {
0075: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
0076: .getAliasedDriver())) {
0077: mDatasource.setPoolsize(poolsize);
0078: }
0079: }
0080: }
0081:
0082: public void testLimit() {
0083: SimpleBean bean1 = new SimpleBean();
0084: SimpleBean bean2 = new SimpleBean();
0085: SimpleBean bean3 = new SimpleBean();
0086:
0087: bean1.setTestString("This is bean1");
0088: bean2.setTestString("This is bean2");
0089: bean3.setTestString("This is bean3");
0090:
0091: mManager.save(bean1);
0092: mManager.save(bean2);
0093: mManager.save(bean3);
0094:
0095: List<SimpleBean> list = mManager.restore(mManager
0096: .getRestoreQuery().limit(2));
0097:
0098: assertEquals(list.size(), 2);
0099: }
0100:
0101: public void testOffset() {
0102: SimpleBean bean1 = new SimpleBean();
0103: SimpleBean bean2 = new SimpleBean();
0104: SimpleBean bean3 = new SimpleBean();
0105:
0106: bean1.setTestString("This is bean1");
0107: bean2.setTestString("This is bean2");
0108: bean3.setTestString("This is bean3");
0109:
0110: mManager.save(bean1);
0111: mManager.save(bean2);
0112: mManager.save(bean3);
0113:
0114: List<SimpleBean> list = mManager.restore(mManager
0115: .getRestoreQuery().limit(1).offset(1).orderBy("id"));
0116:
0117: assertEquals(list.size(), 1);
0118:
0119: assertTrue(list.get(0).getTestString().equals("This is bean2"));
0120: }
0121:
0122: public void testCloneToStringAndClear() {
0123: RestoreQuery query = mManager.getRestoreQuery().where(
0124: "testString", "=", "bean set 1");
0125:
0126: assertEquals(query.toString(),
0127: "SELECT * FROM simplebean WHERE testString = 'bean set 1'");
0128:
0129: RestoreQuery queryclone = query.clone();
0130:
0131: assertEquals(queryclone.toString(),
0132: "SELECT * FROM simplebean WHERE testString = 'bean set 1'");
0133:
0134: queryclone.where("testString", "!=", "bean set 2");
0135:
0136: assertEquals(
0137: queryclone.toString(),
0138: "SELECT * FROM simplebean WHERE testString = 'bean set 1' AND testString != 'bean set 2'");
0139:
0140: queryclone.clear();
0141:
0142: assertEquals(queryclone.toString(),
0143: "SELECT * FROM simplebean WHERE testString = 'bean set 1'");
0144:
0145: query.clear();
0146:
0147: assertEquals(query.toString(), "SELECT * FROM simplebean");
0148: }
0149:
0150: public void testDistinctOn() {
0151: SimpleBean bean1 = new SimpleBean();
0152: SimpleBean bean2 = new SimpleBean();
0153: SimpleBean bean3 = new SimpleBean();
0154: SimpleBean bean4 = new SimpleBean();
0155: SimpleBean bean5 = new SimpleBean();
0156:
0157: bean1.setTestString("bean set 1");
0158: bean2.setTestString("bean set 1");
0159: bean3.setTestString("bean set 1");
0160: bean4.setTestString("bean set 2");
0161: bean5.setTestString("bean set 2");
0162:
0163: mManager.save(bean1);
0164: mManager.save(bean2);
0165: mManager.save(bean3);
0166: mManager.save(bean4);
0167: mManager.save(bean5);
0168:
0169: RestoreQuery query = mManager.getRestoreQuery().distinctOn(
0170: "testString");
0171:
0172: try {
0173: List<SimpleBean> beanList = mManager.restore(query);
0174:
0175: assertEquals(beanList.size(), 2);
0176:
0177: if ("com.mysql.jdbc.Driver".equals(mDatasource
0178: .getAliasedDriver())
0179: || "oracle.jdbc.driver.OracleDriver"
0180: .equals(mDatasource.getAliasedDriver())
0181: || "org.hsqldb.jdbcDriver".equals(mDatasource
0182: .getAliasedDriver())
0183: || "org.h2.Driver".equals(mDatasource
0184: .getAliasedDriver())
0185: || "org.firebirdsql.jdbc.FBDriver"
0186: .equals(mDatasource.getAliasedDriver())
0187: || "com.mckoi.JDBCDriver".equals(mDatasource
0188: .getAliasedDriver())
0189: || "org.apache.derby.jdbc.EmbeddedDriver"
0190: .equals(mDatasource.getAliasedDriver())
0191: || "in.co.daffodil.db.jdbc.DaffodilDBDriver"
0192: .equals(mDatasource.getAliasedDriver())) {
0193: fail();
0194: }
0195: } catch (UnsupportedSqlFeatureException e) {
0196: assertEquals("DISTINCT ON", e.getFeature());
0197: assertTrue("com.mysql.jdbc.Driver".equals(e.getDriver())
0198: || "oracle.jdbc.driver.OracleDriver".equals(e
0199: .getDriver())
0200: || "org.hsqldb.jdbcDriver".equals(e.getDriver())
0201: || "org.h2.Driver".equals(e.getDriver())
0202: || "org.firebirdsql.jdbc.FBDriver".equals(e
0203: .getDriver())
0204: || "com.mckoi.JDBCDriver".equals(e.getDriver())
0205: || "org.apache.derby.jdbc.EmbeddedDriver".equals(e
0206: .getDriver())
0207: || "in.co.daffodil.db.jdbc.DaffodilDBDriver"
0208: .equals(e.getDriver()));
0209: }
0210:
0211: query.clear();
0212:
0213: query = mManager.getRestoreQuery().distinctOn(
0214: new String[] { "testString" });
0215:
0216: try {
0217: List<SimpleBean> beanList = mManager.restore(query);
0218:
0219: assertEquals(beanList.size(), 2);
0220:
0221: if ("com.mysql.jdbc.Driver".equals(mDatasource
0222: .getAliasedDriver())
0223: || "oracle.jdbc.driver.OracleDriver"
0224: .equals(mDatasource.getAliasedDriver())
0225: || "org.hsqldb.jdbcDriver".equals(mDatasource
0226: .getAliasedDriver())
0227: || "org.h2.Driver".equals(mDatasource
0228: .getAliasedDriver())
0229: || "org.firebirdsql.jdbc.FBDriver"
0230: .equals(mDatasource.getAliasedDriver())
0231: || "com.mckoi.JDBCDriver".equals(mDatasource
0232: .getAliasedDriver())
0233: || "org.apache.derby.jdbc.EmbeddedDriver"
0234: .equals(mDatasource.getAliasedDriver())
0235: || "in.co.daffodil.db.jdbc.DaffodilDBDriver"
0236: .equals(mDatasource.getAliasedDriver())) {
0237: fail();
0238: }
0239: } catch (UnsupportedSqlFeatureException e) {
0240: assertEquals("DISTINCT ON", e.getFeature());
0241: assertTrue("com.mysql.jdbc.Driver".equals(e.getDriver())
0242: || "oracle.jdbc.driver.OracleDriver".equals(e
0243: .getDriver())
0244: || "org.hsqldb.jdbcDriver".equals(e.getDriver())
0245: || "org.h2.Driver".equals(e.getDriver())
0246: || "org.firebirdsql.jdbc.FBDriver".equals(e
0247: .getDriver())
0248: || "com.mckoi.JDBCDriver".equals(e.getDriver())
0249: || "org.apache.derby.jdbc.EmbeddedDriver".equals(e
0250: .getDriver())
0251: || "in.co.daffodil.db.jdbc.DaffodilDBDriver"
0252: .equals(e.getDriver()));
0253: }
0254: }
0255:
0256: public void testGetDatasource() {
0257: assertTrue(mDatasource.equals(mManager.getRestoreQuery()
0258: .getDatasource()));
0259: }
0260:
0261: public void testGetFrom() {
0262: assertTrue(mManager.getRestoreQuery().getFrom().equals(
0263: SimpleBean.class.getName().replaceAll(
0264: SimpleBean.class.getPackage().getName() + ".",
0265: "").toLowerCase()));
0266: }
0267:
0268: public void testGetParameters() {
0269: Select select = new Select(mDatasource);
0270: select.from("simplebean").whereParameter("testString", "=");
0271:
0272: RestoreQuery query = new RestoreQuery(select);
0273:
0274: assertEquals(query.getParameters().getOrderedNames().size(), 1);
0275: assertTrue(query.getParameters().getOrderedNames().contains(
0276: "testString"));
0277:
0278: assertEquals(
0279: query.getParameters().getOrderedNamesArray().length, 1);
0280: assertEquals(query.getParameters().getOrderedNamesArray()[0],
0281: "testString");
0282: }
0283:
0284: public void testRestoreFirst() {
0285: SimpleBean bean1 = new SimpleBean();
0286: SimpleBean bean2 = new SimpleBean();
0287: SimpleBean bean3 = new SimpleBean();
0288: SimpleBean bean4 = new SimpleBean();
0289: SimpleBean bean5 = new SimpleBean();
0290:
0291: bean1.setTestString("bean set 1");
0292: bean2.setTestString("bean set 1");
0293: bean3.setTestString("bean set 1");
0294: bean4.setTestString("bean set 2");
0295: bean5.setTestString("bean set 2");
0296:
0297: mManager.save(bean1);
0298: mManager.save(bean2);
0299: mManager.save(bean3);
0300: mManager.save(bean4);
0301: mManager.save(bean5);
0302:
0303: RestoreQuery query = mManager.getRestoreQuery();
0304: query.where("testString", "=", "bean set 1").orderBy("id",
0305: Select.ASC);
0306:
0307: SimpleBean newbean = mManager.restoreFirst(query);
0308:
0309: assertFalse(newbean == bean1);
0310: assertEquals(newbean.getTestString(), bean1.getTestString());
0311: assertEquals(newbean.getId(), bean1.getId());
0312:
0313: query.clear();
0314: query.where("testString", "=", "bean set 2").orderBy("id",
0315: Select.DESC);
0316:
0317: SimpleBean otherbean = mManager.restoreFirst(query);
0318:
0319: assertFalse(otherbean == bean5);
0320: assertEquals(otherbean.getTestString(), bean5.getTestString());
0321: assertEquals(otherbean.getId(), bean5.getId());
0322: }
0323:
0324: public void testJoin() {
0325: SimpleBean bean1 = new SimpleBean();
0326: SimpleBean bean2 = new SimpleBean();
0327: SimpleBean bean3 = new SimpleBean();
0328: SimpleBean bean4 = new SimpleBean();
0329: SimpleBean bean5 = new SimpleBean();
0330:
0331: LinkBean linkbean1 = new LinkBean();
0332: LinkBean linkbean2 = new LinkBean();
0333:
0334: linkbean1.setTestString("linkbean 1");
0335: linkbean2.setTestString("linkbean 2");
0336:
0337: mLinkManager.save(linkbean1);
0338: mLinkManager.save(linkbean2);
0339:
0340: bean1.setTestString("bean set 1");
0341: bean2.setTestString("bean set 1");
0342: bean3.setTestString("bean set 1");
0343: bean4.setTestString("bean set 2");
0344: bean5.setTestString("bean set 2");
0345:
0346: bean1.setLinkBean(linkbean1.getId());
0347: bean2.setLinkBean(linkbean1.getId());
0348: bean3.setLinkBean(linkbean1.getId());
0349: bean4.setLinkBean(linkbean2.getId());
0350: bean5.setLinkBean(linkbean2.getId());
0351:
0352: mManager.save(bean1);
0353: mManager.save(bean2);
0354: mManager.save(bean3);
0355: mManager.save(bean4);
0356: mManager.save(bean5);
0357:
0358: String table = mManager.getTable();
0359: String table2 = mLinkManager.getTable();
0360:
0361: RestoreQuery query = mManager.getRestoreQuery().join(table2)
0362: .where(table2 + ".id = " + table + ".linkBean")
0363: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
0364:
0365: List<SimpleBean> list = mManager.restore(query);
0366:
0367: assertEquals(list.size(), 2);
0368: }
0369:
0370: public void testJoinCross() {
0371: SimpleBean bean1 = new SimpleBean();
0372: SimpleBean bean2 = new SimpleBean();
0373: SimpleBean bean3 = new SimpleBean();
0374: SimpleBean bean4 = new SimpleBean();
0375: SimpleBean bean5 = new SimpleBean();
0376:
0377: LinkBean linkbean1 = new LinkBean();
0378: LinkBean linkbean2 = new LinkBean();
0379:
0380: linkbean1.setTestString("linkbean 1");
0381: linkbean2.setTestString("linkbean 2");
0382:
0383: mLinkManager.save(linkbean1);
0384: mLinkManager.save(linkbean2);
0385:
0386: bean1.setTestString("bean set 1");
0387: bean2.setTestString("bean set 1");
0388: bean3.setTestString("bean set 1");
0389: bean4.setTestString("bean set 2");
0390: bean5.setTestString("bean set 2");
0391:
0392: bean1.setLinkBean(linkbean1.getId());
0393: bean2.setLinkBean(linkbean1.getId());
0394: bean3.setLinkBean(linkbean1.getId());
0395: bean4.setLinkBean(linkbean2.getId());
0396: bean5.setLinkBean(linkbean2.getId());
0397:
0398: mManager.save(bean1);
0399: mManager.save(bean2);
0400: mManager.save(bean3);
0401: mManager.save(bean4);
0402: mManager.save(bean5);
0403:
0404: String table = mManager.getTable();
0405: String table2 = mLinkManager.getTable();
0406:
0407: RestoreQuery query = mManager.getRestoreQuery().joinCross(
0408: table2).where(table2 + ".id = " + table + ".linkBean")
0409: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
0410:
0411: try {
0412: List<SimpleBean> list = mManager.restore(query);
0413:
0414: assertEquals(list.size(), 2);
0415:
0416: if ("org.hsqldb.jdbcDriver".equals(mDatasource
0417: .getAliasedDriver())
0418: || "org.firebirdsql.jdbc.FBDriver"
0419: .equals(mDatasource.getAliasedDriver())
0420: || "com.mckoi.JDBCDriver".equals(mDatasource
0421: .getAliasedDriver())
0422: || "org.apache.derby.jdbc.EmbeddedDriver"
0423: .equals(mDatasource.getAliasedDriver())) {
0424: fail();
0425: }
0426: } catch (UnsupportedSqlFeatureException e) {
0427: assertEquals("CROSS JOIN", e.getFeature());
0428: assertTrue("org.hsqldb.jdbcDriver".equals(e.getDriver())
0429: || "org.firebirdsql.jdbc.FBDriver".equals(e
0430: .getDriver())
0431: || "com.mckoi.JDBCDriver".equals(e.getDriver())
0432: || "org.apache.derby.jdbc.EmbeddedDriver".equals(e
0433: .getDriver()));
0434: }
0435: }
0436:
0437: public void testJoinInner() {
0438: SimpleBean bean1 = new SimpleBean();
0439: SimpleBean bean2 = new SimpleBean();
0440: SimpleBean bean3 = new SimpleBean();
0441: SimpleBean bean4 = new SimpleBean();
0442: SimpleBean bean5 = new SimpleBean();
0443:
0444: LinkBean linkbean1 = new LinkBean();
0445: LinkBean linkbean2 = new LinkBean();
0446:
0447: linkbean1.setTestString("linkbean 1");
0448: linkbean2.setTestString("linkbean 2");
0449:
0450: mLinkManager.save(linkbean1);
0451: mLinkManager.save(linkbean2);
0452:
0453: bean1.setTestString("bean set 1");
0454: bean2.setTestString("bean set 1");
0455: bean3.setTestString("bean set 1");
0456: bean4.setTestString("bean set 2");
0457: bean5.setTestString("bean set 2");
0458:
0459: bean1.setLinkBean(linkbean1.getId());
0460: bean2.setLinkBean(linkbean1.getId());
0461: bean3.setLinkBean(linkbean1.getId());
0462: bean4.setLinkBean(linkbean2.getId());
0463: bean5.setLinkBean(linkbean2.getId());
0464:
0465: mManager.save(bean1);
0466: mManager.save(bean2);
0467: mManager.save(bean3);
0468: mManager.save(bean4);
0469: mManager.save(bean5);
0470:
0471: String table = mManager.getTable();
0472: String table2 = mLinkManager.getTable();
0473:
0474: RestoreQuery query = mManager.getRestoreQuery().joinInner(
0475: table2, Select.ON, "0 = 0")
0476: // evals to true for mysql sake
0477: .where(table2 + ".id = " + table + ".linkBean")
0478: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
0479:
0480: List<SimpleBean> list = mManager.restore(query);
0481:
0482: assertEquals(list.size(), 2);
0483: }
0484:
0485: public void testJoinOuter() {
0486: SimpleBean bean1 = new SimpleBean();
0487: SimpleBean bean2 = new SimpleBean();
0488: SimpleBean bean3 = new SimpleBean();
0489: SimpleBean bean4 = new SimpleBean();
0490: SimpleBean bean5 = new SimpleBean();
0491:
0492: LinkBean linkbean1 = new LinkBean();
0493: LinkBean linkbean2 = new LinkBean();
0494:
0495: linkbean1.setTestString("linkbean 1");
0496: linkbean2.setTestString("linkbean 2");
0497:
0498: mLinkManager.save(linkbean1);
0499: mLinkManager.save(linkbean2);
0500:
0501: bean1.setTestString("bean set 1");
0502: bean2.setTestString("bean set 1");
0503: bean3.setTestString("bean set 1");
0504: bean4.setTestString("bean set 2");
0505: bean5.setTestString("bean set 2");
0506:
0507: bean1.setLinkBean(linkbean1.getId());
0508: bean2.setLinkBean(linkbean1.getId());
0509: bean3.setLinkBean(linkbean1.getId());
0510: bean4.setLinkBean(linkbean2.getId());
0511: bean5.setLinkBean(linkbean2.getId());
0512:
0513: mManager.save(bean1);
0514: mManager.save(bean2);
0515: mManager.save(bean3);
0516: mManager.save(bean4);
0517: mManager.save(bean5);
0518:
0519: String table = mManager.getTable();
0520: String table2 = mLinkManager.getTable();
0521:
0522: RestoreQuery query = mManager.getRestoreQuery().joinOuter(
0523: table2, Select.LEFT, Select.ON, "0 = 0")
0524: // evals to true for mysql sake
0525: .where(table2 + ".id = " + table + ".linkBean")
0526: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
0527:
0528: List<SimpleBean> list = mManager.restore(query);
0529:
0530: assertEquals(list.size(), 2);
0531: }
0532:
0533: public void testJoinCustom() {
0534: SimpleBean bean1 = new SimpleBean();
0535: SimpleBean bean2 = new SimpleBean();
0536: SimpleBean bean3 = new SimpleBean();
0537: SimpleBean bean4 = new SimpleBean();
0538: SimpleBean bean5 = new SimpleBean();
0539:
0540: LinkBean linkbean1 = new LinkBean();
0541: LinkBean linkbean2 = new LinkBean();
0542:
0543: linkbean1.setTestString("linkbean 1");
0544: linkbean2.setTestString("linkbean 2");
0545:
0546: mLinkManager.save(linkbean1);
0547: mLinkManager.save(linkbean2);
0548:
0549: bean1.setTestString("bean set 1");
0550: bean2.setTestString("bean set 1");
0551: bean3.setTestString("bean set 1");
0552: bean4.setTestString("bean set 2");
0553: bean5.setTestString("bean set 2");
0554:
0555: bean1.setLinkBean(linkbean1.getId());
0556: bean2.setLinkBean(linkbean1.getId());
0557: bean3.setLinkBean(linkbean1.getId());
0558: bean4.setLinkBean(linkbean2.getId());
0559: bean5.setLinkBean(linkbean2.getId());
0560:
0561: mManager.save(bean1);
0562: mManager.save(bean2);
0563: mManager.save(bean3);
0564: mManager.save(bean4);
0565: mManager.save(bean5);
0566:
0567: String table = mManager.getTable();
0568: String table2 = mLinkManager.getTable();
0569:
0570: RestoreQuery query = mManager.getRestoreQuery().joinCustom(
0571: "LEFT OUTER JOIN " + table2 + " ON 0 = 0")
0572: // evals to true for mysql sake
0573: .where(table2 + ".id = " + table + ".linkBean")
0574: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
0575:
0576: List<SimpleBean> list = mManager.restore(query);
0577:
0578: assertEquals(list.size(), 2);
0579: }
0580:
0581: public void testOrderBy() throws DatabaseException {
0582: SimpleBean bean1 = new SimpleBean();
0583: SimpleBean bean2 = new SimpleBean();
0584: SimpleBean bean3 = new SimpleBean();
0585:
0586: bean1.setTestString("This is bean1");
0587: bean2.setTestString("This is bean2");
0588: bean3.setTestString("This is bean3");
0589:
0590: mManager.save(bean1);
0591: mManager.save(bean2);
0592: mManager.save(bean3);
0593:
0594: List<SimpleBean> list = mManager.restore(mManager
0595: .getRestoreQuery().orderBy("id", Select.DESC));
0596:
0597: assertEquals(list.size(), 3);
0598:
0599: assertEquals(list.get(0).getId(), bean3.getId());
0600: assertEquals(list.get(1).getId(), bean2.getId());
0601: assertEquals(list.get(2).getId(), bean1.getId());
0602: }
0603:
0604: public void testWhere() throws DatabaseException {
0605: BeanImpl bean1 = new BeanImpl();
0606:
0607: Calendar cal = Calendar.getInstance();
0608: cal.set(2004, 6, 19, 16, 27, 15);
0609: cal.set(Calendar.MILLISECOND, 765);
0610: bean1
0611: .setPropertyBigDecimal(new BigDecimal(
0612: "384834838434.38483"));
0613: bean1.setPropertyBoolean(false);
0614: bean1.setPropertyBooleanObject(true);
0615: bean1.setPropertyByte((byte) 90);
0616: bean1.setPropertyByteObject((byte) 35);
0617: bean1.setPropertyCalendar(cal);
0618: bean1.setPropertyChar('w');
0619: bean1.setPropertyCharacterObject('s');
0620: bean1.setPropertyDate(cal.getTime());
0621: bean1.setPropertyDouble(37478.34d);
0622: bean1.setPropertyDoubleObject(384724.692d);
0623: bean1.setPropertyFloat(34241.2f);
0624: bean1.setPropertyFloatObject(3432.7f);
0625: bean1.setPropertyLong(23432L);
0626: bean1.setPropertyLongObject(23423L);
0627: bean1.setPropertyShort((short) 44);
0628: bean1.setPropertyShortObject((short) 69);
0629: bean1.setPropertyIntegerObject(421);
0630: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
0631: .getTime()));
0632: bean1.setPropertyString("nostringhere");
0633: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
0634: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
0635: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
0636: .getTime()));
0637:
0638: mBigBeanManager.save(bean1);
0639:
0640: List<BeanImpl> list = mBigBeanManager.restore(mBigBeanManager
0641: .getRestoreQuery().where(
0642: "propertyString = 'nostringhere'"));
0643: assertEquals(list.size(), 1);
0644:
0645: list = mBigBeanManager
0646: .restore(mBigBeanManager.getRestoreQuery().where(
0647: "propertyBoolean", "=", false));
0648: assertEquals(list.size(), 1);
0649:
0650: list = mBigBeanManager.restore(mBigBeanManager
0651: .getRestoreQuery()
0652: .where("propertyByte", "=", (byte) 90));
0653: assertEquals(list.size(), 1);
0654:
0655: list = mBigBeanManager.restore(mBigBeanManager
0656: .getRestoreQuery().where("propertyChar", "=", 'w'));
0657: assertEquals(list.size(), 1);
0658:
0659: list = mBigBeanManager.restore(mBigBeanManager
0660: .getRestoreQuery().where("propertyDouble", "=",
0661: 37478.34d));
0662: assertEquals(list.size(), 1);
0663:
0664: if (!("org.postgresql.Driver".equals(mDatasource
0665: .getAliasedDriver()))
0666: && !("com.mysql.jdbc.Driver".equals(mDatasource
0667: .getAliasedDriver()))
0668: && !("org.hsqldb.jdbcDriver".equals(mDatasource
0669: .getAliasedDriver()))
0670: && !("org.h2.Driver".equals(mDatasource
0671: .getAliasedDriver()))
0672: && !("org.apache.derby.jdbc.EmbeddedDriver"
0673: .equals(mDatasource.getAliasedDriver()))) // skip this for pgsql, mysql, hsqldb, h2 and derby since it doesn't work
0674: {
0675: list = mBigBeanManager.restore(mBigBeanManager
0676: .getRestoreQuery().where("propertyFloat", "=",
0677: 34241.2f));
0678:
0679: assertEquals(list.size(), 1);
0680: }
0681:
0682: list = mBigBeanManager.restore(mBigBeanManager
0683: .getRestoreQuery().where("propertyLong", "=", 23432L));
0684: assertEquals(list.size(), 1);
0685:
0686: list = mBigBeanManager.restore(mBigBeanManager
0687: .getRestoreQuery().where("propertyShort", "=",
0688: (short) 44));
0689: assertEquals(list.size(), 1);
0690:
0691: // cheating because the GQM doesn't currently return any queries with a where clause already
0692: RestoreQuery query = new RestoreQuery(mBigBeanManager
0693: .getRestoreQuery().where("id", "=", bean1.getId())
0694: .getDelegate());
0695:
0696: list = mBigBeanManager.restore(query
0697: .where("propertyString = 'nostringhere'"));
0698: assertEquals(list.size(), 1);
0699:
0700: list = mBigBeanManager.restore(query.where("propertyBoolean",
0701: "=", false));
0702: assertEquals(list.size(), 1);
0703:
0704: list = mBigBeanManager.restore(query.where("propertyByte", "=",
0705: (byte) 90));
0706: assertEquals(list.size(), 1);
0707:
0708: list = mBigBeanManager.restore(query.where("propertyChar", "=",
0709: 'w'));
0710: assertEquals(list.size(), 1);
0711:
0712: list = mBigBeanManager.restore(query.where("propertyDouble",
0713: "=", 37478.34d));
0714: assertEquals(list.size(), 1);
0715:
0716: if (!("org.postgresql.Driver".equals(mDatasource
0717: .getAliasedDriver()))
0718: && !("com.mysql.jdbc.Driver".equals(mDatasource
0719: .getAliasedDriver()))
0720: && !("org.hsqldb.jdbcDriver".equals(mDatasource
0721: .getAliasedDriver()))
0722: && !("org.h2.Driver".equals(mDatasource
0723: .getAliasedDriver()))
0724: && !("org.apache.derby.jdbc.EmbeddedDriver"
0725: .equals(mDatasource.getAliasedDriver()))) // skip this for postgres, mysql, hsqldb, h2 and derby since it doesn't work
0726: {
0727: list = mBigBeanManager.restore(query.where("propertyFloat",
0728: "=", 34241.2f));
0729:
0730: assertEquals(list.size(), 1);
0731: }
0732:
0733: list = mBigBeanManager.restore(query.where("id", "=", bean1
0734: .getId())); // primary key
0735: assertEquals(list.size(), 1);
0736:
0737: list = mBigBeanManager.restore(query.where("propertyLong", "=",
0738: 23432L));
0739: assertEquals(list.size(), 1);
0740:
0741: list = mBigBeanManager.restore(query.where("propertyShort",
0742: "=", (short) 44));
0743: assertEquals(list.size(), 1);
0744: }
0745:
0746: public void testWhereAnd() {
0747: BeanImpl bean1 = new BeanImpl();
0748:
0749: Calendar cal = Calendar.getInstance();
0750: cal.set(2004, 6, 19, 16, 27, 15);
0751: cal.set(Calendar.MILLISECOND, 765);
0752: bean1
0753: .setPropertyBigDecimal(new BigDecimal(
0754: "384834838434.38483"));
0755: bean1.setPropertyBoolean(false);
0756: bean1.setPropertyBooleanObject(true);
0757: bean1.setPropertyByte((byte) 90);
0758: bean1.setPropertyByteObject((byte) 35);
0759: bean1.setPropertyCalendar(cal);
0760: bean1.setPropertyChar('w');
0761: bean1.setPropertyCharacterObject('s');
0762: bean1.setPropertyDate(cal.getTime());
0763: bean1.setPropertyDouble(37478.34d);
0764: bean1.setPropertyDoubleObject(384724.692d);
0765: bean1.setPropertyFloat(34241.2f);
0766: bean1.setPropertyFloatObject(3432.7f);
0767: bean1.setPropertyLong(23432L);
0768: bean1.setPropertyLongObject(23423L);
0769: bean1.setPropertyShort((short) 44);
0770: bean1.setPropertyShortObject((short) 69);
0771: bean1.setPropertyIntegerObject(421);
0772: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
0773: .getTime()));
0774: bean1.setPropertyString("nostringhere");
0775: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
0776: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
0777: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
0778: .getTime()));
0779:
0780: mBigBeanManager.save(bean1);
0781:
0782: List<BeanImpl> list = mBigBeanManager.restore(mBigBeanManager
0783: .getRestoreQuery().where("id", "=", bean1.getId())
0784: .whereAnd("propertyString = 'nostringhere'").whereAnd(
0785: "propertyBoolean", "=", false).whereAnd(
0786: "propertyByte", "=", (byte) 90).whereAnd(
0787: "propertyChar", "=", 'w').whereAnd(
0788: "propertyDouble", "=", 37478.34d).whereAnd(
0789: "propertyLong", "=", 23432L).whereAnd(
0790: "propertyString", "=", "nostringhere")
0791: .whereAnd("propertyIntegerObject", "=", 421).whereAnd(
0792: "propertyShort", "=", (short) 44));
0793:
0794: assertEquals(list.size(), 1);
0795:
0796: if (!("org.postgresql.Driver".equals(mDatasource
0797: .getAliasedDriver()))
0798: && !("com.mysql.jdbc.Driver".equals(mDatasource
0799: .getAliasedDriver()))
0800: && !("org.hsqldb.jdbcDriver".equals(mDatasource
0801: .getAliasedDriver()))
0802: && !("org.h2.Driver".equals(mDatasource
0803: .getAliasedDriver()))
0804: && !("org.apache.derby.jdbc.EmbeddedDriver"
0805: .equals(mDatasource.getAliasedDriver()))) // skip this for postgres, mysql, hsqldb, h2 and derby since it doesn't work
0806: {
0807: list = mBigBeanManager.restore(mBigBeanManager
0808: .getRestoreQuery().where("id = 1").whereAnd(
0809: "propertyFloat", "=", 34241.2f));
0810:
0811: assertEquals(list.size(), 1);
0812: }
0813: }
0814:
0815: public void testWhereOr() {
0816: BeanImpl bean1 = new BeanImpl();
0817:
0818: Calendar cal = Calendar.getInstance();
0819: cal.set(2004, 6, 19, 16, 27, 15);
0820: cal.set(Calendar.MILLISECOND, 765);
0821: bean1
0822: .setPropertyBigDecimal(new BigDecimal(
0823: "384834838434.38483"));
0824: bean1.setPropertyBoolean(false);
0825: bean1.setPropertyBooleanObject(true);
0826: bean1.setPropertyByte((byte) 90);
0827: bean1.setPropertyByteObject((byte) 35);
0828: bean1.setPropertyCalendar(cal);
0829: bean1.setPropertyChar('w');
0830: bean1.setPropertyCharacterObject('s');
0831: bean1.setPropertyDate(cal.getTime());
0832: bean1.setPropertyDouble(37478.34d);
0833: bean1.setPropertyDoubleObject(384724.692d);
0834: bean1.setPropertyFloat(34241.2f);
0835: bean1.setPropertyFloatObject(3432.7f);
0836: bean1.setPropertyLong(23432L);
0837: bean1.setPropertyLongObject(23423L);
0838: bean1.setPropertyShort((short) 44);
0839: bean1.setPropertyShortObject((short) 69);
0840: bean1.setPropertyIntegerObject(421);
0841: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
0842: .getTime()));
0843: bean1.setPropertyString("nostringhere");
0844: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
0845: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
0846: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
0847: .getTime()));
0848:
0849: mBigBeanManager.save(bean1);
0850:
0851: List<BeanImpl> list = mBigBeanManager.restore(mBigBeanManager
0852: .getRestoreQuery().where("id = 1").whereOr(
0853: "propertyString = 'nostringhere'").whereOr(
0854: "propertyBoolean", "=", false).whereOr(
0855: "propertyByte", "=", (byte) 90).whereOr(
0856: "propertyChar", "=", 'w').whereOr(
0857: "propertyDouble", "=", 37478.34d).whereOr(
0858: "propertyLong", "=", 23432L).whereOr(
0859: "propertyIntegerObject", "=", 421).whereOr(
0860: "propertyShort", "=", (short) 44).whereOr(
0861: "propertyString", "=", "nostringhere"));
0862:
0863: assertEquals(list.size(), 1);
0864:
0865: if (!("com.mysql.jdbc.Driver".equals(mDatasource
0866: .getAliasedDriver()))
0867: && !("org.hsqldb.jdbcDriver".equals(mDatasource
0868: .getAliasedDriver()))) // skip this for mysql and hsqldb since it doesn't work
0869: {
0870: list = mBigBeanManager.restore(mBigBeanManager
0871: .getRestoreQuery().where("id = 1").whereOr(
0872: "propertyFloat", "=", 34241.2f));
0873:
0874: assertEquals(list.size(), 1);
0875: }
0876: }
0877:
0878: public void testUnion() {
0879: RestoreQuery query = mManager.getRestoreQuery();
0880:
0881: query.union("uexpr1").union(
0882: new Select(mDatasource).from("table2"));
0883:
0884: if ("com.mckoi.JDBCDriver".equals(mDatasource
0885: .getAliasedDriver())) // McKoi only supports UNION All
0886: {
0887: assertEquals(query.getSql(),
0888: "SELECT * FROM simplebean UNION ALL uexpr1 UNION ALL SELECT * FROM table2");
0889: } else {
0890: assertEquals(query.getSql(),
0891: "SELECT * FROM simplebean UNION uexpr1 UNION SELECT * FROM table2");
0892: }
0893: }
0894:
0895: public void testWhereSubselect() {
0896: SimpleBean bean1 = new SimpleBean();
0897: SimpleBean bean2 = new SimpleBean();
0898: SimpleBean bean3 = new SimpleBean();
0899: SimpleBean bean4 = new SimpleBean();
0900: SimpleBean bean5 = new SimpleBean();
0901:
0902: LinkBean linkbean1 = new LinkBean();
0903: LinkBean linkbean2 = new LinkBean();
0904:
0905: linkbean1.setTestString("linkbean 1");
0906: linkbean2.setTestString("linkbean 2");
0907:
0908: mLinkManager.save(linkbean1);
0909: mLinkManager.save(linkbean2);
0910:
0911: bean1.setTestString("bean set 1");
0912: bean2.setTestString("bean set 1");
0913: bean3.setTestString("bean set 1");
0914: bean4.setTestString("bean set 2");
0915: bean5.setTestString("bean set 2");
0916:
0917: bean1.setLinkBean(linkbean1.getId());
0918: bean2.setLinkBean(linkbean1.getId());
0919: bean3.setLinkBean(linkbean1.getId());
0920: bean4.setLinkBean(linkbean2.getId());
0921: bean5.setLinkBean(linkbean2.getId());
0922:
0923: mManager.save(bean1);
0924: mManager.save(bean2);
0925: mManager.save(bean3);
0926: mManager.save(bean4);
0927: mManager.save(bean5);
0928:
0929: Select select = new Select(mDatasource);
0930: select.from(mLinkManager.getTable()).field("id").where("id",
0931: "=", linkbean1.getId());
0932:
0933: RestoreQuery query = mManager.getRestoreQuery();
0934: query.where("linkBean = (" + select.getSql() + ")")
0935: .whereSubselect(select);
0936:
0937: if (!("com.mysql.jdbc.Driver".equals(mDatasource
0938: .getAliasedDriver()))) // skip this for mysql since it doesn't work
0939: {
0940: List<SimpleBean> list = mManager.restore(query);
0941: assertEquals(list.size(), 3);
0942: }
0943: }
0944:
0945: public void testFields() {
0946: SimpleBean bean1 = new SimpleBean();
0947: SimpleBean bean2 = new SimpleBean();
0948:
0949: LinkBean linkbean1 = new LinkBean();
0950:
0951: linkbean1.setTestString("linkbean 1");
0952:
0953: bean1.setTestString("bean set 1");
0954: bean2.setTestString("bean set 1");
0955:
0956: bean1.setLinkBean(1);
0957: bean2.setLinkBean(1);
0958:
0959: mLinkManager.save(linkbean1);
0960:
0961: mManager.save(bean1);
0962: mManager.save(bean2);
0963:
0964: RestoreQuery query = mManager.getRestoreQuery();
0965: query.field("testString");
0966:
0967: List<SimpleBean> bean_list = mManager.restore(query);
0968:
0969: for (SimpleBean bean : bean_list) {
0970: assertEquals(-1, bean.getLinkBean());
0971: assertEquals("bean set 1", bean.getTestString());
0972: }
0973:
0974: query = mManager.getRestoreQuery();
0975: query.fields(SimpleBean.class);
0976:
0977: bean_list = mManager.restore(query);
0978:
0979: query = mManager.getRestoreQuery();
0980: query.fields("simplebean", SimpleBean.class);
0981:
0982: bean_list = mManager.restore(query);
0983:
0984: for (SimpleBean bean : bean_list) {
0985: assertEquals(1, bean.getLinkBean());
0986: assertEquals("bean set 1", bean.getTestString());
0987: }
0988:
0989: query = mManager.getRestoreQuery();
0990: query.fieldsExcluded(SimpleBean.class, "testString");
0991:
0992: bean_list = mManager.restore(query);
0993:
0994: query = mManager.getRestoreQuery();
0995: query.fieldsExcluded("simplebean", SimpleBean.class,
0996: "testString");
0997:
0998: bean_list = mManager.restore(query);
0999:
1000: for (SimpleBean bean : bean_list) {
1001: assertEquals(1, bean.getLinkBean());
1002: assertEquals(null, bean.getTestString());
1003: }
1004:
1005: query = mManager.getRestoreQuery();
1006: query.fields(new String[] { "linkBean" });
1007:
1008: bean_list = mManager.restore(query);
1009:
1010: for (SimpleBean bean : bean_list) {
1011: assertEquals(1, bean.getLinkBean());
1012: assertEquals(null, bean.getTestString());
1013: }
1014:
1015: Select select = new Select(mDatasource).field("name").from(
1016: "tablename");
1017:
1018: query = mManager.getRestoreQuery();
1019: query.fieldSubselect(select).field('(' + select.getSql() + ')');
1020:
1021: assertEquals(
1022: "SELECT (SELECT name FROM tablename) FROM simplebean",
1023: query.getSql());
1024: }
1025:
1026: public void testWhereGroupChaining() {
1027: SimpleBean bean1 = new SimpleBean();
1028: bean1.setTestString("test");
1029:
1030: mManager.save(bean1);
1031:
1032: RestoreQuery query = mManager.getRestoreQuery().where("id",
1033: ">=", 0).startWhereAnd().where("testString", "=",
1034: "test").end();
1035:
1036: assertTrue(mManager.restore(query).size() > 0);
1037: }
1038: }
|