Sunday, April 29, 2012

Tomcat, WebApplication using a DataSource

http://wiki.apache.org/tomcat/UsingDataSources

http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html

copy ojdbc5.jar to $TOMCAT_HOME/lib directory

create your "cmdb" webapp folder under $TOMCAT_HOME/webapps/cmdb

create $TOMCAT_HOME/webapps/cmdb/META-INF/context.xml and put this in it:

<?xml version="1.0" encoding="UTF-8"?>

<Context>

 <Resource name="jdbc/cmdb" auth="Container"
   type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
   url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
   username="cmdb" password="cmdb" maxActive="20" maxIdle="10"
   maxWait="-1"/>
</Context>


in WEB-INF/web.xml put


<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>CMDB</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/cmdb</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>



in WEB-INF\lib put standard.jar and jstl.jar

and your test.jsp is

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/cmdb">
select JMSMODULENAME from NESOA2_JMSMODULES
</sql:query>

<html>
  <head>
    <title>Machines</title>
  </head>
  <body>

  <h2>Results</h2>
  
<c:forEach var="row" items="${rs.rows}">
    JMSMODULENAME ${row.JMSMODULENAME}<br/>
</c:forEach>

  </body>
</html>


If you get errors like

"javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (IO Error: The Network Adapter could not establish the connection)""

and "tnsping" works, it's very possible that you need to ipconfig /renew or something like that...


On another instance of Tomcat, the same WebApp works only if I add

<Context path="/cmdb">
     <ResourceLink name="jdbc/cmdb" type="javax.sql.DataSource" global="jdbc/cmdb"/>
</Context>


No comments: