In the last part of the tutorial I got the basic flow working. We are now getting to probably the longest part in the tutorial. This section will cover a lot of ground such as the database design, beans, dependency injection and connection pooling.
Lets cover the database first. I want to keep a record of all the artist, song and genres I have music for. So I am going to keep this design very simple you can expand on the ideas here and do your own thing later on. I also want to focus on the Camel part more than the database so I am going really going for barebones.
So lets look at all the primary(entity) information I want to store the list is as follows:
- Artist: Artist Name
- Song: Song Name, Artist Name(if available).
- Genre: Genre Name
I also want to keep a track of which artist's and their genres. This will require me to create some relationships in the database. Thus I will need the following relationship table as well.
- Artist Genre: Artist Name, Genre Name
Right the following is the scripts I used to create this database.
delimiter $$
CREATE SCHEMA `mycamelmusic` DEFAULT CHARACTER SET utf8$$
USE `mycamelmusic`$$
CREATE TABLE `artist`
(
`artistName` varchar(127) NOT NULL,
PRIMARY KEY (`artistName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `genre`
(
`genreName` varchar(127) NOT NULL,
PRIMARY KEY (`genreName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `genreartist`
(
`genreName` varchar(127) NOT NULL,
`artistName` varchar(127)NOT NULL,
PRIMARY KEY (`genreName`,`artistName`),
KEY `fk_genreartist_artist_artistName` (`artistName`),
CONSTRAINT `fk_genreartist_artist_artistName` FOREIGN KEY (`artistName`) REFERENCES `artist` (`artistName`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_genreartist_genre_genrename` FOREIGN KEY (`genreName`) REFERENCES `genre` (`genreName`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE TABLE `mycamelmusic`.`song`
(
`songName` VARCHAR(127) NOT NULL ,
`artistName` VARCHAR(127) NOT NULL ,
PRIMARY KEY (`songName`, `artistName`) ,
INDEX `fk_artist_artist_name` (`artistName` ASC) ,
CONSTRAINT `fk_artist_artist_name`
FOREIGN KEY (`artistName` )
REFERENCES `mycamelmusic`.`artist` (`artistName` )
ON DELETE CASCADE
ON UPDATE CASCADE)$$
delimiter ;
Note that I went for natural keys and not auto numbers. The reason is simple a primary key should not change values thus I can use the genre names such as classical, dance, rock and pop for key values as genre names never change. The same goes for artists. Bach will always be Bach, Depeche Mode will always be Depeche Mode. This eliminates the need to have auto numbers as primary keys and enforce uniqueness on the genre and artist names. Remember autonumbers/identities is but one technique in developing databases not the ONLY technique.
One last part of this is to create the stored procedures that the Camel route will use to insert data into the database. These are simple insert stored procedures for each table. Create these once the database scripts has been run.
USE `mycamelmusic`;
DROP procedure IF EXISTS `setArtist`;
DROP procedure IF EXISTS `setGenre`;
DROP procedure IF EXISTS `setGenreArtist`;
DROP procedure IF EXISTS `setSongArtist`;
DELIMITER $$
USE `mycamelmusic`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `setArtist`(artName VARCHAR(127))
BEGIN
INSERT INTO `mycamelmusic`.`artist`
(
`artistName`
)
VALUES
(
artName
)
ON DUPLICATE KEY UPDATE
`artistName`=artName;
END;$$
CREATE PROCEDURE `mycamelmusic`.`setGenre` (genName VARCHAR(127))
BEGIN
INSERT INTO `mycamelmusic`.`genre`
(
`genreName`
)
VALUES
(
genName
)
ON DUPLICATE KEY UPDATE
`genreName`=genName;
END;$$
CREATE PROCEDURE `mycamelmusic`.`setGenreArtist` (genName VARCHAR(127),artName VARCHAR(127))
BEGIN
INSERT INTO `mycamelmusic`.`genreartist`
(
`genreName`,
`artistName`
)
VALUES
(
genName,
artName
)
ON DUPLICATE KEY UPDATE
`genreName` =genName,
`artistName`=artName;
END;$$
CREATE PROCEDURE `mycamelmusic`.`setSongArtist` (sngName VARCHAR(127), artName VARCHAR(127))
BEGIN
INSERT INTO `mycamelmusic`.`song`
(
`songName`,
`artistName`
)
VALUES
(
sngName,
artName
)
ON DUPLICATE KEY UPDATE
`songName` =sngName,
`artistName`=artName;
END;$$
DELIMITER ;
Lets proceed to the next step which is declaring a connection pool to the database. For those of you are not familiar with database connection pooling it is a must have technique in your toolbox. Database connection pooling is one of the most efficient ways to ensure you database handles connections efficiently.
We first need to do is find a Java database pooling manager. If you google for this you will find several such as BoneCP, DBCP and BoneCP. For this example we will be using DBCP so lets add DBCP as a maven dependency. If you followed the tutorial thus far you will know how to do it. If not then read some of the previous tutorial parts. When you complete this you will have added the following code to your pom.xml file:
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
Right on to the next step which is actually declaring a connection pool in our Camel route. One of the great features of Apache Camel is being able to declare and configure a POJO as a bean in the XML. This means you don't have to write code to get the POJO functional in your route. The question is how will a connection pool manager be used to create a basic data-source where we can request connections from?
Well DBCP has a class we can use just for this named org.apache.commons.dbcp.BasicDataSource. So we have to declare a bean in our Camel route that will be of type org.apache.commons.dbcp.BasicDataSource. This bean can then be injected into any other bean. Sounds complicated? Not really.
To do this add the following to the Camel route:
<bean id="dataSourcePool" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://yourservernamehere:3306/mycamelmusic?noAccessToProcedureBodies=true"/>
<property name="username" value="someuser"/>
<property name="password" value="somepassword"/>
<property name="initialSize" value="4"/>
<property name="maxActive" value="16"/>
</bean>
This adds a bean with a name of dataSource to the camel route which is a DBCP basic data-source and handles the connection pooling. You will notice that the connection pool is looking for the com.mysql.jdbc.Driver class. As we will be using this to connect we need to add the com.mysql.jdbc.Driver as a maven dependency. At this point you should be able to handle this task without code from me. If you are struggling to get this dependency resolved here is a tip search for mysql-connector-java.
Now that the mysql driver dependency has been resolved you are finished in creating a connection pool. Can you believe that is all you need to do to create and configure a database connection pool in a Camel route? Simple right? Well if you think it was simple to create a connection pool wait till you see how it easy it is to use the connection pool. Since we are going to be doing the music file tag analysis and database operations in the same class we now need to create the class that will use the JAudioTagger library and the DBCP connection pooling class.
First create a class called tagAnalyser. This class will have a basic data-source property which we will set by using dependency injection in the Camel context. Since this bean will also be handling messages we will mark the function that handles the exchange with the @Handler annotation this clearly marks which function in the POJO/Bean will handle the exchange.One last thing I want to log some actions inside this POJO/Bean to keep track of what's happening. So we will use dependency injection to achieve this, the same way we did with the data-source. Sounds complicated and difficult to code right?
Wrong it is dead easy let me show you the code.
package com.tutorial.namphibiansoftware.camelmusic;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import org.apache.camel.Exchange;
import org.apache.camel.Handler;
import org.apache.camel.Headers;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class tagAnalyser {
//We will use dependency injection to inject the connection pool into this property
private BasicDataSource dataSource;
//Another example of using dependency injection to inject a logger into the class
private static final Log LOG = LogFactory.getLog(tagAnalyser.class);
public BasicDataSource getDataSource() {
return dataSource;
}
public void setDataSource(BasicDataSource dataSource) {
this.dataSource = dataSource;
}
@Handler
public void readTag(@Headers Map<String ,Object> header,Exchange exchange) throws Exception{
Connection conn = null;
CallableStatement cstmt=null;
try
{
conn= dataSource.getConnection();
LOG.info("We just got a connection from the connection pool we can use this to insert data for the file: "+(String)header.get("CamelFilePath"));
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
finally
{
try
{
if (cstmt!=null)
{
cstmt.close();
}
if (conn!=null)
{
conn.close();
}
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
}
}
}
You can see the two injection point in the bean they are the dataSource and the log fields of the tag Analyser class. Lets review quickly, we have a class called dataSourcePool which is of type basic data-source, we also have a class tagAnalyser that has a property of basic data-source. We will need to inject the dataSourcePool bean into the dataSource property of the tagAnalyser bean. What a mouth full.
Once again this very simple and all you need to do is first declare your tagAnalyser as a bean in the Camel context by placing the following XML snippet into the Camel context XML file.
<bean id="tagAnalyser" class="com.tutorial.namphibiansoftware.camelmusic.tagAnalyser">
<property name="dataSource" ref="dataSourcePool"/>
</bean>
You can see the dependency injection here clearly. The dataSourcePool is injected into the dataSource property of the tagAnalyser class. Pretty simple and straight forward. Once last thing to do before we wrap up this part of the tutorial. We need to make sure our newly created tagAnalyser class is used in the Camel route.
Open the camel context file and then proceed to modify the routeby adding a bean to the route. Connect the log component to the bean component. You should end up with a route definition in XML that looks like the following:
<camelContext trace="false" id="blueprintContext" xmlns="http://camel.apache.org/schema/blueprint">
<route shutdownRoute="Default" id="CamelMusicRoute" customId="true">
<from uri="file://c:/mymusic?noop=true&recursive=true&delay=3000&exclude=.*.(jpg|JPG|gif|GIF|doc|DOC|pdf|PDF|avi|AVI|Mpg|mpg|db|DB|ini|INI|txt|TXT|m3u|M3U|mpeg|MPEG)&charset=utf-16" id="camelMusicFileSource" customId="true">
<description>This endpoint will read the source folder for music files.</description>
</from>
<log loggingLevel="INFO" message="File name: ${file:absolute.path} was read at ${date:now:yyyy-MM-dd HH-mm-ss} and placed onto the route." id="LogSourceFileRead" customId="true">
<description>This log component logs the filename and the time the file endpoint read a source file.</description>
</log>
<bean ref="tagAnalyser" id="tagAnalyser" customId="true">
<description>This bean uses dependency injection to use a database connection pool and analyse and insert data around the music collection into the database for further analysis.</description>
</bean>
</route>
</camelContext>
In the visual designer you should see the following:
 |
| Tag Analyser Added |
Time to test all of this and see that our foundations are solid. You can run the camel route and watch the output you should see something like this logged:
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building My Camel Music Route 1.0.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] >>> camel-maven-plugin:2.12.0.redhat-610379:run (default-cli) @ camelmusic >>>
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ camelmusic ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 2 resources
[INFO]
[INFO] --- maven-compiler-plugin:2.5.1:compile (default-compile) @ camelmusic ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] --- maven-resources-plugin:2.6:testResources (default-testResources) @ camelmusic ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 0 resource
[INFO]
[INFO] --- maven-compiler-plugin:2.5.1:testCompile (default-testCompile) @ camelmusic ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] <<< camel-maven-plugin:2.12.0.redhat-610379:run (default-cli) @ camelmusic <<<
[INFO]
[INFO] --- camel-maven-plugin:2.12.0.redhat-610379:run (default-cli) @ camelmusic ---
[INFO] Using org.apache.camel.test.blueprint.Main to initiate a CamelContext
[INFO] Starting Camel ...
[mel.test.blueprint.Main.main()] MainSupport INFO Apache Camel 2.12.0.redhat-610379 starting
[mel.test.blueprint.Main.main()] Activator INFO Camel activator starting
[mel.test.blueprint.Main.main()] Activator INFO Camel activator started
[mel.test.blueprint.Main.main()] BlueprintExtender INFO No quiesce support is available, so blueprint components will not participate in quiesce operations
[ Blueprint Extender: 1] BlueprintContainerImpl INFO Bundle camelmusic is waiting for namespace handlers [http://camel.apache.org/schema/blueprint]
[ Blueprint Extender: 1] BlueprintCamelContext INFO Apache Camel 2.12.0.redhat-610379 (CamelContext: blueprintContext) is starting
[ Blueprint Extender: 1] ManagedManagementStrategy INFO JMX is enabled
[ Blueprint Extender: 1] BlueprintCamelContext INFO AllowUseOriginalMessage is enabled. If access to the original message is not needed, then its recommended to turn this option off as it may improve performance.
[ Blueprint Extender: 1] BlueprintCamelContext INFO StreamCaching is not in use. If using streams then its recommended to enable stream caching. See more details at http://camel.apache.org/stream-caching.html
[ Blueprint Extender: 1] FileEndpoint INFO Endpoint is configured with noop=true so forcing endpoint to be idempotent as well
[ Blueprint Extender: 1] FileEndpoint INFO Using default memory based idempotent repository with cache max size: 1000
[ Blueprint Extender: 1] BlueprintCamelContext INFO Route: CamelMusicRoute started and consuming from: Endpoint[file://c:/mymusic?charset=utf-16&delay=3000&exclude=.*.%28jpg%7CJPG%7Cgif%7CGIF%7Cdoc%7CDOC%7Cpdf%7CPDF%7Cavi%7CAVI%7CMpg%7Cmpg%7Cdb%7CDB%7Cini%7CINI%7Ctxt%7CTXT%7Cm3u%7CM3U%7Cmpeg%7CMPEG%29&noop=true&recursive=true]
[ Blueprint Extender: 1] BlueprintCamelContext INFO Total 1 routes, of which 1 is started.
[ Blueprint Extender: 1] BlueprintCamelContext INFO Apache Camel 2.12.0.redhat-610379 (CamelContext: blueprintContext) started in 0.401 seconds
[ thread #0 - file://c:/mymusic] CamelMusicRoute INFO File name: c:\mymusic\9 Lazy 9\9 Lazy 9 - Electric Lazyland.mp3 was read at 2014-10-28 19-50-09 and placed onto the route.
[ thread #0 - file://c:/mymusic] tagAnalyser INFO We just got a connection from the connection pool we can use this to insert data for the file: c:\mymusic\9 Lazy 9\9 Lazy 9 - Electric Lazyland.mp3
Ok time to wrap it up. This part was a bit heavy going with a lot of work to be done. However we have covered almost all of the brunt work and things will start to flow faster in the next few parts.
