001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one
003: * or more contributor license agreements. See the NOTICE file
004: * distributed with this work for additional information
005: * regarding copyright ownership. The ASF licenses this file
006: * to you under the Apache License, Version 2.0 (the
007: * "License"); you may not use this file except in compliance
008: * with the License. You may obtain a copy of the License at
009: *
010: * http://www.apache.org/licenses/LICENSE-2.0
011: *
012: * Unless required by applicable law or agreed to in writing,
013: * software distributed under the License is distributed on an
014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
015: * KIND, either express or implied. See the License for the
016: * specific language governing permissions and limitations
017: * under the License.
018: */
019: package org.apache.openjpa.persistence.jpql.functions;
020:
021: import java.util.List;
022: import javax.persistence.EntityManager;
023:
024: import org.apache.openjpa.persistence.common.apps.Address;
025: import org.apache.openjpa.persistence.common.apps.CompUser;
026: import org.apache.openjpa.persistence.common.apps.FemaleUser;
027: import org.apache.openjpa.persistence.common.apps.MaleUser;
028: import org.apache.openjpa.persistence.common.utils.AbstractTestCase;
029:
030: public class TestEJBQLFunction extends AbstractTestCase {
031:
032: private int userid1, userid2, userid3, userid4, userid5, userid6;
033:
034: public TestEJBQLFunction(String name) {
035: super (name, "jpqlclausescactusapp");
036: }
037:
038: public void setUp() {
039: deleteAll(CompUser.class);
040: EntityManager em = currentEntityManager();
041: startTx(em);
042:
043: Address[] add = new Address[] {
044: new Address("43 Sansome", "SF", "United-Kingdom",
045: "94104"),
046: new Address("24 Mink", "ANTIOCH", "USA", "94513"),
047: new Address("23 Ogbete", "CoalCamp", "NIGERIA", "00000"),
048: new Address("10 Wilshire", "Worcester", "CANADA",
049: "80080"),
050: new Address("23 Bellflower", "Ogui", null, "02000"),
051: new Address("22 Montgomery", "SF", null, "50054") };
052:
053: CompUser user1 = createUser("Seetha", "MAC", add[0], 36, true);
054: CompUser user2 = createUser("Shannon ", "PC", add[1], 36, false);
055: CompUser user3 = createUser("Ugo", "PC", add[2], 19, true);
056: CompUser user4 = createUser("_Jacob", "LINUX", add[3], 10, true);
057: CompUser user5 = createUser("Famzy", "UNIX", add[4], 29, false);
058: CompUser user6 = createUser("Shade", "UNIX", add[5], 23, false);
059:
060: em.persist(user1);
061: userid1 = user1.getUserid();
062: em.persist(user2);
063: userid2 = user2.getUserid();
064: em.persist(user3);
065: userid3 = user3.getUserid();
066: em.persist(user4);
067: userid4 = user4.getUserid();
068: em.persist(user5);
069: userid5 = user5.getUserid();
070: em.persist(user6);
071: userid6 = user6.getUserid();
072:
073: endTx(em);
074: endEm(em);
075: }
076:
077: public void testConcatSubStringFunc() {
078: EntityManager em = currentEntityManager();
079: startTx(em);
080:
081: CompUser user = em.find(CompUser.class, userid1);
082: assertNotNull("user is null", user);
083: assertEquals("the name is not seetha", "Seetha", user.getName());
084:
085: String query = "UPDATE CompUser e SET e.name = "
086: + "CONCAT('Ablahum', SUBSTRING(e.name, LOCATE('e', e.name), 4)) "
087: + "WHERE e.name='Seetha'";
088: int result = em.createQuery(query).executeUpdate();
089:
090: assertEquals("the result is not 1", 1, result);
091:
092: user = em.find(CompUser.class, userid1);
093: em.refresh(user);
094:
095: assertNotNull("the user is null", user);
096: assertEquals("the users name is not AblahumSeet",
097: "Ablahumeeth", user.getName());
098:
099: endTx(em);
100: endEm(em);
101: }
102:
103: public void testConcatFunc2() {
104: EntityManager em = currentEntityManager();
105: startTx(em);
106:
107: CompUser user = em.find(CompUser.class, userid1);
108: assertNotNull("the user is null", user);
109: assertEquals("the users name is not seetha", user.getName(),
110: "Seetha");
111:
112: String query = "UPDATE CompUser e " + "SET e.name = "
113: + "CONCAT('', '') WHERE e.name='Seetha'";
114: int result = em.createQuery(query).executeUpdate();
115:
116: assertEquals(1, result);
117:
118: user = em.find(CompUser.class, userid1);
119: em.refresh(user);
120: assertNotNull(user);
121: assertEquals("", user.getName());
122:
123: endTx(em);
124: endEm(em);
125: }
126:
127: public void testTrimFunc3() {
128: EntityManager em = currentEntityManager();
129: startTx(em);
130:
131: CompUser user = em.find(CompUser.class, userid2);
132: assertNotNull(user);
133: assertEquals("Shannon ", user.getName());
134:
135: String query = "UPDATE CompUser e SET "
136: + "e.name = Trim(e.name) WHERE " + "e.name='Shannon '";
137: int result = em.createQuery(query).executeUpdate();
138:
139: user = em.find(CompUser.class, userid2);
140: em.refresh(user);
141: assertNotNull(user);
142: assertEquals("Shannon", user.getName());
143:
144: endTx(em);
145: endEm(em);
146: }
147:
148: public void testLowerFunc() {
149: EntityManager em = currentEntityManager();
150: startTx(em);
151:
152: CompUser user = em.find(CompUser.class, userid3);
153: assertNotNull(user);
154: assertEquals("Ugo", user.getName());
155:
156: String query = "UPDATE CompUser e SET "
157: + "e.name = LOWER(e.name) WHERE e.name='Ugo'";
158:
159: int result = em.createQuery(query).executeUpdate();
160:
161: user = em.find(CompUser.class, userid3);
162: em.refresh(user);
163: assertNotNull(user);
164: assertEquals("ugo", user.getName());
165:
166: endTx(em);
167: endEm(em);
168: }
169:
170: public void testUpperFunc() {
171: EntityManager em = currentEntityManager();
172: startTx(em);
173:
174: CompUser user = em.find(CompUser.class, userid3);
175: assertNotNull(user);
176: assertEquals("Ugo", user.getName());
177:
178: String query = "UPDATE CompUser e SET "
179: + "e.name = UPPER(e.name) WHERE e.name='Ugo'";
180:
181: int result = em.createQuery(query).executeUpdate();
182:
183: user = em.find(CompUser.class, userid3);
184: em.refresh(user);
185: assertNotNull(user);
186: assertEquals("UGO", user.getName());
187:
188: endTx(em);
189: endEm(em);
190: }
191:
192: public void testLengthFunc() {
193: EntityManager em = currentEntityManager();
194:
195: String query = "SELECT o.name " + "FROM CompUser o "
196: + "WHERE LENGTH(o.address.country) = 3";
197:
198: List result = em.createQuery(query).getResultList();
199:
200: assertNotNull(result);
201: assertEquals(1, result.size());
202: assertTrue(result.contains("Shannon "));
203:
204: endEm(em);
205: }
206:
207: public void testArithmFunc() {
208: EntityManager em = currentEntityManager();
209: startTx(em);
210:
211: CompUser user = em.find(CompUser.class, userid1);
212:
213: assertNotNull(user);
214: assertEquals("Seetha", user.getName());
215: assertEquals(36, user.getAge());
216:
217: String query = "UPDATE CompUser e SET e.age = ABS(e.age) WHERE e.name='Seetha'";
218: int num = em.createQuery(query).executeUpdate();
219:
220: assertNotNull(num);
221: assertEquals(1, num);
222:
223: user = em.find(CompUser.class, userid1);
224: em.refresh(user);
225:
226: assertEquals(36, user.getAge());
227:
228: //----------------------ABS Tested
229:
230: query = "UPDATE CompUser e SET e.age = SQRT(e.age) WHERE e.name='Seetha'";
231: num = em.createQuery(query).executeUpdate();
232:
233: assertNotNull(num);
234: assertEquals(1, num);
235:
236: user = em.find(CompUser.class, userid1);
237: em.refresh(user);
238:
239: assertEquals(6, user.getAge());
240:
241: //-------------------------SQRT Tested
242:
243: query = "UPDATE CompUser e SET e.age = MOD(e.age, 4) WHERE e.name='Seetha'";
244: num = em.createQuery(query).executeUpdate();
245:
246: assertNotNull(num);
247: assertEquals(1, num);
248:
249: user = em.find(CompUser.class, userid1);
250: em.refresh(user);
251:
252: assertEquals(2, user.getAge());
253:
254: //-------------------------MOD Tested
255:
256: query = "SELECT e.name FROM CompUser e WHERE SIZE(e.nicknames) = 6";
257: List result = em.createQuery(query).getResultList();
258:
259: assertNotNull(result);
260: assertEquals(0, result.size());
261:
262: //------------------------SIZE Tested
263:
264: endTx(em);
265: endEm(em);
266: }
267:
268: public void testGroupByHavingClause() {
269: EntityManager em = currentEntityManager();
270:
271: String query = "SELECT c.name FROM CompUser c GROUP BY c.name HAVING c.name LIKE 'S%'";
272:
273: List result = em.createQuery(query).getResultList();
274:
275: assertNotNull(result);
276: assertEquals(3, result.size());
277: assertTrue(result.contains("Shannon "));
278: assertTrue(result.contains("Shade"));
279: assertTrue(result.contains("Seetha"));
280:
281: endEm(em);
282: }
283:
284: public void testOrderByClause() {
285: EntityManager em = currentEntityManager();
286:
287: String query = "SELECT c.name FROM CompUser c WHERE c.name LIKE 'S%' ORDER BY c.name";
288:
289: List result = em.createQuery(query).getResultList();
290:
291: assertNotNull(result);
292: assertEquals(3, result.size());
293: assertTrue(result.contains("Shannon "));
294: assertTrue(result.contains("Seetha"));
295: assertTrue(result.contains("Shade"));
296:
297: endEm(em);
298: }
299:
300: public void testAVGAggregFunc() {
301: /**
302: * To be Tested: AVG, COUNT, MAX, MIN, SUM
303: */
304:
305: EntityManager em = currentEntityManager();
306:
307: String query = "SELECT AVG(e.age) FROM CompUser e";
308:
309: List result = em.createQuery(query).getResultList();
310:
311: assertNotNull(result);
312: assertEquals(1, result.size());
313: assertTrue(result.contains(25));
314:
315: endEm(em);
316: }
317:
318: public void testCOUNTAggregFunc() {
319: EntityManager em = currentEntityManager();
320:
321: String query = "SELECT COUNT(c.name) FROM CompUser c";
322:
323: List result = em.createQuery(query).getResultList();
324:
325: assertNotNull(result);
326: assertEquals(1, result.size());
327: assertTrue(result.contains(6l));
328:
329: endEm(em);
330: }
331:
332: public void testMAXAggregFunc() {
333: EntityManager em = currentEntityManager();
334:
335: String query = "SELECT DISTINCT MAX(c.age) FROM CompUser c";
336:
337: List result = em.createQuery(query).getResultList();
338:
339: assertNotNull(result);
340: assertEquals(1, result.size());
341: assertTrue(result.contains(36));
342:
343: endEm(em);
344: }
345:
346: public void testMINAggregFunc() {
347: EntityManager em = currentEntityManager();
348:
349: String query = "SELECT DISTINCT MIN(c.age) FROM CompUser c";
350:
351: List result = em.createQuery(query).getResultList();
352:
353: assertNotNull(result);
354: assertEquals(1, result.size());
355: assertTrue(result.contains(10));
356:
357: endEm(em);
358: }
359:
360: public void testSUMAggregFunc() {
361: EntityManager em = currentEntityManager();
362:
363: String query = "SELECT SUM(c.age) FROM CompUser c";
364:
365: List result = em.createQuery(query).getResultList();
366:
367: assertNotNull(result);
368: assertEquals(1, result.size());
369: assertTrue(result.contains(153l));
370:
371: endEm(em);
372: }
373:
374: public CompUser createUser(String name, String cName, Address add,
375: int age, boolean isMale) {
376: CompUser user = null;
377: if (isMale) {
378: user = new MaleUser();
379: user.setName(name);
380: user.setComputerName(cName);
381: user.setAddress(add);
382: user.setAge(age);
383: } else {
384: user = new FemaleUser();
385: user.setName(name);
386: user.setComputerName(cName);
387: user.setAddress(add);
388: user.setAge(age);
389: }
390: return user;
391: }
392: }
|