jpa/eclipselink.jpa.test.jse/src/it/java/org/eclipse/persistence/jpa/test/query/TestQueryIN.java

/*
 * Copyright (c) 2021 IBM Corporation. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */
// Contributors:
//     IBM - Bug 573794: Add support for Parameters in CriteriaBuilder for IN expressions
package org.eclipse.persistence.jpa.test.query;

import java.util.Arrays;
import java.util.List;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaBuilder.In;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.ParameterExpression;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.Subquery;

import org.eclipse.persistence.jpa.test.framework.DDLGen;
import org.eclipse.persistence.jpa.test.framework.Emf;
import org.eclipse.persistence.jpa.test.framework.EmfRunner;
import org.eclipse.persistence.jpa.test.framework.Property;
import org.eclipse.persistence.jpa.test.query.model.EntityTbl01;
import org.eclipse.persistence.jpa.test.query.model.EntityTbl01_;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;

@RunWith(EmfRunner.class)
public class TestQueryIN {
    @Emf(createTables = DDLGen.DROP_CREATE, classes = { EntityTbl01.class }, 
            properties = { @Property(name="eclipselink.logging.level", value="FINE")})
    private EntityManagerFactory emf;

    private static boolean POPULATED = false;

    @Test
    public void testQuery_JPQL_IN_Literals_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ('HELLO', 'ONE', 'WORLD', 'PEOPLE')", EntityTbl01.class);
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_JPQL_IN_Parameters_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN (?1, ?2, ?3, ?4)", EntityTbl01.class);
            query.setParameter(1, "HELLO");
            query.setParameter(2, "ONE");
            query.setParameter(3, "WORLD");
            query.setParameter(4, "PEOPLE");
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());

            // equivalent test 2
            query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ?1", EntityTbl01.class);
            query.setParameter(1, Arrays.asList("HELLO", "ONE", "WORLD", "PEOPLE"));
            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_Literals_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            cquery.where(root.get(EntityTbl01_.itemString1).in("HELLO", "ONE", "WORLD", "PEOPLE"));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            In<String> in = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in.value("HELLO").value("ONE").value("WORLD").value("PEOPLE"));

            query = em.createQuery(cquery);

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());

            // equivalent test 3
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            List<String> strCollection = Arrays.asList("HELLO", "ONE", "WORLD", "PEOPLE");
            cquery.where(root.get(EntityTbl01_.itemString1).in(strCollection));

            query = em.createQuery(cquery);

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_Parameters_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            ParameterExpression<String> strValue1 = cb.parameter(String.class);
            ParameterExpression<String> strValue2 = cb.parameter(String.class);
            ParameterExpression<String> strValue3 = cb.parameter(String.class);
            ParameterExpression<String> strValue4 = cb.parameter(String.class);
            cquery.where(root.get(EntityTbl01_.itemString1).in(strValue1, strValue2, strValue3, strValue4));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);
            query.setParameter(strValue1, "HELLO");
            query.setParameter(strValue2, "ONE");
            query.setParameter(strValue3, "WORLD");
            query.setParameter(strValue4, "PEOPLE");

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            strValue1 = cb.parameter(String.class);
            strValue2 = cb.parameter(String.class);
            strValue3 = cb.parameter(String.class);
            strValue4 = cb.parameter(String.class);
            In<String> in = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in.value(strValue1).value(strValue2).value(strValue3).value(strValue4));

            query = em.createQuery(cquery);
            query.setParameter(strValue1, "HELLO");
            query.setParameter(strValue2, "ONE");
            query.setParameter(strValue3, "WORLD");
            query.setParameter(strValue4, "PEOPLE");

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());

            // equivalent test 3
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            cquery.from(EntityTbl01.class);
            cquery.select(root);

            ParameterExpression<List> colValue = cb.parameter(List.class);
            cquery.where(root.get(EntityTbl01_.itemString1).in(colValue));

            query = em.createQuery(cquery);
            query.setParameter(colValue, Arrays.asList("HELLO", "ONE", "WORLD", "PEOPLE"));

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("ONE", dto01.get(0).getItemString1());
            Assert.assertEquals("TWO", dto01.get(0).getItemString2());
            Assert.assertEquals("THREE", dto01.get(0).getItemString3());
            Assert.assertEquals("FIVE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(1), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_JPQL_IN_SUBQUERY_Literals_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ("
                            + "SELECT u.itemString1 FROM EntityTbl01 u "
                            + "WHERE u.itemString2 = 'SEVEN')", EntityTbl01.class);
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_JPQL_IN_SUBQUERY_Parameters_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ("
                            + "SELECT u.itemString1 FROM EntityTbl01 u "
                            + "WHERE u.itemString2 = ?2)", EntityTbl01.class);
            query.setParameter(2, "SEVEN");
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_SUBQUERY_Literals_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            Subquery<String> subquery = cquery.subquery(String.class);
            Root<EntityTbl01> subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(cb.equal(subroot.get(EntityTbl01_.itemString2), "SEVEN"));

            cquery.where(root.get(EntityTbl01_.itemString1).in(subquery));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            subquery = cquery.subquery(String.class);
            subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(cb.equal(subroot.get(EntityTbl01_.itemString2), "SEVEN"));

            In<String> in2 = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in2.value(subquery));

            query = em.createQuery(cquery);

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_SUBQUERY_Parameters_1() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            ParameterExpression<String> strValue1 = cb.parameter(String.class);
            Subquery<String> subquery = cquery.subquery(String.class);
            Root<EntityTbl01> subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(cb.equal(subroot.get(EntityTbl01_.itemString2), strValue1));

            cquery.where(root.get(EntityTbl01_.itemString1).in(subquery));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);
            query.setParameter(strValue1, "SEVEN");

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            strValue1 = cb.parameter(String.class);
            subquery = cquery.subquery(String.class);
            subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(cb.equal(subroot.get(EntityTbl01_.itemString2), strValue1));

            In<String> in2 = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in2.value(subquery));

            query = em.createQuery(cquery);
            query.setParameter(strValue1, "SEVEN");

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_JPQL_IN_SUBQUERY_Literals_2() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ("
                            + "SELECT u.itemString1 FROM EntityTbl01 u "
                            + "WHERE u.itemString2 IN ('TEN', 'SEVEN', 'ELEVEN'))", EntityTbl01.class);
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_JPQL_IN_SUBQUERY_Parameters_2() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            TypedQuery<EntityTbl01> query = em.createQuery(""
                    + "SELECT t FROM EntityTbl01 t "
                        + "WHERE t.itemString1 IN ("
                            + "SELECT u.itemString1 FROM EntityTbl01 u "
                            + "WHERE u.itemString2 IN (?2, ?3, ?4))", EntityTbl01.class);
            query.setParameter(2, "TEN");
            query.setParameter(3, "SEVEN");
            query.setParameter(4, "ELEVEN");
            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_SUBQUERY_Literals_2() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            Subquery<String> subquery = cquery.subquery(String.class);
            Root<EntityTbl01> subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(subroot.get(EntityTbl01_.itemString2).in("TEN", "SEVEN", "ELEVEN"));

            cquery.where(root.get(EntityTbl01_.itemString1).in(subquery));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            subquery = cquery.subquery(String.class);
            subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            In<String> in = cb.in(subroot.get(EntityTbl01_.itemString2));
            subquery.where(in.value("TEN").value("SEVEN").value("ELEVEN"));

            In<String> in2 = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in2.value(subquery));

            query = em.createQuery(cquery);

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    @Test
    public void testQuery_Criteria_IN_SUBQUERY_Parameters_2() {
        if (emf == null)
            return;

        if(!POPULATED) 
            populate();

        EntityManager em = emf.createEntityManager();

        try {
            // test 1
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<EntityTbl01> cquery = cb.createQuery(EntityTbl01.class);
            Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            ParameterExpression<String> strValue1 = cb.parameter(String.class);
            ParameterExpression<String> strValue2 = cb.parameter(String.class);
            ParameterExpression<String> strValue3 = cb.parameter(String.class);
            Subquery<String> subquery = cquery.subquery(String.class);
            Root<EntityTbl01> subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            subquery.where(root.get(EntityTbl01_.itemString2).in(strValue1, strValue2, strValue3));

            cquery.where(root.get(EntityTbl01_.itemString1).in(subquery));

            TypedQuery<EntityTbl01> query = em.createQuery(cquery);
            query.setParameter(strValue1, "TEN");
            query.setParameter(strValue2, "SEVEN");
            query.setParameter(strValue3, "ELEVEN");

            List<EntityTbl01> dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());

            // equivalent test 2
            cb = em.getCriteriaBuilder();
            cquery = cb.createQuery(EntityTbl01.class);
            root = cquery.from(EntityTbl01.class);
            cquery.select(root);

            strValue1 = cb.parameter(String.class);
            strValue2 = cb.parameter(String.class);
            strValue3 = cb.parameter(String.class);
            subquery = cquery.subquery(String.class);
            subroot = subquery.from(EntityTbl01.class);
            subquery.select(subroot.get(EntityTbl01_.itemString1));
            In<String> in = cb.in(subroot.get(EntityTbl01_.itemString2));
            subquery.where(in.value(strValue1).value(strValue2).value(strValue3));

            In<String> in2 = cb.in(root.get(EntityTbl01_.itemString1));
            cquery.where(in2.value(subquery));

            query = em.createQuery(cquery);
            query.setParameter(strValue1, "TEN");
            query.setParameter(strValue2, "SEVEN");
            query.setParameter(strValue3, "ELEVEN");

            dto01 = query.getResultList();
            Assert.assertNotNull(dto01);
            Assert.assertEquals(1, dto01.size());

            Assert.assertEquals("SIX", dto01.get(0).getItemString1());
            Assert.assertEquals("SEVEN", dto01.get(0).getItemString2());
            Assert.assertEquals("EIGHT", dto01.get(0).getItemString3());
            Assert.assertEquals("NINE", dto01.get(0).getItemString4());
            Assert.assertEquals(new Integer(2), dto01.get(0).getItemInteger1());
        } finally {
            if (em.getTransaction().isActive()) {
                em.getTransaction().rollback();
            }
            if(em.isOpen()) {
                em.close();
            }
        }
    }

    private void populate() {
        EntityManager em = emf.createEntityManager();
        try {
            em.getTransaction().begin();

            EntityTbl01 tbl1 = new EntityTbl01();
            tbl1.setKeyString("Key05");
            tbl1.setItemString1("ONE");
            tbl1.setItemString2("TWO");
            tbl1.setItemString3("THREE");
            tbl1.setItemString4("FIVE");
            tbl1.setItemInteger1(1);
            em.persist(tbl1);

            EntityTbl01 tbl2 = new EntityTbl01();
            tbl2.setKeyString("Key06");
            tbl2.setItemString1("SIX");
            tbl2.setItemString2("SEVEN");
            tbl2.setItemString3("EIGHT");
            tbl2.setItemString4("NINE");
            tbl2.setItemInteger1(2);
            em.persist(tbl2);

            em.getTransaction().commit();

            POPULATED = true;
        } finally {
            if(em.isOpen()) {
                em.close();
            }
        }
    }
}