iBatis get executed sql

Question

Hi all.

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.

Answer 1

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?

Answer 2

You can use p6spy or jdbcdslog for that.

Answer 3

Check this Confluence FAQ on i-Batis

How do I get SqlMapClient to log SQL statements

A common question is "Is it possible to show the ACTUAL query being executed?".

The simple answer is NO.

Why? Because the only information we have available is the PreparedStatement and the parameters.

Quite often, in the process of applying those parameters to the mapped statement it will become very clear where issues lie in the execution of the query

Answer 4

It's posible to show this information. You've to put the next lines in the log4j properties file for example:

# iBatis/SLQ

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.SQL Map.engine.impl.SQL MapClientDelegate=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

iBatis uses the Log4J framework.