How do I connect to a Websphere Datasource with a given JNDI name?

Question

I'm using Websphere Portal 7.0 and creating a portlet with RAD 8.0. My portlet is trying to make a db2 connection to a remote server. I wrote a java program locally to do a basic JDBC connection to the server and get records from a table. The code works fine; however, when I add the code to my portlet as well as the db2jcc4.jar, the connection doesn't work. I'm using the basic:

Connection connection = DriverManager.getConnection("jdbc:db2://server:port/db:user=user;password=pw;");

I figure that using the Websphere datasource is the right way to go. I know the JNDI name for the datasource, but I'm not finding clear cut examples on how to make a connection. Several examples use a DataSource class (I typed this in and this doesn't seem like it comes from a native java package so what import do I use here?) coupled with a Context. I've come across code like:

Context ctx = new InitialContext();
ctx.lookup("jdbc/xxxx");

... Can someone break this down for me?

EDIT 1

I've updated my code per the answers listed. I really think I'm getting closer. Here is my getConnection() method:

private Connection getConnection() throws SQLException {
    javax.naming.InitialContext ctx = null;
    javax.sql.DataSource ds = null;
    System.out.println("Attempting connection..." + DateUtil.now() );
    try {
        ctx = new javax.naming.InitialContext();
        ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/db");
        connection = ds.getConnection();
    } catch (NamingException e) {
        System.out.println("peformanceappraisalstatus: COULDN'T CREATE CONNECTION!");
        e.printStackTrace();
    }       
    System.out.println("connection: " + connection.getClass().getName() + " at " + DateUtil.now());
    return connection;
}

My entire web.xml file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.5" 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">
    <display-name>PeformanceAppraisalStatus</display-name>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.htm</welcome-file>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>default.html</welcome-file>
        <welcome-file>default.htm</welcome-file>
        <welcome-file>default.jsp</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>
        Datasource connection to Db</description>
        <res-ref-name>jdbc/db</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>

I am seeing an error that describes the very thing you guys are telling me Websphere should prompt me to do, but doesn't:

SRVE0169I: Loading Web Module: PeformanceAppraisalStatus.
[8/23/11 18:08:02:166 CDT] 00000009 InjectionProc E   CWNEN0044E: A resource reference binding could not be found for the jdbc/db resource reference, defined for the PeformanceAppraisalStatus component.
[8/23/11 18:08:02:169 CDT] 00000009 InjectionEngi E   CWNEN0011E:  The injection engine failed to process bindings for the metadata.

Yes, I know that I've mispelled performance as peformance throughout the app.

SOLUTION

I was so very close. Here are the missing bits that made it all fall into place:

web.xml:
<resource-ref>      
    <description>
    Datasource connection to db</description>
    <res-ref-name>jdbc/db</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
    <mapped-name>jdbc/db</mapped-name>      
</resource-ref>

ibm-web-bnd.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-bnd 
    xmlns="http://websphere.ibm.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://websphere.ibm.com/xml/ns/javaee http://websphere.ibm.com/xml/ns/javaee/ibm-web-bnd_1_0.xsd"
    version="1.0">

    <virtual-host name="default_host" />


    <resource-ref name="jdbc/db" binding-name="jdbc/mydatasource" />
</web-bnd>

It appears that the ibm-web-bnd.xml file handles the binding between the project resource name and the datasource in websphere. Once I added the line:

<resource-ref name="jdbc/db" binding-name="jdbc/mydatasource" />

Websphere Portal seemed appeased. My code is working and connecting to the database now.

Disadvantaged

It would be nice if you would extract your solution from the question to the separate answer. It would make the topic easier to read. – Achromatism

Answer 1

You need to define a resource reference in your application and then map that logical resource reference to the physical resource (data source) during deployment.

In your web.xml, add the following configuration (modifying the names and properties as appropriate):

<resource-ref>
    <description>Resource reference to my database</description>
    <res-ref-name>jdbc/MyDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Then, during application deployment, WAS will prompt you to map this resource reference (jdbc/MyDB) to the data source you created in WAS.

In your code, you can obtain the DataSource similar to how you've shown it in your example; however, the JNDI name you'll use to look it up should actually be the resource reference's name you defined (res-ref-name), rather than the JNDI name of the physical data source. Also, you'll need to prefix the res-ref-name with the application naming context (java:comp/env/).

Context ctx = new InitialContext();
DataSource dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/MyDB");

Counterclaim

@Jason, what version of the servlet spec is your application using? The approach I described above should work for any version, but if you are using Servlet 2.5 (which is supported on WAS 7), there is an even easier approach you can use with the @Resource annotation. – Counterclaim

I've done this, but was not prompted to associate the resource-ref to the datasource in WAS. Looking around WAS now to see if there's somewhere to do it manually with the portlet settings. – Disadvantaged

WAS should prompt you for this information and the deployment will typically fail if you don't map resource references to physical data sources. Maybe you specified the option to include default bindings? You should be able to see the option to map these in the ISC at "Applications > Application Types > WebSphere enterprise applications > [your_application] > Resource references". If you don't see that option or don't see your resource reference listed there, something may be misconfigured in your app. – Counterclaim

