I was learning to setup a database connection for my Web Application and I came across this link. I followed its example with TomCat and configured conf/Server.xml with my database information. here is my Conf/server.xml file -
<Resource name="jdbc/MyMagnumFt"
global="jdbc/MyMagnumFt"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@<<myCorrectURL>>"
username="user"
password="pass"
maxActive="100"
maxIdle="20"
minIdle="5"
maxWait="10000"/>
The example in above link asks to make a ResourceLink in context.xml file. So I created a ResourceLink in conf/context.xml file -
<ResourceLink name="jdbc/MyLocalMagnumFt"
global="jdbc/MyMagnumFt"
auth="Container"
type="javax.sql.DataSource" />
Now this example doesn't specify anything about making changes in web.xml file. so I didn't bother with that.
Here is the servlet code with which i'm trying to get connection -
public class ResourcePool extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
InitialContext ctx = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/MyLocalMagnumFt");
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select empid, name from Employee");
lookup() works file and gets datasource information as I coded in server.xml. however, when control goes to getConnection() statement, it exits try block and lands in finally block's rs.close() statement. In console I see following error -
May 15, 2017 4:27:46 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [ResourcePool] in context with path [/myJNDI2] threw exception
java.lang.NullPointerException
at org.ashu.tester.ResourcePool.doGet(ResourcePool.java:65)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Now I explored different posts to configure a datasource as setting up database information in Application's context.xml and making an entry in web.xml but I wanted to give this example a try and setup a global datasource which all application's can use.
Please let me know if I'm missing something here or if you need more details on this. here is the screenshot of my project setup - Project Setup
here is the complete code -
package org.ashu.tester;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class ResourcePool extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
InitialContext ctx = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/MyLocalMagnumFt");
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select empid, name from Employee");
PrintWriter out = response.getWriter();
response.setContentType("text/html");
out.print("<html><body><h2>Employee Details</h2>");
out.print("<table border=\"1\" cellspacing=10 cellpadding=5>");
out.print("<th>Employee ID</th>");
out.print("<th>Employee Name</th>");
while(rs.next())
{
out.print("<tr>");
out.print("<td>" + rs.getInt("empid") + "</td>");
out.print("<td>" + rs.getString("name") + "</td>");
out.print("</tr>");
}
out.print("</table></body><br/>");
//lets print some DB information
out.print("<h3>Database Details</h3>");
out.print("Database Product: "+con.getMetaData().getDatabaseProductName()+"<br/>");
out.print("Database Driver: "+con.getMetaData().getDriverName());
out.print("</html>");
}catch(NamingException e){
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
rs.close();
stmt.close();
con.close();
ctx.close();
} catch (SQLException e) {
System.out.println("Exception in closing DB resources");
} catch (NamingException e) {
System.out.println("Exception in closing Context");
}
}
}
}