How to configure Proxool with Hibernate/JPA

In this Java tutorial, we will guide you how to use the database connection pooling library Proxool for your existing Hibernate/JPA projects. Hibernate provides built-in support for Proxool via its ProxoolConnectionProvider class, and it requires just some small XML code to make it works with Proxool.

1. Get Proxool JAR files

If you project uses Maven, add the following dependency declaration to the pom.xml file:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-proxool</artifactId>
    <version>5.4.0.Final</version>
</dependency>

It will download 2 JAR files proxool-0.9.1.jar and proxool-cglib.jar and add them to the project’s dependencies.

In case you don’t use Maven or any build system, go to Proxool Download Page to download the proxool-0.9.1.zip file. Extract it, go to the lib directory, and put the two mentioned JAR files to your project.

2. Configure Proxool with Hibernate (hibernate.cfg.xml)

Normally, the Hibernate configuration file looks like this - with a section that specifies database connection information:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>       
  <session-factory>
    
    <!-- Database connection settings -->
    
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost:3306/dbname</property>
    <property name="connection.username">username</property>
    <property name="connection.password">password</property>
    <property name="show_sql">true</property>
       
    // other configurations
    
    
  </session-factory>
</hibernate-configuration>

Proxool requires its own configuration file, so create the proxool.xml file in the same directory as the hibernate.cfg.xml file with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<proxool-config>
    <proxool>
        <alias>pool</alias>
        
        <driver-url>jdbc:mysql://localhost:3306/dbname</driver-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <driver-properties>
            <property name="user" value="username"></property>
            <property name="password" value="password"></property>
        </driver-properties>
        
        <minimum-connection-count>10</minimum-connection-count>
        <maximum-connection-count>50</maximum-connection-count>
        
    </proxool>
 </proxool-config>

As you can see, Proxool requires the following information:

- Alias name for the pool

- Database connection information such as database URL, JDBC driver, username, and password.

- Configuration properties for the pool. There are 2 fundamental properties you need to specify:

                + minimum-connection-count: the minimum number of connections that are kept open.

                + maximum-connection-count: the maximum number of connections to the database.

You can see the full list of Proxool properties here.

Therefore, in the Hibernate configuration file, you should remove the section about database connection information, and specify the alias name and XML file name for Proxool like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>       
  <session-factory>
         
	<property name="hibernate.proxool.pool_alias">pool</property>
	<property name="hibernate.proxool.xml">proxool.xml</property>
			       
    	// other configurations...
    
  </session-factory>
</hibernate-configuration>

Note that the proxool.xml file must be in the same directory as the hibernate.cfg.xml file like this:

Then you’re all set of configuring Proxool with Hibernate in your project.

3. Configure Proxool with JPA (persistence.xml)

If you use JPA with Hibernate implementation, the database connection information is specified in the persistence.xml file like this:

<?xml version="1.0" encoding="UTF-8"?>
<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="BookstoreWebsite">
		<properties>
			<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/dbname" />
			<property name="javax.persistence.jdbc.user" value="username" />
			<property name="javax.persistence.jdbc.password" value="password" />
			
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.format_sql" value="true" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />

			// other properties			
		</properties>
	</persistence-unit>

</persistence>

To configure Proxool with JPA, create the proxool.xml file as described in the previous section - but it should be placed in the root of the source folder, not in the same location as the persistence.xml file:

The modify the persistence.xml file as follows:

<?xml version="1.0" encoding="UTF-8"?>
<persistence 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"
	version="2.1">
	
	<persistence-unit name="BookStoreWebsite">
		<properties>
					
			<property name="hibernate.proxool.pool_alias" value="pool" />
			<property name="hibernate.proxool.xml" value="proxool.xml" />
			
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.format_sql" value="true" />			

			// other properties
			
		</properties>
	</persistence-unit>
	
</persistence>

As you can see, you also need to specify the pool alias name and the XML configuration file for Proxool.

4. Check if Proxool works

Start your application, and you should see Hibernate uses ProxoolConnectionProvider in the log:

And use a database server management program like MySQL Workbench to see the list of client connections made by your application:

Here, you can see there are some connection threads that are sleeping, waiting for execution. The number of connections here should equal to the minimum-connection-count property you specify in the Proxool configuration file.

References:

• Proxool Home Page
• Proxool Properties
• Hibernate Connection Pooling

Other Hibernate Tutorials:

• How to use c3p0 database connection pooling library with Hibernate/JPA
• Java Hibernate JPA Annotations Tutorial for Beginners
• Hibernate Hello World Tutorial for Beginners with Eclipse and MySQL
• Hibernate One-to-One Association on Primary Key Annotations Example
• Hibernate One-to-Many Using Join Table XML Mapping Example
• Hibernate Many-to-Many Association with Extra Columns in Join Table Example
• Hibernate Enum Type Mapping Example
• Hibernate Binary Data and BLOB Mapping Example
• Hibernate Basics - 3 ways to delete an entity from the datastore
• Hibernate Query Language (HQL) Example
• Java Hibernate Reverse Engineering Tutorial with Eclipse and MySQL

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.