openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestJDBCEscapeDate.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.query;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.Query;

import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.jdbc.sql.HSQLDictionary;
import org.apache.openjpa.jdbc.sql.PostgresDictionary;
import org.apache.openjpa.jdbc.sql.SQLServerDictionary;
import org.apache.openjpa.jdbc.sql.SybaseDictionary;
import org.apache.openjpa.persistence.test.SingleEMFTestCase;

import org.junit.Assert;

/**
 * Test JDBC escape syntax for date, time, and timestamp literals
 */
public class TestJDBCEscapeDate extends SingleEMFTestCase {

    @Override
    public void setUp() {
        setUp(Employee.class, DROP_TABLES);
    }

    public void populate(){
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        Employee e = new Employee();
        e.setEmpId(1);
        e.setName("name1");
        e.setHireDate(new Date());
        e.setHireTime(new Date());
        e.setHireTimestamp(new Date());
        em.persist(e);
        tran.begin();
        tran.commit();
        em.close();
    }

    public void testJDBCEscape() {
        populate();
        EntityManager em = emf.createEntityManager();

        String[] jpql;
        DBDictionary dict = ((JDBCConfiguration)emf.getConfiguration()).getDBDictionaryInstance();
        if ((dict instanceof SQLServerDictionary) || (dict instanceof HSQLDictionary)) {
            jpql = new String[] {
                // some changes to the jpql strings had to be made for MSSQL and HSQLDB
                "select a from Employee a where a.hireDate >= {d '2009-08-25'}",
                "select a from Employee a where a.hireDate >= {d '2009-08-05'}",    // requires yyyy-mm-dd
                // "select a from Employee a where a.hireTime >= {t '00:00:00'}",   // fails ?
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123'}",
                // "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1234'}", // more than 3
                // "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12345'}", // fails
                // "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123456'}",
                "select {t '00:00:00'}, a.empId from Employee a",
            };
        } else if ((dict instanceof SybaseDictionary)) {
            jpql = new String[] {
                "select a from Employee a where a.hireDate >= {d '2009-08-25'}",
                "select a from Employee a where a.hireDate >= {d '2009-8-5'}",
                "select a from Employee a where a.hireTime >= {t '00:00:00'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.0'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1234'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12345'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123456'}",
                "select {t '00:00:00'}, a.empId from Employee a",
            };
        } else if (dict instanceof PostgresDictionary) {
            jpql = new String[] {
                "select a from Employee a where a.hireDate >= {d '2009-08-25'}",
                "select a from Employee a where a.hireDate >= {d '2009-8-5'}",
                "select a from Employee a where a.hireTime >= {t '00:00:00'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00'}",
                //"select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1234'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12345'}",
                "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123456'}",
                "select {t '00:00:00'}, a.empId from Employee a",
            };
        } else {
            jpql = new String[] {
                    "select a from Employee a where a.hireDate >= {d '2009-08-25'}",
                    "select a from Employee a where a.hireDate >= {d '2009-8-5'}",
                    "select a from Employee a where a.hireTime >= {t '00:00:00'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.1234'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.12345'}",
                    "select a from Employee a where a.hireTimestamp >= {ts '2009-08-25 00:00:00.123456'}",
                    "select {t '00:00:00'}, a.empId from Employee a",
                };
        }


        for (int i = 0; i < jpql.length; i++) {
            Query q = em.createQuery(jpql[i]);
            List results = q.getResultList();
            Assert.assertEquals("For jpql["+i+"]", 1, results.size());
        }

        // Test support in HAVING clause.
        String[] havingJpql = {
            "select a from Employee a group by a.hireTime having a.hireTime >= {t '00:00:00'}",
            "select a from Employee a group by a.hireDate having a.hireDate >= {d '2009-08-25'}",
            "select a from Employee a group by a.hireTimestamp having a.hireTimestamp >= {d '2009-08-25'}"
        };

        for (int j = 0; j < havingJpql.length; j++) {
            Query q = em.createQuery(havingJpql[j]);
            List results = q.getResultList();
            Assert.assertEquals("For havingJpql["+j+"]", 1, results.size());
        }
        em.getTransaction().begin();
        String update;
        if ((dict instanceof SQLServerDictionary) || (dict instanceof HSQLDictionary)) {
            // more than 3 digits after 00:00:00. fails on MSSQL and HSQLDB
            update = "update Employee a set a.hireTimestamp = {ts '2009-08-25 00:00:00.123'} where a.empId = 1";
        } else {
            update = "update Employee a set a.hireTimestamp = {ts '2009-08-25 00:00:00.123456'} where a.empId = 1";
        }
        Query q = em.createQuery(update);
        int updateCnt = q.executeUpdate();
        em.getTransaction().commit();
        Assert.assertEquals(1, updateCnt);
        em.close();
    }

    /*
     * Added for OJ-2286.  The test executes the same query multiple times.  Prior
     * to the JIRA fix, upon the second exception an exception would occur.
     */
    public void testMultipleQueryExecutionWithDateLiteral() {
        populate();
        EntityManager em = emf.createEntityManager();

        Query q = em.createQuery("SELECT e FROM Employee e WHERE e.hireTimestamp > {ts '2001-01-01 00:00:00'}");
        Assert.assertEquals("First assertion", 1, q.getResultList().size());
        // Prior to JIRA OJ-2286, an exception would occur here:
        Assert.assertEquals("Second assertion", 1, q.getResultList().size());
        // For good measure execute it a couple more times.  :)
        Assert.assertEquals("Third assertion", 1, q.getResultList().size());
        Assert.assertEquals("Fourth assertion", 1, q.getResultList().size());
    }
}