取消 JDBC 查询

您可以使用 Statement.cancel() 方法取消 JDBC 查询。

以下示例创建一个表 jdbccanceltest 并运行两个查询,取消第一个查询:

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Array;
import java.sql.Struct;


public class CancelSamples
{
    /**
     * Sets up a large test table, queries its contents and cancels the query.
     * @param  conn A connection to a Vertica database
     * @throws SQLException
     */
    public static void sampleCancelTest(Connection conn) throws SQLException
    {
        setup(conn);
        try
        {
            runQueryAndCancel(conn);
            runSecondQuery(conn);
        }
        finally
        {
            cleanup(conn);
        }
    }

    // Set up table used in test.
    private static void setup(Connection conn) throws SQLException
    {
        System.out.println("Creating and loading table...");
        Statement stmt = conn.createStatement();
        String queryText = "DROP TABLE IF EXISTS jdbccanceltest";
        stmt.execute(queryText);

        queryText = "CREATE TABLE jdbccanceltest(id INTEGER, time TIMESTAMP)";
        stmt.execute(queryText);

        queryText = "INSERT INTO jdbccanceltest SELECT row_number() OVER(), slice_time "
                    + "FROM(SELECT slice_time FROM("
                    + "SELECT '2021-01-01'::timestamp s UNION ALL SELECT '2022-01-01'::timestamp s"
                    + ") sq TIMESERIES slice_time AS '1 second' OVER(ORDER BY s)) sq2";
        stmt.execute(queryText);
    }

    /**
     * Execute a long-running query and cancel it.
     * @param  conn A connection to a Vertica database
     * @throws SQLException
     */
    private static void runQueryAndCancel(Connection conn) throws SQLException
    {
        System.out.println("Running and canceling query...");
        Statement stmt = conn.createStatement();
        String queryText = "select id, time from jdbccanceltest";
        ResultSet rs = stmt.executeQuery(queryText);

        int i=0;
        stmt.cancel();
        try
        {
            while (rs.next()) ;
            i++;
        }
        catch (SQLException e)
        {
            System.out.println("Query canceled after retrieving " + i + " rows");
            System.out.println(e.getMessage());
        }
    }

    /**
     * Run a simple query to demonstrate that it can be run after
     * the previous query was canceled.
     * @param conn A connection to a Vertica database
     * @throws SQLException
     */
    private static void runSecondQuery(Connection conn) throws SQLException
    {
        String queryText = "select 1 from dual";
        Statement stmt = conn.createStatement();
        try
        {
            ResultSet rs = stmt.executeQuery(queryText);
            while (rs.next()) ;
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
            System.out.println("warning: no exception should have been thrown on query after cancel");
        }
    }

    /**
     * Clean up table used in test.
     * @param conn A connetion to a Vertica database
     * @throws SQLException
     */
    private static void cleanup(Connection conn) throws SQLException
    {
        String queryText = "drop table if exists jdbccanceltest";
        Statement stmt = conn.createStatement();
        stmt.execute(queryText);
    }
}