« January 2010
Sun	Mon	Tue	Wed	Thu	Fri	Sat
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31
Today

Jagadish's Blog

« Monitoring JDBC... | Main | Resource Adapter... »

14 Jun · Thu 2007

JDBC Connection Pool Templates - GlassFish

JDBC Connection Pool Templates for GlassFish

Templates to create JDBC Connection Pool & Resource

Often, setting up connection pool (datasource) against various databases will require knowledge on
mandatory property names to successfully connect to database.

GlassFish V2 has templates to create connection pool & resource against various databases & jdbc drivers.

Once appropriate values for properties like, user, password, databaseName, serverName are set in the template, they
can be used multiple times.

GlassFish V2 has templates available in GF_INSTALL_DIR/lib/install/templates/resources/jdbc

`How to use :`

1) copy the jdbc-driver to AS_INSTALL_DIR/domains/<DOMAIN_NAME>/lib/ext
2) restart appserver
3) edit the appropriate jdbc-driver template to fill username, password, hostname, databasename, portnumber etc.,
3a) [Optional] select appropriate resource-type & datasource-classname. Templates are provided with the list of
datasource-classnames for respective resource-types of the jdbc-vendor, if available.
4) asadmin add-resources jdbc-driver-template.xml (eg: AS_INSTALL_DIR/lib/install/templates/resources/jdbc/javadb_type4_datasource.xml)
5) asadmin ping-connection-pool connection-pool-name

For more information, refer GF_INSTALL_DIR/lib/install/templates/resources/jdbc/README and respective datasource-template.xml files.

These templates can be used against GlassFish V1 also.

`Templates :`

Database Vendor	JDBC Driver Vendor	datasource-template-file
JavaDB / Derby	Derby	`javadb_type4_datasource.xml`
Oracle	Oracle Thin Driver	oracle_type4_datasource.xml
Oracle	Inet Driver	oracle_inet_datasource.xml
Oracle	Sun bundled DataDirect Driver (available in SJSAS EE builds)	oracle_dd_datasource.xml
MySQL	MySQL Connector/J	mysql_type4_datasource.xml
PostgreSQL	PostgreSQL Driver	postgresql_type4_datasource.xml
DB2	Sun bundled DataDirect Driver (available in SJSAS EE builds)	db2_dd_datasource.xml
DB2	IBM DB2 JDBC type-4 driver	db2_jcc_datasource.xml
Sybase	Sun bundled DataDirect Driver (available in SJSAS EE builds)	sybase_dd_datasource.xml
Sybase	Inet Driver	sybase_inet_datasource.xml
Sybase	jConnect Driver	sybase_jconn_datasource.xml
Microsoft SQL Server	Sun bundled DataDirect Driver (available in SJSAS EE builds)	microsoft_dd_datasource.xml
Microsoft SQL Server	Inet Driver	microsoft_inet_datasource.xml
Microsoft SQL Server	jTDS Driver	microsoft_jtds_datasource.xml
Microsoft SQL Server 2000	Microsoft	microsoft_sqlserver2000_datasource.xml
Microsoft SQL Server 2005	Microsoft	microsoft_sqlserver2005_datasource.xml

The templates listed above will be available in GlassFish v2 b51

Similarly, templates for other databases can be done.
If you have a template for a particular database that is not listed here,
please post it to dev@glassfish.dev.java.net or users@glassfish.dev.java.net

glassfish

Posted by jagadish @ 10:50 AM IST [ Comments [6] ]

Comments:

Very Nice Jagadish! One question...Since we are using "asadmin add-resources" command, do we still need to copy the template xml file to AS_INSTALL_DIR/domains/<DOMAIN_NAME>/lib/ext directory? Why is that necessary? Are we using the template file at runtime?

We may also want to add drag and drop support for these templates where one can copy the one of these template xml file to domains/DOMAIN_NAME/auto-deploy directory to add the resource (similar to applications).

Nazrul

Posted by Nazrul on June 15, 2007 at 09:18 AM IST #

Thanks Dude,
This has been really helpful blog for me now that I have created a jtds drver and pool on glassfish I just have to figure out how to use this with netbeans .
Would you have a step by step guide to a small saple app using this connnection pool . if u could share this knowledge it would be very beneficial to a lot of people including me .

Many thanks
MB

Posted by 203.97.90.174 on May 21, 2008 at 07:19 AM IST #

Hi, excuseme not speak english, but i have a next error:

