Java Code:
CancelQuery11G.java
package pas.jdbc.cancel;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;
public class CancelQuery11G
{
private OracleDataSource ods = null;
public final String userId = "scott";
public final String password = "tiger";
public final String url =
"jdbc:oracle:thin:@myhost:1521:linux11g";
public CancelQuery11G() throws SQLException
{
ods = new OracleDataSource();
ods.setUser(userId);
ods.setPassword(password);
ods.setURL(url);
}
public void run () throws SQLException, IOException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
conn = getConnection();
getDriverDetails(conn);
stmt = conn.createStatement();
CancelThread ct = new CancelThread(stmt);
Thread th = new Thread(ct);
th.start();
Thread.sleep(1000);
System.out.println("Hit enter to cancel:");
BufferedReader stdin = new BufferedReader( new InputStreamReader( System.in ) );
String input = stdin.readLine();
System.out.println("Cancelling Query");
stmt.cancel();
System.out.println("Query Cancelled");
System.out.println("Hit enter to finish:");
stdin.readLine();
}
catch (SQLException e)
{
System.out.println("SQLException in run() : ");
e.printStackTrace();
}
catch (IOException ioe)
{
ioe.printStackTrace();
}
catch (InterruptedException ie)
{
ie.printStackTrace();
}
finally
{
if (ods != null)
ods.close();
if (conn != null)
conn.close();
}
}
private Connection getConnection() throws SQLException
{
return ods.getConnection();
}
private void getDriverDetails (Connection conn) throws SQLException
{
java.sql.DatabaseMetaData meta = conn.getMetaData ();
// gets driver info:
System.out.println("=============\nDatabase Product Name is ... " +
meta.getDatabaseProductName());
System.out.println("Database Product Version is " +
meta.getDatabaseProductVersion());
System.out.println("=============JDBC Driver Name is ........ " +
meta.getDriverName());
System.out.println("JDBC Driver Version is ..... " +
meta.getDriverVersion());
System.out.println("JDBC Driver Major Version is " +
meta.getDriverMajorVersion());
System.out.println("JDBC Driver Minor Version is " + meta.getDriverMinorVersion());
System.out.println("=============");
}
public static void main(String[] args) throws Exception
{
CancelQuery11G test = new CancelQuery11G();
test.run();
System.out.println("Completed");
}
}
CancelThread.java
package pas.jdbc.cancel;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class CancelThread implements Runnable
{
private Statement stmt = null;
String longRunningSQL =
"select * from all_objects";
private int i = 0;
public CancelThread(Statement oracleStmt)
{
stmt = oracleStmt;
}
public void run()
{
ResultSet rset = null;
try
{
System.out.println("Executing statement....");
rset = stmt.executeQuery(longRunningSQL);
System.out.println("Processing resultset now..");
while (rset.next())
{
i++;
}
System.out.println("Finished with resultset and statement now..");
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if (rset != null)
rset.close();
if (stmt != null)
stmt.close();
}
catch (SQLException e)
{
}
}
}
}
Output:
=============
Database Product Name is ... Oracle
Database Product Version is Oracle Database 11g Release 11.1.0.0.0 - Production
=============
JDBC Driver Name is ........ Oracle JDBC driver
JDBC Driver Version is ..... 11.1.0.6.0-Production
JDBC Driver Major Version is 11
JDBC Driver Minor Version is 1
=============
Executing statement....
Processing resultset now..
Hit enter to cancel:
Cancelling Query
java.sql.SQLException: ORA-01013: user requested cancel of current operation
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:119)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:115)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:221)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:467)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:417)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1084)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:187)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1312)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:606)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:493)
at pas.jdbc.cancel.CancelThread.run(CancelThread.java:28)
at java.lang.Thread.run(Thread.java:595)
Query Cancelled
Hit enter to finish:
Completed
1 comment:
very good!
Post a Comment