Problems with JDBC URL in tomcat 9 context.xml for treeAnno

Question

I have a fresh tomcat 9 installation on a server (Ubuntu 18.04). I am trying to set up treeanno (https://github.com/nilsreiter/treeanno/releases) and deploy the web application archive (war) which is given in the github repos. I used the tomcat manager app in order to deploy the war-file, and followed this description to configure tomcat to use the respective database resource: https://github.com/nilsreiter/treeanno/blob/master/INSTALL.md. According to the meagre description, this shall be added in the context.xml of the tomcat installation:

<Resource name="treeanno/jdbc" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           username="USERNAME" password="PASSWORD" driverClassName="com.mysql.jdbc.Driver"
           url="DATABASE URL"/>

As I am new to tomcat, it is unclear to me what properties I need to set, especially the "url". If I try to access the app, I get the following error, pointing at that no data base connection can be build since the url is not adequate:

    Type Exception Report

    Message An exception occurred processing [/index.jsp] at line [12]

    Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

org.apache.jasper.JasperException: An exception occurred processing [/index.jsp] at line [12]

9:      doctype-public="-//W3C//DTD XHTML 1.0 Transitional//EN"
10:         doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
11:         omit-xml-declaration="false" />
12: <sql:query var="rs" dataSource="treeanno/jdbc" sql="select id, username from treeanno_users">
13: </sql:query>
14: <html xmlns="http://www.w3.org/1999/xhtml">
15: <head>


Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:626)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:500)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

Root Cause

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:mysql:3306'"
    org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:666)
    org.apache.jsp.index_jsp._jspService(index_jsp.java:270)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:477)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

Root Cause

javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:mysql:3306'"
    org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(QueryTagSupport.java:285)
    org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(QueryTagSupport.java:168)
    org.apache.jsp.index_jsp._jspx_meth_sql_005fquery_005f0(index_jsp.java:296)
    org.apache.jsp.index_jsp._jspService(index_jsp.java:134)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:477)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

I already figured out that the url is supposed to link to a database server including password and user, but this does not seem to be appropriate for this config file.

The following I found here (https://ci.apache.org/projects/tomcat/tomcat9/docs/jndi-datasource-examples-howto.html):

url=jdbc:mysql://localhost:3306/javatest"/

but adjusting the port etc. does not work: url=jdbc:mysql://localhost:8080/TreeAnno-1.0.2"/

Now, how do I need to formulate the url to allow the app to connect to / initiate a MySQL database?

`jdbc:mysql:3306` is not a valid JDBC connection url... In any case, it sounds like you don't have the MySQL Connector/J driver on the claspath of tomcat itself. — Mark Rotteveel, Feb 14 '20 at 18:56

score 1 · Answer 1 · answered Feb 14 '20 at 17:30

There are various ways to configure a JDBC data connection for a Tomcat-hosted web application.

JNDI Connection in Tomcat

In your notes you describe how you are using a Tomcat Resource entry, added to the Tomcat context.xml file. When I use this approach, I also add an entry to my web application's web.xml file, which refers to the Resource entry. For example:

<resource-ref>
    <res-ref-name>treeanno/jdbc</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Note how the resource reference name in my web.xml file matches the Resource name.

Having done that, I can now refer to that data source in my application's Java code as follows:

// ABSOLUTELY NOT PRODUCTION-READY CODE! JUST FOR TESTING!

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

...

Context initialContext = new InitialContext();
Context context = (Context) initialContext.lookup("java:comp/env");
DataSource ds = (DataSource) context.lookup("treeanno/jdbc");

What does that java:comp/env do? See here.

But First Test Your URL

You may want to independently ensure that you have the correct JDBC connection URL (and user and pass), before you get into the above configuration.

No amount of tinkering will help if you have the wrong URL, or the data source is not available, etc.

How to test? Various ways - for example:

Use a tool such as DBeaver. This will ask for a URL, user, and password to connect to MySQL.

Or write a separate stand-alone Java app (with a main method) and a basic JDBC connection.

Or any other way that focuses only on the MySQL connection details, outside of the Tomcat configuration complications.

And as @CHN pointed out: this is all moot if you don't have the mysql JDBC jar file.

Like I say, this is only one way to get the job done - but it has worked for me.

Ken Chan · Accepted Answer · 2020-02-14T18:14:47.007

First of all , you need to install MySQL (Or install with docker if you are familiar with it) .

During installation , I remember it will ask you to set the password for the root (which is the default username). After installation , create a new database .

Assume you name the database as foo , the <Resource/> in context.xml should update to :

<Resource name="treeanno/jdbc" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           username="root" password="passwordOfTheRoot" driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/foo"/>

Then download MySQL JDBC driver, and put it inside the $TOMCAT_ROOT_FOLDER/lib .Then restart Tomcat and I believe the application will then connect to the foo database and create the tables for you.

Thank you very much for your help, it works now! Basically, the main problem was the missing mysql.jar in the tomcat root folder. After that, fixing the url fixed the issue :) — CLpragmatics, Feb 15 '20 at 12:33

Problems with JDBC URL in tomcat 9 context.xml for treeAnno

2 Answers2

JNDI Connection in Tomcat

But First Test Your URL