How to print the @SqlQuery annotation in JDBI sql

Question

I want to know what exactly sql query is processed by jdbi sql api for debugging purposes. My interface class is following

public inteface myinteface{
    @SqlQuery("select :c1 from tablename where cond = :cd")
    String returnMeValue(@Bind("c1") String c1, @Bind("cd") Integer cd);
}

and later called in another class as String result = myinterfaceclassobject.returnMeValue("Name",1);

I am not getting expected answer so I want to see what actually going to the sql query. So is there any method to get the final processed query?

Answer 1

You can log the sql by writing SqlCustomizer.

import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizer;
import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizerFactory;
import org.skife.jdbi.v2.sqlobject.SqlStatementCustomizingAnnotation;
import org.skife.jdbi.v2.tweak.StatementCustomizer;

import java.lang.annotation.*;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@SqlStatementCustomizingAnnotation(LogSqlFactory.Factory.class)
public @interface LogSqlFactory {

    static class Factory implements SqlStatementCustomizerFactory {

        @Override
        public SqlStatementCustomizer createForMethod(Annotation annotation, Class sqlObjectType, Method method) {
            return null;
        }

        @Override
        public SqlStatementCustomizer createForType(Annotation annotation, Class sqlObjectType) {
            return q -> q.addStatementCustomizer(new StatementCustomizer() {
                @Override
                public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException {
                    System.out.println(stmt.toString());
                }

                @Override
                public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException { }

                @Override
                public void cleanup(StatementContext ctx) throws SQLException { }
            });
        }

        @Override
        public SqlStatementCustomizer createForParameter(Annotation annotation, Class sqlObjectType, Method method, Object arg) {
            return null;
        }
    }

}

Just include this annotation and use this in SqlObject. In your case use this annotation like this,

@LogSqlFactory 
public inteface myinteface{
@SqlQuery("select :c1 from tablename where cond = :cd")
    String returnMeValue(@Bind("c1") String c1, @Bind("cd") Integer cd);
}

If you use custom loggers for logging, then beforeExecution method.

Answer 2

It's much easier to use something like log4jdbc, using Manikandan's method also slows down your code quite a bit.

However if you would still like to use it and your project language level doesn't support lambdas, you can use the following modification:

  @Override
  public SqlStatementCustomizer createForType(Annotation annotation, final Class sqlObjectType) {

     return new SqlStatementCustomizer() {
        @Override
        public void apply(SQLStatement sqlStatement) throws SQLException {
           sqlStatement.addStatementCustomizer(new StatementCustomizer() {
              @Override
              public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException {
                 System.out.println(stmt.toString());
              }

              @Override
              public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException {
              }

              @Override
              public void cleanup(StatementContext ctx) throws SQLException {
              }
           });
        }
     };

  }

However stmt.toString() is not guaranteed to return the SQL statement, it depends on the implementation. This will not work for SQLite.