Java Code Examples for java.sql.PreparedStatement.setQueryTimeout()

private void closeQueryStatement(PreparedStatement ps) throws SQLException {

		try {
			//work around a bug in all known connection pools....
			if ( ps.getMaxRows()!=0 ) ps.setMaxRows(0);
			if ( ps.getQueryTimeout()!=0 ) ps.setQueryTimeout(0);
		}
		catch (Exception e) {
			log.warn("exception clearing maxRows/queryTimeout", e);
			ps.close(); //just close it; do NOT try to return it to the pool!
			return; //NOTE: early exit!
		}
		
		closeStatement(ps);
		if ( lastQuery==ps ) lastQuery = null;
		
	}
 

private static void executeLockWithPreparedStatement(IPersistent object, DataSource dataSource)
throws SQLException, IllegalAccessException, InvocationTargetException {
    ClassMetaData classMetaData = ConfigurationService.getInstance().getMetaData(PersistentInstanceFactory.getActualPersistentClass(object).getName());
    String sql = PreparedStatementHelper.getLockPreparedStatementString(classMetaData, dataSource.getEngineType());
    PreparedStatement pstmt = dataSource.getPreparedStatement(sql);
    
    int i = 0;
    for (Iterator itr = classMetaData.getAllKeyFieldNames().iterator(); itr.hasNext();) {
        ++i;
        String fieldName = (String) itr.next();
        Object value = MoldingService.getInstanceValue(object, classMetaData, fieldName);
        DataTranslator.setAppObject(pstmt, i, value, classMetaData.getSqlType(fieldName), dataSource.getEngineType());
    }
    
    // Added for MS-Sql-Server as 'NO-WAIT" is not implemented like in Oracle
    pstmt.setQueryTimeout(QUERY_TIMEOUT_FOR_LOCKING);
    dataSource.executeUpdate(pstmt);
}
 

/** Executes a query against the underlying data source. Returns a Collection of persistent objects.
 * The Statement object will be automatically closed, once the recordset has been completely traversed.
 * @param statement The query to execute.
 * @param classMetaData The ClassMetaData defintion to be used for molding the ResultSet into Persistent objects.
 * @param criteria The Criteria used for the query. This will provide the values to set the various flags on the Persistent object.
 * @param queryTimeout This will be used for setting the timeout value on the Statement object; zero means there is no limit.
 * @param pagingPlugin The IPagingPlugin implementation that may be used to return a page of Results.
 * @throws SQLException if any database error occurs.
 * @throws PostLoadFailedException if any error is thrown in the PostLoad trigger of the persistent object.
 * @throws DataSourceCursorRuntimeException if any error occurs while molding the row into the Persistent object.
 * @throws IOException if any error occurs in reading the data from the database.
 * @return a Collection of persistent objects.
 */
public Collection executeQuery(PreparedStatement statement, ClassMetaData classMetaData, Criteria criteria, int queryTimeout, IPagingPlugin pagingPlugin)
throws SQLException, PostLoadFailedException, DataSourceCursorRuntimeException, IOException {
    // The following sets the timeout; zero means there is no limit
    // The setting works in MS-Sql-Server only !!
    statement.setQueryTimeout(queryTimeout);

    // set the fetch size
    try {
        statement.setFetchSize(getHitlistSize().intValue());
    } catch (Throwable e) {
        // NOTE: The setFetchSize feature may not be implemented by all the drivers. eg.Postgresql.
        // so just ignore the exception
    }
    ResultSet resultSet = null;
    if (log.isInfoEnabled()) {
        log.info("Executing the Prepared Statement\n" + statement);
        long currentTimeMillis = System.currentTimeMillis();
        resultSet = statement.executeQuery();
        log.info("Elapsed:" + (System.currentTimeMillis() - currentTimeMillis));
    } else {
        resultSet = statement.executeQuery();
    }
    registerStatement(statement, resultSet);
    return new DataSourceCursor(this, statement, resultSet, classMetaData, criteria, pagingPlugin);
}
 

protected void applyTimeout ( final PreparedStatement stmt ) throws SQLException
{
    if ( getTimeout () != null )
    {
        stmt.setQueryTimeout ( getTimeout () / 1000 );
    }
    else if ( this.connection.getTimeout () != null )
    {
        stmt.setQueryTimeout ( this.connection.getTimeout () / 1000 );
    }
}
 