javax.servlet.ServletException: javax.naming.NameNotFoundException: No object bound to name java:comp/env/jdbc/wdatos

I'm created connection pool and jdbc resource, my sun-web.xml content is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Application Server 9.0 Servlet 2.5//EN' 'http://www.sun.com/software/appserver/dtds/sun-web-app_2_5-0.dtd'>
<sun-web-app>
<session-config>
<session-manager/>
</session-config>
<resource-ref>
<res-ref-name>mail/Session</res-ref-name>
<jndi-name>mail/Session</jndi-name>
</resource-ref>
<resource-ref>
<res-ref-name>wdata</res-ref-name>
<jndi-name>jdbc/data</jndi-name>
</resource-ref>
<jsp-config/>
</sun-web-app>

and my web.xml file content is:

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5">

<display-name>Tomcat Manager Application</display-name>
<description>
A scriptable management web application for the Tomcat Web Server;
Manager lets you view, load/unload/etc particular web applications.
</description>
......
..... other data
.....
<resource-ref>
<description>DataSource Reference</description>
<res-ref-name>wdata</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
</web-app>

my jsp code is:

<%@ page import="javax.naming.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%

if(request.getParameter("op") == null || request.getParameter("op").equals("3"))
request.getSession(true).invalidate();

%>
<html>
<head>
<meta http-equiv="content-type" content="text-html; charset=iso-8859-1">
<link rel='stylesheet' type='text/css' href='styles/themes/default/main.css'>

<title>Panel de Control</title>
<body>
<%
InitialContext ic = new InitialContext();
String dsName = "java:comp/env/jdbc/wdata";
DataSource ds = (javax.sql.DataSource)ic.lookup(dsName);
Connection con = ds.getConnection();
out.print(con);
con.close();
%>
</body>
</html>

why display error?
how configure pool connections?

Thank's, and i need help please.....

Posted by No object bound to name on May 31, 2008 at 12:03 AM IST #

Excuseme, i have a error in previous message:

The sun-web.xml file content is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-web-app PUBLIC '-//Sun Microsystems, Inc.//DTD Application Server 9.0 Servlet 2.5//EN' 'http://www.sun.com/software/appserver/dtds/sun-web-app_2_5-0.dtd'>
<sun-web-app>
<session-config>
<session-manager/>
</session-config>
<resource-ref>
<res-ref-name>mail/Session</res-ref-name>
<jndi-name>mail/Session</jndi-name>
</resource-ref>
<resource-ref>
<res-ref-name>wdata</res-ref-name>
<jndi-name>jdbc/wdata</jndi-name>
</resource-ref>
<jsp-config/>
</sun-web-app>

thank's

Posted by No object bound to name on May 31, 2008 at 12:05 AM IST #

MS Access template is at http://www.hxtt.com/test/microsoft_access_type4_datasource.xml
MS Excel template is at http://www.hxtt.com/test/microsoft_excel_type4_datasource.xml
Corel Paradox template is at http://www.hxtt.com/test/paradox_type4_datasource.xml
Xbase template is at http://www.hxtt.com/test/dbf_type4_datasource.xml
CSV template is at http://www.hxtt.com/test/csv_type4_datasource.xml
Text template is at http://www.hxtt.com/test/text_type4_datasource.xml

Posted by 61.150.43.81 on June 30, 2008 at 03:55 PM IST #

org.apache.jasper.JasperException: com.sun.gjc.spi.DataSource
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:384)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:251)
javax.servlet.http.HttpServlet.service(HttpServlet.java:860)
sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:585)
org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:249)
java.security.AccessController.doPrivileged(Native Method)
javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:282)
org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:165)

root cause

java.lang.ClassCastException: com.sun.gjc.spi.DataSource
org.apache.jsp.index_jsp._jspService(index_jsp.java:54)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:105)
javax.servlet.http.HttpServlet.service(HttpServlet.java:860)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:336)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:251)
javax.servlet.http.HttpServlet.service(HttpServlet.java:860)
sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:585)
org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:249)
java.security.AccessController.doPrivileged(Native Method)
javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:282)
org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:165)

note The full stack trace of the root cause is available in the Sun-Java-System/Application-Server logs.

I am trying CMP (EJB) will u please send me the deployment steps in Sun(TM) Enterprise Edition 8

I nedd this plese help me

Posted by somnath on March 18, 2009 at 11:19 AM IST #