Many years ago a sample web application for using JDBC was published as part of the oracle-db-examples GitHub repository. The HR Web Application example was the starting point for some to build their first simple web interface to their database. Who knows how many simple, in-house applications have this as their inspiration?
The example had a particular focus on Apache Tomcat and the steps to getting it built and deployed where simple for the standard Apache Tomcat setup at the time.
-- Get the code
git clone https://github.com/oracle/oracle-db-examples.git
cd oracle-db-examples/java/HRWebApp
-- Copy the tomcat-users.xml and start tomcat
cp tomcat-users.xml $CATALINA_HOME/conf
catalina.sh start
-- Build the war file and deploy it
mvn package
cp target/JdbcWebSamples.war $CATALINA_HOME/webapps
By copying the JdbcWebSamples.war into the $CATALINA_HOME/webapps directory, the web application is automatically deployed by Apache Tomcat. The context path is based on the file name so the URL is http://localhost:8080/JdbcWebSamples/
Use your browser to access the web application.
The tomcat-users.xml defined two new users: hradmin and hrstaff. Both have welcome as their password. Login and click on the List All menu item to see the list of HR.EMPLOYEES records. Of course, that’s if the database connection details are correct.
Your JdbcBeanImpl.java will have to be changed to point to the correct database in the getConnection() method.
public static Connection getConnection() throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@//mydatabaseserver:1521/orclpdb1",
"hr",
"hr");
return connection;
}
Make the above change for your database, run mvn package and copy the JdbcWebSamples.war to $CATALINA_HOME/webapps to get that running with the new connection details.
Take a look through JdbcBeanImpl methods in detail and you’ll notice that getConnection() method is called using the try-with-resource syntax so the connection is automatically closed after every operation. It’s a good practice because it does not leave INACTIVE sessions on the database. In fact, if you check your database v$session you shouldn’t find any records.
SELECT
*
FROM
v$session
WHERE
program = 'JDBC Thin Client'
AND schemaname = 'HR';
Creating a database connection for every request does have an overhead and if that is a remote database the network latency could be sufficient to cause problems for the users of the web application. To see the impact of creating a connection every time, let’s add some basic logging on the elapsed time to that getConnection() method.
public static Connection getConnection() throws SQLException {
final long start = System.currentTimeMillis();
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@//mydatabaseserver:1521/orclpdb1",
"hr",
"hr");
final long end = System.currentTimeMillis();
logger.log(Level.INFO,
"Creating a connection duration(ms): " + (end - start));
return connection;
}
Like before, make the above change for your database, run mvn package and copy the JdbcWebSamples.war to $CATALINA_HOME/webapps. Every time the list of employees is retrieved, or any action that involves the connection, the cataline.out will show a log message.
One way to address this is by using Oracle’s Universal Connection Pool. This could be used through a data source defined in the Tomcat configuration but it can also be done used programmatically. Let’s do that.
The first thing is to upgrade to a more recent version of JDBC/UCP. Not the latest 21.1 version, more on that later, but we’ll use 19.9.0.0 for now. Note that the group id has changed from com.oracle.jdbc to com.oracle.database.jdbc and that’s what we’ll use in the pom.xml
Change this
<dependency>
<groupId>com.oracle.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0.1</version>
</dependency>
to this
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.9.0.0</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ucp</artifactId>
<version>19.9.0.0</version>
</dependency>
Now add a class to create and configure a Universal Connection Pool PoolDataSource. Let’s call it JdbcSource in the com.oracle.jdbc.samples.bean package.
package com.oracle.jdbc.samples.bean;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
public class JdbcSource {
JdbcSource() {
//Create pool-enabled data source instance.
this.pds = PoolDataSourceFactory.getPoolDataSource();
//set the connection properties on the data source.
try {
pds.setConnectionPoolName(POOL_NAME);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//mydatabaseserver:1521/orclpdb1");
pds.setUser("hr");
pds.setPassword("hr");
//Override any pool properties.
pds.setInitialPoolSize(2);
} catch (SQLException ex) {
logger.log(Level.SEVERE, null, ex);
ex.printStackTrace();
}
}
public Connection connection() throws SQLException {
return this.pds.getConnection();
}
private final PoolDataSource pds;
private static final String POOL_NAME = "JdbcWebSamples_pool";
// Singleton data source. Not a great pattern but simple for demonstrations.
public static JdbcSource INSTANCE = new JdbcSource();
static final Logger logger = Logger.getLogger("com.oracle.jdbc.samples.bean.JdbcSource");
}
The above will create a pool of database connections that can be reused every time connection() method is called. The pool is initialised with 2 connections. That means there will be two sessions on the database that will be INACTIVE most of the time. The web application may deal with concurrent requests so having an extra connection ready will help with that additional load.
Let’s revisit that JdbcBeanImpl getConnection() method and change it to use the new pooled connection. It’s not all that complicated…
public static Connection getConnection() throws SQLException {
final long start = System.currentTimeMillis();
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection = JdbcSource.INSTANCE.connection();
final long end = System.currentTimeMillis();
logger.log(Level.INFO,
"Creating a connection duration(ms): " + (end - start));
return connection;
}
Like before, make the above for your code, run mvn package and copy the JdbcWebSamples.war to $CATALINA_HOME/webapps. Everytime the list of employees is retrieved, or any action that involves the connection, the cataline.out will show a log message for the first connection taking a long time, but every subsequent call takes milliseconds.
The query on v$sessions will show 2 INACTIVE sessions at least. As more concurrent requests are received, the pool size will grow automatically so more sessions could be created. In fact, hit that List All menu item repeatedly 20 or 30 times and you’ll see the number of v$sessions for HR schema grow. There’s more to explore here on setting UCP properties for optimising pool behaviour.
Upgrade to JDBC / UCP 21.1
Let’s leave that as a bonus point exercise for you and discuss upgrading to 21.1.0.0. This version has support for defining data sources in JBoss and Spring. That enhancement does break these example web applications which include the UCP jars in them.
Update the pom.xml to use the new 21.1.0.0 JDBC and UCP jars.
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>21.1.0.0</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ucp</artifactId>
<version>21.1.0.0</version>
</dependency>
Rebuild and deploy to see the following SEVERE error messages.
SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.startInternal One or more listeners failed to start. Full details will be found in the appropriate container log file
SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.startInternal Context [/JdbcWebSamples] startup failed due to previous errors
The localhost log will have more details.
SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.listenerStart Error configuring application listener of class [oracle.ucp.jdbc.UCPServletContextListener]
java.lang.NoSuchMethodException: oracle.ucp.jdbc.UCPServletContextListener.<init>()
at java.base/java.lang.Class.getConstructor0(Class.java:3427)
at java.base/java.lang.Class.getConstructor(Class.java:2165)
at org.apache.catalina.core.DefaultInstanceManager.newInstance(DefaultInstanceManager.java:151)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4607)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5146)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:717)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:690)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:705)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:978)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1849)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.base/java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:118)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:773)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:427)
at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1620)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:305)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1151)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1353)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1357)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1335)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:832)
SEVERE [Catalina-utility-1] org.apache.catalina.core.StandardContext.listenerStart Skipped installing application listeners due to previous error(s)
Put simply there is an annotated class that Tomcat discovers in the web application classpath and tries to initialise it, and it fails. It’s not a great work around, but a quick fix is to use the web.xml to tell Tomcat the names of components it should attempt to initialise. In this example web application there are two servlets defined: GetRole and WebController. These are in the com.oracle.jdbc.samples.web package. We’ll mention their names explicitly in the web.xml. Add this absolute-ordering entry just after the login-config.
</login-config>
<absolute-ordering>
<name>GetRole</name>
<name>WebController</name>
</absolute-ordering>
</web-app>
It may not be a feasible work around for all, and it sort of defeats the purpose of having annotated servlets, but explicitly mentioning their names in absolute-ordering makes upgrading the JDBC/UCP jars possible.
Peter's Random Topic Library 
thank you so much!
LikeLike