protected int doExecute(QueryParameters parameters, SessionImplementor session, String sql,
		List parameterSpecifications) throws HibernateException {
	BulkOperationCleanupAction action = new BulkOperationCleanupAction( session, persister );
	if ( session.isEventSource() ) {
		( (EventSource) session ).getActionQueue().addAction( action );
	}
	else {
		action.getAfterTransactionCompletionProcess().doAfterTransactionCompletion( true, session );
	}

	PreparedStatement st = null;
	RowSelection selection = parameters.getRowSelection();

	try {
		try {
			st = session.getTransactionCoordinator().getJdbcCoordinator().getStatementPreparer().prepareStatement( sql, false );
			Iterator paramSpecItr = parameterSpecifications.iterator();
			int pos = 1;
			while ( paramSpecItr.hasNext() ) {
				final ParameterSpecification paramSpec = (ParameterSpecification) paramSpecItr.next();
				pos += paramSpec.bind( st, parameters, session, pos );
			}
			if ( selection != null ) {
				if ( selection.getTimeout() != null ) {
					st.setQueryTimeout( selection.getTimeout() );
				}
			}

			return session.getTransactionCoordinator().getJdbcCoordinator().getResultSetReturn().executeUpdate( st );
		}
		finally {
			if ( st != null ) {
				session.getTransactionCoordinator().getJdbcCoordinator().release( st );
			}
		}
	}
	catch( SQLException sqle ) {
		throw factory.getSQLExceptionHelper().convert( sqle, "could not execute update query", sql );
	}
}
 

/**
 * フェッチサイズとクエリタイムアウトをPreparedStatementに設定する
 *
 * @param preparedStatement PreparedStatement
 * @throws SQLException SQL例外
 */
protected void applyProperties(final PreparedStatement preparedStatement) throws SQLException {
	// フェッチサイズ指定
	if (getFetchSize() >= 0) {
		preparedStatement.setFetchSize(getFetchSize());
	}

	// クエリタイムアウト指定
	if (getQueryTimeout() >= 0) {
		preparedStatement.setQueryTimeout(getQueryTimeout());
	}
}
 

@Test
public void testPopulateData() throws Exception {
    int count = 100000;
    int actual = testCheckData();
    if (actual>=count) {
        System.out.println("Test tables has "+actual+" rows of data. No need to populate.");
        return;
    }

    datasource.setJdbcInterceptors(ResetAbandonedTimer.class.getName());
    String insert = "insert into test values (?,?,?,?,?)";
    this.datasource.setRemoveAbandoned(false);
    Connection con = datasource.getConnection();

    boolean commit = con.getAutoCommit();
    con.setAutoCommit(false);
    if (recreate) {
        Statement st = con.createStatement();
        try {
            st.execute("drop table test");
        }catch (Exception ignore) {}
        st.execute("create table test(id int not null, val1 varchar(255), val2 varchar(255), val3 varchar(255), val4 varchar(255))");
        st.close();
    }


    PreparedStatement ps = con.prepareStatement(insert);
    ps.setQueryTimeout(0);
    for (int i=actual; i<count; i++) {
        ps.setInt(1,i);
        String s = getRandom();
        ps.setString(2, s);
        ps.setString(3, s);
        ps.setString(4, s);
        ps.setString(5, s);
        ps.addBatch();
        ps.clearParameters();
        if ((i+1) % 1000 == 0) {
            System.out.print(".");
        }
        if ((i+1) % 10000 == 0) {
            System.out.print("\n"+(i+1));
            ps.executeBatch();
            ps.close();
            con.commit();
            ps = con.prepareStatement(insert);
            ps.setQueryTimeout(0);
        }

    }
    ps.close();
    con.setAutoCommit(commit);
    con.close();
}
 

/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(
		final String sql,
		final QueryParameters queryParameters,
		final LimitHandler limitHandler,
		final boolean scroll,
		final SessionImplementor session) throws SQLException, HibernateException {
	final Dialect dialect = getFactory().getDialect();
	final RowSelection selection = queryParameters.getRowSelection();
	final boolean useLimit = LimitHelper.useLimit( limitHandler, selection );
	final boolean hasFirstRow = LimitHelper.hasFirstRow( selection );
	final boolean useLimitOffset = hasFirstRow && useLimit && limitHandler.supportsLimitOffset();
	final boolean callable = queryParameters.isCallable();
	final ScrollMode scrollMode = getScrollMode( scroll, hasFirstRow, useLimitOffset, queryParameters );

	final PreparedStatement st = session.getTransactionCoordinator().getJdbcCoordinator()
			.getStatementPreparer().prepareQueryStatement( sql, callable, scrollMode );

	try {

		int col = 1;
		//TODO: can we limit stored procedures ?!
		col += limitHandler.bindLimitParametersAtStartOfQuery( st, col );

		if (callable) {
			col = dialect.registerResultSetOutParameter( (CallableStatement)st, col );
		}

		col += bindParameterValues( st, queryParameters, col, session );

		col += limitHandler.bindLimitParametersAtEndOfQuery( st, col );

		limitHandler.setMaxRows( st );

		if ( selection != null ) {
			if ( selection.getTimeout() != null ) {
				st.setQueryTimeout( selection.getTimeout() );
			}
			if ( selection.getFetchSize() != null ) {
				st.setFetchSize( selection.getFetchSize() );
			}
		}

		// handle lock timeout...
		final LockOptions lockOptions = queryParameters.getLockOptions();
		if ( lockOptions != null ) {
			if ( lockOptions.getTimeOut() != LockOptions.WAIT_FOREVER ) {
				if ( !dialect.supportsLockTimeouts() ) {
					if ( log.isDebugEnabled() ) {
						log.debugf(
								"Lock timeout [%s] requested but dialect reported to not support lock timeouts",
								lockOptions.getTimeOut()
						);
					}
				}
				else if ( dialect.isLockTimeoutParameterized() ) {
					st.setInt( col++, lockOptions.getTimeOut() );
				}
			}
		}

		if ( log.isTraceEnabled() ) {
			log.tracev( "Bound [{0}] parameters total", col );
		}
	}
	catch ( SQLException sqle ) {
		session.getTransactionCoordinator().getJdbcCoordinator().release( st );
		throw sqle;
	}
	catch ( HibernateException he ) {
		session.getTransactionCoordinator().getJdbcCoordinator().release( st );
		throw he;
	}

	return st;
}
 

