Saturday, January 23, 2021

Setting up Connection pooling via JNDI in TomCat 9.0.41 Java Web Server

This Howto follows official guidelines and might be a bit more straight forward and easier to reproduce then original . That is a way I was able to get JNDI up and running on 9.0.41 release . I have also compiled in one post displaying mariadb's table data via Servlet and via JSP based on utilizing tags supported via the most recent versions of taglibs 1.2.5.

See for details  https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html  

 Overall application file layout .















[tomcat@sever33fedora ConnectionPool]$ ls -CRl

.:

total 8

drwxr-x---. 2 tomcat tomcat  24 Jan 23 20:33 images

-rw-r-----. 1 tomcat tomcat 752 Jan 23 16:22 index.html

drwxr-x---. 3 tomcat tomcat  56 Jan 23 20:33 META-INF

-rw-r-----. 1 tomcat tomcat 558 Jan 23 18:33 showbooks.jsp

drwxr-x---. 5 tomcat tomcat  58 Jan 23 20:33 WEB-INF

./images:

total 4

-rw-r-----. 1 tomcat tomcat 1441 Jan 22 22:02 tomcat.gif

./META-INF:

total 4

-rw-r-----. 1 tomcat tomcat 63 Jan 23 20:24 MANIFEST.MF

-rw-r-----. 1 tomcat tomcat  0 Jan 23 20:33 war-tracker

./WEB-INF:

total 4

drwxr-x---. 3 tomcat tomcat  19 Jan 23 20:33 classes

drwxr-x---. 2 tomcat tomcat 237 Jan 23 20:33 lib

drwxr-x---. 3 tomcat tomcat  47 Jan 23 20:33 src

-rw-r-----. 1 tomcat tomcat 800 Jan 22 18:59 web.xml

./WEB-INF/classes:

total 0

drwxr-x---. 2 tomcat tomcat 36 Jan 23 20:33 mypkg

./WEB-INF/classes/mypkg:

total 4

-rw-r-----. 1 tomcat tomcat 3381 Jan 23 18:32 MySQLDbcpServlet.class

*******************************************************

All required jars placed under WEB-INF/lib    from https://tomcat.apache.org/download-taglibs.cgi

Mysql-connector-java-8.0.23.jar is also present in $CATALINA_HOME/lib and includes in $CLASSPATH of "javac" build of MySQLDbcpServlet.java

*******************************************************

./WEB-INF/lib:

total 4440

-rw-r-----. 1 tomcat tomcat 2415211 Jan 21 18:17 mysql-connector-java-8.0.23.jar

-rw-r-----. 1 tomcat tomcat 1660960 Jan 21 18:17 protobuf-java-3.11.4.jar

-rw-r-----. 1 tomcat tomcat   52794 Jan 23 15:54 taglibs-standard-compat-1.2.5.jar

-rw-r-----. 1 tomcat tomcat  206430 Jan 23 15:54 taglibs-standard-impl-1.2.5.jar

-rw-r-----. 1 tomcat tomcat  163271 Jan 23 15:54 taglibs-standard-jstlel-1.2.5.jar

-rw-r-----. 1 tomcat tomcat   40153 Jan 23 15:54 taglibs-standard-spec-1.2.5.jar

./WEB-INF/src:

total 4

-rw-r-----. 1 tomcat tomcat 2714 Jan 23 18:32 MySQLDbcpServlet.java

*********************************

web.xml of particular application

********************************

[tomcat@sever33fedora ConnectionPool]$ cd WEB*

[tomcat@sever33fedora WEB-INF]$ cat web.xml

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

    <resource-ref>

      <description>DB Connection Pool</description>

      <res-ref-name>jdbc/TestDB</res-ref-name>

      <res-type>javax.sql.DataSource</res-type>

      <res-auth>Container</res-auth>

      <res-sharing-scope>Shareable</res-sharing-scope>

   </resource-ref>

   <servlet>

      <servlet-name>MySQLDbcpExample</servlet-name>

      <servlet-class>mypkg.MySQLDbcpServlet</servlet-class>

   </servlet>

   <servlet-mapping>

      <servlet-name>MySQLDbcpExample</servlet-name>

      <url-pattern>/trydbcp</url-pattern>

   </servlet-mapping>

</web-app>

***************************************************

$CATALINA_HOME/conf/context.xml contains Recource

entry available over all apps on Tomcat 9.0.41

***************************************************

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"

     maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true"

     username="root" password="root" driverClassName="com.mysql.jdbc.Driver"

     url="jdbc:mysql://localhost:3306/ebookshop" />

****************

Servlet code

***************

[tomcat@sever33fedora src]$ cat MySQLDbcpServlet.java

package mypkg;

import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.sql.*;

import javax.sql.DataSource;

import javax.naming.*;

public class MySQLDbcpServlet extends HttpServlet {

   DataSource pool;  // Database connection pool

   @Override

   public void init( ) throws ServletException {

      try {

         // Create a JNDI Initial context to be able to lookup the DataSource

         InitialContext ctx = new InitialContext();

         // Lookup the DataSource, which will be backed by a pool

         //   that the application server provides.

         pool = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");

         if (pool == null)

            throw new ServletException("Unknown DataSource 'jdbc/TestDB'");

      } catch (NamingException ex) {

         ex.printStackTrace();

      }

   }

   @Override

   public void doGet(HttpServletRequest request, HttpServletResponse response)

               throws IOException, ServletException {

      // Set the response message's MIME type

      response.setContentType("text/html;charset=UTF-8");

      // Allocate a output writer to write the response message into the network socket

      PrintWriter out = response.getWriter();

      Connection conn = null;

      Statement  stmt = null;

      try {

         out.println("<!DOCTYPE html>");

         out.println("<html>");

         out.println("<head><title>Qurey Servlet</title></head>");

         out.println("<body bgcolor='C0C0C0'>");

         out.println("<h3>Servlet displayes table content via connection pool</h3>");

         out.println("<br>"); 

         // Get a connection from the pool

         conn = pool.getConnection();

        // Normal JBDC programming hereafter. Close the Connection to return it to the pool

         stmt = conn.createStatement();

         ResultSet rset = stmt.executeQuery("SELECT title, author ,price, qty  FROM books");

         int count=0;

         out.println("<table border='2'>");

    while(rset.next()) {

       out.println("<tr><td>" + rset.getString("title") + ", "

      + rset.getString("author") + ", " + String.valueOf(rset.getDouble("price")) + ", " + Integer.toString(rset.getInt("qty")) +  "</td></tr>");

          ++count;

        }

         out.println("</table>");

         out.println("<p>==== " + count + " rows found =====</p>");

         out.println("</body></html>");

      } catch (SQLException ex) {

         ex.printStackTrace();

      } finally {

         out.close();

         try {

            if (stmt != null) stmt.close();

            if (conn != null) conn.close();  // return to pool

         } catch (SQLException ex) {

             ex.printStackTrace();

         }

      }

   }

}

***********************

JSP showbooks.jsp code

**********************

[tomcat@sever33fedora ConnectionPool]$ cat showbooks.jsp

<%@ 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/TestDB">

select id, title, author, price from books

</sql:query>

<html>

  <head>

    <title>DB Test</title>

  </head>

  <body bgcolor="C0C0C0">

  <h3>JSP displayes table content via tags "c:" and "sql:"</h3>

<table border='2'>

<c:forEach var="row" items="${rs.rows}">

   <tr><td> ${row.id} ;  ${row.title} ; ${row.author} ; ${row.price} </td></tr>

</c:forEach>

</table>

  </body>

</html>



























No comments:

Post a Comment