The Caboteria / Tech Web / PostgresqlAndJ2ee (03 Apr 2001, TobyCabot)
Sun's reference j2ee server (j2sdk1.2.1 and j2sdk1.3) will work with Postgresql, although it's not a supported configuration and therefore probably not a great idea for production systems. I like the combination, though, since my machines are resource constrained and I'm usually running Postgresql anyway - I'd rather not have another JVM running to handle database queries.

The steps to follow are:

Install, test

Beyond the scope of this doc - but note that I'm using Postgresql 7.0.2, Sun's jdk 1.3.0, Sun j2sdk1.2.1 on a Debian 2.1 (potato) i386 system. Your mileage may vary.

Follow all instructions - you can test that you've got the server (and Cloudscape) set up correctly by running some of the basic Sun examples, see

$J2EE_HOME/doc/j2sdkee1.2.1/doc/guides/ejb/html/DevGuideTOC.html

(you need to download the docs in a separate bundle from the sdk.) Good tests to start with are the Converter (to test basic client-server operation) and the Account (to test basic jdbc database operations).

You'll also want to test the Postgresql jdbc driver. There are some test programs in the postgresql source tree at src/interfaces/jdbc/example/. Try basic.java for a simple test. This tests that: you've got a database, you can describe it in a jdbc URL, you've got a user/password combo that can read and write the database.

Hook Postgresql jdbc Driver into J2EE

First find the postgresql.jar file (it's in /usr/share/java on my Debian box) and copy it to $J2EE_HOME/lib/system (according to the j2ee docs it needs to be there for security reasons). Then edit $J2EE_HOME/bin/userconfig.sh so that =$J2EE_CLASSPATH includes postgresql.jar.

1.2.1:

Edit $J2EE_HOME/config/default.properties. First look for the line that looks like:

jdbc.drivers=COM.cloudscape.core.RmiJdbcDriver

...and change it to read:

jdbc.drivers=COM.cloudscape.core.RmiJdbcDriver:org.postgresql.Driver

This tells j2ee how to load the drivers. Now you need to tell it how to map the database names that the application uses onto jdbc database names. Just below the jdbc.drivers line you'll find one that sets jdbc.datasources. I set up a database called account in postgresql so mine reads:

jdbc.datasources=jdbc/Cloudscape|jdbc:postgresql:account

I suppose this is somewhat misleading but it requires the fewest changes from the Cloudscape configuration.

1.3:

Edit $J2EE_HOME/config/resource.properties. Add a line:

jdbcDriver.1.name=org.postgresql.Driver

and then change

jdbcDataSource.0.url=jdbc:cloudscape:rmi:CloudscapeDB;create=true

to

jdbcDataSource.0.url=jdbc:postgresql:account

Change the user ID in the beans The Cloudscape server doesn't seem to use the jdbc user/password that postgresql does so you'll need to add it to your beans. In the deploytool you choose the bean (say "AccountBean") in the left pane then choose the "Resource Ref's" tab at the top of the right pane. Select the resource factory whose name is "jdbc/AccountDB" and you'll see that the "Deployment Settings" box at the bottom will now allow you to enter a JNDI name (which has to match the one in the jdbc.datasources, e.g. jdbc/Cloudscape, and a username and password. You can now save and deploy the application. (As an aside: if you're building the .ear by hand you'll need to put these values in the META-INF/sun-j2ee-ri.xml file).

Go for it!

Run the AccountClient client and bask in the glory that is postgresql and java. A few troubleshooting notes:

*** AccountEJB.java	  Mon Feb  5 16:40:23 2001
--- AccountEJB.java.old	  Wed May  3 23:07:56 2000
***************
*** 282,288 ****
  
		  String selectStatement =
				  "select id from account " +
!				 "where balance::float between ? and ?";
		  PreparedStatement prepStmt = 
				  con.prepareStatement(selectStatement);
  
--- 282,288 ----
  
		  String selectStatement =
				  "select id from account " +
!				 "where balance between  ? and ?";
		  PreparedStatement prepStmt = 
				  con.prepareStatement(selectStatement);

HTH.

-- TobyCabot - 05 Feb 2001

Edit | Attach | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Copyright © 2008-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding The Caboteria? Send feedback