Hibernate Connection Properties for Mysql

Configuring Hibernate Connection Properties for Mysql:

                        Hibernate connection properties will give a greater control over transactions and increase the performance.

hibernate.connection.autoReconnect – Will enable auto Reconnection to Mysql, but using property is not recommended by the community, as it decreases performance.

hibernate.connection.is-connection-validation-required – As the name says, it check the connection whether it is a valid or not before executing any transaction.

hibernate.dialect – Mysql supports three dialects, select the dialect as per the Table Engine you use.

                            I recommend InnoDB because it supports transactions & row-level locking.

                            Dialect for InnoDB is org.hibernate.dialect.MySQLInnoDBDialect

hibernate.connection.autocommit – will override the autocommit attribute of the db user.

(This property cannot be overridden for root/super user of mysql)

hibernate.connection.release_mode – Can be configured to release the connection as soon as a

statement gets executed or transaction commits or can be released automatically.

hibernate.connection.isolation – Configure the isolation level for the transaction.

                                                1 - TRANSACTION_READ_UNCOMMITTED

                                                2 - TRANSACTION_READ_COMMITTED

                                                3 - TRANSACTION_REPEATABLE_READ

                                                4 - TRANSACTION_SERIALIZABLE

hibernate.connection.pool_size – Specify pool size so that you can reuse the connection established.

                        If you are using this property, set the “thread_cache_size” to some value based on the “max_connections”. This will help us to cache the threads established for connecting the DB.

Using c3p0 Connection Manager will make sure the connection is active(active means ready for communicating with DB) all the time. It will not allow the connection to be destroyed, so we will not get
Connection refused: due to last packet sent 23,224,579 seconds ago……

add the following properties to the persistence.xml,

<property name="hibernate.connection.provider_class"

value="org.hibernate.connection.C3P0ConnectionProvider" />

            <property name="hibernate.c3p0.min_size" value="1" />

            <property name="hibernate.c3p0.max_size" value="20" />

            <property name="hibernate.c3p0.timeout" value="1800" />

            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true" />

            <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1" />

            <property name="hibernate.c3p0.idle_test_period" value="3600" />

Include hibernate.c3p0.jar & c3p0.2.6.0.jar in the classpath of your application.

Note: Do not use encrypted password in the persistence.xml when using c3p0 Connection Manager.

If your application is mounted on Web Server, include the WebServer’s Connection Manager.

For eg., JBoss has internal JBossTransactionManager for Managing the Transaction.

<property name="hibernate.transaction.manager_lookup_class"

 value="org.hibernate.transaction.JBossTransactionManagerLookup" />

 

For more info,

Related to Mysql InnoDB Transaction Model : http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html

Related to Transaction Management : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/transactions.html

Related to Hibernate connection properties : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html