Question about "Statistics sql" when using JPA

[Richard Walker]

Hi. I'm just starting to use JavaMelody with a JPA application.
(I'm using Java 7, JPA, Hibernate as the JPA provider, C3P0 pooling, MySQL, Tomcat.)

The existing database configuration is specified thus:
javax.persistence.jdbc.url=jdbc:mysql://...
javax.persistence.jdbc.user=...
javax.persistence.jdbc.driver=com.mysql.jdbc.Driver
javax.persistence.jdbc.password=...
hibernate.c3p0.testConnectionOnCheckout=true
hibernate.connection.isolation=2

I configured JPA monitoring as per the instructions, by adding
the "provider" element to the "persistence-unit" element in persistence.xml:

<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="..." transaction-type="RESOURCE_LOCAL">
        <provider>net.bull.javamelody.JpaPersistence</provider>
        <class>...</class>
    </persistence-unit>
</persistence>

There is no _other_ database configuration (e.g., JNDI, Spring, Hibernate).

The JPA statistics _do_ appear correctly, in the "Statistics jpa" section.
For example, the different requests are listed, with the number of hits, etc.

My question: There is also a "Statistics sql" section displayed, but
it is empty (it says "None"). _Should_ it be empty?

Richard.

[evernat]

Hi Richard,

JPA monitoring and SQL monitoring are independent. So configuring JPA monitoring is not enough to have SQL monitoring.
Given you jdbc configuration without a datasource, it's normal that you don't have SQL monitoring.

If you want SQL monitoring, you should read this chapter in the user's guide:
https://github.com/javamelody/javamelody/wiki/UserGuide#7-jdbc
In your case, probably the part about:

<property name="hibernate.connection.driver_class">net.bull.javamelody.JdbcDriver</property>
<property name="hibernate.connection.driver">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/myschema</property>
<property name="hibernate.connection.username">myuser</property>
<property name="hibernate.connection.password">mypassword</property>

(I don't see a way to give properties to the jdbc driver with "javax.persistence" properties, so I suggest to use "hibernate.connection" properties as above.)

bye,
Emeric

[Richard Walker]

On Tuesday, October 20, 2015 at 10:50:39 PM UTC+11, evernat wrote:

(I don't see a way to give properties to the jdbc driver with "javax.persistence" properties, so I suggest to use "hibernate.connection" properties as above.

I now have this working, thank you.

I had already tried adding the hibernate.connection.driver_class property ... without success.

It seems it is necessary both to _add_ some of the hibernate.connection.*
properties and to _remove_ some of the javax.persistence.* properties.

Certainly, it works by removing _all_ of the javax.persistence.* properties and replacing
them with the hibernate.connection.* properties.

Then I did some more experiments to see if it was necessary to replace _all_
of the javax.persistence.* properties.

In case anyone is interested, this seems to be the _minimum_ you have to do:
(a) Remove the setting javax.persistence.jdbc.driver=com.mysql.jdbc.Driver
(b) Add the two settings:
hibernate.connection.driver_class=net.bull.javamelody.JdbcDriver
hibernate.connection.driver=com.mysql.jdbc.Driver

So, it is possible to keep the javax.persistence.jdbc.url, javax.persistence.jdbc.user,
and javax.persistence.jdbc.password settings as they are.

Richard.