title: Intercept all JDBC calls using AspectJ within a request scope and returning as response
body: <p>I am having trouble implementing a query profiler for my dataservice (Spring boot application) for debugging purpose. Any help would be appreciated.</p>
  
  <p><strong>Problem statement</strong>:</p>
  
  <p>I need to return all the database queries being executed for a particular endpoint along with response.</p>
  
  <p><strong>My approach</strong>:</p>
  
  <p>I have created a request scope component and autowired in the aspect, populating the queries in the request scope object and injecting it with the response. I have provided all the files required below.</p>
  
  <p><strong>Issue</strong>:</p>
  
  <p>Some of the endpoints execute query in multiple threads. I was getting error but was able to fix the errors using <code>simpleThreadScope</code>. However I am not able to see any of the queries executed by threads (I can see queries executed outside threads). Could you help me get queries executed inside thread in the response as well?</p>
  
  <p>AspectJ configuration:</p>
  
  <pre><code>&lt;plugin&gt;
              &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
              &lt;artifactId&gt;maven-compiler-plugin&lt;/artifactId&gt;
              &lt;executions&gt;
                  &lt;execution&gt;
                      &lt;id&gt;default-compile&lt;/id&gt;
                      &lt;phase&gt;none&lt;/phase&gt;
                  &lt;/execution&gt;
              &lt;/executions&gt;
          &lt;/plugin&gt;
          &lt;plugin&gt;
              &lt;groupId&gt;org.codehaus.mojo&lt;/groupId&gt;
              &lt;artifactId&gt;aspectj-maven-plugin&lt;/artifactId&gt;
              &lt;version&gt;1.7&lt;/version&gt;
              &lt;configuration&gt;
                  &lt;aspectDirectory&gt;aspect&lt;/aspectDirectory&gt;
                  &lt;complianceLevel&gt;1.8&lt;/complianceLevel&gt;
              &lt;/configuration&gt;
              &lt;dependencies&gt;
                  &lt;dependency&gt;
                      &lt;groupId&gt;org.aspectj&lt;/groupId&gt;
                      &lt;artifactId&gt;aspectjtools&lt;/artifactId&gt;
                      &lt;version&gt;1.8.10&lt;/version&gt;
                      &lt;scope&gt;compile&lt;/scope&gt;
                  &lt;/dependency&gt;
              &lt;/dependencies&gt;
              &lt;executions&gt;
                  &lt;execution&gt;
                      &lt;!-- Compile and weave aspects after all classes compiled by javac --&gt;
                      &lt;goals&gt;
                          &lt;goal&gt;compile&lt;/goal&gt;
                      &lt;/goals&gt;
                  &lt;/execution&gt;
              &lt;/executions&gt;
          &lt;/plugin&gt;
  </code></pre>
  
  <p>Aspect:</p>
  
  <pre><code>@Aspect
  @Component
  public class QueryProfilerAspect {
      private static final Logger logger = LoggerFactory
              .getLogger(QueryProfilerAspect.class);
      private static String TIME_FORMAT = "HH:mm:ss.SSS";
      private static String QUERY_PROFILER = "QueryProfiler";
  
      @Autowired
      QueryProfile queryProfile;
  
      @Pointcut("(call(* org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations.query*(..)) &amp;&amp; args(query,params,..))")
      public void anyJDBCOperations(String query, Map params) {
      }
  
      @Pointcut("execution(* *(..)) &amp;&amp; @annotation(org.springframework.web.bind.annotation.PostMapping) || @annotation(org.springframework.web.bind.annotation.PutMapping) || @annotation(org.springframework.web.bind.annotation.DeleteMapping) || @annotation(org.springframework.web.bind.annotation.GetMapping)")
      private void anyGetPutPostDeleteMappingMethodPointCut() {
          // pointcut
      }
  
      @Pointcut("execution(* *(..)) &amp;&amp; @annotation(org.springframework.web.bind.annotation.RequestMapping)")
      private void anyRequestMappingMethodPointCut() {
          // pointcut
      }
  
      @Around("anyJDBCOperations(sqlQuery, params)")
      public Object log(ProceedingJoinPoint jp, String sqlQuery, Map params)
              throws Throwable {
              long start = System.currentTimeMillis();
              Object output = jp.proceed();
              long elapsedTime = System.currentTimeMillis() - start;
  
              DataSource dataSource = ((JdbcTemplate) ((NamedParameterJdbcOperations) jp
                      .getTarget()).getJdbcOperations()).getDataSource();
  
              if (params instanceof Map &amp;&amp; !params.isEmpty()) {
                  logger.debug("inside instance of MAP!!!! ::param {}", params);
                  sqlQuery = replaceMap(sqlQuery, (Map&lt;?, ?&gt;) params);
              }
  
              queryProfile.getQuery().add(sqlQuery);
              logger.info("Intercepted Query is::: {}", sqlQuery);
              return output;
      }
  
      @AfterReturning(value = "anyRequestMappingMethodPointCut() || anyGetPutPostDeleteMappingMethodPointCut()", returning = "returnVal")
      public void anyPublicControllerMethod(JoinPoint jp,
              ResponseEntity returnVal)
              throws Throwable {
              HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder
                      .currentRequestAttributes()).getRequest();
  
              QueryProfile qp = new QueryProfile(queryProfile);
              qp.setRequestURL(request.getRequestURL().toString());
              qp.setHostName(getHostName());
  
              Object responseBody = returnVal.getBody();
              if (responseBody instanceof ResponseDTO) {
                  List&lt;QueryProfile&gt; profileList = new ArrayList&lt;&gt;();
                  if (((ResponseDTO) responseBody).getMeta().get(QUERY_PROFILER)
                          != null) {
                      profileList.add((QueryProfile) ((ResponseDTO) responseBody)
                              .getMeta().get(QUERY_PROFILER));
                  }
                  profileList.add(qp);
                  ((ResponseDTO) responseBody)
                          .addMeta(QUERY_PROFILER, profileList);
              }
      }
  }
  </code></pre>
  
  <p>Request Scope object:</p>
  
  <pre><code>@Component
  @Scope(value = "simpleThreadScope", proxyMode = ScopedProxyMode.TARGET_CLASS)
  public class QueryProfile {
      private String hostName;
      private String requestURL;
      private Long duration;
      private String time;
      private String dataSource;
      private List&lt;String&gt; query = new ArrayList&lt;&gt;();
  
      public QueryProfile() {
          //Default constructor
      }
  
      public QueryProfile(QueryProfile qp) {
          setHostName(qp.getHostName());
          setRequestURL(qp.getRequestURL());
          setDataSource(qp.getDataSource());
          setDuration(qp.getDuration());
          setTime(qp.getTime());
          setQuery(qp.getQuery());
      }
  
      public String getHostName() {
          return hostName;
      }
  
      public void setHostName(String hostName) {
          this.hostName = hostName;
      }
  
      public String getRequestURL() {
          return requestURL;
      }
  
      public void setRequestURL(String requestURL) {
          this.requestURL = requestURL;
      }
  
      public Long getDuration() {
          return duration;
      }
  
      public void setDuration(Long duration) {
          this.duration = duration;
      }
  
      public String getTime() {
          return time;
      }
  
      public void setTime(String time) {
          this.time = time;
      }
  
      public String getDataSource() {
          return dataSource;
      }
  
      public void setDataSource(String dataSource) {
          this.dataSource = dataSource;
      }
  
      public List&lt;String&gt; getQuery() {
          return query;
      }
  
      public void setQuery(List&lt;String&gt; query) {
          this.query = query;
      }
  }
  </code></pre>
  
  <p><code>SimpleTheradScope</code> configuration:</p>
  
  <pre><code>@Configuration
  public class MainConfig implements BeanFactoryAware {
  
      private static final Logger logger = LoggerFactory.getLogger(MainConfig.class);
  
      @Override
      public void setBeanFactory(BeanFactory beanFactory) throws BeansException {
          if (beanFactory instanceof ConfigurableBeanFactory) {
  
              logger.info("MainConfig is backed by a ConfigurableBeanFactory");
              ConfigurableBeanFactory cbf = (ConfigurableBeanFactory) beanFactory;
  
              /*Notice:
               *org.springframework.beans.factory.config.Scope
               * !=
               *org.springframework.context.annotation.Scope
               */
              org.springframework.beans.factory.config.Scope simpleThreadScope = new SimpleThreadScope();
              cbf.registerScope("simpleThreadScope", simpleThreadScope);
  
              /*why the following? Because "Spring Social" gets the HTTP request's username from
               *SecurityContextHolder.getContext().getAuthentication() ... and this 
               *by default only has a ThreadLocal strategy...
               *also see http://stackoverflow.com/a/3468965/923560 
               */
              SecurityContextHolder.setStrategyName(SecurityContextHolder.MODE_INHERITABLETHREADLOCAL);
          }
          else {
              logger.info("MainConfig is not backed by a ConfigurableBeanFactory");
          } 
      }
  }
  </code></pre>
  
  <p>My thread executor class: in the class, I am creating a list of Runnable tasks (that call DAO method to execute queries) and calling below class method to execute them in parallel.</p>
  
  <pre><code>public class ThreadUtils {
      protected static final Logger logger = LoggerFactory
              .getLogger(ThreadUtils.class);
  
      public static void executeInParallel(List&lt;Runnable&gt; runnableTasks) {
          ExecutorService executorService = new DelegatingSecurityContextExecutorService(
                  Executors.newFixedThreadPool(5),
                  SecurityContextHolder.getContext());
  
          CompletableFuture&lt;?&gt;[] futures = runnableTasks.stream()
                  .map(task -&gt; CompletableFuture.runAsync(task, executorService))
                  .toArray(CompletableFuture[]::new);
          CompletableFuture.allOf(futures).join();
          executorService.shutdown();
      }
  </code></pre>
  
  <p>My response is as:</p>
  
  <pre><code>"data":{},
  "meta": {
          "QueryProfiler": [
              {
                  "hostName": "xxx.xx.com",
                  "requestURL": "http://localhost:7010/abc/5",
                  "duration": null,
                  "time": null,
                  "dataSource": null,
                  "query": [
                      "SELECT * FROM table1",
                      "SELECT COUNT(1) FROM table2 WHERE abc = 1 AND def = 2"
                  ]
              }
          ]
      }
  </code></pre>