openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestDateStringConversion.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 java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.List;

import javax.persistence.Query;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.apache.openjpa.kernel.Filters;

/**
 * Test JDBC Escape Syntax date/time strings in query.
 *
 *
 * @author Pinaki Poddar
 *
 */
public class TestDateStringConversion extends CriteriaTest {
    private static final String OPEN_BRACKET  = "{";
    private static final String CLOSE_BRACKET = "}";
    private static final String SINGLE_QUOTE  = "'";

    void createData(String name) {
        long now = System.currentTimeMillis();
        long tomorrow = now + 24*60*60*1000+1;

        Date date = new Date(tomorrow);
        DependentId id = new DependentId();
        id.setEffDate(date);
        id.setName(name);
        Dependent pc = new Dependent();
        pc.setId(id);
        pc.setEndDate(date);
        em.getTransaction().begin();
        em.persist(pc);
        em.getTransaction().commit();
    }

    /**
     * The persistent property Dependent.endDate being tested is declared as Date but mapped with
     * @Temporal(TemporalType.TIMESTAMP).
     *
     * When executing JPQL, the query string directly uses a JDBC Escape Syntax that is passed
     * to JDBC Driver and hence that string should encode a Timestamp.
     *
     * Criteria Query, on the other hand, must conform to the API signature and hence the expression
     * that takes the JDBC Escape Syntax must be cast to Date.class to match the declared type of
     * Dependent.endDate.
     *
     */
    public void testDateString() {
        createData("testDateString");
        long now = System.currentTimeMillis();

        String dateString = createJDBCEscapeString(new Date(now));
        String tsString = createJDBCEscapeString(new Timestamp(now));

        // add Timestamp string because Dependent.endDate is mapped to TemporalType.TimeStamp
        String jpql = "select d from Dependent d where d.endDate >= " + tsString + " ORDER BY d.endDate";


        CriteriaQuery<Dependent> c = cb.createQuery(Dependent.class);
        Root<Dependent> d = c.from(Dependent.class);
        // the literal is cast to Date.class because Dependent.endDate is declared as Date
        c.where(cb.greaterThanOrEqualTo(d.get(Dependent_.endDate), cb.literal(dateString).as(Date.class)));
        c.orderBy(cb.asc(d.get(Dependent_.endDate)));

        // can not compare SQL because JPQL and Criteria handles JDBC escape syntax in
        // a different way.
        assertSameResult(c, jpql);
    }

    /**
     * A similar query but the JDBC Escape String is passed as an parameter.
     * Parameter value binding is relaxed to accept the string as a value
     * of temporal type instance.
     * Hence does not require, as in the the earlier case, to use different
     * escape string.
     */
    public void testDateStringAsParameter() {
        createData("testDateStringAsParameter");
        long now = System.currentTimeMillis();

        Date earlier = new Date(now - 1000);

        final String dateString = createJDBCEscapeString(earlier);
        String jpql = "select d from Dependent d where d.endDate >= :dateString ORDER BY d.endDate";
        CriteriaQuery<Dependent> c = cb.createQuery(Dependent.class);
        Root<Dependent> d = c.from(Dependent.class);

        c.where(cb.greaterThanOrEqualTo(d.get(Dependent_.endDate), cb.parameter(String.class, "dateString")
                .as(Date.class)));
        c.orderBy(cb.asc(d.get(Dependent_.endDate)));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("dateString", dateString);
            }
        }, c, jpql);

    }

    String createJDBCEscapeString(Object time) {
        String key = "";
        if (time instanceof Date)
            key = "d ";
        else if (time instanceof Time)
            key = "t ";
        else if (time instanceof Timestamp)
            key = "ts ";
        else {
            fail("Wrong object " + time + " of " + time.getClass() + " for JDBC conversion");
        }
        return OPEN_BRACKET + key + SINGLE_QUOTE + time.toString() + SINGLE_QUOTE + CLOSE_BRACKET;
    }

    public void testJDBCEscapeSyntaxTimestamp() {
        Timestamp ts = new Timestamp(System.currentTimeMillis());
        String s = "{ts '" + ts.toString() + "'}";
        assertTrue(Filters.isJDBCTemporalSyntax(s));
        assertNotNull(Filters.parseJDBCTemporalSyntax(s));
        Object converted = Filters.convert(s, Timestamp.class);
        assertTrue(converted instanceof Timestamp);
        assertEquals("Original=" + s + " Converted " + converted, ts.toString(), converted.toString());
    }

    public void testJDBCEscapeSyntaxTime() {
        Time t = new Time(System.currentTimeMillis());
        String s = "{t '" + t.toString() + "'}";
        assertTrue(Filters.isJDBCTemporalSyntax(s));
        assertNotNull(Filters.parseJDBCTemporalSyntax(s));
        Object converted = Filters.convert(s, Time.class);
        assertTrue(converted instanceof Time);
        assertEquals("Original=" + s + " Converted " + converted, t.toString(), converted.toString());
    }

    public void testJDBCEscapeSyntaxDate() {
        Date d = new Date(System.currentTimeMillis());
        String s = "{d '" + d.toString() + "'}";
        assertTrue(Filters.isJDBCTemporalSyntax(s));
        assertNotNull(Filters.parseJDBCTemporalSyntax(s));
        Object converted = Filters.convert(s, Date.class);
        assertTrue(converted instanceof Date);
        assertEquals("Original=" + s + " Converted " + converted, d.toString(), converted.toString());
    }

    void assertSameResult(CriteriaQuery<Dependent> c, String jpql) {
        List<Dependent> jResult = em.createQuery(jpql).getResultList();
        List<Dependent> cResult = em.createQuery(c).getResultList();
        assertFalse(jResult.isEmpty());
        assertEquals(cResult.size(), jResult.size());
        for (int i = 0; i < jResult.size(); i++) {
            assertEquals(jResult.get(i).getEndDate(), cResult.get(i).getEndDate());
        }
    }
}