从mybatis mapper获取sql查询的字符串表示

7uhlpewt  于 2021-09-13  发布在  Java
关注(0)|答案(1)|浏览(466)

我对mybatis和执行的sql查询有疑问。是否有可能从表示为字符串的Map器方法中执行sql?
有一个searchrepository在mapper中调用searchtmp方法,mapper从mybatis xml文件中调用并执行searchtmp方法。
在理想情况下,我希望将searchtmp方法的void返回类型更改为字符串,并强制mybatis将执行的sql作为字符串返回。我有可能做到吗?

public class SearchRepository {

    private final SearchTmpMapper mapper;

    public void searchTmp() {
        SearchFilter filter = intializeFilter();
        mapper.searchTmp(filter);
}

public interface SearchTmpMapper {

    void searchTmp(
            @Param("filter") SearchFilter filter);
}

<select id="searchTmp">
        /* some SQL select */
</select>
klr1opcd

klr1opcd1#

您可以使用SpringAOP来实现,但需要向应用程序添加一个额外的类,该类将实现新的“方面”。见下文:
几年前,我实现了一个aop方面(我使用的是spring),它记录了我正在处理的hotrod orm的所有执行的sql语句及其响应时间。
下面的代码(开源)记录每个sql语句及其响应时间。it内部类(此处未显示)将其执行从重到轻进行排序;RESTAPI可以向应用程序的管理界面提供“最坏的违规者”。
下面的示例使用spring aop来执行此操作:

@Aspect
@Component
public class SQLMetricsAspect {

  private ThreadLocal<String> sql = new ThreadLocal<String>();

  @Autowired
  private SQLMetrics sqlMetrics;

  @Around(value = "execution(* javax.sql.DataSource.getConnection())")
  public Object measureGetConnection(final ProceedingJoinPoint joinPoint) throws Throwable {
    try {
      Object conn = joinPoint.proceed();
      AspectJProxyFactory proxyFactory = new AspectJProxyFactory(conn);
      proxyFactory.addAspect(this);
      Object proxyConn = proxyFactory.getProxy();
      return proxyConn;
    } catch (Throwable e) { throw e; }
  }

  @Around(value = "execution(* java.sql.Connection.prepareStatement(..)) && args(sql)")
  public Object measurePrepareStatement(final ProceedingJoinPoint joinPoint, final String sql) throws Throwable {
    try {
      this.sql.set(sql);
      Object ps = joinPoint.proceed();
      AspectJProxyFactory proxyFactory = new AspectJProxyFactory(ps);
      proxyFactory.addAspect(this);
      PreparedStatement proxyPS = proxyFactory.getProxy();
      return proxyPS;
    } catch (Throwable e) { throw e; }
  }

  // Then advice each SQL execution method, as in:

  @Around(value = "execution(* java.sql.PreparedStatement.execute(..))")
  public Object adviceExecute(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  // All other java.sql.PreparedStatement declared methods...

java 8中的其他方法包括:

@Around(value = "execution(* java.sql.PreparedStatement.executeLargeUpdate(..))")
  public Object adviceExecuteLargeUpdate(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  @Around(value = "execution(* java.sql.PreparedStatement.executeQuery(..))")
  public Object adviceExecExecuteQuery(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  @Around(value = "execution(* java.sql.PreparedStatement.executeUpdate(..))")
  public Object adviceExecuteUpdate(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  // java.sql.Statement declared methods

  @Around(value = "execution(* java.sql.Statement.execute(..)) && args(sql)")
  public Object adviceExecute(final ProceedingJoinPoint joinPoint, final String sql) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.execute(..)) && args(sql, autoGeneratedKeys)")
  public Object adviceExecute(final ProceedingJoinPoint joinPoint, final String sql, final int autoGeneratedKeys)
      throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.execute(..)) && args(sql, columnIndexes)")
  public Object adviceExecute(final ProceedingJoinPoint joinPoint, final String sql, final int[] columnIndexes)
      throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.execute(..)) && args(sql, columnNames)")
  public Object adviceExecute(final ProceedingJoinPoint joinPoint, final String sql, final String[] columnNames)
      throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  // TODO Check how batches work
  @Around(value = "execution(* java.sql.Statement.executeBatch(..))")
  public Object adviceExecuteBatch(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  // TODO Check how batches work
  @Around(value = "execution(* java.sql.Statement.executeLargeBatch(..))")
  public Object adviceExecuteLargeBatch(final ProceedingJoinPoint joinPoint) throws Throwable {
    return measureSQLExecution(joinPoint, this.sql.get());
  }

  @Around(value = "execution(* java.sql.Statement.executeLargeUpdate(..)) && args(sql)")
  public Object adviceExecuteLargeUpdate(final ProceedingJoinPoint joinPoint, final String sql) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeLargeUpdate(..)) && args(sql, autoGeneratedKeys)")
  public Object adviceExecuteLargeUpdateAK(final ProceedingJoinPoint joinPoint, final String sql,
      final int autoGeneratedKeys) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeLargeUpdate(..)) && args(sql, columnIndexes)")
  public Object adviceExecuteLargeUpdate(final ProceedingJoinPoint joinPoint, final String sql,
      final int[] columnIndexes) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeLargeUpdate(..)) && args(sql, columnNames)")
  public Object adviceExecuteLargeUpdate(final ProceedingJoinPoint joinPoint, final String sql,
      final String[] columnNames) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeQuery(..)) && args(sql)")
  public Object adviceExecuteQuery(final ProceedingJoinPoint joinPoint, final String sql) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeUpdate(..)) && args(sql)")
  public Object adviceExecuteUpdate(final ProceedingJoinPoint joinPoint, final String sql) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeUpdate(..)) && args(sql, autoGeneratedKeys)")
  public Object adviceExecuteUpdateAK(final ProceedingJoinPoint joinPoint, final String sql,
      final int autoGeneratedKeys) throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeUpdate(..)) && args(sql, columnIndexes)")
  public Object adviceExecuteUpdate(final ProceedingJoinPoint joinPoint, final String sql, final int[] columnIndexes)
      throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

  @Around(value = "execution(* java.sql.Statement.executeUpdate(..)) && args(sql, columnNames)")
  public Object adviceExecuteUpdate(final ProceedingJoinPoint joinPoint, final String sql, final String[] columnNames)
      throws Throwable {
    return measureSQLExecution(joinPoint, sql);
  }

最后,实现该方法来处理sql语句。在我的例子中,我记录了sql语句、它的执行时间和执行错误(如果有):

private Object measureSQLExecution(final ProceedingJoinPoint joinPoint,
      final String sql) throws Throwable {
    long start = System.currentTimeMillis();
    try {
      Object ps = joinPoint.proceed();
      long end = System.currentTimeMillis();
      this.sqlMetrics.record(sql, end - start, null);
      return ps;

    } catch (Throwable t) {
      long end = System.currentTimeMillis();
      this.sqlMetrics.record(sql, end - start, t);
      throw t;
    }
  }

相关问题