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     lt  xml version  1 0  encoding  UTF-8   gt   lt 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  gt       lt display-name gt PeformanceAppraisalStatus lt  display-name gt       lt welcome-file-list gt           lt welcome-file gt index html lt  welcome-file gt           lt welcome-file gt index htm lt  welcome-file gt           lt welcome-file gt index jsp lt  welcome-file gt           lt welcome-file gt default html lt  welcome-file gt           lt welcome-file gt default htm lt  welcome-file gt           lt welcome-file gt default jsp lt  welcome-file gt       lt  welcome-file-list gt       lt resource-ref gt           lt description gt          Datasource connection to Db lt  description gt           lt res-ref-name gt jdbc db lt  res-ref-name gt           lt res-type gt javax sql DataSource lt  res-type gt           lt res-auth gt Container lt  res-auth gt           lt res-sharing-scope gt Shareable lt  res-sharing-scope gt       lt  resource-ref gt   lt  web-app gt    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   lt resource-ref gt             lt description gt      Datasource connection to db lt  description gt       lt res-ref-name gt jdbc db lt  res-ref-name gt       lt res-type gt javax sql DataSource lt  res-type gt       lt res-auth gt Container lt  res-auth gt       lt res-sharing-scope gt Shareable lt  res-sharing-scope gt       lt mapped-name gt jdbc db lt  mapped-name gt         lt  resource-ref gt   ibm-web-bnd xml   lt  xml version  1 0  encoding  UTF-8   gt   lt 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  gt        lt virtual-host name  default host    gt         lt resource-ref name  jdbc db  binding-name  jdbc mydatasource    gt   lt  web-bnd gt    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    lt resource-ref name  jdbc db  binding-name  jdbc mydatasource    gt    Websphere Portal seemed appeased  My code is working and connecting to the database now

User · Answer

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..

User · Answer

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  amp  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

User · Answer

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    lt resource-ref gt       lt res-ref-name gt jdbc xxxx lt  res-ref-name gt       lt res-type gt javax sql DataSource lt  res-type gt       lt res-auth gt Container lt  res-auth gt       lt res-sharing-scope gt Shareable lt  res-sharing-scope gt   lt  resource-ref gt    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

User · Answer

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

User · Answer

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     lt resource-ref gt       lt description gt Resource reference to my database lt  description gt       lt res-ref-name gt jdbc MyDB lt  res-ref-name gt       lt res-type gt javax sql DataSource lt  res-type gt       lt res-auth gt Container lt  res-auth gt       lt res-sharing-scope gt Shareable lt  res-sharing-scope gt   lt  resource-ref gt    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

User · Answer

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

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

Examples related to java

Examples related to db2

Examples related to websphere

Examples related to jndi

Examples related to ibm-rational