iBatis get executed sql

Is there any way where I can get the executed query of iBatis? I want to reuse the query for an UNION query.

For example:

<sqlMap namespace="userSQLMap">
   <select id="getUser" resultClass="UserPackage.User">
        SELECT username,
               password 
        FROM table 
        WHERE id=#value#
   </select>
</sqlMap>

And when I execute the query through

int id = 1
List<User> userList = queryDAO.executeForObjectList("userSQLMap.getUser",id)

I want to get SELECT username, password FROM table WHERE id=1

Is there any way I could get the query?

Thanks.

回答1:

It's posible to show this information.iBatis uses some the Logging framework including Log4J.
To use Log4J create file log4j.properties in the class path.You've to put the next lines in the file for example:

log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
    
log4j.logger.com.ibatis=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG

For other logging framework and detail info see this link

回答2:

Add this to your log4j.xml file and you can see the output on console.

<logger name="java.sql" additivity="false">
    <level value="debug" />
    <appender-ref ref="console" />
</logger>

You will see the parameters being passed, the query being executed and the output of query.

回答3:

Get the Configuration object from your SqlSessionFactory, then:

MappedStatement ms = configuration.getMappedStatement("MyMappedStatementId");
BoundSql boundSql = ms.getBoundSql(parameters); // pass in parameters for the SQL statement
System.out.println("SQL" + boundSql.getSql());

回答4:

    import java.util.Properties;
    import org.apache.ibatis.executor.Executor;
    import org.apache.ibatis.mapping.BoundSql;
    import org.apache.ibatis.mapping.MappedStatement;
    import org.apache.ibatis.mapping.MappedStatement.Builder;
    import org.apache.ibatis.mapping.SqlSource;
    import org.apache.ibatis.plugin.Interceptor;
    import org.apache.ibatis.plugin.Intercepts;
    import org.apache.ibatis.plugin.Invocation;
    import org.apache.ibatis.plugin.Plugin;
    import org.apache.ibatis.plugin.Signature;
    import org.apache.ibatis.session.ResultHandler;
    import org.apache.ibatis.session.RowBounds;

    import com.gm.common.orm.mybatis.dialect.Dialect;
    import com.gm.common.utils.PropertiesHelper;

    /**
     * 为Mybatis提供基于方言(Dialect)的分页查询的插件
     * 
     * 将拦截Executor.query()方法实现分页方言的插入.
     * 
     * 配置文件内容:
     * 
     * <pre>
     *  &lt;plugins>
     *  &lt;plugin interceptor="com.gm.common.orm.mybatis.plugin.OffsetLimitInterceptor">
     *      &lt;property name="dialectClass" value="com.gm.common.orm.mybatis.dialect.MySQLDialect"/>
     *  &lt;/plugin>
     * &lt;/plugins>
     * </pre>
     */

    @Intercepts({@Signature(type=Executor.class,method="query",args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})
    public class OffsetLimitInterceptor implements  Interceptor {
        static int MAPPED_STATEMENT_INDEX = 0;
        static int PARAMETER_INDEX = 1;
        static int ROWBOUNDS_INDEX = 2;
        static int RESULT_HANDLER_INDEX = 3;

        Dialect dialect;

        public Object intercept(Invocation invocation) throws Throwable {
            processIntercept(invocation.getArgs());
            return invocation.proceed();
        }

        void processIntercept(final Object[] queryArgs) {
            // queryArgs = query(MappedStatement ms, Object parameter, RowBounds
            // rowBounds, ResultHandler resultHandler)
            MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
            Object parameter = queryArgs[PARAMETER_INDEX];
            final RowBounds rowBounds = (RowBounds) queryArgs[ROWBOUNDS_INDEX];
            int offset = rowBounds.getOffset();
            int limit = rowBounds.getLimit();

            if (dialect.supportsLimit()
                    && (offset != RowBounds.NO_ROW_OFFSET || limit != RowBounds.NO_ROW_LIMIT)) {
                BoundSql boundSql = ms.getBoundSql(parameter);
                String sql = boundSql.getSql().trim();
                if (dialect.supportsLimitOffset()) {
                    sql = dialect.getLimitString(sql, offset, limit);
                    offset = RowBounds.NO_ROW_OFFSET;
                } else {
                    sql = dialect.getLimitString(sql, 0, limit);
                }
                limit = RowBounds.NO_ROW_LIMIT;

                queryArgs[ROWBOUNDS_INDEX] = new RowBounds(offset, limit);
                BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),
                        sql, boundSql.getParameterMappings(), boundSql
                                .getParameterObject());
                MappedStatement newMs = copyFromMappedStatement(ms,
                        new BoundSqlSqlSource(newBoundSql));
                queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
            }
        }

        // see: MapperBuilderAssistant
        private MappedStatement copyFromMappedStatement(MappedStatement ms,
                SqlSource newSqlSource) {
            Builder builder = new MappedStatement.Builder(ms
                    .getConfiguration(), ms.getId(), newSqlSource, ms
                    .getSqlCommandType());

            builder.resource(ms.getResource());
            builder.fetchSize(ms.getFetchSize());
            builder.statementType(ms.getStatementType());
            builder.keyGenerator(ms.getKeyGenerator());
            builder.keyProperty(ms.getKeyProperty());

            // setStatementTimeout()
            builder.timeout(ms.getTimeout());

            // setStatementResultMap()
            builder.parameterMap(ms.getParameterMap());

            // setStatementResultMap()
            builder.resultMaps(ms.getResultMaps());
            builder.resultSetType(ms.getResultSetType());

            // setStatementCache()
            builder.cache(ms.getCache());
            builder.flushCacheRequired(ms.isFlushCacheRequired());
            builder.useCache(ms.isUseCache());

            return builder.build();
        }

        public Object plugin(Object target) {
            return Plugin.wrap(target, this );
        }

        public void setProperties(Properties properties) {
            String dialectClass = new PropertiesHelper(properties)
                    .getRequiredString("dialectClass");
            try {
                dialect = (Dialect) Class.forName(dialectClass)
                        .newInstance();
            } catch (Exception e) {
                throw new RuntimeException(
                        "cannot create dialect instance by dialectClass:"
                                + dialectClass, e);
            }
            System.out.println(OffsetLimitInterceptor.class.getSimpleName()
                    + ".dialect=" + dialectClass);
        }

        public static class BoundSqlSqlSource implements  SqlSource {
            BoundSql boundSql;

            public BoundSqlSqlSource(BoundSql boundSql) {
                this .boundSql = boundSql;
            }

            public BoundSql getBoundSql(Object parameterObject) {
                return boundSql;
            }
        }

    }

My reference : https://www.java2s.com/Open-Source/Java-Document-2/UnTagged/gmc/com/gm/common/orm/mybatis/plugin/OffsetLimitInterceptor.java.htm

回答5:

Most SQL engines allow you to "log" all the queries executed (typically together with information about the time the query took, the number of results it returned, and the like). Do you have access to your engine's logs, and can you configure it so it will log all you need?

回答6:

You can use p6spy or jdbcdslog for that.