Search code examples
javaurljdbcwartomcat9

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

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?

Solution

  • 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.