private void setStatementTimeout(PreparedStatement preparedStatement) throws SQLException {
	final int remainingTransactionTimeOutPeriod = jdbcCoordinator.determineRemainingTransactionTimeOutPeriod();
	if ( remainingTransactionTimeOutPeriod > 0 ) {
		preparedStatement.setQueryTimeout( remainingTransactionTimeOutPeriod );
	}
}
 

/**
 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
 * limit parameters.
 */
protected final PreparedStatement prepareQueryStatement(
	String sql,
	final QueryParameters queryParameters,
	final boolean scroll,
	final SessionImplementor session)
throws SQLException, HibernateException {

	Dialect dialect = session.getFactory().getDialect();
	RowSelection selection = queryParameters.getRowSelection();
	boolean useLimit = useLimit(selection, dialect);
	boolean hasFirstRow = getFirstRow(selection)>0;
	boolean useOffset = hasFirstRow && useLimit && dialect.supportsLimitOffset();
	boolean scrollable = session.getFactory().isScrollableResultSetsEnabled() && (
		scroll || //ie. a query called using scroll()
		( hasFirstRow && !useOffset ) //we want to skip some rows at the start
	);
	ScrollMode scrollMode = scroll ? queryParameters.getScrollMode() : ScrollMode.SCROLL_INSENSITIVE;

	if (useLimit) sql = dialect.getLimitString( sql.trim(), useOffset, getMaxOrLimit(selection, dialect) );

	PreparedStatement st = session.getBatcher().prepareQueryStatement(sql, scrollable, scrollMode);

	try {

		int col=1;

		if ( useLimit && dialect.bindLimitParametersFirst() ) {
			col += bindLimitParameters(st, col, selection, session);
		}
		col += bindPositionalParameters(st, queryParameters, col, session);
		col += bindNamedParameters(st, queryParameters.getNamedParameters(), col, session);

		if ( useLimit && !dialect.bindLimitParametersFirst() ) {
			col += bindLimitParameters(st, col, selection, session);
		}

		if (!useLimit) setMaxRows(st, selection);
		if (selection!=null) {
			if ( selection.getTimeout()!=null ) {
				st.setQueryTimeout( selection.getTimeout().intValue() );
			}
			if ( selection.getFetchSize()!=null ) {
				st.setFetchSize( selection.getFetchSize().intValue() );
			}
		}
	}
	catch (SQLException sqle) {
		JDBCExceptionReporter.logExceptions(sqle);
		session.getBatcher().closeQueryStatement(st, null);
		throw sqle;
	}
	catch (HibernateException he) {
		session.getBatcher().closeQueryStatement(st, null);
		throw he;
	}

	return st;
}
 

@Test
public void testPopulateData() throws Exception {
    int count = 100000;
    int actual = testCheckData();
    if (actual>=count) {
        System.out.println("Test tables has "+actual+" rows of data. No need to populate.");
        return;
    }

    datasource.setJdbcInterceptors(ResetAbandonedTimer.class.getName());
    String insert = "insert into test values (?,?,?,?,?)";
    this.datasource.setRemoveAbandoned(false);
    Connection con = datasource.getConnection();

    boolean commit = con.getAutoCommit();
    con.setAutoCommit(false);
    if (recreate) {
        Statement st = con.createStatement();
        try {
            st.execute("drop table test");
        }catch (Exception ignore) {}
        st.execute("create table test(id int not null, val1 varchar(255), val2 varchar(255), val3 varchar(255), val4 varchar(255))");
        st.close();
    }


    PreparedStatement ps = con.prepareStatement(insert);
    ps.setQueryTimeout(0);
    for (int i=actual; i<count; i++) {
        ps.setInt(1,i);
        String s = getRandom();
        ps.setString(2, s);
        ps.setString(3, s);
        ps.setString(4, s);
        ps.setString(5, s);
        ps.addBatch();
        ps.clearParameters();
        if ((i+1) % 1000 == 0) {
            System.out.print(".");
        }
        if ((i+1) % 10000 == 0) {
            System.out.print("\n"+(i+1));
            ps.executeBatch();
            ps.close();
            con.commit();
            ps = con.prepareStatement(insert);
            ps.setQueryTimeout(0);
        }

    }
    ps.close();
    con.setAutoCommit(commit);
    con.close();
}