openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestQueryByExample.java

/*
 * 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.criteria;

import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.metamodel.Attribute;

import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
import org.apache.openjpa.jdbc.sql.DB2Dictionary;
import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.jdbc.sql.OracleDictionary;
import org.apache.openjpa.persistence.OpenJPAEntityManagerSPI;

/**
 * Tests different styles for query by example.
 *
 * @author Pinaki Poddar
 *
 */
public class TestQueryByExample extends CriteriaTest {
    DBDictionary dict = null;

    @Override
    public void setUp() throws Exception {
        super.setUp();

        // If using an Oracle DB, use sql92 syntax in order to get a correct
        // comparison of SQL.  This may not work on Oracle JDBC drivers
        // prior to 10.x
        OpenJPAEntityManagerSPI ojem = (OpenJPAEntityManagerSPI)em;
        dict = ((JDBCConfiguration) ojem.getConfiguration())
            .getDBDictionaryInstance();
        if (dict instanceof OracleDictionary) {
            dict.setJoinSyntax("sql92");
        }
    }

    @Override
    public void tearDown() throws Exception {
        dict = null;
        super.tearDown();
    }

    public void testBasicFieldsWithNonDefaultValue() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1 AND e.salary=1100";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setSalary(1000+100);
        example.setRating(1);

        ComparisonStyle style = null;
        Attribute<?,?>[] excludes = null;
        q.where(cb.qbe(q.from(Employee.class), example, style, excludes));

        assertEquivalence(q, jpql);
    }

    public void testExcludeBasicFieldWithNonDefaultValue() {
        String jpql = "SELECT e FROM Employee e WHERE e.salary=1100";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setSalary(1000+100);
        example.setRating(1);

        ComparisonStyle style = null;
        Attribute<?,?>[] excludes = {Employee_.rating};
        q.where(cb.qbe(q.from(Employee.class), example, style, excludes));

        assertEquivalence(q, jpql);
    }

    public void testBasicFieldWithDefaultValueExcludedByDefaultStyle() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setRating(1);

        ComparisonStyle style = null;
        Attribute<?,?>[] excludes = null;
        q.where(cb.qbe(q.from(Employee.class), example, style, excludes));

        executeAndCompareSQL(q, "WHERE (t0.rating = ?)");
        assertEquivalence(q, jpql);
    }

    public void testBasicFieldWithDefaultValueCanBeIncludedByStyle() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1 AND e.salary=1100";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setRating(1);

        ComparisonStyle style = cb.qbeStyle();
        Attribute<?,?>[] excludes = null;
        q.where(cb.qbe(q.from(Employee.class), example, style.setExcludeDefault(false), excludes));

        executeAndCompareSQL(q, "WHERE (t0.rating = ? AND t0.salary = ?)");
        assertEquivalence(q, jpql);
    }

    public void testRelationFieldWithNonDefaultValue() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1 AND e.salary=1100 AND e.department.name='ExampleDept'";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setSalary(1100);
        example.setRating(1);
        Department dept = new Department();
        dept.setName("ExampleDept");
        example.setDepartment(dept);


        ComparisonStyle style = cb.qbeStyle();
        Attribute<?,?>[] excludes = null;
        q.where(cb.qbe(q.from(Employee.class), example, style, excludes));

        executeAndCompareSQL(q, "WHERE (t1.name = ? AND t0.rating = ? AND t0.salary = ?)");
    }

    public void testRelationFieldWithNullValueIncluded() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1 AND e.salary=1100 AND e.department IS NULL";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setName("ExampleEmployee");
        example.setSalary(1100);
        example.setRating(1);
        example.setDepartment(null);

        ComparisonStyle style = cb.qbeStyle();
        Attribute<?,?>[] excludes = {Employee_.frequentFlierPlan, Employee_.manager, Employee_.spouse};
        q.where(cb.qbe(q.from(Employee.class), example, style.setExcludeNull(false).setExcludeDefault(false),
                excludes));

        executeAndCompareSQL(q, "WHERE (1 <> 1 AND t0.DEPARTMENT_DEPTNO IS NULL "
                + "AND t0.name = ? AND t0.rating = ? AND t0.salary = ?)");
    }

    public void testEmbeddedField() {
        String jpql = "SELECT e FROM Employee e WHERE e.rating=1 AND e.salary=1100 AND e.department IS NULL";

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);

        Employee example = new Employee();
        example.setName("ExampleEmployee");
        example.setSalary(1100);
        example.setRating(1);
        Contact contact = new Contact();
        Address address = new Address();
        address.setCity("Plano");
        address.setState("TX");
        address.setCountry("USA");
        contact.setAddress(address);
        example.setContactInfo(contact);

        ComparisonStyle style = cb.qbeStyle();
        Attribute<?,?>[] excludes = {Employee_.department, Employee_.frequentFlierPlan,
                Employee_.manager, Employee_.spouse};
        q.where(cb.qbe(q.from(Employee.class), example, style, excludes));

        executeAndCompareSQL(q, "WHERE (t1.city = ? AND t1.country = ? AND t1.state = ? "
                + "AND t0.name = ? AND t0.rating = ? AND t0.salary = ?)");
    }

    @Override
    void executeAndCompareSQL(CriteriaQuery<?> q, String expected) {
        auditor.clear();
        em.createQuery(q).getResultList();
        assertEquals(1,auditor.getSQLs().size());
        String actual = extract("WHERE", auditor.getSQLs().get(0));
        if (dict instanceof DB2Dictionary)
            return;
        assertEquals(expected, actual);
    }

    String extract(String key, String s) {
        int index = s.indexOf(key);
        return index == -1 ? "" : s.substring(index);
    }
}