JDBC Usage with NsJava

Postgresql

Using JDBC with nsjava and postgresql is pretty straightforward, with the installation for nsjava having a one-to-one correspondence with the postgresql installation directions. First compile postgresql using the --with-java option. This requires that you have ant installed and configured, and JAVA_HOME should point to your jdk. See the postgresql docs for more details. Once you have postgresql compiled copy the postgresql.jar file from src/interfaces/jdbc/jars to your aolserver bin directory. Next modify the nsjava ClassPath variable to include the postgresql.jar file. There are two methods for loading the driver. If you don't anticipate switching db's, you can hard-code the postgresql driver in your application code that uses jdbc as follows: 

	try {
	  Class.forName("org.postgresql.Driver");
	} catch(Exception e) {
	  // your error handling code goes here
	}

If you would like to have the jdbc driver configurable, then add the following JavaOption into your nsd.tcl file instead: 

ns_section "ns/server/${server}/module/nsjava"
        ns_param   JavaOption "-Djdbc.drivers=org.postgresql.Driver"
        ns_param   ClassPath  "/home/nsadmin/bin/nsjava.jar:/home/nsadmin/bin/postgresql.jar:/home/nsadmin/sourceforge/nsjava/test/java"

I've copied a basic example (basic.java) from the postgresql distro into the nsjava source directory under nsjava/test/java/org/nsjava/test. This example shows the basic sequence for loading the driver and running a few simple queries. It's important to note that the example doesn't provide connection pooling. This shouldn't be too difficult to rectify however, and I plan to write up an example when I get the time.

To call the example code from tcl, I do the following: 


    set args [nsjava::new {String[]} 3 [list jdbc:postgresql:nsjava danw ""]]
    nsjava::call org.nsjava.test.basic {main String[]} $args

where jdbc:postgresql:nsjava is the url for connecting to the postgresql database (nsjava is the name of the db running on localhost), and danw followed by "" is my username and password for connecting to the nsjava db.

I did a google search, for jdbc connection pooling solutions, and I came across several solutions for providing connection pooling, and I also noticed that the jdbc driver for postgresql will support connection pooling in the 7.3 release. It also give a JNDI example for accessing the driver datasource from anywhere in the code. This will make it possible to obtain a pooled connection from anywhere in the code without having to pass around a reference to the connection pooling source. I will try and add an example after the 7.3 driver is released.

Oracle

Oracle also works in a similar manner. To specify the oracle driver, modify the classpath to include the oracle jdbc .zip files and add the following JavaOption in your nsd.tcl file: 

ns_section "ns/server/${server}/module/nsjava"
        ns_param   JavaOption "-Djdbc.drivers=oracle.jdbc.driver.OracleDriver"
        ns_param   ClassPath  "/home/nsadmin/bin/nsjava.jar:/ora8/m01/app/oracle/product/8.1.7/jdbc/lib/classes12.zip:/ora8/m01/app/oracle/product/8.1.7/jdbc/lib/nls_charset12.zip:/home/nsadmin/sourceforge/nsjava/test/java"

The classes12.zip and nls_charset12.zip files must be downloaded from the otn web-site.

In addition to modifying the classpath and the driver string, it is also necessary to modify the url for connecting to the db. For oracle, the url string will look something like the following: jdbc:oracle:thin:@localhost:1521:ora8 Where ora8 is the SID that is associated with the oracle db and localhost:1521 corresponds to the host name and port where the oracle instance is listening. So for example if I have a db named nsjava, I can call the same sample code that I used for the postgresql test as follows: 


    set args [nsjava::new {String[]} 3 [list jdbc:oracle:thin:@localhost:1521:ora8 nsjava $password]]
    nsjava::call org.nsjava.test.basic {main String[]} $args

I have modified the sample code to support both the oracle and postgresql dbs.

Daniel Wickstrom
Last modified: Tue Nov 19 07:53:46 EST 2002