Ok, I ditched SQL Anywhere (which I was using because the Open University course I was on used it to teach me SQL) and I did some research on database servers. I downloaded PostgreSQL, and BINGO!!! Hooray! I have a working connection pool, and not only that, it's free!
There were some slight hitches in setting it up, so I'll give details of what I did here:
<H1>Setting up PostgreSQL 8.3 on Glassfish V2.1 to create a ConnectionPool data source to use for dependency injection in a Java EE project in Netbeans 6.5.1 on WindowsXP</H1>
I am assuming the relevant software has been installed and a database has been created on PostgreSQL and populated (make sure you install the PGjdbc driver as well). Also that a new Enterprise project has been opened on Netbeans. (New project>Java EE>Enterprise application) and that defaults were generally accepted for the installations.
1)
You should have installed the PGjdbc driver
http://jdbc.postgresql.org/. Locate the jar file, which is probably located on C:\Program Files\PostgreSQL\pgJDBC\postgresql-8.3-604.jdbc3.jar if you accepted default installation settings. Copy it and place the copy in the Glassfish v2.1 domain1 library. Probably, this will be C:\Program Files\glassfish-v2.1\domains\domain1\lib.
2) Return to Netbeans projects tab and make sure the driver is installed in the project libraries. Do this by right-clicking a library folder and choosing ADD JAR/FOLDER then browse to the driver you just saved in the Glassfish domain library C:\Program Files\glassfish-v2.1\domains\domain1\lib\postgresql-8.3-604.jdbc3.jar.
3) Add the driver on the Netbeans Services tab. Expand the Databases node to see the Drivers folder. Right click the Drivers folder and choose New Driver. Click on Add and browse to the driver you just saved in the Glassfish domain library C:\Program Files\glassfish-v2.1\domains\domain1\lib\postgresql-8.3-604.jdbc3.jar. The driver class should automatically be filled in: org.postgresql.Driver. You can choose any name for the driver - e.g. PGDriver.
4) Expand the Drivers node on Netbeans Services tab and right click the driver you just added (PGDriver). Choose Connect using... from the menu. The name should already be filled in with the name of the Driver (PGDriver). Fill in the rest as follows:
Host: localhost
Port: 5432
Database: <NameOfDatabase e.g. MyDatabase>
User Name: <User Name for Database on Postgres e.g. postgres>
Password: <Password for Database on Postgres>
Leave Additional properties empty.
The wizard will then ask which schema to use - the default is public.
5) You should now have a connection listed under the Drivers folder in the Services tab! It will have a name something like jdbc:postgresql://localhost:5432/MyDatabase(postgres on public). You can test it by right-clicking and choosing connect (if it's not already connected) then right click again and choose execute command. An SQL command window opens. You can now execute SQL statements on your postgresql database e.g. SELECT * FROM myTable
6) Now set up the connection pool. I tried setting up the connection from the Glassfish admin console first, as follows, but I think it would probably work if you just skip to step 10:
7) In the Services tab, right click Glassfish V2 and select Start if the server is not already running. If the server is running, you should be able to expand the node. Once it is running, right click and choose View Admin Console. The console will open in your browser. (Alternatively, you can just paste
http://localhost:4848/asadmin/index.html in your browser address bar, assuming you are using Glassfish default settings)
Log in using your Glassfish username and password (default admin and adminadmin)
8) Click on CommonTasks on the left, then choose Create new JDBC Connection Pool.
9) Give the Connection Pool a name you will remember e.g. PGConnect.
Resource: javax.sql.ConnectionPoolDataSource
Database vendor: PostgreSQL
10) Fill in General Settings as follows:
Name: PGConnect
Resource Type: javax.sql.ConnectionPoolDataSource
Database Vendor: PostgreSQL
Datasource Classname: org.postgresql.jdbc3.Jdbc3ConnectionPool
Description: <Any description you want!>
Accept defaults for Pool Settings, Connection Validation and Transaction, but add the following data to the Additional properties at the bottom of the page:
Check the boxes by DatabaseName, Password, PortNumber, ServerName, User and add the following details:
DatabaseName: <NameOfDatabase e.g. MyDatabase>
Password: <Password for Database on Postgres>
PortNumber: 5432
ServerName: localhost
User: <User Name for Database on Postgres e.g. postgres>
Now click finish, and a connection pool should be created. However, you still need to add it to your project...but hopefully you should be able to ping it successfully by choosing it in the Glassfish console Resources>JDBC>Connection Pools and clicking ping. This made me very happy!
10) Back in Netbeans, expand the
EJB module. Right click Server Resources and choose Glassfish>JDBC Connection Pool
11) JDBC Connection Pool Name should have the same name you used to set up the Connection Pool on the Glassfish server (if you did that bit and didn't just skip here, in which case just give it a name!) e.g. PGConnect. Select the connection you made earlier from the drop down list (it will be something like jdbc:postgresql://localhost:5432/MyDatabase(postgres on public)) then click next.
12) Fill in the form as follows:
Datasource Classname:
Resource Type:
Description:
URL: <Doesn't matter what you put, we're going to change this in a minute>
User: <User Name for Database on Postgres e.g. postgres>
Password: <Password for Database on Postgres>
Then click finish (accepting the defaults on the next page)
13) Expand Server Resources under the EJB module and you should find a sun-resources xml document has been added. Open the XML document. You should see a list of propert tags in the body of the document that looks like this:
<property name="Url" value="<Whatever you filled in step 12 above>"/>
<property name="User" value="postgres"/>
<property name="Password" value="<Password for Database on Postgres>"/>
Change that section to look like this (adding 3 extra properties and changing URL to something else):
<property name="User" value="postgres"/>
<property name="DatabaseName" value="Plantlore"/>
<property name="Password" value="<Password for Database on Postgres>"/>
<property name="PortNumber" value="5432"/>
<property name="ServerName" value="localhost"/>
<property name="JDBC30DataSource" value="true"/>
14) While you're in here, you might as well add the JDBC resource, so the whole XML document should read something like this:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0 Resource Definitions //EN" "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
<jdbc-resource enabled="true" jndi-name="jdbc/PGResource" object-type="user" pool-name="PGConnect">
<description>PostgreSQL DataSource</description>
</jdbc-resource>
<jdbc-resource enabled="true" jndi-name="jdbc/PGDatasource" object-type="user" pool-name="PGConnect">
<description/>
</jdbc-resource>
<jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="org.postgresql.jdbc3.Jdbc3ConnectionPool" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="PGConnectionPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.ConnectionPoolDataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
<description>PostgreSQL Connection Pool</description>
<property name="User" value="postgres"/>
<property name="DatabaseName" value="Plantlore"/>
<property name="Password" value="<Password for Database on Postgres>"/>
<property name="PortNumber" value="5432"/>
<property name="ServerName" value="localhost"/>
<property name="JDBC30DataSource" value="true"/>
</jdbc-connection-pool>
</resources>
Now save and close.
15) You need to add the entity classes from the database. I found for my database that you can't do this directly from the Data Source (it gives an error message about not being able to find the driver, which is actually incorrect and may be something to do with the trigger I have on one of my tables) and have to use the Database Schema instead. So to set up the Database Schema, right-click the EJB Module and choose new>Other then Persistence>Database Schema. Click next.
16) Choose any name for your schema e.g. PGScheme. Click next. Select the postgres connection (something like jdbc:postgresql://localhost:5432/MyDatabase(postgres on public)). Click "Add all" then Finish. Expand your EJB module>Configuration Files and you should see the new schema there. You can expand it to view table information.
17) Right click your EJB module and choose new>Entity Classes from Database. Select Database Schema and choose the schema you added in step 16. The tables should appear in the left hand box. You can now add them all (add all) then click next and follow the instructions on the wizard to set up a persistence unit and add the entity classes. I found the wizard was appearing to run successfully, but not adding any beans to my EJB module. If this happens to you, run the wizard again but try just adding the simplest tables - I could add all my tables except the one with a trigger on it. I had to write the entity bean for that table manually.
That's as far as I got. Hope this post is useful to someone. :-)
