Due to the lack of support for certain data base types with the default DataSource resource factory, I developed a generic datasource resource factory org.apache.naming.factory.DataSourceFactory. This resource factor allows you to create any object which implements the javax.sql.DataSource interface.
It uses a simple introspection techique discover the properties of the DataSource implementation your trying to instantiate and then matches these properties with the values supplied in the <ResourceParams> <parameter> elements. The version supplied with this e-mail supports properties of the following types String, int, short, long, float, double, byte, char and boolean. I've attached 3 files src.zip (which contains the source code), rf.jar a packaged version which you can place in your common\lib directory and a readme file which explains how to use the DataSourceFactory. Regards Anthony Dodd
rf.jar
Description: Binary data
CONTENTS
1. INTRODUCTION
2. CREATING AN ORACLE DATASOURCE
3. CREATING A SQL SERVER DATASOURCE USING DATADIRECTS' CONNECTJDBC
4. CREATING AN ORACLE CONNECTION POOLED DATASOURCE
5. CREATING A SQL SERVER CONNECTION POOLED DATA SOURCE USING DATADIRECTS' CONNECTJDBC
6. CLASSCASTEXCEPTION WHEN NARROWING JNDI REFERENCED OBJECTS
7. FINALLY
1. INTRODUCTION
Due to the lack of support for certain data base types with the default DataSource
resource factory, I developed a generic datasource resource
factory org.apache.naming.factory.DataSourceFactory. This resource factor allows you
to create any object which implements
the javax.sql.DataSource interface.
It uses a simple introspection techique discover the properties of the DataSource
implementation your trying to instantiate and
then matches these properties with the values supplied in the <ResourceParams>
<parameter> elements.
The version supplied with this e-mail supports properties of the following types
String, int, short, long, float, double,
byte, char and boolean.
If you supply the following parameter
<parameter>
<name>debug</name>
<value>true</true>
</parameter>
it will trace out its activity to the jvm.stdout, or where ever System.out has been
redirected.
2. CREATING AN ORACLE DATASOURCE
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from Oracles' DataSource implementation 'oracle.jdbc.pool.OracleDataSource'.
server.xml
---------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYORACLEDATASOURCE" reloadable="true" auth="Container"
type="oracle.jdbc.pool.OracleDataSource"/>
<ResourceParams name="jdbc/MYORACLEDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>Oracle DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1521</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>driverType</name>
<value>thin</value>
</parameter>
<parameter>
<name>user</name>
<value>scott</value>
</parameter>
<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
</ResourceParams>
</Context>
web.xml
---------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>Oracle DataSource</description>
<res-ref-name>jdbc/MYORACLEDATASOURCE</res-ref-name>
<res-type>oracle.jdbc.pool.OracleDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
3. CREATING A SQL SERVER DATASOURCE USING DATADIRECTS' CONNECTJDBC
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from DataDirects' ConnectJDBC DataSurce implementation
'com.ddtek.jdbcx.sqlserver.SQLServerDataSource'. DataDirect/Merant were the
people who developed the odbcjdbc bridge used in the early days when connecting to SQL
7.0 databases. Microsoft are currently using
a version of DataDirects ConnectJDBC to deliver there own JDBC 2 implementation for
SQL Server 2000. At the time of writing the
Microsoft version appears to be extremely problematic. I've also shown a sample JSP
page using this resource.
server.xml
-------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYSSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name="jdbc/MYSSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1433</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>user</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value/>
</parameter>
</ResourceParams>
</Context>
web.xml
----------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>SQL Server DataSource</description>
<res-ref-name>jdbc/MYSSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.jdbcx.sqlserver.SQLServerDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
sample jsp page
-------------------
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
//Reference holder for connection
Connection conn = null;
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
out.println("Initial context established.<br/>");
out.println("envCtx.lookup(\"jdbc/MYSSQLDATASOURCE\").getClass().getName()="+envCtx.lookup("jdbc/MYSSQLDATASOURCE").getClass().getName()+"<br/>");
javax.sql.DataSource ds =
(javax.sql.DataSource)envCtx.lookup("jdbc/MYSSQLDATASOURCE");
conn = ds.getConnection();
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from CATEGORIES");
out.println("<h1>Categories</h1>");
while (rs.next()) {
out.println(rs.getString("TextE")+"</br>");
}
} catch (java.lang.Exception e) {
out.println("A "+e.getClass().getName()+" exception has occured :
"+e.getMessage()+"<br/>");
e.printStackTrace(System.out);
} finally {
if (conn != null)
conn.close();
}
%>
4. CREATING AN ORACLE CONNECTION POOLED DATASOURCE
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from Oracles' ConnectionPoolDataSource implementation
'oracle.jdbc.pool.OracleConnectionPoolDataSource'.
server.xml
---------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYORACLEPOOLEDDATASOURCE" reloadable="true" auth="Container"
type="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
<ResourceParams name="jdbc/MYORACLEPOOLEDDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>Oracle Connection Pool DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1521</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>driverType</name>
<value>thin</value>
</parameter>
<parameter>
<name>user</name>
<value>scott</value>
</parameter>
<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
</ResourceParams>
</Context>
web.xml
---------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>Oracle Connection Pool DataSource</description>
<res-ref-name>jdbc/MYORACLEPOOLEDDATASOURCE</res-ref-name>
<res-type>oracle.jdbc.pool.OracleConnectionPoolDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Here you would use getPooledConnection method to get a connection from the pool, to be
sure you can always check that the DataSource
returned implements javax.sql.ConnectionPooledDataSource.
5. CREATING A SQL SERVER CONNECTION POOLED DATA SOURCE USING DATADIRECTS' CONNECTJDBC
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
pooled datasource from DataDirects ConnectJDBC ConnectionPoolDataSource
implenmentation 'com.ddtek.pool.PooledConnectionDataSource'.
Since this implementation does not implement javax.sql.ConnectionPoolDataSource, but
instead expects to be supplied with a datasource
which it can use to create a pooled instance, a helper resource factory has been
included 'com.ddtek.pool.PooledConnectionDataSourceFactory'.
You still need to create a datasource as with 3 above, then create a
'com.ddtek.pool.PooledConnectionDataSource' which is passed the jndi name
of the first.
I've also shown a sample jsp page using the pooled connection resource, identical in
aspects to that shown in two above, apart from the jndi name
of the pooled resource being used. Note the use of getConnection as
'com.ddtek.pool.PooledConnectionDataSource' doesn't implement
javax.sql.ConnectionPoolDataSource.
server.xml
-------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name="jdbc/MYSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1433</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>user</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value/>
</parameter>
<parameter>
<name>debug</name>
<value>true</value>
</parameter>
</ResourceParams>
<!-- Create ConnectionPoolDataSource -->
<Resource name="jdbc/MYPOOLEDSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.pool.PooledConnectionDataSource"/>
<ResourceParams name="jdbc/MYPOOLEDSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>com.ddtek.pool.PooledConnectionDataSourceResourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server Connection Pool DataSource</value>
</parameter>
<parameter>
<name>dataSourceName</name>
<value>jdbc/MYSQLDATASOURCE</value>
</parameter>
<parameter>
<name>initialPoolSize</name>
<value>5</value>
</parameter>
<parameter>
<name>minPoolSize</name>
<value>5</value>
</parameter>
<parameter>
<name>maxPoolSize</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdleTime</name>
<value>300</value>
</parameter>
</ResourceParams>
</Context>
web.xml
----------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>SQL Server Connection Pool DataSource</description>
<res-ref-name>jdbc/MYPOOLEDSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.pool.PooledConnectionDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
<description>SQL Server DataSource</description>
<res-ref-name>jdbc/MYSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.jdbcx.sqlserver.SQLServerDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
sample jsp page
-------------------
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
//Reference holder for connection
Connection conn = null;
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
out.println("Initial context established.<br/>");
out.println("envCtx.lookup(\"jdbc/MYPOOLEDSQLDATASOURCE\").getClass().getName()="+envCtx.lookup("jdbc/MYPOOLEDSQLDATASOURCE").getClass().getName()+"<br/>");
javax.sql.DataSource ds =
(javax.sql.DataSource)envCtx.lookup("jdbc/MYPOOLEDSQLDATASOURCE");
out.println("Acquiring Connection</br>");
conn = ds.getConnection();
out.println("Connection Acquired</br>");
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from CATEGORIES");
out.println("<h1>Categories</h1>");
while (rs.next()) {
out.println(rs.getString("TextE")+"</br>");
}
} catch (java.lang.Throwable t) {
out.println("A "+t.getClass().getName()+" exception has occured :
"+t.getMessage()+"<br/>");
t.printStackTrace(System.out);
} finally {
if (conn != null)
conn.close();
}
%>
6. CLASSCASTEXCEPTION WHEN NARROWING JNDI REFERENCED OBJECTS
Something to be aware of. One of the problems I constantly battled with was the
ClassCastException been thrown when I tried to narrow the returned resource to a
javax.sql.DataSource.
The reason for this turned out to be multiple occurrences of javax.sql.DataSource. For
example if using the jdbc2 extensions and oracles classes12.jar both contain
javax.sql.DataSource.
I'm not sure if this is just a tomcat problem.
7. FINALLY
Hope you find these code snippets useful, any feed back would be grateful. Do let's us
know of any problems encountered.
[EMAIL PROTECTED]
src.zip
Description: Zip compressed data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
