How to access Database from JSP Web page running in Tomcat

Configure Database connection strings for Tomcat via Server.xml configuration file:

…TomcatConfServer.xml

Configure DB connection entry in GlobalNamingResources and Realm tags

<?xml version="1.0" encoding="UTF-8"?>
<Server>
    <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
    <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener"/>
    <GlobalNamingResources>
        <Environment name="simpleValue" type="java.lang.Integer" value="30"/>
        <Resource auth="Container" description="User database that can be updated and saved" name="UserDatabase" type="org.apache.catalina.UserDatabase" pathname="conf/tomcat-users.xml" factory="org.apache.catalina.users.MemoryUserDatabaseFactory"/>
        <Resource name="jdbc/myprod" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" password="myP@ssw0rD" maxIdle="5" maxWait="5000" username="myprod" url="jdbc:oracle:oci8:@inst03" maxActive="60"/>
    </GlobalNamingResources>
    <Service name="Catalina">
        <Connector port="8080" redirectPort="8443" minSpareThreads="25" connectionTimeout="20000" maxSpareThreads="75" maxThreads="150">
        </Connector>
        <Connector port="8009" redirectPort="8443" connectionTimeout="-1" protocol="AJP/1.3">
        </Connector>
        <Engine defaultHost="localhost" name="Catalina">
        <Realm className="com.myprod.security.tomcat.JDBCRealm2" connectionName="myproddb" connectionPassword="myP@ssw0rD" connectionURL="jdbc:oracle:oci8:@inst03" driverName="oracle.jdbc.driver.OracleDriver" roleNameCol="rolename" userCredCol="password" userNameCol="login" userRoleTable="my_user_rolelist_v" userTable="my_users_auth_v" validate="true"/>
        <Host appBase="webapps" name="localhost">
            <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" prefix="access_log-www." suffix=".txt" pattern="%h %l %u %t &quot;%r&quot; %s %b %{JSESSIONID}c" resolveHosts="false"/>
            <Valve className="org.apache.catalina.authenticator.SingleSignOn" debug="0"/>
        </Host>
        </Engine>
    </Service>
</Server>

In your web app JSP page say gen-reports.jsp, use (DataSource)ctx.lookup(“java:comp/env/jdbc/myprod”); to establish connection to your DB:

<%@ page import="com.myprod.db.*" %><%@ page import="java.sql.*" %><%@ page import="com.myprod.customer.*"%><%@ page import="com.myprod.beans.*"%><%@ page import="java.util.*" %><%@ page import="java.text.DecimalFormat" %><%@ page import="javax.sql.*" %><%@ page import="javax.naming.*"%><%

        DataSource dataSource;

        Context ctx = new InitialContext();
        dataSource = (DataSource)ctx.lookup("java:comp/env/jdbc/myprod");

Once connection is established you can run your query:

<%
        DataSource dataSource;

        Context ctx = new InitialContext();
        dataSource = (DataSource)ctx.lookup("java:comp/env/jdbc/myprod");

    Connection conn = null;
    Statement stmt = null;

    try {
        conn = dataSource.getConnection();
        stmt = conn.createStatement();

        String query = "select to_char(timeofday,'MM/DD/YYYY:HH24'), " +
            "max(cnt) from usage_Stats";


        ResultSet rs = stmt.executeQuery(query);

        while(rs.next()) {
%><%= rs.getString(1) %>:00, <%= rs.getInt(2) %>
<%
        }
    } catch(Exception e) {
        e.printStackTrace();
        throw new JspException("Internal error: " + e.toString());

    } finally {
        if(stmt != null) {
            try { stmt.close(); } catch(Exception e) {}
        }
        if(conn != null)
            try { conn.close(); } catch(Exception e) {}
    }
%>