Profile SQL statements in Java / Spring

[rico]

Hello all,

I am searching for some solution how to monitor SQL select in our
application. I found nice article
http://www.gotoquiz.com/web-coding/programming/java-programming/profile-sql-statements-in-java-spring/
but I do not know how can I run it with myBatis.

I need to know, base on input data, how our statement are time
consuming and if it is more than X sec. I'd like to react on it (send
mail, cancel query, ....)

Could anyone help?

Or knows anybody another solution? Thx.

Our project Spring 3, Mybatis 3, Oralce

Radim

[Eduardo]

You can do this by at least two ways.

- Intercept calls to SqlSessionTemplate in the same way the sample is
intercepting calls to JdbcOperations.
- For finer information, create a mybatis plugin in your spring
application context and register it in mybatis
(SqlSessionFactoryBean#setPlugins()) (See page 17 on MyBatis manual)

On 14 jun, 15:49, rico <radim.hartm...@gmail.com> wrote:
> Hello all,
>
> I am searching for some solution how to monitor SQL select in our

> application. I found nice articlehttp://www.gotoquiz.com/web-coding/programming/java-programming/profi...

[rico]

Thx for your reply.

I replaced @Pointcut....with my new value
@Pointcut("execution(*
org.mybatis.spring.SqlSessionTemplate.*(String, ..))")

but I have empty Map of SQLs.

my MyBatis conf:

<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis-
configuration.xml" />
<property name="dataSource" ref="dataSource" />
</bean>

<bean id="...mapperA"
class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
<property name="mapperInterface" value="...mapperA" />
</bean>
<bean id="...mapperB"
class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
<property name="mapperInterface" value="...mapperB" />
</bean>

Is there any missing conf?

[Eduardo]

It should be
@Pointcut("execution(* org.mybatis.spring.SqlSession.*(String, ..))")

Then create a SqlSessionTemplate in spring (have a look at MyBatis-
Spring manual)

<bean id="sqlSession"
class="org.mybatis.spring.SqlSessionTemplate">

        <property name="sqlSessionFactory" ref="sqlSessionFactory" />

</bean>

Inject it:

<bean id="...mapperA"
class="org.mybatis.spring.mapper.MapperFactoryBean">

        <property name="sqlSessionTemplate" ref="sqlSession" />

        <property name="mapperInterface" value="...mapperA" />
</bean>

And you should get the calls to mapper statements, not to sql
sentences, although their are almost the same if they map one to one.

If you need sql sentences you should definitely go with a MyBatis
plugin. Have also a look at MyBatis manual, it will be easy to code.

[rico]

Hello Edurado,

I decided to implement MyBatis plugin.

@Intercepts({ @Signature(type = Executor.class, method = "update",
args = {MappedStatement.class, Object.class }) })
@Override
public Object intercept(Invocation invocation) throws Throwable {
long start = System.currentTimeMillis();
System.out.println("START intercept " + start);
Object proceed = invocation.proceed();
System.out.println("END intercept " + (System.currentTimeMillis() -
start));
return proceed;
}

It works fine for me only for update, when I change method = "query" I
have got

org.apache.ibatis.exceptions.PersistenceException:
### Error opening session. Cause:
org.apache.ibatis.plugin.PluginException: Could not find method on
interface org.apache.ibatis.executor.Executor named query. Cause:
java.lang.NoSuchMethodException:
org.apache.ibatis.executor.Executor.query(org.apache.ibatis.mapping.MappedStatement,
java.lang.Object)
### The error may exist in .....Mapper.xml
### Cause: org.apache.ibatis.plugin.PluginException: Could not find
method on interface org.apache.ibatis.executor.Executor named query.
Cause: java.lang.NoSuchMethodException:
org.apache.ibatis.executor.Executor.query(org.apache.ibatis.mapping.MappedStatement,
java.lang.Object)
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromConnection(DefaultSqlSessionFactory.java:
104)
at
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:
60)
at
org.mybatis.spring.SqlSessionUtils.getSqlSession(SqlSessionUtils.java:
134)
at org.mybatis.spring.SqlSessionTemplate
$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:333)
at $Proxy5.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:
189)
at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
85)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
65)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
at $Proxy6.selectByLogin(Unknown Source)


Mapper.xml

<select id="selectByLogin" parameterType="String"
resultMap="BaseResultMap">
select * from USERS_RIGHTS
where
login = #{value}
</select>

Thx for reply

[Eduardo]

That is because the signature of .query() is

List query(MappedStatement ms, Object parameter, RowBounds rowBounds,
ResultHandler resultHandler) throws SQLException;

[rico]

Great it is working now,

I have another question:

On my con output is:

.....WHERE t.month between ? and ? AND t.pharmacy_id = p.pharmacy_id
but from my MappedStatement I received
.....WHERE t.pharmacy_id = p.pharmacy_id

Have you any tip or explanation for this?

Thank you


<where>
<include refid="monthsFromToRestrictions" />
<include refid="mandatoryJoinToPharmacy" />
</where>


<sql id="monthsFromToRestrictions">
<if test="from != null">
and t.month
<choose>
<when test="from == to">
= #{from,jdbcType=DECIMAL}
</when>
<when test="from != to">
between #{from,jdbcType=DECIMAL} and #{to,jdbcType=DECIMAL}
</when>
</choose>
</if>
</sql>



<sql id="mandatoryJoinToPharmacy">
<trim prefix="AND" prefixOverrides="AND">
t.pharmacy_id = p.pharmacy_id
</trim>
</sql>

params:
{to=201106, from=201005}