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