RAD will create WAS-specific deployment files, .xmi files, which will automatically make the choices which prevent deployment from prompting you for them. You can edit those files from within RAD, or you can try excluding them from the RAD-built artifacts. Or, as shelley says, you can re-map them after deployment. – Photoconductivity

Okay, I keep saying WAS. I'm actually uploading the .WAR file through Admin --> Portlet Management from Websphere Portal. Are we talking about the same thing here? – Disadvantaged

I am not familiar with WebSphere Portal, although I think it may be built on the WAS architecture. Regardless, you should still specify the resource-reference and perform the lookup based on the res-ref-name as I've described. As far as the tool used to properly map the resource-ref to your data source, I'm not sure how WebSphere Portal differs from WAS. I'd also suggest clarifying your question to indicate that you're using WebSphere Portal. – Counterclaim

I stumbled onto this technote which seems to indicate that the WAS ISC is available for WebSphere Portal. If you are able to access it, you may be able to see the option to map the resource reference as I've described in my comment above. – Counterclaim

I've revised the question to include the solution, but I'm marking your answer as the solution, Shelley. Thank you so much for your continued feedback on this. This would have taken so much longer without everyones comment, so, again, thank you all. – Disadvantaged

@Counterclaim Hello, I am having a similar issue with a Maven Built UI project which I want to run on WAS. I try to map a JNDI in the menu of "Applications > Application Types > WebSphere enterprise applications > [your_application] > Resource references" but after I click browse select a jndi and click apply, there is no "ok" or "save" button. Is there some way we can talk about this? I have a question about it if you see my profile. – Inerney

Answer 2

To get a connection from a data source, the following code should work:

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

Context ctx = new InitialContext();
DataSource dataSource = ctx.lookup("java:comp/env/jdbc/xxxx");
Connection conn = dataSource.getConnection();

// use the connection

conn.close();

While you can look up a data source as defined in the Websphere Data Sources config (i.e. through the websphere console) directly, the lookup from java:comp/env/jdbc/xxxx means that there needs to be an entry in web.xml:

<resource-ref>
    <res-ref-name>jdbc/xxxx</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

This means that data sources can be mapped on a per application bases and you don't need to change the name of the data source if you want to point your app to a different data source. This is useful when deploying the application to different servers (e.g. test, preprod, prod) which need to point to different databases.

Guilder

Answer 3

DNS for Services

JNDI needs to be approached with the understanding that it is a service locator. When the desired service is hosted on the same server/node as the application, then your use of InitialContext may work.

What makes it more complicated is that defining a Data Source in Web Sphere (at least back in 4.0) allowed you to define the visibility to various degrees. Basically it adds namespaces to the environment and clients have to know where the resource is hosted.

Simple example.

javax.naming.InitialContext ctx = new javax.naming.InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/DataSourceAlias");

Here is IBM's reference page.

If you are trying to reference a data source from an app that is NOT in the J2EE container, you'll need a slightly different approach starting with needing some J2EE client jars in your classpath. http://www.coderanch.com/t/75386/Websphere/lookup-datasources-JNDI-outside-EE

Froth

See that's just it...I thought the JNDI name was a sort of key that could be used to lookup the actual connection information, but the more I research this, the bigger JNDI seems to be. I appreciate the high level explaination, but I am trying to find more specifics. It'd be too much to ask of ibm for an example of 'here is how you connect to a websphere data source using jndi from inside a portlet'...</sarcasm> – Disadvantaged

I feel for you. If I had my old codebase handy, I'd try to post some real examples. As it is, know that there are many moving parts that all have to line up for it to work. – Froth

Answer 4

Jason,

This is how it works.

Localnamespace - java:comp/env is a local name space used by the application. The name that you use in it jdbc/db is just an alias. It does not refer to a physical resource.

During deployment this alias should be mapped to a physical resource (in your case a data source) that is defined on the WAS/WPS run time.

This is actually stored in ejb-bnd.xmi files. In the latest versions the XMIs are replaced with XML files. These files are referred to as the Binding files.

HTH Manglu

Chlordane

Answer 5

Find below code to get database connection from your web app server. Just create datasource in app server and use following code to get connection :

// To Get DataSource
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/abcd");
// Get Connection and Statement
Connection c = ds.getConnection();
stmt = c.createStatement();

Import naming and sql classes. No need to add any xml file or to edit anything in project.
That's it..

Emikoemil

Answer 6

For those like me, only needing information on how to connect to a (DB2) WAS Data Source from Java using JNDI lookup (Used IBM Websphere 8.5.5 & DB2 Universal JDBC Driver Provider with implementation class: com.ibm.db2.jcc.DB2ConnectionPoolDataSource):

public DataSource getJndiDataSource() throws NamingException {
    DataSource datasource = null;
    InitialContext context = new InitialContext();
    // Tomcat/Possibly others: java:comp/env/jdbc/myDatasourceJndiName
    datasource = (DataSource) context.lookup("jdbc/myDatasourceJndiName");
    return datasource;
}

Caboose