In the 1st part of this series we took a look at using Apache Derby. In the scenario we went over I came to the conclusion that I would need to write some custom functions for Apache Derby to solve my problems. In part 2 of this short series I will walk you through creating your own custom functions.

In other database management systems you have the ability to create user defined functions. You have the same option in Derby only you have to write these functions in Java. If your not a Java expert, don't worry I am not either and I can promise you this is a piece of cake. If you take a look the create function docs you will get the following explanation.

The CREATE FUNCTION statement allows you to create Java functions, which you can then use in an expression.
Before we look at writing our functions we should look at how they work.
When you declare a function or procedure using CREATE FUNCTION/PROCEDURE, Derby does not verify whether a matching Java method exists. Instead, Derby looks for a matching method only when you invoke the function or procedure in a later SQL statement. At that time, Derby searches for a public, static method having the class and method name declared in the EXTERNAL NAME clause of the earlier CREATE FUNCTION/PROCEDURE statement. Furthermore, the Java types of the method's arguments and return value must match the SQL types declared in the CREATE FUNCTION/PROCEDURE statement.
If you take a look at the argument matching docs you will get an idea of how Derby maps specific SQL types to Java data types. Now that you have an idea of how it works, it is time to write some code.

In your favorite editor start a new Java project. If you are using ColdFusion Builder like me you can switch over to the Java perspective and create a new Java project. In your new project create a new class. In the package field use something like "org.vega.derby.functions" and the name of our class is going to be StringUtils. I also check the fina l modifier because we don't want this class to be extended. We should now have a base class that looks something like this. If you remember from the docs earlier Derby is going to look for a static method that matches the name of the function you create. In our case we are creating a replace function, so we should create a replace method. Even if you don't know Java you should understand what is going on here. We are taking a string, looking for string and replacing it with one.

Right now all you have is a Java source file. To actually use this you need to compile it into a class. Another thing to consider is that we may have many of these classes in the future. Thinking about the future I am going to create a jar file. Luckily Eclipse has a great little wizard for creating a jar file. If you right click on your project and click export you should get the following screen.

Select Jar and the click next. Now you are going to select the resources you want to export. Only select the Java src files that we want to include, in our case its the Make sure you pay attention to where I am exporting this. It needs to go in {cfusion}/lib, more on this later. After this step click next, finish and your done.

Now we have our class files ready to go we need to create the function in derby. You can read more about this in the docs but basically the sql looks like this. Remember to use the full path to your class name, this includes the package. If you just use StringUtils it will never know how to find it. Now you should be good to go right? Not so fast. As a quick little test I will write some SQL. If you try and run this code you are going to get an error along the lines of "Can not find the class org.vega.derby.functions.StringUtils". This could be either a miss match on the name you created the function with or the actual package/class. More likely though its because we have not restarted ColdFusion. Now remember earlier I talked about where to store this jar. You could store this jar anywhere on the system if you wanted to, that path just needs to be added to the class path. There is an easy way to add a directory right in the ColdFusion administrator if you need it. I like it in the lib folder because I don't have to add anything to the class path. Even if you drop it in a sub folder your going to have to add that path to the class path. Any changes to the class path are going to require a restart so go do that now.

In part 3 I will share all of this code along with a jar that you can use without having to do all of this. I think that this is also important time to kill some dreams. I thought that I a may be able to load this jar at runtime using the awesome open source project Java Loader. I tried it and it did not work. This is probably because ColdFusion loads Derby when it starts up. I will let someone smarter explain this but basically what happens is because Derby is already loaded it will never see the path to your jar file. Stay tuned, should have the code, and a ton of other functions I wrote up shortly.