Creating Apache Derby Custom Functions: Part 2

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 StringUtils.java. 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.

Categories: ColdFusion,Java

About The Author

My name is Dan Vega and I am a Software Developer based out of Cleveland OH. I love to play with new technologies and write about my experiences here. When I am not busy being a full time geek I love to lift heavy weights and hang out with friends and family. If you have any questions please don't hesitate to contact me.

Follow me on:
  • http://www.bennadel.com Ben Nadel

    This looks very cool and seems extremely powerful. This might seem like a silly question, but, to compile the Java into a JAR, do you need the Java SDK or anything? Or is simply having ColdFusion installed good enough?

    I always get confused as to what is required to actually run Java. For that matter, the difference between teh JVM and SDK also confuses me.

  • http://therealdanvega.com Dan Vega

    It is not a silly question at all because they all made me go crazy at one point or another.

    The JRE (Java Runtime Environment) is needed to run applications written in Java. You can compare this to creating a program in Flash. When you distribute a flash application, the client needs flash to run it just as your client will need the JRE to run your Java application.

    JDK/SDK (Java Software Development Kit) – Here is the list of what comes in the latest sdk http://java.sun.com/javase/6/webnotes/README.html#contents. In the bin directory is a program called javac which compiles java source code into class files.

    Eclipse – I had to look this up because I was not 100% sure. You don’t need the JDK if you are using eclipse because it has its own built in compiler. Basically if you just pull down Eclipse or your already using builder you should be good to go.

    If anyone wants to correct any of that or call me out please feel free, I am no Java expert, but I do play one on TV :)

  • http://itreminder.blogspot.com Marko Simic

    So, the reason why you were receiving "namespace" error was because you had to restart CF after copying jar in lib dir. Right? If so, then I must say that developing UDFs in Java for Derby is very "not production friendly" :)

    If you find a way how to load java udfs w/o restarting a server, please share it ;)
    I’ll try to find some Derby nerd who can know it :)

  • http://therealdanvega.com Dan Vega

    I just don’t know think you can load these at runtime. The other thing is you don’t need to always do this. In my case I am writing some all purpose libraries and just moving the jar into production.

  • http://www.bennadel.com Ben Nadel

    @Dan, thanks that clears things up in my mind a bit.

  • Matthew

    How do I make a default value for a derby function, or use optional arguments? Is it possible? For example I created a function called foo(a,b) but b is optional. What is the syntax for optional arguments?