Postgres PL/JAVA: java.lang.ClassNotFoundException error after loading JAR file in database

Question

I am getting the java.lang.ClassNotFoundException: error inside Postgres when running a function that calls a JAR file I have loaded. I have installed and configured PL/JAVA (including the delivered examples) in my database and can run the examples to success. I am not attempting to load/install my first JAR, but I am doing something wrong.

My host controls the OS version: CentOS 6.8. Postgres is version 8.4.

I am attempting to install my own very simple java class, which is a derivative of the delivered example Parameters.addOne class. All my code is in /tmp. Here are the steps I've followed:

Doug.java:

    package com.msmetric;
    import java.math.BigDecimal;
    import java.sql.Date;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Time;
    import java.sql.Timestamp;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.TimeZone;
    import java.util.logging.Logger;

    public class Doug {
      public static int addOne(int value) {
        return value + 1;
       }
    }

2. Compile Doug.java using 'javac Doug.java' succeeds.

3. Create JAR file with Doug.class file in it using 'jar -cvf Doug.jar Doug.class. This works fine.

Now I load the JAR file into Postgres (public schema), change the classpath, create the function that calls the JAR, then attempt to run at psql prompt.

1. Run sqlj.install_jar from psql:

   select sqlj.install_jar('file:/tmp/Doug.jar','Doug',false);
   

2. Set the classpath inside Postgres (from psql prompt postgres=#):

   select sqlj.set_classpath('public','Doug');
   

3. Create the function that calls the JAR. This create function code is taken directly from the examples.ddr file that came with PL/JAVA. I simply changed org.postgres to com.msmetric.

   create or replace function addone(int) returns int as 'com.msmetric.Doug.addOne(java.lang.Integer)' language java;
   

Now with the JAR loaded and function created, I attempt to run it. This function should simply add 1 to the number provided.

    select addone(3);

Results: ERROR: java.lang.ClassNotFoundException: com.msmetric.Doug

Thoughts?

Answer 1

I'm very sorry I didn't see your question sooner. Underneath all the exotic details (PostgreSQL, PL/Java, schemas, classpaths...), there's just a bit of basic Java going on here: if a jar file contains a class Doug.class in package com.msmetric, its path within the jar has to reflect that: it has to be com/msmetric/Doug.class. Otherwise, it won't be found.

You can set up that whole structure step by step:

javac Doug.java mkdir com mkdir com/msmetric mv Doug.class com/msmetric/ jar -cvf Doug.jar com/msmetric/Doug.class

Or, you can let javac do more of the work for you:

mkdir classes javac -d classes Doug.java jar -cvf Doug.jar -C classes .

When you give javac a -ddirectory option, instead of just writing class files next to their .java sources, it will put them all in their proper places under the directory you named, and then you can just tell jar to change into that directory and slurp them all up (don't overlook the . at the end of that jar command).

Once you fix that, if you retry your original steps, you'll see that you now get a different error:

ERROR:  Unable to find static method com.msmetric.Doug.addOne with signature (Ljava/lang/Integer;)I

That happens because you declared the function in Doug.java with int addOne(int value) (that is, taking a primitive int argument), but you declared it in SQL with returns int as 'com.msmetric.Doug.addOne(java.lang.Integer)' taking an Integer object.

Once you correct that:

create or replace function addone(int) returns int as 'com.msmetric.Doug.addOne(int)' language java;

you'll be able to see:

# select addone(3);
 addone 
--------
      4
(1 row)

If you happen to see this belated answer, may I ask what version of PL/Java you are using? That's one detail you didn't mention. If it is older than 1.5.0, there are newer features that can help you out. For one, you can just annotate that function:

@Function
public static int addOne(int value) {
  return value + 1;
}

and have javac spit out not only the Doug.class file but also a pljava.ddr file with your SQL function declaration already written correctly (no mixing up argument types!). There is a way to include that .ddr file into the jar you create so that you can just call sqlj.install_jar with the last parameter true so it runs the commands in the .ddr and your functions are ready to use. There's a Hello, world example in the docs that shows more of how it's done.

Cheers,
-Chap