001: /*
002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com> and
003: * JR Boyens <gnu-jrb[remove] at gmx dot net>
004: * Distributed under the terms of either:
005: * - the common development and distribution license (CDDL), v1.0; or
006: * - the GNU Lesser General Public License, v2.1 or later
007: * $Id: TestCountQuery.java 3634 2007-01-08 21:42:24Z gbevin $
008: */
009: package com.uwyn.rife.database.querymanagers.generic;
010:
011: import com.uwyn.rife.database.Datasource;
012: import com.uwyn.rife.database.exceptions.DatabaseException;
013: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
014: import com.uwyn.rife.database.queries.Select;
015: import com.uwyn.rife.database.querymanagers.generic.beans.BeanImpl;
016: import com.uwyn.rife.database.querymanagers.generic.beans.LinkBean;
017: import com.uwyn.rife.database.querymanagers.generic.beans.SimpleBean;
018: import java.math.BigDecimal;
019: import java.sql.Time;
020: import java.sql.Timestamp;
021: import java.util.Calendar;
022: import junit.framework.TestCase;
023:
024: public class TestCountQuery extends TestCase {
025: private Datasource mDatasource = null;
026: private GenericQueryManager<SimpleBean> mManager = null;
027: private GenericQueryManager<LinkBean> mLinkManager = null;
028: private GenericQueryManager<BeanImpl> mBigBeanManager = null;
029:
030: public TestCountQuery(Datasource datasource, String datasourceName,
031: String name) {
032: super (name);
033: mDatasource = datasource;
034: }
035:
036: protected void setUp() {
037: mManager = GenericQueryManagerFactory.getInstance(mDatasource,
038: SimpleBean.class);
039: mLinkManager = GenericQueryManagerFactory.getInstance(
040: mDatasource, LinkBean.class);
041: mBigBeanManager = GenericQueryManagerFactory.getInstance(
042: mDatasource, BeanImpl.class);
043:
044: int poolsize = mDatasource.getPoolsize();
045: // disabling pool for firebird
046: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
047: .getAliasedDriver())) {
048: mDatasource.setPoolsize(0);
049: }
050: try {
051: mManager.install();
052: mLinkManager.install();
053: mBigBeanManager.install();
054: } finally {
055: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
056: .getAliasedDriver())) {
057: mDatasource.setPoolsize(poolsize);
058: }
059: }
060: }
061:
062: protected void tearDown() {
063: int poolsize = mDatasource.getPoolsize();
064: // disabling pool for firebird
065: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
066: .getAliasedDriver())) {
067: mDatasource.setPoolsize(0);
068: }
069: try {
070: mManager.remove();
071: mLinkManager.remove();
072: mBigBeanManager.remove();
073: } finally {
074: if ("org.firebirdsql.jdbc.FBDriver".equals(mDatasource
075: .getAliasedDriver())) {
076: mDatasource.setPoolsize(poolsize);
077: }
078: }
079: }
080:
081: public void testCloneToStringAndClear() {
082: CountQuery query = mManager.getCountQuery().where("testString",
083: "=", "bean set 1");
084:
085: assertEquals(query.toString(),
086: "SELECT count(*) FROM simplebean WHERE testString = 'bean set 1'");
087:
088: CountQuery queryclone = query.clone();
089:
090: assertEquals(queryclone.toString(),
091: "SELECT count(*) FROM simplebean WHERE testString = 'bean set 1'");
092:
093: queryclone.where("testString", "!=", "bean set 2");
094:
095: assertEquals(
096: queryclone.toString(),
097: "SELECT count(*) FROM simplebean WHERE testString = 'bean set 1' AND testString != 'bean set 2'");
098:
099: queryclone.clear();
100:
101: assertEquals(queryclone.toString(),
102: "SELECT count(*) FROM simplebean WHERE testString = 'bean set 1'");
103:
104: query.clear();
105:
106: assertEquals(query.toString(),
107: "SELECT count(*) FROM simplebean");
108: }
109:
110: public void testGetDatasource() {
111: assertTrue(mDatasource.equals(mManager.getCountQuery()
112: .getDatasource()));
113: }
114:
115: public void testGetFrom() {
116: assertTrue(mManager.getCountQuery().getFrom().equals(
117: SimpleBean.class.getName().replaceAll(
118: SimpleBean.class.getPackage().getName() + ".",
119: "").toLowerCase()));
120: }
121:
122: public void testGetParameters() {
123: Select select = new Select(mDatasource);
124: select.from("simplebean").whereParameter("testString", "=");
125:
126: CountQuery query = new CountQuery(select);
127:
128: assertEquals(query.getParameters().getOrderedNames().size(), 1);
129: assertTrue(query.getParameters().getOrderedNames().contains(
130: "testString"));
131:
132: assertEquals(
133: query.getParameters().getOrderedNamesArray().length, 1);
134: assertEquals(query.getParameters().getOrderedNamesArray()[0],
135: "testString");
136: }
137:
138: public void testJoin() {
139: SimpleBean bean1 = new SimpleBean();
140: SimpleBean bean2 = new SimpleBean();
141: SimpleBean bean3 = new SimpleBean();
142: SimpleBean bean4 = new SimpleBean();
143: SimpleBean bean5 = new SimpleBean();
144:
145: LinkBean linkbean1 = new LinkBean();
146: LinkBean linkbean2 = new LinkBean();
147:
148: linkbean1.setTestString("linkbean 1");
149: linkbean2.setTestString("linkbean 2");
150:
151: mLinkManager.save(linkbean1);
152: mLinkManager.save(linkbean2);
153:
154: bean1.setTestString("bean set 1");
155: bean2.setTestString("bean set 1");
156: bean3.setTestString("bean set 1");
157: bean4.setTestString("bean set 2");
158: bean5.setTestString("bean set 2");
159:
160: bean1.setLinkBean(linkbean1.getId());
161: bean2.setLinkBean(linkbean1.getId());
162: bean3.setLinkBean(linkbean1.getId());
163: bean4.setLinkBean(linkbean2.getId());
164: bean5.setLinkBean(linkbean2.getId());
165:
166: mManager.save(bean1);
167: mManager.save(bean2);
168: mManager.save(bean3);
169: mManager.save(bean4);
170: mManager.save(bean5);
171:
172: String table = mManager.getTable();
173: String table2 = mLinkManager.getTable();
174:
175: CountQuery query = mManager.getCountQuery().join(table2).where(
176: table2 + ".id = " + table + ".linkBean").whereAnd(
177: table + ".linkBean", "=", linkbean2.getId());
178:
179: assertEquals(2, mManager.count(query));
180: }
181:
182: public void testJoinCross() {
183: SimpleBean bean1 = new SimpleBean();
184: SimpleBean bean2 = new SimpleBean();
185: SimpleBean bean3 = new SimpleBean();
186: SimpleBean bean4 = new SimpleBean();
187: SimpleBean bean5 = new SimpleBean();
188:
189: LinkBean linkbean1 = new LinkBean();
190: LinkBean linkbean2 = new LinkBean();
191:
192: linkbean1.setTestString("linkbean 1");
193: linkbean2.setTestString("linkbean 2");
194:
195: mLinkManager.save(linkbean1);
196: mLinkManager.save(linkbean2);
197:
198: bean1.setTestString("bean set 1");
199: bean2.setTestString("bean set 1");
200: bean3.setTestString("bean set 1");
201: bean4.setTestString("bean set 2");
202: bean5.setTestString("bean set 2");
203:
204: bean1.setLinkBean(linkbean1.getId());
205: bean2.setLinkBean(linkbean1.getId());
206: bean3.setLinkBean(linkbean1.getId());
207: bean4.setLinkBean(linkbean2.getId());
208: bean5.setLinkBean(linkbean2.getId());
209:
210: mManager.save(bean1);
211: mManager.save(bean2);
212: mManager.save(bean3);
213: mManager.save(bean4);
214: mManager.save(bean5);
215:
216: String table = mManager.getTable();
217: String table2 = mLinkManager.getTable();
218:
219: CountQuery query = mManager.getCountQuery().joinCross(table2)
220: .where(table2 + ".id = " + table + ".linkBean")
221: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
222:
223: try {
224: assertEquals(2, mManager.count(query));
225:
226: if ("org.hsqldb.jdbcDriver".equals(mDatasource
227: .getAliasedDriver())
228: || "org.firebirdsql.jdbc.FBDriver"
229: .equals(mDatasource.getAliasedDriver())
230: || "com.mckoi.JDBCDriver".equals(mDatasource
231: .getAliasedDriver())
232: || "org.apache.derby.jdbc.EmbeddedDriver"
233: .equals(mDatasource.getAliasedDriver())) {
234: fail();
235: }
236: } catch (UnsupportedSqlFeatureException e) {
237: assertEquals("CROSS JOIN", e.getFeature());
238: assertTrue("org.hsqldb.jdbcDriver".equals(e.getDriver())
239: || "org.firebirdsql.jdbc.FBDriver".equals(e
240: .getDriver())
241: || "com.mckoi.JDBCDriver".equals(e.getDriver())
242: || "org.apache.derby.jdbc.EmbeddedDriver".equals(e
243: .getDriver()));
244: }
245: }
246:
247: public void testJoinInner() {
248: SimpleBean bean1 = new SimpleBean();
249: SimpleBean bean2 = new SimpleBean();
250: SimpleBean bean3 = new SimpleBean();
251: SimpleBean bean4 = new SimpleBean();
252: SimpleBean bean5 = new SimpleBean();
253:
254: LinkBean linkbean1 = new LinkBean();
255: LinkBean linkbean2 = new LinkBean();
256:
257: linkbean1.setTestString("linkbean 1");
258: linkbean2.setTestString("linkbean 2");
259:
260: mLinkManager.save(linkbean1);
261: mLinkManager.save(linkbean2);
262:
263: bean1.setTestString("bean set 1");
264: bean2.setTestString("bean set 1");
265: bean3.setTestString("bean set 1");
266: bean4.setTestString("bean set 2");
267: bean5.setTestString("bean set 2");
268:
269: bean1.setLinkBean(linkbean1.getId());
270: bean2.setLinkBean(linkbean1.getId());
271: bean3.setLinkBean(linkbean1.getId());
272: bean4.setLinkBean(linkbean2.getId());
273: bean5.setLinkBean(linkbean2.getId());
274:
275: mManager.save(bean1);
276: mManager.save(bean2);
277: mManager.save(bean3);
278: mManager.save(bean4);
279: mManager.save(bean5);
280:
281: String table = mManager.getTable();
282: String table2 = mLinkManager.getTable();
283:
284: CountQuery query = mManager.getCountQuery().joinInner(table2,
285: Select.ON, "0 = 0")
286: // evals to true for mysql sake
287: .where(table2 + ".id = " + table + ".linkBean")
288: .whereAnd(table + ".linkBean", "=", linkbean2.getId());
289:
290: assertEquals(2, mManager.count(query));
291: }
292:
293: public void testJoinOuter() {
294: SimpleBean bean1 = new SimpleBean();
295: SimpleBean bean2 = new SimpleBean();
296: SimpleBean bean3 = new SimpleBean();
297: SimpleBean bean4 = new SimpleBean();
298: SimpleBean bean5 = new SimpleBean();
299:
300: LinkBean linkbean1 = new LinkBean();
301: LinkBean linkbean2 = new LinkBean();
302:
303: linkbean1.setTestString("linkbean 1");
304: linkbean2.setTestString("linkbean 2");
305:
306: bean1.setTestString("bean set 1");
307: bean2.setTestString("bean set 1");
308: bean3.setTestString("bean set 1");
309: bean4.setTestString("bean set 2");
310: bean5.setTestString("bean set 2");
311:
312: bean1.setLinkBean(1);
313: bean2.setLinkBean(1);
314: bean3.setLinkBean(1);
315: bean4.setLinkBean(2);
316: bean5.setLinkBean(2);
317:
318: mLinkManager.save(linkbean1);
319: mLinkManager.save(linkbean2);
320:
321: mManager.save(bean1);
322: mManager.save(bean2);
323: mManager.save(bean3);
324: mManager.save(bean4);
325: mManager.save(bean5);
326:
327: String table = mManager.getTable();
328: String table2 = mLinkManager.getTable();
329:
330: CountQuery query = mManager.getCountQuery().joinOuter(table2,
331: Select.LEFT, Select.ON,
332: table2 + ".id = " + table + ".linkBean") // evals to true for mysql sake
333: .where(table + ".linkBean = 2");
334:
335: assertEquals(2, mManager.count(query));
336: }
337:
338: public void testJoinCustom() {
339: SimpleBean bean1 = new SimpleBean();
340: SimpleBean bean2 = new SimpleBean();
341: SimpleBean bean3 = new SimpleBean();
342: SimpleBean bean4 = new SimpleBean();
343: SimpleBean bean5 = new SimpleBean();
344:
345: LinkBean linkbean1 = new LinkBean();
346: LinkBean linkbean2 = new LinkBean();
347:
348: linkbean1.setTestString("linkbean 1");
349: linkbean2.setTestString("linkbean 2");
350:
351: bean1.setTestString("bean set 1");
352: bean2.setTestString("bean set 1");
353: bean3.setTestString("bean set 1");
354: bean4.setTestString("bean set 2");
355: bean5.setTestString("bean set 2");
356:
357: bean1.setLinkBean(1);
358: bean2.setLinkBean(1);
359: bean3.setLinkBean(1);
360: bean4.setLinkBean(2);
361: bean5.setLinkBean(2);
362:
363: mLinkManager.save(linkbean1);
364: mLinkManager.save(linkbean2);
365:
366: mManager.save(bean1);
367: mManager.save(bean2);
368: mManager.save(bean3);
369: mManager.save(bean4);
370: mManager.save(bean5);
371:
372: String table = mManager.getTable();
373: String table2 = mLinkManager.getTable();
374:
375: CountQuery query = mManager.getCountQuery().joinCustom(
376: "LEFT OUTER JOIN " + table2 + " ON " + table2
377: + ".id = " + table + ".linkBean") // evals to true for mysql sake
378: .where(table + ".linkBean = 2");
379:
380: assertEquals(2, mManager.count(query));
381: }
382:
383: public void testWhere() throws DatabaseException {
384: BeanImpl bean1 = new BeanImpl();
385:
386: Calendar cal = Calendar.getInstance();
387: cal.set(2004, 6, 19, 16, 27, 15);
388: cal.set(Calendar.MILLISECOND, 765);
389: bean1
390: .setPropertyBigDecimal(new BigDecimal(
391: "384834838434.38483"));
392: bean1.setPropertyBoolean(false);
393: bean1.setPropertyBooleanObject(true);
394: bean1.setPropertyByte((byte) 90);
395: bean1.setPropertyByteObject((byte) 35);
396: bean1.setPropertyCalendar(cal);
397: bean1.setPropertyChar('w');
398: bean1.setPropertyCharacterObject('s');
399: bean1.setPropertyDate(cal.getTime());
400: bean1.setPropertyDouble(37478.34d);
401: bean1.setPropertyDoubleObject(384724.692d);
402: bean1.setPropertyFloat(34241.2f);
403: bean1.setPropertyFloatObject(3432.7f);
404: bean1.setPropertyLong(23432L);
405: bean1.setPropertyLongObject(23423L);
406: bean1.setPropertyShort((short) 44);
407: bean1.setPropertyShortObject((short) 69);
408: bean1.setPropertyIntegerObject(421);
409: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
410: .getTime()));
411: bean1.setPropertyString("nostringhere");
412: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
413: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
414: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
415: .getTime()));
416:
417: mBigBeanManager.save(bean1);
418:
419: assertEquals(1, mBigBeanManager.count(mBigBeanManager
420: .getCountQuery().where(
421: "propertyString = 'nostringhere'")));
422:
423: assertEquals(1, mBigBeanManager.count(mBigBeanManager
424: .getCountQuery().where("propertyBoolean", "=", false)));
425:
426: assertEquals(1, mBigBeanManager.count(mBigBeanManager
427: .getCountQuery().where("propertyByte", "=", (byte) 90)));
428:
429: assertEquals(1, mBigBeanManager.count(mBigBeanManager
430: .getCountQuery().where("propertyChar", "=", 'w')));
431:
432: assertEquals(1, mBigBeanManager.count(mBigBeanManager
433: .getCountQuery()
434: .where("propertyDouble", "=", 37478.34d)));
435:
436: if (!("org.postgresql.Driver".equals(mDatasource
437: .getAliasedDriver()))
438: && !("com.mysql.jdbc.Driver".equals(mDatasource
439: .getAliasedDriver()))
440: && !("org.hsqldb.jdbcDriver".equals(mDatasource
441: .getAliasedDriver()))
442: && !("org.h2.Driver".equals(mDatasource
443: .getAliasedDriver()))
444: && !("org.apache.derby.jdbc.EmbeddedDriver"
445: .equals(mDatasource.getAliasedDriver()))) // skip this for postgres, mysql, hsqldb, h2 and derby since it doesn't work
446: {
447: assertEquals(1, mBigBeanManager.count(mBigBeanManager
448: .getCountQuery().where("propertyFloat", "=",
449: 34241.2f)));
450: }
451:
452: assertEquals(1, mBigBeanManager.count(mBigBeanManager
453: .getCountQuery().where("propertyLong", "=", 23432L)));
454:
455: assertEquals(1, mBigBeanManager.count(mBigBeanManager
456: .getCountQuery()
457: .where("propertyShort", "=", (short) 44)));
458:
459: // cheating because the GQM doesn't currently return any queries with a where clause already
460: CountQuery query = new CountQuery(mBigBeanManager
461: .getCountQuery().where("id", "=", bean1.getId())
462: .getDelegate());
463:
464: assertEquals(1, mBigBeanManager.count(query
465: .where("propertyString = 'nostringhere'")));
466:
467: assertEquals(1, mBigBeanManager.count(query.where(
468: "propertyBoolean", "=", false)));
469:
470: assertEquals(1, mBigBeanManager.count(query.where(
471: "propertyByte", "=", (byte) 90)));
472:
473: assertEquals(1, mBigBeanManager.count(query.where(
474: "propertyChar", "=", 'w')));
475:
476: assertEquals(1, mBigBeanManager.count(query.where(
477: "propertyDouble", "=", 37478.34d)));
478:
479: if (!("org.postgresql.Driver".equals(mDatasource
480: .getAliasedDriver()))
481: && !("com.mysql.jdbc.Driver".equals(mDatasource
482: .getAliasedDriver()))
483: && !("org.hsqldb.jdbcDriver".equals(mDatasource
484: .getAliasedDriver()))
485: && !("org.h2.Driver".equals(mDatasource
486: .getAliasedDriver()))
487: && !("org.apache.derby.jdbc.EmbeddedDriver"
488: .equals(mDatasource.getAliasedDriver()))) // skip this for postgres, mysql, hsqldb, h2 and derby since it doesn't work
489: {
490: assertEquals(1, mBigBeanManager.count(query.where(
491: "propertyFloat", "=", 34241.2f)));
492: }
493:
494: assertEquals(1, mBigBeanManager.count(query.where("id", "=",
495: bean1.getId()))); // primary key
496:
497: assertEquals(1, mBigBeanManager.count(query.where(
498: "propertyLong", "=", 23432L)));
499:
500: assertEquals(1, mBigBeanManager.count(query.where(
501: "propertyShort", "=", (short) 44)));
502: }
503:
504: public void testWhereAnd() {
505: BeanImpl bean1 = new BeanImpl();
506:
507: Calendar cal = Calendar.getInstance();
508: cal.set(2004, 6, 19, 16, 27, 15);
509: cal.set(Calendar.MILLISECOND, 765);
510: bean1
511: .setPropertyBigDecimal(new BigDecimal(
512: "384834838434.38483"));
513: bean1.setPropertyBoolean(false);
514: bean1.setPropertyBooleanObject(true);
515: bean1.setPropertyByte((byte) 90);
516: bean1.setPropertyByteObject((byte) 35);
517: bean1.setPropertyCalendar(cal);
518: bean1.setPropertyChar('w');
519: bean1.setPropertyCharacterObject('s');
520: bean1.setPropertyDate(cal.getTime());
521: bean1.setPropertyDouble(37478.34d);
522: bean1.setPropertyDoubleObject(384724.692d);
523: bean1.setPropertyFloat(34241.2f);
524: bean1.setPropertyFloatObject(3432.7f);
525: bean1.setPropertyLong(23432L);
526: bean1.setPropertyLongObject(23423L);
527: bean1.setPropertyShort((short) 44);
528: bean1.setPropertyShortObject((short) 69);
529: bean1.setPropertyIntegerObject(421);
530: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
531: .getTime()));
532: bean1.setPropertyString("nostringhere");
533: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
534: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
535: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
536: .getTime()));
537:
538: mBigBeanManager.save(bean1);
539:
540: assertEquals(1, mBigBeanManager.count(mBigBeanManager
541: .getCountQuery().where("id", "=", bean1.getId())
542: .whereAnd("propertyString = 'nostringhere'").whereAnd(
543: "propertyBoolean", "=", false).whereAnd(
544: "propertyByte", "=", (byte) 90).whereAnd(
545: "propertyChar", "=", 'w').whereAnd(
546: "propertyDouble", "=", 37478.34d).whereAnd(
547: "propertyLong", "=", 23432L).whereAnd(
548: "propertyString", "=", "nostringhere")
549: .whereAnd("propertyIntegerObject", "=", 421).whereAnd(
550: "propertyShort", "=", (short) 44)));
551:
552: if (!("org.postgresql.Driver".equals(mDatasource
553: .getAliasedDriver()))
554: && !("com.mysql.jdbc.Driver".equals(mDatasource
555: .getAliasedDriver()))
556: && !("org.hsqldb.jdbcDriver".equals(mDatasource
557: .getAliasedDriver()))
558: && !("org.h2.Driver".equals(mDatasource
559: .getAliasedDriver()))
560: && !("org.apache.derby.jdbc.EmbeddedDriver"
561: .equals(mDatasource.getAliasedDriver()))) // skip this for postgres, mysql, hsqldb, h2 and derby since it doesn't work
562: {
563: assertEquals(1, mBigBeanManager.count(mBigBeanManager
564: .getCountQuery().where("id", "=", bean1.getId())
565: .whereAnd("propertyFloat", "=", 34241.2f)));
566: }
567: }
568:
569: public void testWhereOr() {
570: BeanImpl bean1 = new BeanImpl();
571:
572: Calendar cal = Calendar.getInstance();
573: cal.set(2004, 6, 19, 16, 27, 15);
574: cal.set(Calendar.MILLISECOND, 765);
575: bean1
576: .setPropertyBigDecimal(new BigDecimal(
577: "384834838434.38483"));
578: bean1.setPropertyBoolean(false);
579: bean1.setPropertyBooleanObject(true);
580: bean1.setPropertyByte((byte) 90);
581: bean1.setPropertyByteObject((byte) 35);
582: bean1.setPropertyCalendar(cal);
583: bean1.setPropertyChar('w');
584: bean1.setPropertyCharacterObject('s');
585: bean1.setPropertyDate(cal.getTime());
586: bean1.setPropertyDouble(37478.34d);
587: bean1.setPropertyDoubleObject(384724.692d);
588: bean1.setPropertyFloat(34241.2f);
589: bean1.setPropertyFloatObject(3432.7f);
590: bean1.setPropertyLong(23432L);
591: bean1.setPropertyLongObject(23423L);
592: bean1.setPropertyShort((short) 44);
593: bean1.setPropertyShortObject((short) 69);
594: bean1.setPropertyIntegerObject(421);
595: bean1.setPropertySqlDate(new java.sql.Date(cal.getTime()
596: .getTime()));
597: bean1.setPropertyString("nostringhere");
598: bean1.setPropertyStringbuffer(new StringBuffer("buffbuffbuff"));
599: bean1.setPropertyTime(new Time(cal.getTime().getTime()));
600: bean1.setPropertyTimestamp(new Timestamp(cal.getTime()
601: .getTime()));
602:
603: mBigBeanManager.save(bean1);
604:
605: assertEquals(1, mBigBeanManager.count(mBigBeanManager
606: .getCountQuery().where("id", "=", bean1.getId())
607: .whereOr("propertyString = 'nostringhere'").whereOr(
608: "propertyBoolean", "=", false).whereOr(
609: "propertyByte", "=", (byte) 90).whereOr(
610: "propertyChar", "=", 'w').whereOr(
611: "propertyDouble", "=", 37478.34d).whereOr(
612: "propertyLong", "=", 23432L).whereOr(
613: "propertyIntegerObject", "=", 421).whereOr(
614: "propertyShort", "=", (short) 44).whereOr(
615: "propertyString", "=", "nostringhere")));
616:
617: if (!("com.mysql.jdbc.Driver".equals(mDatasource
618: .getAliasedDriver()))
619: && !("org.hsqldb.jdbcDriver".equals(mDatasource
620: .getAliasedDriver()))) // skip this for mysql and hsqldb since it doesn't work
621: {
622: assertEquals(1, mBigBeanManager.count(mBigBeanManager
623: .getCountQuery().where("id", "=", bean1.getId())
624: .whereOr("propertyFloat", "=", 34241.2f)));
625: }
626: }
627:
628: public void testUnion() {
629: CountQuery query = mManager.getCountQuery();
630:
631: query.union("uexpr1").union(
632: new Select(mDatasource).field("count(*)")
633: .from("table2"));
634:
635: if ("com.mckoi.JDBCDriver".equals(mDatasource
636: .getAliasedDriver())) // McKoi only supports UNION All
637: {
638: assertEquals(
639: query.getSql(),
640: "SELECT count(*) FROM simplebean UNION ALL uexpr1 UNION ALL SELECT count(*) FROM table2");
641: } else {
642: assertEquals(
643: query.getSql(),
644: "SELECT count(*) FROM simplebean UNION uexpr1 UNION SELECT count(*) FROM table2");
645: }
646: }
647:
648: public void testWhereSubselect() {
649: SimpleBean bean1 = new SimpleBean();
650: SimpleBean bean2 = new SimpleBean();
651: SimpleBean bean3 = new SimpleBean();
652: SimpleBean bean4 = new SimpleBean();
653: SimpleBean bean5 = new SimpleBean();
654:
655: LinkBean linkbean1 = new LinkBean();
656: LinkBean linkbean2 = new LinkBean();
657:
658: linkbean1.setTestString("linkbean 1");
659: linkbean2.setTestString("linkbean 2");
660:
661: mLinkManager.save(linkbean1);
662: mLinkManager.save(linkbean2);
663:
664: bean1.setTestString("bean set 1");
665: bean2.setTestString("bean set 1");
666: bean3.setTestString("bean set 1");
667: bean4.setTestString("bean set 2");
668: bean5.setTestString("bean set 2");
669:
670: bean1.setLinkBean(linkbean1.getId());
671: bean2.setLinkBean(linkbean1.getId());
672: bean3.setLinkBean(linkbean1.getId());
673: bean4.setLinkBean(linkbean2.getId());
674: bean5.setLinkBean(linkbean2.getId());
675:
676: mManager.save(bean1);
677: mManager.save(bean2);
678: mManager.save(bean3);
679: mManager.save(bean4);
680: mManager.save(bean5);
681:
682: Select select = new Select(mDatasource);
683: select.from(mLinkManager.getTable()).field("id").where("id",
684: "=", linkbean1.getId());
685:
686: CountQuery query = mManager.getCountQuery();
687: query.where("linkBean = (" + select.getSql() + ")")
688: .whereSubselect(select);
689:
690: if (!("com.mysql.jdbc.Driver".equals(mDatasource
691: .getAliasedDriver()))) // skip this for mysql since it doesn't work
692: {
693: assertEquals(3, mManager.count(query));
694: }
695: }
696: }
|