Java > Open Source Codes > jodd > db > orm > DbSqlTemplateTest


1 // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
2
3 package jodd.db.orm;
4
5 import static jodd.db.orm.sqlgen.DbSqlTemplate.ColumnAliasType.TABLE_NAME;
6 import static jodd.db.orm.sqlgen.DbSqlTemplate.ColumnAliasType.TABLE_REFERENCE;
7 import static jodd.db.orm.sqlgen.DbSqlTemplate.ColumnAliasType.COLUMN_CODE;
8 import junit.framework.TestCase;
9 import jodd.db.orm.sqlgen.DbSqlTemplate;
10 import jodd.db.orm.sqlgen.DbDynamicSqlTemplate;
11 import jodd.db.orm.test.Boy;
12 import jodd.db.orm.test.BadBoy;
13
14 import java.util.Map ;
15
16 public class DbSqlTemplateTest extends TestCase {
17
18     public void assertContains(String string, String ... chunks) {
19         for (String chunk : chunks) {
20             assertTrue(string.indexOf(chunk) != -1);
21         }
22     }
23
24
25     public void testTables() {
26         DbSqlTemplate t;
27
28         // 1
29 t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy}");
30         assertEquals("BOY Boy", t.generateQuery());
31         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy -}");
32         assertEquals("BOY", t.generateQuery());
33
34         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy}");
35         assertEquals("BOY BadBoy", t.generateQuery());
36         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy -}");
37         assertEquals("BOY", t.generateQuery());
38
39         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy b}");
40         assertEquals("BOY b", t.generateQuery());
41
42         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy b}");
43         assertEquals("BOY b", t.generateQuery());
44
45         // 2
46 t = new DbSqlTemplate("$T{b}").use("b", new Boy());
47         assertEquals("BOY b", t.generateQuery());
48         t = new DbSqlTemplate("$T{b -}").use("b", new Boy());
49         assertEquals("BOY", t.generateQuery());
50
51         t = new DbSqlTemplate("$T{b}").use("b", new BadBoy());
52         assertEquals("BOY b", t.generateQuery());
53         t = new DbSqlTemplate("$T{b -}").use("b", new BadBoy());
54         assertEquals("BOY", t.generateQuery());
55
56         t = new DbSqlTemplate("$T{b bb}").use("b", new Boy());
57         assertEquals("BOY bb", t.generateQuery());
58
59         t = new DbSqlTemplate("$T{b bb}").use("b", new BadBoy());
60         assertEquals("BOY bb", t.generateQuery());
61
62
63         // 3
64 t = new DbSqlTemplate("$T{b}").use("b", Boy.class);
65         assertEquals("BOY b", t.generateQuery());
66         t = new DbSqlTemplate("$T{b -}").use("b", Boy.class);
67         assertEquals("BOY", t.generateQuery());
68
69         t = new DbSqlTemplate("$T{b}").use("b", BadBoy.class);
70         assertEquals("BOY b", t.generateQuery());
71         t = new DbSqlTemplate("$T{b -}").use("b", BadBoy.class);
72         assertEquals("BOY", t.generateQuery());
73
74         t = new DbSqlTemplate("$T{b bb}").use("b", Boy.class);
75         assertEquals("BOY bb", t.generateQuery());
76
77         t = new DbSqlTemplate("$T{b bb}").use("b", BadBoy.class);
78         assertEquals("BOY bb", t.generateQuery());
79
80     }
81
82     public void testColumns1() {
83         DbSqlTemplate t;
84
85         // 1
86 t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $C{Boy.id} | $C{Boy.*} | $C{Boy}").columnAliases(false);
87         assertEquals("BOY Boy | Boy.ID | Boy.GIRL_ID, Boy.NAME, Boy.ID | GIRL_ID, NAME, ID", t.generateQuery());
88         try {
89             t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy -} | $C{Boy.id} | $C{Boy.*} | $C{Boy}").columnAliases(false);
90             t.generateQuery();
91             fail(); // because no reference is used!
92 } catch(DbOrmException dboex) {}
93
94         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*} | $C{BadBoy}").columnAliases(false);
95         assertEquals("BOY BadBoy | BadBoy.ID | BadBoy.NAME, BadBoy.GIRL_ID, BadBoy.ID | NAME, GIRL_ID, ID", t.generateQuery());
96
97         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy b} | $C{b.id} | $C{b.*} | $C{b}").columnAliases(false);
98         assertEquals("BOY b | b.ID | b.GIRL_ID, b.NAME, b.ID | GIRL_ID, NAME, ID", t.generateQuery());
99
100         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy b} | $C{b.ajdi} | $C{b.*} | $C{b}").columnAliases(false);
101         assertEquals("BOY b | b.ID | b.NAME, b.GIRL_ID, b.ID | NAME, GIRL_ID, ID", t.generateQuery());
102
103
104         // 2
105 t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{+b.*} | $C{+b}").use("b", new Boy()).columnAliases(false);
106         assertEquals("BOY b | b.ID | b.GIRL_ID, b.NAME, b.ID | GIRL_ID, NAME, ID", t.generateQuery());
107
108         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{+b.*} | $C{+b}").use("b", new BadBoy()).columnAliases(false);
109         assertEquals("BOY b | b.ID | b.NAME, b.GIRL_ID, b.ID | NAME, GIRL_ID, ID", t.generateQuery());
110
111         t = new DbSqlTemplate("$T{b bb} | $C{bb.id} | $C{bb.*} | $C{bb}").use("b", new Boy()).columnAliases(false);
112         assertEquals("BOY bb | bb.ID | bb.GIRL_ID, bb.NAME, bb.ID | GIRL_ID, NAME, ID", t.generateQuery());
113
114         t = new DbSqlTemplate("$T{b bb} | $C{bb.ajdi} | $C{bb.*} | $C{bb}").use("b", new BadBoy()).columnAliases(false);
115         assertEquals("BOY bb | bb.ID | bb.NAME, bb.GIRL_ID, bb.ID | NAME, GIRL_ID, ID", t.generateQuery());
116
117         // 3
118 t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{b.*} | $C{b}").use("b", Boy.class).columnAliases(false);
119         assertEquals("BOY b | b.ID | b.GIRL_ID, b.NAME, b.ID | GIRL_ID, NAME, ID", t.generateQuery());
120
121         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{b.*} | $C{b}").use("b", BadBoy.class).columnAliases(false);
122         assertEquals("BOY b | b.ID | b.NAME, b.GIRL_ID, b.ID | NAME, GIRL_ID, ID", t.generateQuery());
123
124         t = new DbSqlTemplate("$T{b bb} | $C{bb.id} | $C{bb.*} | $C{bb}").use("b", Boy.class).columnAliases(false);
125         assertEquals("BOY bb | bb.ID | bb.GIRL_ID, bb.NAME, bb.ID | GIRL_ID, NAME, ID", t.generateQuery());
126
127         t = new DbSqlTemplate("$T{b bb} | $C{bb.ajdi} | $C{bb.*} | $C{bb}").use("b", BadBoy.class).columnAliases(false);
128         assertEquals("BOY bb | bb.ID | bb.NAME, bb.GIRL_ID, bb.ID | NAME, GIRL_ID, ID", t.generateQuery());
129
130     }
131
132
133     public void testColumns2() {
134         DbSqlTemplate t;
135
136         // 1
137 t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $C{Boy.id} | $C{Boy.*} | $C{Boy}").setColumnAliasesType(TABLE_NAME);
138         assertEquals("BOY Boy | Boy.ID as BOY$ID | Boy.GIRL_ID as BOY$GIRL_ID, Boy.NAME as BOY$NAME, Boy.ID as BOY$ID | GIRL_ID, NAME, ID", t.generateQuery());
139         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $C{Boy.id} | $C{Boy.*} | $C{Boy}").setColumnAliasesType(TABLE_REFERENCE);
140         assertEquals("BOY Boy | Boy.ID as Boy$ID | Boy.GIRL_ID as Boy$GIRL_ID, Boy.NAME as Boy$NAME, Boy.ID as Boy$ID | GIRL_ID, NAME, ID", t.generateQuery());
141         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $C{Boy.id} | $C{Boy.*} | $C{Boy}").setColumnAliasesType(COLUMN_CODE);
142         assertEquals("BOY Boy | Boy.ID as col_0_ | Boy.GIRL_ID as col_1_, Boy.NAME as col_2_, Boy.ID as col_3_ | GIRL_ID, NAME, ID", t.generateQuery());
143
144         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*} | $C{BadBoy}").setColumnAliasesType(TABLE_NAME);
145         assertEquals("BOY BadBoy | BadBoy.ID as BOY$ID | BadBoy.NAME as BOY$NAME, BadBoy.GIRL_ID as BOY$GIRL_ID, BadBoy.ID as BOY$ID | NAME, GIRL_ID, ID", t.generateQuery());
146         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*} | $C{BadBoy}").setColumnAliasesType(TABLE_REFERENCE);
147         assertEquals("BOY BadBoy | BadBoy.ID as BadBoy$ID | BadBoy.NAME as BadBoy$NAME, BadBoy.GIRL_ID as BadBoy$GIRL_ID, BadBoy.ID as BadBoy$ID | NAME, GIRL_ID, ID", t.generateQuery());
148         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy} | $C{BadBoy.ajdi} | $C{BadBoy.*} | $C{BadBoy}").setColumnAliasesType(COLUMN_CODE);
149         assertEquals("BOY BadBoy | BadBoy.ID as col_0_ | BadBoy.NAME as col_1_, BadBoy.GIRL_ID as col_2_, BadBoy.ID as col_3_ | NAME, GIRL_ID, ID", t.generateQuery());
150
151
152         // 2
153 t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{+b.*} | $C{+b}").use("b", new Boy()).setColumnAliasesType(TABLE_NAME);
154         assertEquals("BOY b | b.ID as BOY$ID | b.GIRL_ID as BOY$GIRL_ID, b.NAME as BOY$NAME, b.ID as BOY$ID | GIRL_ID, NAME, ID", t.generateQuery());
155         t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{+b.*} | $C{+b}").use("b", new Boy()).setColumnAliasesType(TABLE_REFERENCE);
156         assertEquals("BOY b | b.ID as b$ID | b.GIRL_ID as b$GIRL_ID, b.NAME as b$NAME, b.ID as b$ID | GIRL_ID, NAME, ID", t.generateQuery());
157         t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{+b.*} | $C{+b}").use("b", new Boy()).setColumnAliasesType(COLUMN_CODE);
158         assertEquals("BOY b | b.ID as col_0_ | b.GIRL_ID as col_1_, b.NAME as col_2_, b.ID as col_3_ | GIRL_ID, NAME, ID", t.generateQuery());
159
160         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{+b.*} | $C{+b}").use("b", new BadBoy()).setColumnAliasesType(TABLE_NAME);
161         assertEquals("BOY b | b.ID as BOY$ID | b.NAME as BOY$NAME, b.GIRL_ID as BOY$GIRL_ID, b.ID as BOY$ID | NAME, GIRL_ID, ID", t.generateQuery());
162         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{+b.*} | $C{+b}").use("b", new BadBoy()).setColumnAliasesType(TABLE_REFERENCE);
163         assertEquals("BOY b | b.ID as b$ID | b.NAME as b$NAME, b.GIRL_ID as b$GIRL_ID, b.ID as b$ID | NAME, GIRL_ID, ID", t.generateQuery());
164         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{+b.*} | $C{+b}").use("b", new BadBoy()).setColumnAliasesType(COLUMN_CODE);
165         assertEquals("BOY b | b.ID as col_0_ | b.NAME as col_1_, b.GIRL_ID as col_2_, b.ID as col_3_ | NAME, GIRL_ID, ID", t.generateQuery());
166
167
168         // 3
169 t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{b.*} | $C{b}").use("b", Boy.class).setColumnAliasesType(TABLE_NAME);
170         assertEquals("BOY b | b.ID as BOY$ID | b.GIRL_ID as BOY$GIRL_ID, b.NAME as BOY$NAME, b.ID as BOY$ID | GIRL_ID, NAME, ID", t.generateQuery());
171         t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{b.*} | $C{b}").use("b", Boy.class).setColumnAliasesType(TABLE_REFERENCE);
172         assertEquals("BOY b | b.ID as b$ID | b.GIRL_ID as b$GIRL_ID, b.NAME as b$NAME, b.ID as b$ID | GIRL_ID, NAME, ID", t.generateQuery());
173         t = new DbSqlTemplate("$T{b} | $C{b.id} | $C{b.*} | $C{b}").use("b", Boy.class).setColumnAliasesType(COLUMN_CODE);
174         assertEquals("BOY b | b.ID as col_0_ | b.GIRL_ID as col_1_, b.NAME as col_2_, b.ID as col_3_ | GIRL_ID, NAME, ID", t.generateQuery());
175
176         t = new DbSqlTemplate("$T{b} | $C{b.ajdi} | $C{b.*} | $C{b}").use("b", BadBoy.class).setColumnAliasesType(TABLE_NAME);
177         assertEquals("BOY b | b.ID as BOY$ID | b.NAME as BOY$NAME, b.GIRL_ID as BOY$GIRL_ID, b.ID as BOY$ID | NAME, GIRL_ID, ID", t.generateQuery());
178     }
179
180     public void testColumns3() {
181         DbSqlTemplate t;
182
183         t = new DbSqlTemplate("$C{b.id,b.name} from $T{b}").use("b", Boy.class).columnAliases(false);
184         assertEquals("b.ID, b.NAME from BOY b", t.generateQuery());
185         t = new DbSqlTemplate("$C{ a.ajdi , a.nejm } from $T{b a}").use("b", BadBoy.class).columnAliases(false);
186         assertEquals("a.ID, a.NAME from BOY a", t.generateQuery());
187
188         t = new DbSqlTemplate("$C{b.id,b.name} from $T{b}").use("b", Boy.class).setColumnAliasesType(TABLE_NAME);
189         assertEquals("b.ID as BOY$ID, b.NAME as BOY$NAME from BOY b", t.generateQuery());
190         t = new DbSqlTemplate("$C{ a.ajdi , a.nejm } from $T{b a}").use("b", BadBoy.class).setColumnAliasesType(TABLE_NAME);
191         assertEquals("a.ID as BOY$ID, a.NAME as BOY$NAME from BOY a", t.generateQuery());
192
193
194         t = new DbSqlTemplate("$b.id as d, $C{b.name} from $T{b}").use("b", Boy.class).setColumnAliasesType(TABLE_NAME);
195         assertEquals("b.ID as d, b.NAME as BOY$NAME from BOY b", t.generateQuery());
196         t = new DbSqlTemplate("$a.ajdi as d, $C{ a.nejm } from $T{b a}").use("b", BadBoy.class).setColumnAliasesType(TABLE_NAME);
197         assertEquals("a.ID as d, a.NAME as BOY$NAME from BOY a", t.generateQuery());
198
199         t = new DbSqlTemplate("$C{*} from $T{b}").use("b", Boy.class);
200         try {
201             t.generateQuery();
202             fail();
203         } catch (DbOrmException doex) {
204             // ignore
205 }
206
207         t = new DbSqlTemplate("$C{b.*} from $T{b}").use("b", Boy.class).setColumnAliasesType(TABLE_NAME);
208         assertEquals("b.GIRL_ID as BOY$GIRL_ID, b.NAME as BOY$NAME, b.ID as BOY$ID from BOY b", t.generateQuery());
209         t = new DbSqlTemplate("$C{a.*} from $T{b a}").use("b", BadBoy.class).columnAliases(false);
210         assertEquals("a.NAME, a.GIRL_ID, a.ID from BOY a", t.generateQuery());
211
212         t = new DbSqlTemplate("$C{a.*} from $T{jodd.db.orm.test.BadBoy a}").columnAliases(false);
213         assertEquals("a.NAME, a.GIRL_ID, a.ID from BOY a", t.generateQuery());
214         t = new DbSqlTemplate("$C{BadBoy.ajdi} from $T{jodd.db.orm.test.BadBoy}").columnAliases(false);
215         assertEquals("BadBoy.ID from BOY BadBoy", t.generateQuery());
216
217     }
218
219     public void testColumnAliases() {
220         DbSqlTemplate t;
221         
222         t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $Boy.id as foo | $C{Boy.*} | $C{Boy}").columnAliases(false);
223         assertEquals("BOY Boy | Boy.ID as foo | Boy.GIRL_ID, Boy.NAME, Boy.ID | GIRL_ID, NAME, ID", t.generateQuery());
224
225         t = new DbSqlTemplate("$T{jodd.db.orm.test.BadBoy} | $BadBoy.ajdi as foo | $C{BadBoy.*} | $C{BadBoy}").setColumnAliasesType(TABLE_NAME);
226         assertEquals("BOY BadBoy | BadBoy.ID as foo | BadBoy.NAME as BOY$NAME, BadBoy.GIRL_ID as BOY$GIRL_ID, BadBoy.ID as BOY$ID | NAME, GIRL_ID, ID", t.generateQuery());
227
228         try {
229             t = new DbSqlTemplate("$T{jodd.db.orm.test.Boy} | $C{Boy.id as foo} | $Boy.* as foo | $C{Boy}").columnAliases(false);
230             t.generateQuery();
231             fail();
232         } catch (DbOrmException doex) {
233         }
234     }
235
236
237     public void testColref() {
238         DbSqlTemplate t;
239         DbOrm.getInstance().setPackagePrefix("jodd.db.orm.test");
240
241         t = new DbSqlTemplate("$T{.Boy, .BadBoy b} - \\$$Boy.id $b.ajdi");
242         assertEquals("BOY Boy, BOY b - $Boy.ID b.ID", t.generateQuery());
243
244         t = new DbSqlTemplate("$C{b.ajdi} $T{.Boy, .BadBoy b} - \\$$Boy.id $b.ajdi").escape(true).setColumnAliasesType(TABLE_NAME);
245         assertEquals("b.ID as BOY$ID BOY Boy, BOY b - $Boy.ID b.ID", t.generateQuery());
246
247         t = new DbSqlTemplate("\\$C{b.ajdi} $T{.Boy, .BadBoy b} - \\$$Boy.id $b.ajdi").escape(true);
248         assertEquals("$C{b.ajdi} BOY Boy, BOY b - $Boy.ID b.ID", t.generateQuery());
249
250         t = new DbSqlTemplate("\\$C{b.*} $T{.Boy, .BadBoy b} - $Boy.id").escape(true);
251         assertEquals("$C{b.*} BOY Boy, BOY b - Boy.ID", t.generateQuery());
252
253         t = new DbSqlTemplate("$C{b.*} $T{.Boy, .BadBoy b} - $b.ajdi 'foo\\$'").escape(true).setColumnAliasesType(TABLE_NAME);
254         assertEquals("b.NAME as BOY$NAME, b.GIRL_ID as BOY$GIRL_ID, b.ID as BOY$ID BOY Boy, BOY b - b.ID 'foo$'", t.generateQuery());
255
256         t = new DbSqlTemplate("$T{.BadBoy b} - $b.ajdi=2,$b.ajdi<$b.ajdi").escape(true).setColumnAliasesType(TABLE_NAME);
257         assertEquals("BOY b - b.ID=2,b.ID<b.ID", t.generateQuery());
258     }
259     
260
261     // ---------------------------------------------------------------- dynamics
262
263     public void testWhere() {
264         DbDynamicSqlTemplate t;
265
266         Boy boy = new Boy();
267         boy.id = 1;
268         boy.girlId = 3;
269         t = new DbDynamicSqlTemplate("$T{boy} $W{where boy}").use("boy", boy);
270         assertEquals("BOY boy where boy.GIRL_ID=:boy.girlId and boy.ID=:boy.id", t.generateQuery());
271         Map map = t.getQueryParameters();
272         assertEquals(2, map.size());
273         assertEquals(Integer.valueOf(1), map.get("boy.id"));
274         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
275
276
277         boy.id = 4;
278         boy.girlId = 1;
279         t = new DbDynamicSqlTemplate("$T{boy b} $W{where b2.boy}").use("boy", boy);
280         assertEquals("BOY b where b2.GIRL_ID=:boy.girlId and b2.ID=:boy.id", t.generateQuery());
281         map = t.getQueryParameters();
282         assertEquals(2, map.size());
283         assertEquals(Integer.valueOf(4), map.get("boy.id"));
284         assertEquals(Integer.valueOf(1), map.get("boy.girlId"));
285
286         boy.id = 4;
287         boy.girlId = 1;
288         t = new DbDynamicSqlTemplate("$T{boy b} $W{where .boy}").use("boy", boy);
289         assertEquals("BOY b where GIRL_ID=:boy.girlId and ID=:boy.id", t.generateQuery());
290         map = t.getQueryParameters();
291         assertEquals(2, map.size());
292         assertEquals(Integer.valueOf(4), map.get("boy.id"));
293         assertEquals(Integer.valueOf(1), map.get("boy.girlId"));
294
295
296
297         BadBoy badBoy = new BadBoy();
298         t = new DbDynamicSqlTemplate("$T{boy b} $W{where b.boy}").use("boy", badBoy);
299         assertEquals("BOY b ", t.generateQuery());
300         map = t.getQueryParameters();
301         assertEquals(0, map.size());
302
303         badBoy.ajdi = Integer.valueOf(4);
304         badBoy.girlId = Integer.valueOf(1);
305         badBoy.ajdiGreaterEqual = Integer.valueOf(3);
306         badBoy.nejmLike = "x";
307         t = new DbDynamicSqlTemplate("$T{boy b} $W{where b.boy}").use("boy", badBoy);
308         assertEquals("BOY b where b.GIRL_ID=:badBoy.girlId and b.ID=:badBoy.ajdi", t.generateQuery());
309         map = t.getQueryParameters();
310         assertEquals(2, map.size());
311         assertEquals(Integer.valueOf(4), map.get("badBoy.ajdi"));
312         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
313
314         badBoy.ajdi = null;
315         t = new DbDynamicSqlTemplate("$T{boy b} $W{where !b.boy}").use("boy", badBoy);
316         assertEquals("BOY b where b.GIRL_ID=:badBoy.girlId and b.ID>=:badBoy.ajdiGreaterEqual and b.NAME like :badBoy.nejmLike", t.generateQuery());
317         map = t.getQueryParameters();
318         assertEquals(3, map.size());
319         assertEquals(Integer.valueOf(3), map.get("badBoy.ajdiGreaterEqual"));
320         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
321         assertEquals("x", map.get("badBoy.nejmLike"));
322     }
323
324     public void testWhere2() {
325         DbDynamicSqlTemplate t;
326
327         Boy boy = new Boy();
328         boy.id = 1;
329         boy.girlId = 3;
330         t = new DbDynamicSqlTemplate("$W{where boy}").use("boy", boy);
331         assertEquals("where boy.GIRL_ID=:boy.girlId and boy.ID=:boy.id", t.generateQuery());
332         Map map = t.getQueryParameters();
333         assertEquals(2, map.size());
334         assertEquals(Integer.valueOf(1), map.get("boy.id"));
335         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
336
337         t = new DbDynamicSqlTemplate("$W{where r.boy}").use("boy", boy);
338         assertEquals("where r.GIRL_ID=:boy.girlId and r.ID=:boy.id", t.generateQuery());
339         map = t.getQueryParameters();
340         assertEquals(2, map.size());
341         assertEquals(Integer.valueOf(1), map.get("boy.id"));
342         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
343
344         BadBoy badBoy = new BadBoy();
345         badBoy.ajdi = Integer.valueOf(1);
346         badBoy.girlId = Integer.valueOf(3);
347         t = new DbDynamicSqlTemplate("$W{where boy}").use("boy", badBoy);
348         assertEquals("where boy.GIRL_ID=:badBoy.girlId and boy.ID=:badBoy.ajdi", t.generateQuery());
349         map = t.getQueryParameters();
350         assertEquals(2, map.size());
351         assertEquals(Integer.valueOf(1), map.get("badBoy.ajdi"));
352         assertEquals(Integer.valueOf(3), map.get("badBoy.girlId"));
353
354         t = new DbDynamicSqlTemplate("$W{where r.boy}").use("boy", badBoy);
355         assertEquals("where r.GIRL_ID=:badBoy.girlId and r.ID=:badBoy.ajdi", t.generateQuery());
356         map = t.getQueryParameters();
357         assertEquals(2, map.size());
358         assertEquals(Integer.valueOf(1), map.get("badBoy.ajdi"));
359         assertEquals(Integer.valueOf(3), map.get("badBoy.girlId"));
360
361     }
362
363
364     public void testColumnsList() {
365         DbDynamicSqlTemplate t;
366
367         t = new DbDynamicSqlTemplate("$C{Boy.*} $T{jodd.db.orm.test.Boy}").columnAliases(false);
368         assertEquals("Boy.GIRL_ID, Boy.NAME, Boy.ID BOY Boy", t.generateQuery());
369
370         t = new DbDynamicSqlTemplate("$C{+Boy.*} $T{jodd.db.orm.test.Boy}").columnAliases(false);
371         assertEquals("Boy.GIRL_ID, Boy.NAME, Boy.ID BOY Boy", t.generateQuery());
372
373         t = new DbDynamicSqlTemplate("$C{BadBoy.*} $T{jodd.db.orm.test.BadBoy}").columnAliases(false);
374         assertEquals("BadBoy.NAME, BadBoy.GIRL_ID, BadBoy.ID BOY BadBoy", t.generateQuery());
375
376         t = new DbDynamicSqlTemplate("$C{+BadBoy.*} $T{jodd.db.orm.test.BadBoy}").columnAliases(false);
377         assertEquals("BadBoy.NAME, BadBoy.GIRL_ID, BadBoy.ID BOY BadBoy", t.generateQuery());
378
379         t = new DbDynamicSqlTemplate("$C{Boy} $T{jodd.db.orm.test.Boy}");
380         assertEquals("GIRL_ID, NAME, ID BOY Boy", t.generateQuery());
381
382         t = new DbDynamicSqlTemplate("$C{+BadBoy} $T{jodd.db.orm.test.BadBoy}").columnAliases(false);
383         assertEquals("NAME, GIRL_ID, ID BOY BadBoy", t.generateQuery());
384
385
386         BadBoy badBoy = new BadBoy();
387         badBoy.nejm = "123";
388
389         t = new DbDynamicSqlTemplate("$C{+boy.*} $T{boy}").columnAliases(false).use("boy", badBoy);
390         assertEquals("boy.NAME, boy.GIRL_ID, boy.ID BOY boy", t.generateQuery());
391
392         t = new DbDynamicSqlTemplate("$C{boy.*} $T{boy}").columnAliases(false).use("boy", badBoy);
393         assertEquals("boy.NAME BOY boy", t.generateQuery());
394
395         t = new DbDynamicSqlTemplate("$C{+boy} $T{boy}").use("boy", badBoy);
396         assertEquals("NAME, GIRL_ID, ID BOY boy", t.generateQuery());
397
398         t = new DbDynamicSqlTemplate("$C{boy} $T{boy}").use("boy", badBoy);
399         assertEquals("NAME BOY boy", t.generateQuery());
400     }
401
402     public void testValues() {
403         DbDynamicSqlTemplate t;
404
405         Boy boy = new Boy();
406         boy.id = 1;
407         boy.girlId = 3;
408
409         t = new DbDynamicSqlTemplate("$V{boy}").use("boy", boy);
410         assertEquals(":boy.girlId, :boy.id", t.generateQuery());
411         Map map = t.getQueryParameters();
412         assertEquals(2, map.size());
413         assertEquals(Integer.valueOf(1), map.get("boy.id"));
414         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
415
416         t = new DbDynamicSqlTemplate("$V{+boy}").use("boy", boy);
417         assertEquals(":boy.girlId, :boy.name, :boy.id", t.generateQuery());
418         map = t.getQueryParameters();
419         assertEquals(3, map.size());
420         assertEquals(Integer.valueOf(1), map.get("boy.id"));
421         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
422         assertNull(map.get("boy.name"));
423         
424
425         BadBoy badBoy = new BadBoy();
426         t = new DbDynamicSqlTemplate("$V{boy}").use("boy", badBoy);
427         assertEquals("", t.generateQuery());
428         map = t.getQueryParameters();
429         assertEquals(0, map.size());
430
431         t = new DbDynamicSqlTemplate("$V{+boy}").use("boy", badBoy);
432         assertEquals(":badBoy.nejm, :badBoy.girlId, :badBoy.ajdi", t.generateQuery());
433         map = t.getQueryParameters();
434         assertEquals(3, map.size());
435         assertNull(map.get("boy.id"));
436         assertNull(map.get("boy.girlId"));
437         assertNull(map.get("boy.name"));
438
439
440
441         badBoy.ajdi = Integer.valueOf(4);
442         badBoy.girlId = Integer.valueOf(1);
443         badBoy.ajdiGreaterEqual = Integer.valueOf(3);
444         badBoy.nejmLike = "x";
445         t = new DbDynamicSqlTemplate("$V{boy}").use("boy", badBoy);
446         assertEquals(":badBoy.girlId, :badBoy.ajdi", t.generateQuery());
447         map = t.getQueryParameters();
448         assertEquals(2, map.size());
449         assertEquals(Integer.valueOf(4), map.get("badBoy.ajdi"));
450         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
451
452         t = new DbDynamicSqlTemplate("$V{+boy}").use("boy", badBoy);
453         assertEquals(":badBoy.nejm, :badBoy.girlId, :badBoy.ajdi", t.generateQuery());
454         map = t.getQueryParameters();
455         assertEquals(3, map.size());
456         assertEquals(Integer.valueOf(4), map.get("badBoy.ajdi"));
457         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
458         assertNull(map.get("boy.name"));
459
460         badBoy.ajdi = null;
461         t = new DbDynamicSqlTemplate("$V{boy}").use("boy", badBoy);
462         assertEquals(":badBoy.girlId", t.generateQuery());
463         map = t.getQueryParameters();
464         assertEquals(1, map.size());
465         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
466
467         t = new DbDynamicSqlTemplate("$V{+boy}").use("boy", badBoy);
468         assertEquals(":badBoy.nejm, :badBoy.girlId, :badBoy.ajdi", t.generateQuery());
469         map = t.getQueryParameters();
470         assertEquals(3, map.size());
471         assertNull(map.get("badBoy.ajdi"));
472         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
473         assertNull(map.get("boy.name"));
474
475
476     }
477
478     public void testUpdateSet() {
479         DbDynamicSqlTemplate t;
480
481         Boy boy = new Boy();
482         boy.id = 1;
483         boy.girlId = 3;
484         t = new DbDynamicSqlTemplate("$U{boy}").use("boy", boy);
485         assertEquals("GIRL_ID=:boy.girlId, ID=:boy.id", t.generateQuery());
486         Map map = t.getQueryParameters();
487         assertEquals(2, map.size());
488         assertEquals(Integer.valueOf(1), map.get("boy.id"));
489         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
490
491         t = new DbDynamicSqlTemplate("$U{+boy}").use("boy", boy);
492         assertEquals("GIRL_ID=:boy.girlId, NAME=:boy.name, ID=:boy.id", t.generateQuery());
493         map = t.getQueryParameters();
494         assertEquals(3, map.size());
495         assertEquals(Integer.valueOf(1), map.get("boy.id"));
496         assertEquals(Integer.valueOf(3), map.get("boy.girlId"));
497         assertNull(map.get("boy.name"));
498         
499
500         BadBoy badBoy = new BadBoy();
501         t = new DbDynamicSqlTemplate("$U{boy}").use("boy", badBoy);
502         assertEquals("", t.generateQuery());
503         map = t.getQueryParameters();
504         assertEquals(0, map.size());
505
506         t = new DbDynamicSqlTemplate("$U{+boy}").use("boy", badBoy);
507         assertEquals("NAME=:badBoy.nejm, GIRL_ID=:badBoy.girlId, ID=:badBoy.ajdi", t.generateQuery());
508         map = t.getQueryParameters();
509         assertEquals(3, map.size());
510
511
512         badBoy.ajdi = Integer.valueOf(4);
513         badBoy.girlId = Integer.valueOf(1);
514         badBoy.ajdiGreaterEqual = Integer.valueOf(3);
515         badBoy.nejmLike = "x";
516         t = new DbDynamicSqlTemplate("$U{boy}").use("boy", badBoy);
517         assertEquals("GIRL_ID=:badBoy.girlId, ID=:badBoy.ajdi", t.generateQuery());
518         map = t.getQueryParameters();
519         assertEquals(2, map.size());
520         assertEquals(Integer.valueOf(4), map.get("badBoy.ajdi"));
521         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
522
523         t = new DbDynamicSqlTemplate("$U{+boy}").use("boy", badBoy);
524         assertEquals("NAME=:badBoy.nejm, GIRL_ID=:badBoy.girlId, ID=:badBoy.ajdi", t.generateQuery());
525         map = t.getQueryParameters();
526         assertEquals(3, map.size());
527         assertEquals(Integer.valueOf(4), map.get("badBoy.ajdi"));
528         assertEquals(Integer.valueOf(1), map.get("badBoy.girlId"));
529         assertNull(map.get("boy.name"));
530     }
531
532
533 }
534

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags