如何取消正在运行的SQL查询？ [英] How to cancel a running SQL query?

问题描述

我知道 statement.cancel（） a>可以用来取消正在运行的SQL查询，但是我想知道的是，如何在另一个线程中获得这个语句对象。

用例：

• 我请求启动一个运行语句的线程。
然后从一个单独的请求（另一个线程）我可能要取消此线程。

我将如何获取新请求该语句在其中调用取消方法。

有时可能会有多个语句在运行。

附加信息，它是一个使用spring framework，hibernate和JPA的web应用程序。现在在UI中有2个按钮，按钮1将触发SQL查询，而按钮2必须取消该查询。 我提到这个例子，但它使用相同的线程来调用新的线程，我不能做。

这是查询开始的方式：

 查询查询= mEntityManager.createNativeQuery（globalQuery.toString（））; 
 List< Object []> results = query.getResultList（）; 
  

编辑：

1. 我可以想到的一种方式是跟踪所有正在运行的语句，然后找到必须取消SQL语句的语句。

ol>


解决方案

有两个不同的会话可以帮助您：



想要在来自同一个用户的两个请求之间交换像你的语句这样的对象，如果这些请求并行或一个接一个地运行，那么你通常将它们存储在 HttpSession 中 HttpServletRequest的。





您可以使用Hibernate的 Session 来取消当前查询：

  public void startLongRunningStatement（）{
 EntityManager entityManager = ... 
 
 // Aquire session 
 Session hibernateSession =（（HibernateEntityManager）em.getDelegate（））。getSession（）; 
 
 //将HibernateSession存储在HttpSession中
 HttpSession httpSession = servletRequest.getSession（）
 httpSession.setAttribute（hibernateSession，hibernateSession）; 
 
尝试{
 //运行您的查询
查询查询= mEntityManager.createNativeQuery（globalQuery.toString（））; 
列表<？> results = query.getResultList（）; 
 $ b} finally {
 //清除会话对象，如果它仍然是我们的
 if（httpSession.getAttribute（hibernateSession）== hibernateSession）{
 httpSession.removeAttribute（ hibernateSession）; 
 
 
 
 $ b public void cancel（）{
 //从HTTP会话获取Hibernate会话
 HttpSession httpSession = servletRequest.getSession（）
 Session hibernateSession =（Session）httpSession.getAttribute（hibernateSession）; 
 if（hibernateSession！= null）{
 //取消前面的查询
 hibernateSession.cancelQuery（）; 
} 
 
} 
  




I know that statement.cancel() can be used to cancel a running SQL query, but what I want to know is, how will I get hold of this statement object in another thread.

Use case:

• I request to start a thread that runs a statement.
• Then from a separate request(another thread) I might want to cancel this thread.

How in this new request will I get the statement to call the cancel method in it.

There might be cases when there will me more than one statements running.

Additional information, it is a web application, using spring framework, hibernate and JPA. Now in the UI there are 2 buttons, Button 1 will trigger the SQL query and button 2 has to cancel that query

I referred to this example but it uses the same thread to call the new thread, which I cannot do.

This is how the query is started:

    Query query = mEntityManager.createNativeQuery(globalQuery.toString());
    List<Object[]> results = query.getResultList();

Edit:

1. One way I can think of is to keep track of all the running statements and then find the one for which the SQL statement has to be cancelled.

解决方案

There are two different sessions that will help you:

If you want to exchange an object like your statement between two requests from the same user, independent if these requests run in parallel or one after another, you usually store them in the HttpSession of the HttpServletRequest.

And you can use the Session of Hibernate to cancel the current query:

public void startLongRunningStatement() {
  EntityManager entityManager = ...

  // Aquire session
  Session hibernateSession = ((HibernateEntityManager) em.getDelegate()).getSession();

  // Store the HibernateSession in the HttpSession
  HttpSession httpSession = servletRequest.getSession()
  httpSession.setAttribute("hibernateSession", hibernateSession);

  try {
    // Run your query  
    Query query = mEntityManager.createNativeQuery(globalQuery.toString());
    List<?> results = query.getResultList();

  } finally {
    // Clear the session object, if it is still ours
    if (httpSession.getAttribute("hibernateSession") == hibernateSession) {
      httpSession.removeAttribute("hibernateSession");
    }
  }
}

public void cancel() {
  // Get the Hibernate session from the HTTP session
  HttpSession httpSession = servletRequest.getSession()
  Session hibernateSession = (Session) httpSession.getAttribute("hibernateSession");
  if (hibernateSession != null) {
    // Cancel the previous query
    hibernateSession.cancelQuery();
  }

}

这篇关于如何取消正在运行的SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！