File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3)" Watch "Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3)" New topic
Author

Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3)

Susannah Relf
Greenhorn

Joined: Apr 06, 2009
Posts: 3
Hi,

I'm trying to create a Java Enterprise web application. I used Netbeans to automatically generate entities and entity session beans, then created a servlet to test the dependency injection was working. It isn't. I think I must have got something wrong with the persistence unit?

It currently looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="Plantlore-ejbPU" transaction-type="JTA">
<jta-data-source>SybaseDS</jta-data-source>
<properties/>
</persistence-unit>
</persistence>


The trace from my application attempting to run shows lots of successful things, then followed by:

Error occurred during application loading phase. The application will not run properly. Please fix your application and redeploy.
WARNING: com.sun.enterprise.deployment.backend.IASDeploymentException: java.lang.RuntimeException: javax.naming.NameNotFoundException: SybaseDS not found
run-deploy:
Browsing: http://localhost:8080/Plantlore-war/ListPlantNames
run-display-browser:
run:
BUILD SUCCESSFUL (total time: 34 seconds)

Driver I am using is: ianywhere.ml.jdbcodbc.jdbc3.IDriver

Thanks for your help!!
Susannah Relf
Greenhorn

Joined: Apr 06, 2009
Posts: 3
I've got a bit further now! But now my connection pool doesn't work...

Here are the steps I've taken so far to give as much information as possible:

Added a JConnect driver service to Netbeans:
file: jconn3.jar, (also added to the EE App libraries.)
datasource: com.sybase.jdbc3.jdbc.SybConnectionPoolDataSource

Added a JConnect driver connection service to Netbeans:
Name: Name of JConnect driver service
User Name: User name for database
Password: Password for database
JDBC URL: jdbc:sybase:Tds:localhost:2638/Database name

Tested the JConnect driver connection service with Netbeans SQL editor. It works fine and you can view table content and make database changes from Netbeans.

Now to the connection pool...

I am using the Sun Javasystem Application server.
I created the connection pool based on the already successful connection above.
DataSourceClassName: com.sybase.jdbc3.jdbc.SybConnectionPoolDataSource
Resource-type: ResType javax.sql.ConnectionPoolDataSource
Default values for all other values.

However, I try to ping the connection pool using the Sun Javasystem Application server and get the following error:

Operation 'pingConnectionPool' failed in 'resources' Config Mbean. Target exception message: Connection could not be allocated because: JZ0PN: Specified port number of -1 was out of range. Port numbers must meet the following conditions: 0 <= portNumber <= 65535

Anyone know what I've got wrong? I'd really appreciate help here. One thought I had is that maybe I need to add the SybObjectFactory to the Sun Javasystem Application server somehow, but I'm not sure how to do it. I've tried adding a custom resource, settings below, but it made no difference to the connection pool problem.
resource type: java.naming.factory.object
factory class: com.sybase.jdbc3.SybObjectFactory
properties: none added

I've spent days on this now and would really appreciate help! Thanks. :-)
Susannah Relf
Greenhorn

Joined: Apr 06, 2009
Posts: 3
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. :-)
komarios marios
Greenhorn

Joined: Dec 14, 2009
Posts: 4
There is a simple PostgreSQL Connection Pool in Glassfish 2.1.1 here

http://mariosgaee.blogspot.com/2009/12/configure-postegresql-connection-pool.html

I hope you'll find it useful.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Tutorial PostgreSQL 8.3 on Glassfish V2.1 for ConnectionPool in Netbeans 6.5.1, WindowsXP (Post 3)