Skip to content

How to Use DB Connection Pool

Jump to bottom
Zemian Deng edited this page Apr 2, 2019 · 5 revisions

When configure Quart to use JDBC Store, you may want to use a DB Connection Pool library to reuse connections to DB. Quartz comes with c3p0 connection pool as default. You can change to other pool provider and add their dependency as well.

Using Default c3p0 DB Conn Pool

Example:

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.dataSource = quartzDataSource
org.quartz.dataSource.quartzDataSource.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.quartzDataSource.URL = jdbc:mysql://localhost:3306/quartz
org.quartz.dataSource.quartzDataSource.user = quartz
org.quartz.dataSource.quartzDataSource.password = quartz123

# Enable C3P0 DB conn Pool (It's default anyway)
org.quartz.dataSource.quartzDataSource.provider = c3p0

# Custom c3p0 config properties. You can add more based on their docs:
# https://www.mchange.com/projects/c3p0/#configuration_properties
org.quartz.dataSource.quartzDataSource.maxPoolSize = 10
org.quartz.dataSource.quartzDataSource.preferredTestQuery = SELECT 1
org.quartz.dataSource.quartzDataSource.maxIdleTime = 0
org.quartz.dataSource.quartzDataSource.idleConnectionTestPeriod = 50
org.quartz.dataSource.quartzDataSource.testConnectionOnCheckin = false

Using HarikiCP DB Conn Pool

Example:

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.dataSource = quartzDataSource
org.quartz.dataSource.quartzDataSource.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.quartzDataSource.URL = jdbc:mysql://localhost:3306/quartz
org.quartz.dataSource.quartzDataSource.user = quartz
org.quartz.dataSource.quartzDataSource.password = quartz123

# Enable HikariCP DB conn Pool
org.quartz.dataSource.quartzDataSource.provider = hikaricp

# Custom hikaricp config properties. You can add more based on their docs:
# https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
org.quartz.dataSource.quartzDataSource.maximumPoolSize = 10
org.quartz.dataSource.quartzDataSource.connectionTestQuery = SELECT 1
org.quartz.dataSource.quartzDataSource.validationTimeout = 5000
org.quartz.dataSource.quartzDataSource.idleTimeout = 0

The For Quartz 2.3.1 or older already comes with HirakiCP dependency, so you don’t need to do anything. But if you want different HirakiCP version, then you need to exclude it first, like this:

        <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz</artifactId>
            <version>2.3.1</version>
            <exclusions>
                <exclusion>
                    <groupId>com.zaxxer</groupId>
                    <artifactId>HikariCP-java7</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
          <groupId>com.zaxxer</groupId>
          <artifactId>HikariCP</artifactId>
          <version>3.3.1</version>
        </dependency>

Using Custom DB Conn Pool

You can write your own DB Conn Pool class that implements org.quartz.utils.ConnectionProvider, and then let Quartz uses that.

Example:

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.dataSource = quartzDataSource
org.quartz.dataSource.quartzDataSource.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.quartzDataSource.URL = jdbc:mysql://localhost:3306/quartz
org.quartz.dataSource.quartzDataSource.user = quartz
org.quartz.dataSource.quartzDataSource.password = quartz123

# Enable Custom DB conn Pool
org.quartz.dataSource.quartzDataSource.connectionProvider.class = com.example.MyConnectionProvider
org.quartz.dataSource.quartzDataSource.propertyValue1 = 123
org.quartz.dataSource.quartzDataSource.propertyValue2 = foo
org.quartz.dataSource.quartzDataSource.propertyValue3 = bar
Clone this wiki locally