How to use DBCPService to connect to a MySQL database in Apache NiFi

To set up a DBCPService in NiFi you must first change your pom.xml to add the following dependencies (note that these versions are correct as of 4/12/2017 and may have been updated since today)

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
<dependency>
    <groupId>org.apache.nifi</groupId>
    <artifactId>nifi-dbcp-service-api</artifactId>
    <version>1.5.0-SNAPSHOT</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>
<dependency>
    <groupId>org.apache.nifi</groupId>
    <artifactId>nifi-standard-services-api-nar</artifactId>
    <type>nar</type>
</dependency>

Go to your desired processor and create the following property, this will create the dropdown box which allows you to edit and update the controller service.

public static final PropertyDescriptor MY_PROPERTY = new PropertyDescriptor
        .Builder().name("DBCPService test processor")
        .description("DBCPService test processor")
        .identifiesControllerService(DBCPService.class)
        .required(true)
        .build();

The code below shows how to turn the service into a connection and execute an update, note that the only difference here is the use of dbcpService.getConnection() instead of mySQLDataSource.getConnection() and therefore is easily added into existing code.

final DBCPService dbcpService =  context.getProperty(MY_PROPERTY).asControllerService(DBCPService.class);

Connection connection =  dbcpService.getConnection();

try {
    Statement statement = connection.createStatement();

    statement.executeUpdate("INSERT INTO `temp` (`number`) VALUES ('1')");
} catch (SQLException e) {
    e.printStackTrace();
}

Build the .nar file and you can create the controller service, although most of the properties are fairly standard, here are the two difficult ones.

JDBC URL

jdbc:mysql://localhost:3306/databasename?useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

JDBC Driver

com.mysql.jdbc.Driver