1 package ejava.examples.orm.ejbql;
2
3 import static org.junit.Assert.*;
4
5 import java.math.BigDecimal;
6
7 import java.util.Date;
8 import java.util.HashMap;
9 import java.util.Iterator;
10 import java.util.List;
11 import java.util.Map;
12
13 import javax.persistence.Query;
14
15 import org.junit.Test;
16
17 import ejava.examples.orm.ejbql.annotated.Clerk;
18 import ejava.examples.orm.ejbql.annotated.Sale;
19
20 public class EJBQLDemo extends DemoBase {
21
22 private List<Object> executeQuery(String ejbqlString) {
23 return executeQuery(ejbqlString, null);
24 }
25
26 @SuppressWarnings("unchecked")
27 private List<Object> executeQuery(String ejbqlString,
28 Map<String, Object> params) {
29 Query query = em.createQuery(ejbqlString);
30 log.info("executing query:" + ejbqlString);
31 if (params != null && !params.isEmpty()) {
32 StringBuilder text=new StringBuilder();
33 for(String key: params.keySet()) {
34 Object param = params.get(key);
35 text.append(key +"=" + param + ",");
36 query.setParameter(key, param);
37 }
38 log.info(" with params:{" + text + "}");
39 }
40 List<Object> objects = query.getResultList();
41 for(Object o: objects) {
42 log.info("found result:" + o);
43 }
44 return objects;
45 }
46
47 @Test
48 public void testSimpleSelect() {
49 log.info("*** testSimpleSelect() ***");
50
51 int rows = executeQuery("select object(c) from Customer as c").size();
52 assertTrue("unexpected number of customers:" + rows, rows > 0);
53 }
54
55 @SuppressWarnings("unchecked")
56 @Test
57 public void testEntityProperties() {
58 log.info("*** testEntityProperties() ***");
59
60 Query query = em.createQuery(
61 "select c.firstName, c.hireDate from Clerk c");
62 List<Object> results = query.getResultList();
63 assertTrue("no results", results.size() > 0);
64 for(Iterator<Object> itr=results.iterator(); itr.hasNext(); ) {
65 Object[] result = (Object[])itr.next();
66 assertNotNull("no result array", result);
67 assertEquals("unexpected result length:" + result.length,
68 2, result.length);
69 String firstName = (String) result[0];
70 Date hireDate = (Date) result[1];
71 log.info("firstName=" + firstName + " hireDate=" + hireDate);
72 }
73 }
74
75 @SuppressWarnings("unchecked")
76 @Test
77 public void testEntityRelationships() {
78 log.info("*** testEntityRelationships() ***");
79
80 Query query = em.createQuery(
81 "select s.id, s.store.name from Sale s");
82 List<Object> results = query.getResultList();
83 assertTrue("no results", results.size() > 0);
84 for(Iterator<Object> itr=results.iterator(); itr.hasNext(); ) {
85 Object[] result = (Object[])itr.next();
86 assertNotNull("no result array", result);
87 assertEquals("unexpected result length:" + result.length,
88 2, result.length);
89 Long id = (Long) result[0];
90 String name = (String) result[1];
91 log.info("sale.id=" + id + ", sale.store.name=" + name);
92 }
93 }
94
95 @SuppressWarnings("unchecked")
96 @Test
97 public void testConstructorExpressions() {
98 log.info("*** testConstructorExpressions() ***");
99
100 Query query = em.createQuery(
101 "select new ejava.examples.orm.ejbql.Receipt(" +
102 "s.id,s.buyerId,s.date, s.amount) " +
103 "from Sale s");
104 List<Object> results = query.getResultList();
105 assertTrue("no results", results.size() > 0);
106 for(Iterator<Object> itr=results.iterator(); itr.hasNext(); ) {
107 Receipt receipt = (Receipt)itr.next();
108 assertNotNull("no receipt", receipt);
109 log.info("receipt=" + receipt);
110 }
111 }
112
113 @Test
114 public void testIN() {
115 log.info("*** testIN() ***");
116
117 int rows = executeQuery(
118 "select sale from Store s, IN(s.sales) sale").size();
119 assertTrue("unexpected number of sales:" + rows, rows > 0);
120
121 rows = executeQuery(
122 "select sale.date from Store s, IN(s.sales) sale").size();
123 assertTrue("unexpected number of sales:" + rows, rows > 0);
124 }
125
126 @Test
127 public void testInnerJoin() {
128 log.info("*** testInnerJoin() ***");
129
130 int rows = executeQuery(
131 "select sale from Store s INNER JOIN s.sales sale").size();
132 assertTrue("unexpected number of sales:" + rows, rows > 0);
133
134 rows = executeQuery(
135 "select sale.date from Store s INNER JOIN s.sales sale").size();
136 assertTrue("unexpected number of sales:" + rows, rows > 0);
137 }
138
139 @SuppressWarnings("unchecked")
140 @Test
141 public void testOuterJoin() {
142 log.info("*** testOuterJoin() ***");
143
144 Query query = em.createQuery(
145 "select c.id, c.firstName, sale.amount " +
146 "from Clerk c " +
147 "LEFT JOIN c.sales sale");
148 List<Object> results = query.getResultList();
149 assertTrue("no results", results.size() > 0);
150 for(Iterator<Object> itr=results.iterator(); itr.hasNext(); ) {
151 Object[] result = (Object[])itr.next();
152 assertNotNull("no result array", result);
153 assertEquals("unexpected result length:" + result.length,
154 3, result.length);
155 Long id = (Long) result[0];
156 String name = (String) result[1];
157 BigDecimal amount = (BigDecimal) result[2];
158 log.info("clerk.id=" + id + ", clerk.firstName=" + name +
159 ", amount=" + amount);
160 }
161 }
162
163 @Test
164 public void testFetchJoin() {
165 log.info("** testFetchJoin() ***");
166 executeQuery(
167 "select s from Store s LEFT JOIN s.sales sale");
168 }
169
170 @Test
171 public void testDISTINCT() {
172 log.info("*** testDISTINCT() ***");
173
174 int rows = executeQuery(
175 "select DISTINCT c.lastName from Customer c").size();
176 assertEquals("unexpected number of rows:" + rows, 3, rows);
177 rows = executeQuery(
178 "select DISTINCT c.firstName from Customer c").size();
179 assertEquals("unexpected number of rows for DISTINCT:" + rows, 2, rows);
180 }
181
182 @Test
183 public void testLiteral() {
184 log.info("*** testLiteral() ***");
185 int rows = executeQuery(
186 "select c from Customer c " +
187 "where c.firstName='cat'"
188 ).size();
189 assertEquals("unexpected number of rows:" + rows, 1, rows);
190 }
191
192 @Test
193 public void testSpecialCharacter() {
194 log.info("*** testSpecialCharacter() ***");
195 int rows = executeQuery(
196 "select s from Store s " +
197 "where s.name='Big Al''s'"
198 ).size();
199 assertEquals("unexpected number of rows:" + rows, 1, rows);
200 }
201
202 @Test
203 public void testLike() {
204 log.info("*** testLike() ***");
205
206 int rows = executeQuery(
207 "select c from Clerk c " +
208 "where c.firstName like 'M%'").size();
209 assertEquals("unexpected number of rows", 2, rows);
210
211 Map<String,Object> params = new HashMap<String, Object>();
212 params.put("firstName", "M%");
213 rows = executeQuery(
214 "select c from Clerk c " +
215 "where c.firstName like :firstName",params).size();
216 assertEquals("unexpected number of rows", 2, rows);
217
218 params = new HashMap<String, Object>();
219 params.put("firstName", "M");
220 rows = executeQuery(
221 "select c from Clerk c " +
222 "where c.firstName like concat(:firstName,'%')",params).size();
223 assertEquals("unexpected number of rows", 2, rows);
224
225 rows = executeQuery(
226 "select c from Clerk c " +
227 "where c.firstName like '_anny'").size();
228 assertEquals("unexpected number of rows", 1, rows);
229
230 }
231
232 @Test
233 public void testArithmetic() {
234 log.info("*** testArithmetic() ***");
235
236 String query = "select s from Sale s " +
237 "where (s.amount * :tax) > :amount";
238 Map<String, Object> params = new HashMap<String, Object>();
239 params.put("amount", new BigDecimal(10.00));
240
241 double tax = 0.04;
242 int rows=0;
243
244 while (rows==0) {
245 params.put("tax", new BigDecimal(tax));
246 rows = executeQuery(query, params).size();
247 if (rows == 0) { tax += 0.01; }
248 }
249 log.info("raise taxes to:" + tax);
250
251 assertEquals("unexpected level for tax:" + tax,
252 (int)(0.07 * 100), (int)(tax * 100));
253 }
254
255 @Test
256 public void testLogical() {
257 log.info("*** testLogical() ***");
258 int rows = executeQuery(
259 "select c from Customer c " +
260 "where (c.firstName='cat' AND" +
261 " c.lastName='inhat') OR" +
262 " c.firstName='thing' "
263 ).size();
264 assertEquals("unexpected number of rows:" + rows, 3, rows);
265
266 rows = executeQuery(
267 "select c from Customer c " +
268 "where NOT (c.firstName='cat' AND" +
269 " c.lastName='inhat') OR" +
270 " c.firstName='thing' "
271 ).size();
272 assertEquals("unexpected number of rows:" + rows, 2, rows);
273 }
274
275 @Test
276 public void testEquality() {
277 log.info("*** testEquality() ***");
278
279
280 Clerk clerk = (Clerk)
281 em.createQuery(
282 "select c from Clerk c where c.firstName = 'Manny'")
283 .getSingleResult();
284
285
286 String ejbqlQueryString =
287 "select s " +
288 "from Sale s, IN (s.clerks) c " +
289 "where c = :clerk";
290 Map<String, Object> params = new HashMap<String, Object>();
291 params.put("clerk", clerk);
292 int rows=executeQuery(ejbqlQueryString,params).size();
293 assertEquals("unexpected number of rows", 2, rows);
294 }
295
296 @Test
297 public void testBetween() {
298 log.info("*** testBetween() ***");
299
300 String query = "select s from Sale s " +
301 "where s.amount BETWEEN :low AND :high";
302 Map<String, Object> params = new HashMap<String, Object>();
303 params.put("low", new BigDecimal(90.00));
304 params.put("high", new BigDecimal(110.00));
305
306 int rows = executeQuery(query, params).size();
307 assertEquals("unexpected number of rows:", 1, rows);
308 }
309
310 @Test
311 public void testIsNull() {
312 log.info("*** testIsNull() ***");
313
314 String query = "select s from Sale s " +
315 "where s.store IS NULL";
316
317 int rows = executeQuery(query).size();
318 assertEquals("unexpected number of rows:", 0, rows);
319
320 query = "select s from Sale s " +
321 "where s.store IS NOT NULL";
322
323 rows = executeQuery(query).size();
324 assertEquals("unexpected number of rows:", 2, rows);
325 }
326
327 @Test
328 public void testIsEmpty() {
329 log.info("*** testIsEmpty() ***");
330
331 String query = "select c from Clerk c " +
332 "where c.sales IS EMPTY";
333
334 int rows = executeQuery(query).size();
335 assertEquals("unexpected number of rows:", 1, rows);
336
337 query = "select c from Clerk c " +
338 "where c.sales IS NOT EMPTY";
339
340 rows = executeQuery(query).size();
341 assertEquals("unexpected number of rows:", 2, rows);
342 }
343
344 @Test
345 public void testMemberOf() {
346 log.info("*** testMemberOf() ***");
347
348
349 Clerk clerk = (Clerk)
350 em.createQuery(
351 "select c from Clerk c where c.firstName = 'Manny'")
352 .getSingleResult();
353
354
355 String ejbqlQueryString =
356 "select s " +
357 "from Sale s " +
358 "where :clerk MEMBER OF s.clerks";
359 Map<String, Object> params = new HashMap<String, Object>();
360 params.put("clerk", clerk);
361 int rows=executeQuery(ejbqlQueryString,params).size();
362 assertEquals("unexpected number of rows", 2, rows);
363 }
364
365 @Test
366 public void testStringFunctions() {
367 log.info("*** testStringFunctions() ***");
368
369 int rows = executeQuery(
370 "select c from Customer c " +
371 "where c.firstName='CAT'"
372 ).size();
373 assertEquals("unexpected number of rows:" + rows, 0, rows);
374
375 rows = executeQuery(
376 "select c from Customer c " +
377 "where c.firstName=LOWER('CAT')"
378 ).size();
379 assertEquals("unexpected number of rows:" + rows, 1, rows);
380
381 rows = executeQuery(
382 "select UPPER(c.firstName) from Customer c " +
383 "where c.firstName=LOWER('CAT')"
384 ).size();
385 assertEquals("unexpected number of rows:" + rows, 1, rows);
386
387 rows = executeQuery(
388 "select TRIM(LEADING 'c' FROM c.firstName) from Customer c " +
389 "where c.firstName='cat'"
390 ).size();
391 assertEquals("unexpected number of rows:" + rows, 1, rows);
392
393 rows = executeQuery(
394 "select c from Customer c " +
395 "where CONCAT(CONCAT(c.firstName,' '),c.lastName) ='cat inhat'"
396 ).size();
397 assertEquals("unexpected number of rows:" + rows, 1, rows);
398
399 rows = executeQuery(
400 "select c from Customer c " +
401 "where LENGTH(c.firstName) = 3"
402 ).size();
403 assertEquals("unexpected number of rows:" + rows, 1, rows);
404
405 rows = executeQuery(
406 "select c from Customer c " +
407 "where LOCATE('cat',c.firstName,2) > 0"
408 ).size();
409 assertEquals("unexpected number of rows:" + rows, 0, rows);
410
411 rows = executeQuery(
412 "select c from Customer c " +
413 "where LOCATE('at',c.firstName,2) > 1"
414 ).size();
415 assertEquals("unexpected number of rows:" + rows, 1, rows);
416
417 rows = executeQuery(
418 "select SUBSTRING(c.firstName,2,2) from Customer c " +
419 "where c.firstName = 'cat'"
420 ).size();
421 assertEquals("unexpected number of rows:" + rows, 1, rows);
422
423 rows = executeQuery(
424 "select c from Customer c " +
425 "where SUBSTRING(c.firstName,2,2) = 'at'"
426 ).size();
427 assertEquals("unexpected number of rows:" + rows, 1, rows);
428 }
429
430 @Test
431 public void testDates() {
432 log.info("*** testDates() ***");
433
434 int rows = executeQuery(
435 "select s from Sale s " +
436 "where s.date < CURRENT_DATE"
437 ).size();
438 assertEquals("unexpected number of rows:" + rows, 2, rows);
439
440 rows = executeQuery(
441 "select s from Sale s " +
442 "where s.date = CURRENT_DATE"
443 ).size();
444 assertEquals("unexpected number of rows:" + rows, 0, rows);
445
446 rows = em.createQuery(
447 "update Sale s " +
448 "set s.date = CURRENT_TIMESTAMP"
449 ).executeUpdate();
450 assertEquals("unexpected number of rows:" + rows, 2, rows);
451
452 em.getTransaction().commit();
453 em.clear();
454 em.getTransaction().begin();
455
456 rows = executeQuery(
457 "select s from Sale s " +
458 "where s.date = CURRENT_DATE"
459 ).size();
460 assertEquals("unexpected number of rows:" + rows, 2, rows);
461 }
462
463 @Test
464 public void testCount() {
465 log.info("*** testCount() ***");
466
467 List<Object> results= executeQuery(
468 "select COUNT(s) from Sale s");
469 assertEquals("unexpected number of rows", 1, results.size());
470 assertEquals("unexpected result", 2, ((Long)results.get(0)).intValue());
471 }
472
473 @Test
474 public void testMaxMin() {
475 log.info("*** testMaxMin() ***");
476
477 List<Object> results= executeQuery(
478 "select max(s.amount) from Sale s");
479 assertEquals("unexpected number of rows", 1, results.size());
480 assertEquals("unexpected result", 150, ((BigDecimal)results.get(0)).intValue());
481
482 results= executeQuery(
483 "select min(s.amount) from Sale s");
484 assertEquals("unexpected number of rows", 1, results.size());
485 assertEquals("unexpected result", 100, ((BigDecimal)results.get(0)).intValue());
486 }
487
488 @Test
489 public void testSumAve() {
490 log.info("*** testSumAve() ***");
491
492 List<Object> results= executeQuery(
493 "select sum(s.amount) from Sale s");
494 assertEquals("unexpected number of rows", 1, results.size());
495 assertEquals("unexpected result", 250, ((BigDecimal)results.get(0)).intValue());
496
497 results= executeQuery(
498 "select avg(s.amount) from Sale s");
499 assertEquals("unexpected number of rows", 1, results.size());
500 assertEquals("unexpected result", 125, ((Double)results.get(0)).intValue());
501 }
502
503 @Test
504 public void testOrderBy() {
505 log.info("*** testOrderBy() ***");
506
507 List<Object> results = executeQuery(
508 "select s from Sale s ORDER BY s.amount ASC");
509 assertEquals("unexpected number of rows", 2, results.size());
510 assertEquals("unexpected first element",
511 100,
512 ((Sale)results.get(0)).getAmount().intValue());
513 assertEquals("unexpected first element",
514 150,
515 ((Sale)results.get(1)).getAmount().intValue());
516
517
518 results = executeQuery(
519 "select s from Sale s ORDER BY s.amount DESC");
520 assertEquals("unexpected number of rows", 2, results.size());
521 assertEquals("unexpected first element",
522 150,
523 ((Sale)results.get(0)).getAmount().intValue());
524 assertEquals("unexpected first element",
525 100,
526 ((Sale)results.get(1)).getAmount().intValue());
527 }
528
529 @Test
530 public void testSubqueries() {
531 log.info("*** testSubqueries() ***");
532
533 List<Object> results = executeQuery(
534 "select c from Customer c " +
535 "where c.id IN " +
536 " (select s.buyerId from Sale s " +
537 " where s.amount > 100) "
538 );
539 assertEquals("unexpected number of rows", 1, results.size());
540 }
541
542 @Test
543 public void testAll() {
544 log.info("*** testAll() ***");
545
546 executeQuery("select s from Sale s");
547
548 @SuppressWarnings("unused")
549 List<Object> results = executeQuery(
550 "select c from Clerk c " +
551 "where 125 < ALL " +
552 " (select s.amount from c.sales s)"
553 );
554 results = executeQuery(
555 "select c from Clerk c " +
556 "where 125 > ALL " +
557 " (select s.amount from c.sales s)"
558 );
559
560
561 results = executeQuery(
562 "select c from Clerk c " +
563 "where 125 < ANY " +
564 " (select s.amount from c.sales s)"
565 );
566 results = executeQuery(
567 "select c from Clerk c " +
568 "where 125 > ANY " +
569 " (select s.amount from c.sales s)"
570 );
571
572
573 }
574
575 }