My server uses Hibernate in order to connect to a MySQL db. I'm also using c3p0 for connection pooling. I want to execute a specific sql query once on each connection when it is created, but since I'm using ComboPooledDataSource, there is no way to detect when a new session is created.
I found out that I can run my query by enabling test connection on checkout and set the query that I want to run as preferred test query (but it can run more than once).
String query = "my query";
comboPooledDataSource.setTestConnectionOnCheckout(true);
comboPooledDataSource.setPreferredTestQuery(query);
Is there a more elegant way to accomplish this?
* Edit *
I'm trying to make my db accept utf8mb4 characters. The acceptable way to do it is by applying the following to the my.cnf file:
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
The problem is that I don't have access to anything but my db, so my solution is to execute SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; for each connection.
See c3p0's ConnectionCustomizer interface. Override the onAcquire(...) method of AbstractConnectionCustomizer. onAcquire(...) is called just once for each Connection, after it has been acquired from the database, before it is made available for check-out by clients.
Or, if you want you can just use c3p0's built-in example for just this sort of use case, com.mchange.v2.c3p0.example.InitSqlConnectionCustomizer
In c3p0.properties:
c3p0.connectionCustomizerClassName=com.mchange.v2.c3p0.example.InitSqlConnectionCustomizer
c3p0.extensions.initSql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci