DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Demystifying SPF Record Limitations
  • Does the OCP Exam Still Make Sense?
  • How Agile Works at Tesla [Video]
  • You’ve Got Mail… and It’s a SPAM!

Trending

  • Demystifying SPF Record Limitations
  • Does the OCP Exam Still Make Sense?
  • How Agile Works at Tesla [Video]
  • You’ve Got Mail… and It’s a SPAM!
  1. DZone
  2. Data Engineering
  3. Data
  4. Mule Quartz Connector: How to Use JDBC-JobStore Instead of RAMJobStore.

Mule Quartz Connector: How to Use JDBC-JobStore Instead of RAMJobStore.

Let's take a look at the Mule Quartz connector as well as explore how to use JDBC-JobStore instead of using RAMJobStore.

Rajiv Mishra user avatar by
Rajiv Mishra
·
Updated Sep. 05, 18 · Analysis
Like (3)
Save
Tweet
Share
16.26K Views

Join the DZone community and get the full member experience.

Join For Free

Drawbacks of RAMJobStore

Mule internally uses RAMJobstore, so when running in an environment with multiple workers or nodes, a Quartz scheduler, by default, runs on each node as it keeps all the data in RAM.

Secondly, when the application ends (or crashes), all of the scheduling information is lost — this means RAMJobStore cannot honor the setting of “non-volatility” on jobs and triggers.

JDBC JobStore

JDBC-JobStore is also aptly named — it keeps all of its data in a database via JDBC. Because of this, it is a bit more complicated to configure than RAMJobStore, and it also is not as fast. However, the performance drawback is not terribly bad, especially if you build the database tables with indexes on the primary keys. On a fairly modern set of machines with a decent LAN (between the scheduler and database), the time to retrieve and update a firing trigger will typically be less than 10 milliseconds.

JDBC-JobStore works with nearly any database, it has been used widely with Oracle, PostgreSQL, MySQL, MS SQLServer, HSQLDB, and DB2. To use JDB-JobStore, you must first create a set of database tables for Quartz to use. You can find table-creation SQL scripts in the “docs/dbTables” directory of the Quartz distribution. If there is not already a script for your database type, just look at one of the existing ones, and modify it in any way necessary for your DB. One thing to note is that in these scripts, all the tables start with the prefix “QRTZ_” (such as the tables “QRTZ_TRIGGERS”, and “QRTZ_JOB_DETAIL”). This prefix can actually be anything you’d like, as long as you inform JDBCJobStore what the prefix is (in your Quartz properties). Using different prefixes may be useful for creating multiple sets of tables, for multiple scheduler instances, within the same database.

Once you’ve got the tables created, you have one more major decision to make before configuring and firing up JDBCJobStore. You need to decide what type of transactions your application needs. If you don’t need to tie your scheduling commands (such as adding and removing triggers) to other transactions, then you can let Quartz manage the transaction by using JobStoreTX as your JobStore (this is the most common selection).

If you need Quartz to work along with other transactions (i.e. within a J2EE application server), then you should use JobStoreCMT - in which case Quartz will let the app server container manage the transactions.

The last piece of the puzzle is setting up a DataSource from which JDBC-JobStore can get connections to your database. DataSources are defined in your Quartz properties using one of a few different approaches. One approach is to have Quartz create and manage the DataSource itself - by providing all of the connection information for the database. Another approach is to have Quartz use a DataSource that is managed by an application server that Quartz is running inside of - by providing JDBC-JobStore the JNDI name of the DataSource. For details on the properties, consult the example config files in the “docs/config” folder.

To use JDBCJobStore (and assuming you’re using StdSchedulerFactory) you first need to set the JobStore class property of your Quartz configuration to be either org.quartz.impl.jdbcjobstore.JobStoreTX or org.quartz.impl.jdbcjobstore.JobStoreCMT depending on the selection you made based on the explanations in the above few paragraphs.

Configuring Quartz to Use JobStoreTx

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX

Next, you need to select a DriverDelegate for the JobStore to use. The DriverDelegate is responsible for doing any JDBC work that may be needed for your specific database. StdJDBCDelegate is a delegate that uses “vanilla” JDBC code (and SQL statements) to do its work. If there isn’t another delegate made specifically for your database, try using this delegate - we’ve only made database-specific delegates for databases that we’ve found problems using StdJDBCDelegate with (which seems to be most!). Other delegates can be found in the “org.quartz.impl.jdbcjobstore” package, or in its sub-packages. Other delegates include DB2v6Delegate (for DB2 version 6 and earlier), HSQLDBDelegate (for HSQLDB), MSSQLDelegate (for Microsoft SQLServer), PostgreSQLDelegate (for PostgreSQL), WeblogicDelegate (for using JDBC drivers made by Weblogic), OracleDelegate (for using Oracle), and others.

Once you’ve selected your delegate, set its class name as the delegate for JDBC-JobStore to use.

Configuring JDBC-JobStore to use a DriverDelegate

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

Next, you need to inform the JobStore what table prefix (discussed above) you are using.

Configuring JDBC-JobStore with the Table Prefix

org.quartz.jobStore.tablePrefix = QRTZ_

And finally, you need to set which DataSource should be used by the JobStore. The named DataSource must also be defined in your Quartz properties. In this case, we’re specifying that Quartz should use the DataSource name “myDS” (that is defined elsewhere in the configuration properties).

Configuring JDBC-JobStore with the name of the DataSource to use

org.quartz.jobStore.dataSource = myDS

How to Configure JDBC QUARTZ in Mule

Below Configuration needed if you are using MSSQL:

Create the Global QUARTZ Configuration

<quartz:connector name="quartzPWConnectorCluster" validateConnections="true" doc:name="quartzPWConnectorCluster">

             <quartz:factory-property key="org.quartz.scheduler.makeSchedulerThreadDaemon" value="true" />

             <quartz:factory-property key="org.quartz.jobStore.makeThreadsDaemons" value="true" />

             <quartz:factory-property key="org.quartz.scheduler.instanceName" value="QUARTZSchedulerInstance" />

             <quartz:factory-property key="org.quartz.scheduler.instanceId" value="AUTO" />

             <quartz:factory-property key="org.quartz.threadPool.class" value="org.quartz.simpl.SimpleThreadPool" />

             <quartz:factory-property key="org.quartz.threadPool.threadCount" value="1" />

             <quartz:factory-property key="org.quartz.threadPool.threadPriority" value="1" />

             <quartz:factory-property key="org.quartz.jobStore.misfireThreshold" value="6000" />

             <quartz:factory-property key="org.quartz.jobStore.class" value="org.quartz.impl.jdbcjobstore.JobStoreTX" />

             <quartz:factory-property key="org.quartz.jobStore.useProperties" value="false" />

             <quartz:factory-property key="org.quartz.jobStore.dataSource" value="quartzDataSource" />

             <quartz:factory-property key="org.quartz.jobStore.driverDelegateClass" value="org.quartz.impl.jdbcjobstore.MSSQLDelegate" />

             <quartz:factory-property key="org.quartz.jobStore.isClustered" value="true" />

             <quartz:factory-property key="org.quartz.jobStore.clusterCheckinInterval" value="20000" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.connectionProvider.class" value="QuartzConnectionProvider" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.URL" value="${URL}" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.user" value="${user}" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.password" value="${password}" />

             <quartz:factory-property key="org.quartz.dataSource.quartzDataSource.maxConnections" value="10" />

       </quartz:connector>

Then create the flow:

<flow name="quartzconnectorutilFlow">

             <quartz:inbound-endpoint jobName="invoke-service-job" repeatInterval="120000" connector-ref="quartzConnectorCluster" responseTimeout="10000" doc:name="Quartz">

                    <quartz:event-generator-job />

             </quartz:inbound-endpoint>

       <logger message="Application is running at Server: #[java.net.InetAddress.getLocalHost().getHostName()]" level="INFO" doc:name="Host Name" />

       </flow>

Datasource Class

public class QuartzConnectionProvider implements ConnectionProvider
{

/**
* Default maximum number of database connections in the pool.
*/
public static final int DEFAULT_DB_MAX_CONNECTIONS = 10;

private volatile boolean initialized;

private BasicDataSource datasource;
private String driver;
// preserve this case otherwise property setters don't work for quartz
private String URL;
private String user;
private String password;
private String maxConnections;
private String validationQuery;


/**
* Create the underlying DBCP BasicDataSource with the
* default supported properties.
*/
public void initialize()
{
if (URL == null)
{
try {
throw new SQLException(
"DBPool could not be created: DB URL cannot be null");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

if (driver == null)
{
try {
throw new SQLException(
"DBPool '" + URL + "' could not be created: " +
"DB driver class name cannot be null!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/*if (maxConnections < 0)
{
throw new SQLException(
"DBPool '" + URL + "' could not be created: " +
"Max connections must be greater than zero!");
}*/

datasource = new BasicDataSource();
datasource.setDriverClassName(driver);
datasource.setUrl(URL);
datasource.setUsername(user);
datasource.setPassword(password);
//datasource.setMaxActive(maxConnections);
if (validationQuery != null)
{
datasource.setValidationQuery(validationQuery);
}

this.initialized = true;
}

/**
* Get the DBCP BasicDataSource created during initialization.
* <p/>
* <p>
* This can be used to set additional data source properties in a
* subclass's constructor.
* </p>
*/
protected BasicDataSource getDataSource()
{
return datasource;
}

public synchronized Connection getConnection() throws SQLException
{
if (!initialized)
{
try
{
initialize();
}
catch (Exception e)
{
throw new RuntimeException(e);
}
}
return datasource.getConnection();
}

public void shutdown() throws SQLException
{
datasource.close();
}

public String getDriver()
{
return driver;
}

public void setDriver(String driver)
{
this.driver = driver;
}

public String getURL()
{
return URL;
}

public void setURL(String url)
{
this.URL = url;
}

public String getUser()
{
return user;
}

public void setUser(String user)
{
this.user = user;
}

public String getPassword()
{
return password;
}

public void setPassword(String password)
{
this.password = password;
}

public String getMaxConnections()
{
return maxConnections;
}

public void setMaxConnections(String maxConnections)
{
this.maxConnections = maxConnections;
}

public String getValidationQuery()
{
return validationQuery;
}

Thanks, and let me know your thoughts in the comments.

Quartz (scheduler) Database Datasource Connector (mathematics)

Opinions expressed by DZone contributors are their own.

Trending

  • Demystifying SPF Record Limitations
  • Does the OCP Exam Still Make Sense?
  • How Agile Works at Tesla [Video]
  • You’ve Got Mail… and It’s a SPAM!

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: