12/07/2008

How to create jdbc connection pool and DataSource in GlassFish

Every GlassFish DataSource depends on a connection pool that specifies how to connect to database. DataSource is just a thin layer on top of a connection pool. This is different from several other appservers.

Usually the easiest way is to do it in admin GUI at http://localhost:4848/ , choose Create New JDBC Connection Pools from the Common Tasks list on the front page right after login. You may need to scroll down a little bit to see it. On my screen, it is always hidden underneath the panel bottom. So for a while, I didn't notice its existence and always go to the left panel and choose Resources | JDBC | Connection Pools, which does the same with more clicks. The rest steps are self-explanatory.

For repeated configuration tasks, I prefer using GlassFish command line tool asadmin ($GLASSFISH_HOME/bin/asadmin). For example, this is script for creating mysql connection pool and datasource:

cp $HOME/mysql-connector-java-5.1.5-bin.jar $GLASSFISH_HOME/domains/domain1/lib/ext

$GLASSFISH_HOME/bin/asadmin stop-domain
$GLASSFISH_HOME/bin/asadmin start-domain

$GLASSFISH_HOME/bin/asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --property user=root:password=test:DatabaseName=test:ServerName=localhost:port=3306 test-pool

$GLASSFISH_HOME/bin/asadmin create-jdbc-resource --connectionpoolid test-pool jdbc/test
To create a connection pool that supports distributed transaction, use com.mysql.jdbc.jdbc2.optional.MysqlXADataSource as datasourceclassname, and set --restype javax.sql.XADataSource option:
$GLASSFISH_HOME/bin/asadmin create-jdbc-connection-pool --restype javax.sql.XADataSource --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlXADataSource --property user=root:password=test:DatabaseName=test:ServerName=localhost:port=3306 test-pool
Run asadmin ping-connection-pool test-pool to verify whether the created connection pool can connect to the database. The database server needs to be running.

8 comments:

Anonymous said...

Fucked doc, no information -- how to use etc. No one string of code.

komarios said...

There are some nice and simple Connection Pool tutorials for GlassFish v2 like :
Oracle:
http://mariosgaee.blogspot.com/2009/12/oracle-connection-pool-in-glassfish.html
MySQL:
http://mariosgaee.blogspot.com/2009/12/mysql-connection-pool-in-glassfish.html
PostGreSQL:
http://mariosgaee.blogspot.com/2009/12/configure-postegresql-connection-pool.html
FireBird:
http://mariosgaee.blogspot.com/2009/12/firebird-connection-pool-in-glassfish.html

Alex said...

@komarios - thanks for the tips on the Glassfish tutorials. They came in very handy!

Anonymous said...

thanks, very useful

duane5000 said...

Is there an export facility for jdbc-connection-pool and jdbc-resources that would enable us to borrow from an existing configured pool? I've proven a configuration in localhost and wish to copy these to our DEV and QA environments. There is a --help section on these commands and a reference to export, but no example usage. see > list-jdbc-connection-pool --help

javahowto said...

I'm not aware of any CLI command for exporting jdbc resource configurations. But I think the quickest way is to copy the relevant xml elements in domain.xml, and paste them to the target domain.xml.

This is not a clean solution. Maybe a better approach is to put your jdbc resource config in a glassfish-resources.xml, then you can apply that xml file to dev or QA environment, with 'asadmin add-resources path-to-resources-xml-file

http://javahowto.blogspot.com/2011/02/sample-glassfish-resourcesxml.html

Sayan Guharoy said...

here is a thread for a GUI admin based jdbc connection pool

glassfish jdbc connection pool


Anonymous said...

Excellent! Precise and correct. Very relevant for admin tasks where there is not time for reading irrelevant material. Thanks