/*******************************************************************************
* Copyright (c) 1998, 2015 Oracle and/or its affiliates. All rights reserved.
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* Contributors:
* Oracle - initial API and implementation from Oracle TopLink
******************************************************************************/
package org.eclipse.persistence.testing.tests.queries.options;
import org.eclipse.persistence.sessions.*;
import org.eclipse.persistence.exceptions.*;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.internal.security.PrivilegedAccessHelper;
import org.eclipse.persistence.testing.framework.*;
import org.eclipse.persistence.internal.databaseaccess.*;
import java.math.*;
import java.util.*;
import java.sql.*;
import java.lang.reflect.*;
/**
* Test to verify that max rows, query timeout and result set fetch size are cleared
* on PreparedStatement objects utilized by TopLink. After a query has been executed,
* these settings must be cleared so that other queries do not use these options
* that are set local to each query.
* For Bug 5709179 - MAX-ROWS/TIMEOUT NOT RESET ON CACHED STATEMENTS
* @author dminsky
*/
public class ClearQueryOptionsOnStatementTest extends AutoVerifyTestCase {
private List employeesCreated;
protected boolean TYPE_SCROLL_INSENSITIVE_isSupported;
protected boolean CONCUR_UPDATABLE_isSupported;
public ClearQueryOptionsOnStatementTest() {
super();
setDescription("This test verifies max rows, query timeout & result set fetch size are cleared on prepared statements");
}
public void setup() {
TYPE_SCROLL_INSENSITIVE_isSupported = true;
CONCUR_UPDATABLE_isSupported = true;
if(getSession().getPlatform().isSQLServer()) {
// In case either TYPE_SCROLL_INSENSITIVE or CONCUR_UPDATABLE used
// MS SQL Server Version: 9.00.2050; MS SQL Server 2005 JDBC Driver Version: 1.2.2828.100 throws exception:
// com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported.
TYPE_SCROLL_INSENSITIVE_isSupported = false;
CONCUR_UPDATABLE_isSupported = false;
}
// must enable statement caching
getDatabaseSession().getLogin().cacheAllStatements();
getDatabaseSession().getIdentityMapAccessor().initializeAllIdentityMaps();
UnitOfWork uow = getDatabaseSession().acquireUnitOfWork();
employeesCreated = new ArrayList(10);
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(190), "Jak"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(191), "Daxter"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(192), "Ratchet"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(193), "Clank"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(194), "Crash"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(195), "Sonic"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(196), "Mario"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(197), "Luigi"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(198), "Peach"));
employeesCreated.add(new QueryOptionEmployee(new BigDecimal(199), "Bowser"));
uow.registerAllObjects(employeesCreated);
uow.commit();
}
public void test() {
DatabaseSession session = getDatabaseSession();
testQueryTimeoutReset(session);
testMaxRowsReset(session);
testResultSetFetchSizeReset(session);
}
public void testMaxRowsReset(Session session) {
// MAX ROWS
// 1. Execute query to read employees with a max-rows set to 4
ReadAllQuery query = new ReadAllQuery(QueryOptionEmployee.class);
query.setMaxRows(4);
List employees = (List) session.executeQuery(query);
// 2. Check employees read = 4 per MaxRows setting - just with an assert
if (employees.size() != 4) {
throw new TestErrorException("Max Rows reset - Rows returned: " + employees.size() + " (expecting 4)");
}
// 3. Execute another (new) query 100 times with same SQL & no max-rows setting
for (int iteration = 0; iteration < 100; iteration++) {
query = new ReadAllQuery(QueryOptionEmployee.class);
employees = (List) session.executeQuery(query);
if (employees.size() <= 4) {
throw new TestErrorException("Max Rows reset - Rows returned: " + employees.size() + " (expecting >= 10)");
}
}
}
public void testResultSetFetchSizeReset(Session session) {
// H2 sets the query fetch size on the connection, and does not clear it, so this will fail.
if (getSession().getLogin().getDatasourcePlatform().isH2()) {
return;
}
// Resultset fetch size
ReadAllQuery query = new ReadAllQuery(QueryOptionEmployee.class);
// HANA supports only TYPE_FORWARD_ONLY and CONCUR_READ_ONLY
if (getSession().getPlatform().isHANA()) {
ScrollableCursorPolicy policy = new ScrollableCursorPolicy();
policy.setResultSetType(ScrollableCursorPolicy.TYPE_FORWARD_ONLY);
policy.setResultSetConcurrency(ScrollableCursorPolicy.CONCUR_READ_ONLY);
policy.setPageSize(10);
query.useScrollableCursor(policy);
} else if(TYPE_SCROLL_INSENSITIVE_isSupported && CONCUR_UPDATABLE_isSupported) {
query.useScrollableCursor(2);
} else {
ScrollableCursorPolicy policy = new ScrollableCursorPolicy();
if(!TYPE_SCROLL_INSENSITIVE_isSupported) {
policy.setResultSetType(ScrollableCursorPolicy.TYPE_SCROLL_SENSITIVE);
}
if(!CONCUR_UPDATABLE_isSupported) {
policy.setResultSetConcurrency(ScrollableCursorPolicy.CONCUR_READ_ONLY);
}
policy.setPageSize(10);
query.useScrollableCursor(policy);
}
String sql = "SELECT ID, NAME, HISTORY_ID FROM QUERY_OPTION_EMPLOYEE";
int fetchSize = 100;
query.setSQLString(sql);
query.setFetchSize(fetchSize);
// The statement cache is protected - need to obtain the internal hashtable from the accessor
org.eclipse.persistence.internal.sessions.DatabaseSessionImpl impl =
(org.eclipse.persistence.internal.sessions.DatabaseSessionImpl) session;
DatabaseAccessor accessor = (DatabaseAccessor) impl.getAccessor();
Map statementCache = null;
try {
Method method = PrivilegedAccessHelper.getDeclaredMethod(DatabaseAccessor.class,
"getStatementCache", new Class[]{});
method.setAccessible(true);
statementCache = (Map) method.invoke(accessor, new Object[] {});
} catch (Exception nsme) {
throwError("Could not invoke DatabaseAccessor>>getStatementCache()", nsme);
}
// now cache the statement's previous fetch size
int previousFetchSize = 0;
Statement statement = (Statement) statementCache.get(sql);
if (statement != null) {
try {
previousFetchSize = statement.getFetchSize();
} catch (SQLException sqle) {
throwError("Error whilst invoking intial Statement>>getFetchSize()", sqle);
}
}
// execute query
ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);
List employees = new ArrayList();
while (cursor.hasNext()) {
employees.add(cursor.next());
}
cursor.close();
// now check the statement
int postQueryFetchSize = 0;
statement = (Statement) statementCache.get(sql);
if (statement != null) {
try {
postQueryFetchSize = statement.getFetchSize();
} catch (SQLException sqle) {
throwError("Error whilst invoking secondary Statement>>getFetchSize()", sqle);
}
}
if (postQueryFetchSize == fetchSize) {
throwError("Statement fetch size was not reset");
}
}
public void testQueryTimeoutReset(Session session) {
if (getSession().getPlatform().isSymfoware()) {
throwWarning("Test testQueryTimeoutReset skipped for this platform, "
+ "the driver does not support query timeout. (bug 304905)");
}
boolean query1TimedOut = false;
boolean query2TimedOut = false;
// H2 sets the query timeout on the connection, and does not clear it, so this will fail.
if (getSession().getLogin().getDatasourcePlatform().isH2()) {
return;
}
String sql;
if (getSession().getLogin().getDatasourcePlatform().isDB2() || getSession().getLogin().getDatasourcePlatform().isMySQL()) {
sql = "SELECT SUM(e.EMP_ID) from EMPLOYEE e , EMPLOYEE b, EMPLOYEE c,EMPLOYEE d";
} else {
sql = "SELECT SUM(e.EMP_ID) from EMPLOYEE a , EMPLOYEE b, EMPLOYEE c, EMPLOYEE d, EMPLOYEE e, EMPLOYEE f, EMPLOYEE g";
}
// set the lowest timeout value on a query which is virtually guaranteed to produce a timeout
try {
DataReadQuery query = new DataReadQuery();
query.setSQLString(sql);
query.setQueryTimeout(1);
session.executeQuery(query);
} catch (Exception e) {
if (e instanceof DatabaseException) {
// cache value for debug purposes
query1TimedOut = true;
}
}
// do not set a timeout on the query, and test for a timeout
try {
DataReadQuery query = new DataReadQuery();
query.setSQLString(sql);
session.executeQuery(query);
} catch (Exception e) {
if (e instanceof DatabaseException) {
query2TimedOut = true;
}
}
// we're interested in if query 2 timed out
// if no timeout value was set, query 2 should not produce a timeout
if (query2TimedOut == true) {
throw new TestErrorException("Query timeout occurred - PreparedStatement query timeout setting not cleared");
}
}
public void reset() {
getDatabaseSession().getLogin().dontCacheAllStatements();
UnitOfWork uow = getDatabaseSession().acquireUnitOfWork();
uow.deleteAllObjects(employeesCreated);
uow.commit();
getDatabaseSession().getIdentityMapAccessor().initializeAllIdentityMaps();
}
}