| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| package org.apache.openjpa.persistence.jdbc.query; |
| |
| import java.sql.Timestamp; |
| import java.text.DateFormat; |
| import java.text.ParseException; |
| import java.text.SimpleDateFormat; |
| import java.util.Date; |
| import java.util.List; |
| |
| import javax.persistence.EntityManager; |
| import javax.persistence.Query; |
| |
| import org.apache.openjpa.persistence.jdbc.query.domain.Magazine; |
| import org.apache.openjpa.persistence.jdbc.query.domain.Publisher; |
| import org.apache.openjpa.persistence.test.SingleEMFTestCase; |
| |
| /** |
| * Tests multiple entities in projection are returned with both inner and outer |
| * joins. |
| * |
| * Originally reported in |
| * <A HREF= |
| * "http://n2.nabble.com/Selecting-multiple-objects---bug--tc732941.html"> |
| * OpenJPA mailing list</A> |
| * |
| * @author Pinaki Poddar |
| * |
| */ |
| public class TestMultipleEntityProjection extends SingleEMFTestCase { |
| private static boolean INNER_JOIN = true; |
| private static String[][] MAGAZINE_PUBLISHER_NAME_PAIRS = { |
| new String[] {"Times", "Publisher-0"}, |
| new String[] {"Times Life", "Publisher-1"}, |
| new String[] {"Times Kid", null}, |
| new String[] {"Newsweek", "Publisher-3"}, |
| new String[] {"People", null}, |
| new String[] {"Nature", "Publisher-5"}, |
| new String[] {"MIT Review", "Publisher-6"}, |
| new String[] {"Wired", "Publisher-7"}, |
| |
| }; |
| |
| @Override |
| public void setUp() { |
| super.setUp(CLEAR_TABLES, Magazine.class, Publisher.class); |
| createData(); |
| } |
| |
| /** |
| * Create Magazine and associate Publisher unless its name is null. |
| */ |
| void createData() { |
| EntityManager em = emf.createEntityManager(); |
| em.getTransaction().begin(); |
| for (String[] magazinePublisherNamePair : MAGAZINE_PUBLISHER_NAME_PAIRS) { |
| String magName = magazinePublisherNamePair[0]; |
| String pubName = magazinePublisherNamePair[1]; |
| Magazine mag = new Magazine(); |
| mag.setName(magName); |
| if (pubName != null) { |
| Publisher pub = new Publisher(); |
| pub.setName(pubName); |
| mag.setPublisher(pub); |
| try { |
| DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); |
| Date date = df.parse("2001-01-01"); |
| mag.setDatePublished(date); |
| } |
| catch (ParseException e) { |
| mag.setDatePublished(null); |
| } |
| mag.setTsPublished(new Timestamp(System.currentTimeMillis() - 100000)); |
| |
| em.persist(pub); |
| } |
| em.persist(mag); |
| } |
| em.getTransaction().commit(); |
| } |
| |
| public void testMultipleEntitiesInProjectionUsingOuterJoin() { |
| String jpql = "select m, p " + |
| "from Magazine m left outer join m.publisher p " + |
| "where ((:useName = false) or (m.name like :name))"; |
| EntityManager em = emf.createEntityManager(); |
| Query query = em.createQuery(jpql); |
| |
| String magKey = "Times"; |
| query.setParameter("useName", true); |
| query.setParameter("name", '%'+magKey+'%'); |
| |
| List result = query.getResultList(); |
| |
| int expecetedCount = getExpecetedResultCount(magKey, !INNER_JOIN); |
| assertFalse(result.isEmpty()); |
| assertEquals(expecetedCount, result.size()); |
| for (Object o : result) { |
| assertTrue(o instanceof Object[]); |
| Object[] row = (Object[])o; |
| assertEquals(2, row.length); |
| assertTrue(row[0] instanceof Magazine); |
| assertTrue(row[1] == null || row[1] instanceof Publisher); |
| assertNotNull(row[0]); |
| assertEquals(((Magazine)row[0]).getPublisher(), row[1]); |
| } |
| } |
| |
| public void testMultipleEntitiesInProjectionUsingInnerJoin() { |
| String jpql = "select m, p " + |
| "from Magazine m inner join m.publisher p " + |
| "where ((:useName = false) or (m.name like :name))"; |
| EntityManager em = emf.createEntityManager(); |
| Query query = em.createQuery(jpql); |
| |
| String magKey = "Times"; |
| query.setParameter("useName", true); |
| query.setParameter("name", '%'+magKey+'%'); |
| |
| List result = query.getResultList(); |
| |
| int expecetedCount = getExpecetedResultCount(magKey, INNER_JOIN); |
| assertFalse(result.isEmpty()); |
| assertEquals(expecetedCount, result.size()); |
| for (Object o : result) { |
| assertTrue(o instanceof Object[]); |
| Object[] row = (Object[])o; |
| assertEquals(2, row.length); |
| assertTrue(row[0] instanceof Magazine); |
| assertTrue(row[1] instanceof Publisher); |
| assertNotNull(row[0]); |
| assertNotNull(row[1]); |
| assertEquals(((Magazine)row[0]).getPublisher(), row[1]); |
| } |
| } |
| |
| public void testAggregateExpressionInHavingExpression() { |
| String jpql = "select m.publisher, max(m.datePublished) " + |
| "from Magazine m group by m.publisher " + |
| "having max(m.datePublished) is null"; |
| |
| EntityManager em = emf.createEntityManager(); |
| Query query = em.createQuery(jpql); |
| List result = query.getResultList(); |
| assertTrue(result.isEmpty()); |
| |
| jpql = "select m.publisher, max(m.datePublished) " + |
| "from Magazine m group by m.publisher " + |
| "having max(m.tsPublished) = CURRENT_TIMESTAMP"; |
| query = em.createQuery(jpql); |
| result = query.getResultList(); |
| assertTrue(result.isEmpty()); |
| |
| // The following JPQL results in syntax error, |
| // see comments in OPENJPA-1814 |
| // jpql = "select m.publisher, max(m.datePublished) " + |
| // "from Magazine m group by m.publisher " + |
| // "having max(m.tsPublished) IN " + |
| // "(select max(m.tsPublished) from Magazine m " + |
| // "where m.datePublished = CURRENT_TIMESTAMP)"; |
| // query = em.createQuery(jpql); |
| // result = query.getResultList(); |
| // assertTrue(result.isEmpty()); |
| |
| jpql = "select m.publisher, max(m.datePublished) " + |
| "from Magazine m group by m.publisher " + |
| "having max(m.tsPublished) = " + |
| "(select max(m.tsPublished) from Magazine m " + |
| "where m.datePublished = CURRENT_TIMESTAMP)"; |
| query = em.createQuery(jpql); |
| result = query.getResultList(); |
| assertTrue(result.isEmpty()); |
| } |
| |
| /** |
| * Count number of expected result based on inner/outer join condition and |
| * the name of the Magazine. |
| */ |
| private int getExpecetedResultCount(String key, boolean innerJoin) { |
| int count = 0; |
| for (String[] magazinePublisherNamePair : MAGAZINE_PUBLISHER_NAME_PAIRS) { |
| String magName = magazinePublisherNamePair[0]; |
| String pubName = magazinePublisherNamePair[1]; |
| if (magName.indexOf(key) != -1) |
| if (!innerJoin || (innerJoin && pubName != null)) |
| count++; |
| } |
| return count; |
| } |
| } |