001: // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
002:
003: package jodd.db.orm;
004:
005: import static jodd.db.orm.sqlgen.DbSqlTemplate.ColumnAliasType.COLUMN_CODE;
006: import junit.framework.TestCase;
007: import jodd.db.connection.ConnectionProvider;
008: import jodd.db.pool.CoreConnectionPool;
009: import jodd.db.DbSession;
010: import jodd.db.DbThreadSession;
011: import jodd.db.DbQuery;
012: import jodd.db.DbQueryMode;
013: import jodd.db.orm.test.BadBoy;
014: import jodd.db.orm.test.BadGirl;
015: import jodd.db.orm.test.BadGirlFinder;
016: import jodd.db.orm.test.Boy;
017: import jodd.db.orm.test.Boy2;
018: import jodd.db.orm.test.Girl;
019: import jodd.db.orm.test.IdName;
020: import jodd.db.orm.sqlgen.DbDynamicSqlTemplate;
021: import jodd.db.orm.sqlgen.DbSqlCount;
022: import jodd.db.orm.sqlgen.DbSqlDelete;
023: import jodd.db.orm.sqlgen.DbSqlFind;
024: import jodd.db.orm.sqlgen.DbSqlInsert;
025: import jodd.db.orm.sqlgen.DbSqlTemplate;
026: import jodd.db.orm.sqlgen.DbSqlUpdate;
027:
028: import java.util.Iterator;
029: import java.util.List;
030: import java.util.Set;
031:
032: public class DbOrmTest extends TestCase {
033:
034: ConnectionProvider cp;
035:
036: @Override
037: protected void setUp() throws Exception {
038: super .setUp();
039: //noinspection NonConstantStringShouldBeStringBuffer
040: String dbFile = System.getProperty("prj.build.dir");
041: if (dbFile == null) {
042: dbFile = "";
043: }
044: dbFile += "/db/jodd-db";
045: CoreConnectionPool pool = new CoreConnectionPool();
046: pool.setDriver("org.hsqldb.jdbcDriver");
047: pool.setUrl("jdbc:hsqldb:" + dbFile);
048: pool.setUser("sa");
049: pool.setPassword("");
050: pool.init();
051: cp = pool;
052: }
053:
054: @Override
055: protected void tearDown() throws Exception {
056: super .tearDown();
057: cp.close();
058: }
059:
060: public void testOrm() {
061:
062: DbSession session = new DbThreadSession(cp);
063: String sql = "create table GIRL (" + "ID integer not null,"
064: + "NAME varchar(20) not null,"
065: + "SPECIALITY varchar(20) null," + "primary key (ID)"
066: + ')';
067: DbQuery query = new DbQuery(sql);
068: query.executeUpdateAndClose();
069: assertTrue(query.isClosed());
070:
071: sql = "create table BOY (" + "ID integer not null,"
072: + "GIRL_ID integer null," + "NAME varchar(20) null,"
073: + "primary key (ID),"
074: + "FOREIGN KEY (GIRL_ID) REFERENCES GIRL (ID)" + ')';
075: query = new DbQuery(sql);
076: query.executeUpdateAndClose();
077: assertTrue(query.isClosed());
078:
079: session.closeSession();
080: assertTrue(session.isSessionClosed());
081:
082: // prepare
083: session = new DbThreadSession(cp);
084:
085: // ---------------------------------------------------------------- insert
086:
087: assertEquals(1, new DbOrmQuery(new DbSqlInsert(new Girl(1,
088: "Anna", "seduction"))).executeUpdateAndClose());
089: assertEquals(1, new DbOrmQuery(new DbSqlInsert(new Girl(2,
090: "Sandra", "spying"))).executeUpdateAndClose());
091: assertEquals(0, session.getTotalActiveQueries());
092: assertEquals(2, session.getTotalQueries());
093:
094: DbOrmQuery q2 = new DbOrmQuery(new DbSqlInsert(new Girl(3,
095: "Monica", null)), new DbQueryMode().setDebug(true));
096: assertEquals(
097: "insert into GIRL (NAME, ID) values ('Monica', 3)", q2
098: .getQueryString());
099: assertEquals(1, q2.executeUpdateAndClose());
100: assertTrue(q2.isClosed());
101:
102: assertEquals(1, new DbOrmQuery(new DbSqlInsert(new BadBoy(
103: Integer.valueOf(1), "Johny", Integer.valueOf(3))))
104: .executeUpdateAndClose());
105: assertEquals(0, session.getTotalActiveQueries());
106: assertEquals(4, session.getTotalQueries());
107:
108: DbQuery dq = new DbQuery(
109: "select count(*) from GIRL where id>:id");
110: dq.setInteger("id", 1);
111: assertEquals(2, dq.executeCount());
112:
113: dq.reset();
114: dq.setInteger("id", 10);
115: assertEquals(1, session.getTotalActiveQueries());
116: assertEquals(0, dq.executeCountAndClose());
117:
118: assertEquals(0, session.getTotalActiveQueries());
119: assertEquals(5, session.getTotalQueries());
120:
121: // ---------------------------------------------------------------- girl
122:
123: DbOrmQuery q = new DbOrmQuery("select * from GIRL order by ID");
124:
125: Girl girl = q.findOne(Girl.class);
126: checkGirl1(girl);
127: assertTrue(q.isActive());
128:
129: IdName idName = q.findOne(IdName.class);
130: assertNotNull(idName);
131: assertEquals(1, idName.id);
132: assertEquals("Anna", idName.name);
133:
134: girl = (Girl) (q.find(Girl.class))[0];
135: checkGirl1(girl);
136:
137: // list
138: List<Girl> listGirl = q.listOne(Girl.class);
139: assertEquals(3, listGirl.size());
140: girl = listGirl.get(1);
141: checkGirl2(girl);
142:
143: List list = q.list(Girl.class);
144: assertEquals(3, list.size());
145: girl = (Girl) ((Object[]) list.get(2))[0];
146: checkGirl3(girl);
147: assertFalse(q.isClosed());
148:
149: // set
150: Set<Girl> setGirl = q.listSetOne(Girl.class);
151: assertEquals(3, setGirl.size());
152: girl = (setGirl.iterator().next());
153: checkGirl1(girl);
154:
155: Set set = q.listSet(Girl.class);
156: assertEquals(3, set.size());
157: girl = (Girl) ((Object[]) set.iterator().next())[0];
158: checkGirl1(girl);
159:
160: // iterator
161: Iterator<Girl> it = q.iterateOne(Girl.class);
162: while (it.hasNext()) {
163: girl = it.next();
164: }
165: checkGirl3(girl);
166:
167: Iterator<Object[]> it2 = q.iterate(Girl.class);
168: while (it2.hasNext()) {
169: girl = (Girl) (it2.next())[0];
170: }
171: checkGirl3(girl);
172:
173: q.close();
174: // ---------------------------------------------------------------- boy
175:
176: q = new DbOrmQuery("select * from BOY order by ID");
177:
178: BadBoy badBoy = q.findOne(BadBoy.class);
179: checkBoy(badBoy);
180:
181: badBoy = (BadBoy) (q.find(BadBoy.class))[0];
182: checkBoy(badBoy);
183:
184: // list
185: List<BadBoy> listBadBoyt = q.listOne(BadBoy.class);
186: assertEquals(1, listBadBoyt.size());
187: badBoy = listBadBoyt.get(0);
188: checkBoy(badBoy);
189:
190: list = q.list(BadBoy.class);
191: assertEquals(1, list.size());
192: badBoy = (BadBoy) ((Object[]) list.get(0))[0];
193: checkBoy(badBoy);
194:
195: // set
196: Set<BadBoy> setBadBoy = q.listSetOne(BadBoy.class);
197: assertEquals(1, setBadBoy.size());
198: badBoy = (setBadBoy.iterator().next());
199: checkBoy(badBoy);
200:
201: set = q.listSet(BadBoy.class);
202: assertEquals(1, set.size());
203: badBoy = (BadBoy) ((Object[]) set.iterator().next())[0];
204: checkBoy(badBoy);
205:
206: // iterator
207: Iterator<BadBoy> itBad = q.iterateOne(BadBoy.class);
208: while (itBad.hasNext()) {
209: badBoy = itBad.next();
210: }
211: checkBoy(badBoy);
212:
213: Iterator<Object[]> itBad2 = q.iterate(BadBoy.class);
214: while (itBad2.hasNext()) {
215: badBoy = (BadBoy) (itBad2.next())[0];
216: }
217: checkBoy(badBoy);
218:
219: q.close();
220:
221: // ---------------------------------------------------------------- join
222:
223: //q = new DbOrmQuery("select * from GIRL, BOY where BOY.GIRL_ID=GIRL.ID");
224: q = new DbOrmQuery(
225: "select * from GIRL join BOY on GIRL.ID=BOY.GIRL_ID");
226:
227: badBoy = q.findOne(BadBoy.class);
228: assertNull(badBoy); // wrong mapping order, girl is first!
229:
230: BadGirl badGirl = q.findOne(BadGirl.class);
231: checkBadGirl3(badGirl);
232:
233: Object[] res = q.find(BadBoy.class, BadGirl.class);
234: badBoy = (BadBoy) res[0];
235: badGirl = (BadGirl) res[1];
236: checkBadGirl3(badGirl);
237: assertNull(badBoy); // order is invalid
238:
239: res = q.find(BadGirl.class, BadBoy.class);
240: badBoy = (BadBoy) res[1];
241: badGirl = (BadGirl) res[0];
242: checkBadGirl3(badGirl);
243: checkBoy(badBoy);
244:
245: res = q.find(Boy.class, Girl.class);
246: Boy boy = (Boy) res[0];
247: girl = (Girl) res[1];
248: assertNull(boy); // order is invalid
249: checkGirl3(girl);
250:
251: res = q.find(Girl.class, Boy.class);
252: boy = (Boy) res[1];
253: girl = (Girl) res[0];
254: checkBoy(boy);
255: checkGirl3(girl);
256:
257: // ---------------------------------------------------------------- left join
258:
259: q = new DbOrmQuery(
260: "select * from GIRL left join BOY on GIRL.ID=BOY.GIRL_ID order by GIRL.ID");
261:
262: list = q.list(Girl.class, Boy.class);
263: assertEquals(3, list.size());
264: checkGirl1((Girl) ((Object[]) list.get(0))[0]);
265: assertNull(((Object[]) list.get(0))[1]);
266:
267: checkGirl2((Girl) ((Object[]) list.get(1))[0]);
268: assertNull(((Object[]) list.get(1))[1]);
269:
270: checkGirl3((Girl) ((Object[]) list.get(2))[0]);
271: checkBoy((Boy) ((Object[]) list.get(2))[1]);
272:
273: // ---------------------------------------------------------------- etc
274:
275: badGirl = new BadGirl();
276: badBoy = new BadBoy();
277: DbDynamicSqlTemplate t = new DbDynamicSqlTemplate(
278: "select g.*, b.* from $T{g}, $T{b} where 1=1 $W{and g} $W{and b}")
279: .use("g", badGirl).use("b", badBoy);
280: q = new DbOrmQuery(t);
281: list = q.list(BadBoy.class, BadGirl.class);
282: assertEquals(3, list.size());
283:
284: t = new DbDynamicSqlTemplate(
285: "select g.*, b.* from $T{g}, $T{b} $W{where g, b}")
286: .use("g", badGirl).use("b", badBoy);
287: q = new DbOrmQuery(t);
288: list = q.list(BadBoy.class, BadGirl.class);
289: assertEquals(3, list.size());
290:
291: t = new DbDynamicSqlTemplate(
292: "select g.*, b.* from $T{g}, $T{b} $W{where b, g}")
293: .use("g", badGirl).use("b", badBoy);
294: q = new DbOrmQuery(t);
295: list = q.list(BadBoy.class, BadGirl.class);
296: assertEquals(3, list.size());
297:
298: badGirl.fooname = "Sandra";
299: t = new DbDynamicSqlTemplate(
300: "select g.*, b.* from $T{g}, $T{b} where 1=1 $W{and b} $W{and g}")
301: .use("g", badGirl).use("b", badBoy);
302: q = new DbOrmQuery(t);
303: Object[] result = q.find(BadGirl.class, BadBoy.class);
304: checkBoy((BadBoy) result[1]);
305: checkBadGirl2((BadGirl) result[0]);
306:
307: t = new DbDynamicSqlTemplate(
308: "select g.*, b.* from $T{g}, $T{b} $W{where b, g}")
309: .use("g", badGirl).use("b", badBoy);
310: q = new DbOrmQuery(t);
311: result = q.find(BadGirl.class, BadBoy.class);
312: checkBoy((BadBoy) result[1]);
313: checkBadGirl2((BadGirl) result[0]);
314:
315: t = new DbDynamicSqlTemplate(
316: "select b.*, g.* from $T{g}, $T{b} $W{where g, b}")
317: .use("g", badGirl).use("b", badBoy);
318: q = new DbOrmQuery(t);
319: result = q.find(BadBoy.class, BadGirl.class);
320: checkBoy((BadBoy) result[0]);
321: checkBadGirl2((BadGirl) result[1]);
322:
323: badBoy.ajdi = Integer.valueOf(1);
324: badBoy.nejm = "Johny";
325: t = new DbDynamicSqlTemplate(
326: "select b.*, g.* from $T{g}, $T{b} where 1=1 $W{and g} $W{and b}")
327: .use("g", badGirl).use("b", badBoy);
328: q = new DbOrmQuery(t);
329: result = q.find(BadBoy.class, BadGirl.class);
330: checkBoy((BadBoy) result[0]);
331: checkBadGirl2((BadGirl) result[1]);
332:
333: t = new DbDynamicSqlTemplate(
334: "select b.*, g.* from $T{g}, $T{b} $W{where g, b}")
335: .use("g", badGirl).use("b", badBoy);
336: q = new DbOrmQuery(t);
337: result = q.find(BadBoy.class, BadGirl.class);
338: checkBoy((BadBoy) result[0]);
339: checkBadGirl2((BadGirl) result[1]);
340:
341: t = new DbDynamicSqlTemplate(
342: "select b.*, g.* from $T{g}, $T{b} $W{where b, g}")
343: .use("g", badGirl).use("b", badBoy);
344: q = new DbOrmQuery(t);
345: result = q.find(BadBoy.class, BadGirl.class);
346: checkBoy((BadBoy) result[0]);
347: checkBadGirl2((BadGirl) result[1]);
348:
349: t = new DbDynamicSqlTemplate(
350: "select $C{g.fooid}, $C{+b.*} from $T{g}, $T{b} $W{where g, b}")
351: .use("g", badGirl).use("b", badBoy);
352: q = new DbOrmQuery(t);
353: result = q.find(BadGirl.class, BadBoy.class);
354: badGirl = (BadGirl) result[0];
355: checkBoy((BadBoy) result[1]);
356: assertEquals(2, badGirl.fooid.intValue());
357: assertNull(badGirl.fooname);
358: assertNull(badGirl.foospeciality);
359:
360: // ---------------------------------------------------------------- special
361:
362: t = new DbDynamicSqlTemplate(
363: "select $g.fooid * 2 as did, $C{g.fooid} from $T{g} order by $g.fooid")
364: .setColumnAliasesType(COLUMN_CODE).use("g",
365: BadGirl.class);
366: q = new DbOrmQuery(t);
367: list = q.list(null, BadGirl.class); // explicitly ignore the first column 'did'
368: assertEquals(3, list.size());
369: assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid
370: .intValue());
371: assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid
372: .intValue());
373: assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid
374: .intValue());
375:
376: list = q.list(Integer.class, BadGirl.class);
377: assertEquals(3, list.size());
378: assertEquals(2, ((Integer) ((Object[]) list.get(0))[0])
379: .intValue());
380: assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid
381: .intValue());
382: assertEquals(4, ((Integer) ((Object[]) list.get(1))[0])
383: .intValue());
384: assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid
385: .intValue());
386: assertEquals(6, ((Integer) ((Object[]) list.get(2))[0])
387: .intValue());
388: assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid
389: .intValue());
390:
391: q = new DbOrmQuery(
392: "select g.ID * 2 as did, g.ID from Girl g order by g.ID");
393: list = q.list(Integer.class, BadGirl.class);
394: assertEquals(3, list.size());
395: assertEquals(2, ((Integer) ((Object[]) list.get(0))[0])
396: .intValue());
397: assertEquals(1, ((BadGirl) ((Object[]) list.get(0))[1]).fooid
398: .intValue());
399: assertEquals(4, ((Integer) ((Object[]) list.get(1))[0])
400: .intValue());
401: assertEquals(2, ((BadGirl) ((Object[]) list.get(1))[1]).fooid
402: .intValue());
403: assertEquals(6, ((Integer) ((Object[]) list.get(2))[0])
404: .intValue());
405: assertEquals(3, ((BadGirl) ((Object[]) list.get(2))[1]).fooid
406: .intValue());
407:
408: q = new DbOrmQuery(
409: new DbSqlTemplate(
410: "select $g.id+$b.id as total, $C{g.*}, $g.id*2 as gdub, $C{b.*}, $g.id/3.0, $g.name from $T{g}, $T{b} where $b.girlId=$g.id")
411: .setColumnAliasesType(COLUMN_CODE).use("b",
412: Boy.class).use("g", Girl.class));
413: list = q.list(Integer.class, Girl.class, Long.class, Boy.class,
414: Float.class, String.class);
415: assertEquals(1, list.size());
416: result = (Object[]) list.get(0);
417: assertEquals(6, result.length);
418: assertEquals(4, ((Integer) result[0]).intValue());
419: checkGirl3((Girl) result[1]);
420: assertEquals(6, ((Long) result[2]).intValue());
421: checkBoy((Boy) result[3]);
422:
423: assertEquals(1.0f, ((Float) result[4]).floatValue(), 0.05);
424: assertEquals("Monica", (String) result[5]);
425:
426: q = new DbOrmQuery(new DbSqlTemplate(
427: "select $C{g.*}, $C{g.*} from $T{g} where $g.id=3")
428: .setColumnAliasesType(COLUMN_CODE).use("g", Girl.class));
429: list = q.list(Girl.class, Girl.class);
430: assertEquals(1, list.size());
431: result = (Object[]) list.get(0);
432: checkGirl3((Girl) result[0]);
433: checkGirl3((Girl) result[1]);
434:
435: q = new DbOrmQuery(new DbSqlTemplate(
436: "select $C{g.*}, $g.name from $T{g} where $g.id=3")
437: .setColumnAliasesType(COLUMN_CODE).use("g", Girl.class));
438: list = q.list(Girl.class, String.class);
439: assertEquals(1, list.size());
440: result = (Object[]) list.get(0);
441: checkGirl3((Girl) result[0]);
442: assertEquals("Monica", result[1]);
443:
444: q = new DbOrmQuery(new DbSqlTemplate(
445: "select $g.name, $C{g.*} from $T{g} where $g.id=3")
446: .setColumnAliasesType(COLUMN_CODE).use("g", Girl.class));
447: list = q.list(String.class, Girl.class);
448: assertEquals(1, list.size());
449: result = (Object[]) list.get(0);
450: checkGirl3((Girl) result[1]);
451: assertEquals("Monica", (String) result[0]);
452:
453: q.reset();
454: list = q.list(String.class, Girl.class);
455: result = (Object[]) list.get(0);
456: assertEquals("Monica", (String) result[0]);
457:
458: q.close();
459:
460: // ---------------------------------------------------------------- finder
461:
462: girl = new Girl();
463: badGirl = new BadGirl();
464: badBoy = new BadBoy();
465:
466: DbOrmQuery f = new DbOrmQuery(new DbSqlFind(girl)
467: .columnAliases(false), new DbQueryMode().setDebug(true));
468: assertEquals(
469: "select t.NAME, t.SPECIALITY, t.ID from GIRL t where t.ID=0",
470: f.toString());
471: f.close();
472: f = new DbOrmQuery(new DbSqlFind(badGirl).columnAliases(false),
473: new DbQueryMode().setDebug(true));
474: assertEquals("select t.SPECIALITY, t.ID, t.NAME from GIRL t ",
475: f.toString());
476: f.close();
477: f = new DbOrmQuery(new DbSqlFind(badBoy).columnAliases(false),
478: new DbQueryMode().setDebug(true));
479: assertEquals("select t.NAME, t.GIRL_ID, t.ID from BOY t ", f
480: .toString());
481: f.close();
482:
483: girl.name = "Monica";
484: badGirl.fooname = "Anna";
485: badBoy.nejm = "David";
486:
487: f = new DbOrmQuery(new DbSqlFind(girl).columnAliases(false),
488: new DbQueryMode().setDebug(true));
489: assertEquals(
490: "select t.NAME, t.SPECIALITY, t.ID from GIRL t where t.NAME='Monica' and t.ID=0",
491: f.toString());
492: f.close();
493: f = new DbOrmQuery(new DbSqlFind(badGirl).columnAliases(false),
494: new DbQueryMode().setDebug(true));
495: assertEquals(
496: "select t.SPECIALITY, t.ID, t.NAME from GIRL t where t.NAME='Anna'",
497: f.toString());
498: f.close();
499: f = new DbOrmQuery(new DbSqlFind(badBoy).columnAliases(false),
500: new DbQueryMode().setDebug(true));
501: assertEquals(
502: "select t.NAME, t.GIRL_ID, t.ID from BOY t where t.NAME='David'",
503: f.toString());
504: f.close();
505:
506: badBoy.nejm = null;
507: badBoy.nejmLike = "Jo%";
508: f = new DbOrmQuery(new DbSqlFind(badBoy).columnAliases(false),
509: new DbQueryMode().setDebug(true));
510: assertEquals(
511: "select t.NAME, t.GIRL_ID, t.ID from BOY t where t.NAME like 'Jo%'",
512: f.toString());
513: f.close();
514: badBoy.ajdiGreaterEqual = Integer.valueOf(3);
515: f = new DbOrmQuery(new DbSqlFind(badBoy).columnAliases(false),
516: new DbQueryMode().setDebug(true));
517: assertEquals(
518: "select t.NAME, t.GIRL_ID, t.ID from BOY t where t.ID>=3 and t.NAME like 'Jo%'",
519: f.toString());
520: f.close();
521:
522: BadGirlFinder badGirlFinder = new BadGirlFinder();
523: badGirlFinder.fooidGreaterEqual = Integer.valueOf(2);
524: badGirlFinder.foospecialityLike = "s%";
525: f = new DbOrmQuery(new DbSqlFind(badGirlFinder)
526: .columnAliases(false), new DbQueryMode().setDebug(true));
527: assertEquals(
528: "select t.SPECIALITY, t.ID, t.NAME from GIRL t where t.ID>=2 and t.SPECIALITY like 's%'",
529: f.toString());
530: f.close();
531:
532: // ---------------------------------------------------------------- whole round
533:
534: badGirl = new BadGirl();
535: badGirl.fooid = Integer.valueOf(2);
536: f = new DbOrmQuery(new DbSqlFind(badGirl));
537: list = f.listOneAndClose(BadGirl.class);
538: assertTrue(f.isClosed());
539: assertEquals(1, list.size());
540: checkBadGirl2((BadGirl) list.get(0));
541:
542: f = new DbOrmQuery(new DbSqlCount(badGirl));
543: int count = (int) f.executeCountAndClose();
544: assertEquals(1, count);
545: assertTrue(f.isClosed());
546:
547: f = new DbOrmQuery(new DbSqlDelete(badGirl));
548: f.executeUpdateAndClose();
549: assertTrue(f.isClosed());
550:
551: f = new DbOrmQuery(new DbSqlFind("select count(*)", badGirl));
552: count = (int) f.executeCountAndClose();
553: assertEquals(0, count);
554: assertTrue(f.isClosed());
555:
556: badGirl.fooid = null;
557: f = new DbOrmQuery(new DbSqlFind("select count(*)", badGirl));
558: count = (int) f.executeCountAndClose();
559: assertEquals(2, count);
560: assertTrue(f.isClosed());
561:
562: girl = new Girl();
563: girl.id = 1;
564: girl.name = "A%";
565: f = new DbOrmQuery(
566: "select * from GIRL where id >= :girl.id and name like :girl.name",
567: new DbQueryMode().debug());
568: f.setBean("girl", girl);
569: assertEquals(
570: "select * from GIRL where id >= 1 and name like 'A%'",
571: f.toString());
572: count = (int) f.executeCount();
573: assertEquals(1, count);
574:
575: f.reset();
576: girl.id = -2;
577: f.setBean("girl", girl);
578: assertEquals(
579: "select * from GIRL where id >= -2 and name like 'A%'",
580: f.toString());
581: count = (int) f.executeCount();
582: assertEquals(1, count);
583:
584: f.close();
585:
586: // ---------------------------------------------------------------- update
587:
588: badGirl = new BadGirl();
589: badGirl.fooid = Integer.valueOf(1);
590: badGirl = new DbOrmQuery(new DbSqlFind(badGirl))
591: .findOne(badGirl.getClass());
592: checkBadGirl1(badGirl);
593:
594: badGirl.fooname = "Ticky";
595: new DbOrmQuery(new DbSqlUpdate(badGirl, true, new BadGirl(
596: Integer.valueOf(1), null, null))).executeUpdate();
597:
598: badGirl = new BadGirl();
599: badGirl.fooid = Integer.valueOf(1);
600: badGirl = new DbOrmQuery(new DbSqlFind(badGirl))
601: .findOne(badGirl.getClass());
602: checkBadGirl1Alt(badGirl);
603:
604: badGirl.foospeciality = null;
605: new DbOrmQuery(new DbSqlUpdate(badGirl, false, new BadGirl(
606: Integer.valueOf(1), null, null))).executeUpdate();
607:
608: badGirl = new BadGirl();
609: badGirl.fooid = Integer.valueOf(1);
610: badGirl = new DbOrmQuery(new DbSqlFind(badGirl))
611: .findOne(badGirl.getClass());
612: checkBadGirl1Alt2(badGirl);
613:
614: // ---------------------------------------------------------------- relations
615:
616: q = new DbOrmQuery(
617: "select b.*, g.*, (select count(1) from GIRL) as c from BOY b join GIRL g on b.GIRL_ID=g.ID");
618:
619: DbOrmRelation r = new DbOrmRelation();
620: r.map(Boy2.class).join(Girl.class).join(Integer.class,
621: "totalGirls");
622:
623: List<Boy2> boys = q.listOne(r);
624: assertEquals(1, boys.size());
625: assertNotNull(boys.get(0));
626: assertEquals(3, boys.get(0).girlId);
627: assertNotNull(boys.get(0).girl);
628: assertEquals(3, boys.get(0).girl.id);
629: assertEquals(2, boys.get(0).totalGirls);
630:
631: Boy2 b2 = q.findOne(r);
632: assertNotNull(b2);
633: assertEquals(3, b2.girlId);
634: assertNotNull(b2.girl);
635: assertEquals(3, b2.girl.id);
636: assertEquals(2, b2.totalGirls);
637:
638: Object[] bx = q.find(r);
639: assertEquals(1, bx.length);
640: b2 = (Boy2) bx[0];
641: assertNotNull(b2);
642: assertEquals(3, b2.girlId);
643: assertNotNull(b2.girl);
644: assertEquals(3, b2.girl.id);
645: assertEquals(2, b2.totalGirls);
646:
647: List<Object[]> boysx = q.list(r);
648: assertEquals(1, boysx.size());
649: b2 = (Boy2) boysx.get(0)[0];
650:
651: assertNotNull(b2);
652: assertEquals(3, b2.girlId);
653: assertNotNull(b2.girl);
654: assertEquals(3, b2.girl.id);
655: assertEquals(2, b2.totalGirls);
656:
657: q.close();
658:
659: // ---------------------------------------------------------------- double table names
660:
661: q = new DbOrmQuery("select g.*, g.* from GIRL g order by g.ID");
662: List<Object[]> g2 = q.list(Girl.class, Girl.class);
663: assertEquals(2, g2.size());
664: Object[] g2o = g2.get(0);
665: assertEquals(2, g2o.length);
666: checkGirl1Alt((Girl) g2o[0]);
667: checkGirl1Alt((Girl) g2o[1]);
668: q.close();
669:
670: q = new DbOrmQuery(
671: "select g.*, g2.* from GIRL g, GIRL g2 where g.ID=1 and g2.ID=3");
672: g2 = q.list(Girl.class, Girl.class);
673: assertEquals(1, g2.size());
674: g2o = g2.get(0);
675: assertEquals(2, g2o.length);
676: checkGirl1Alt((Girl) g2o[0]);
677: checkGirl3Alt((Girl) g2o[1]);
678: q.close();
679:
680: session.closeSession();
681:
682: }
683:
684: // ---------------------------------------------------------------- utitlities
685:
686: private void checkGirl1(Girl girl) {
687: assertNotNull(girl);
688: assertEquals(1, girl.id);
689: assertEquals("Anna", girl.name);
690: assertEquals("seduction", girl.speciality);
691: }
692:
693: private void checkGirl2(Girl girl) {
694: assertNotNull(girl);
695: assertEquals(2, girl.id);
696: assertEquals("Sandra", girl.name);
697: assertEquals("spying", girl.speciality);
698: }
699:
700: private void checkGirl3(Girl girl) {
701: assertNotNull(girl);
702: assertEquals(3, girl.id);
703: assertEquals("Monica", girl.name);
704: assertNull(girl.speciality);
705: }
706:
707: private void checkBadGirl1(BadGirl girl) {
708: assertNotNull(girl);
709: assertEquals(1, girl.fooid.intValue());
710: assertEquals("Anna", girl.fooname);
711: assertEquals("seduction", girl.foospeciality);
712: }
713:
714: private void checkBadGirl1Alt(BadGirl girl) {
715: assertNotNull(girl);
716: assertEquals(1, girl.fooid.intValue());
717: assertEquals("Ticky", girl.fooname);
718: assertEquals("seduction", girl.foospeciality);
719: }
720:
721: private void checkGirl1Alt(Girl girl) {
722: assertNotNull(girl);
723: assertEquals(1, girl.id);
724: assertEquals("Ticky", girl.name);
725: assertNull(girl.speciality);
726: }
727:
728: private void checkGirl3Alt(Girl girl) {
729: assertNotNull(girl);
730: assertEquals(3, girl.id);
731: assertEquals("Monica", girl.name);
732: assertNull(girl.speciality);
733: }
734:
735: private void checkBadGirl1Alt2(BadGirl girl) {
736: assertNotNull(girl);
737: assertEquals(1, girl.fooid.intValue());
738: assertEquals("Ticky", girl.fooname);
739: assertNull(girl.foospeciality);
740: }
741:
742: private void checkBadGirl2(BadGirl girl) {
743: assertNotNull(girl);
744: assertEquals(2, girl.fooid.intValue());
745: assertEquals("Sandra", girl.fooname);
746: assertEquals("spying", girl.foospeciality);
747: }
748:
749: private void checkBadGirl3(BadGirl girl) {
750: assertNotNull(girl);
751: assertEquals(3, girl.fooid.intValue());
752: assertEquals("Monica", girl.fooname);
753: assertNull(girl.foospeciality);
754: }
755:
756: private void checkBoy(Boy boy) {
757: assertNotNull(boy);
758: assertEquals(1, boy.id);
759: assertEquals("Johny", boy.name);
760: assertEquals(3, boy.girlId);
761: }
762:
763: private void checkBoy(BadBoy boy) {
764: assertNotNull(boy);
765: assertEquals(1, boy.ajdi.intValue());
766: assertEquals("Johny", boy.nejm);
767: assertEquals(3, boy.girlId.intValue());
768: }
769: }
|