Tomcat MySQL Connection DataSource not working

Question

I was hard coding the DB connection in my app, and right now I trying to make it better. The issue is that it seems I do everything that's supposed to be done, and it still fails.

1- Download the binary distribution appropriate for your platform, extract the JAR file, and copy it to "$CATALINA_HOME/lib" - Done.

2- Configure Your MySQL Database as a JNDI Resource. As a Resource in your application's Context elemen and a Resource Reference in your application's "WEB-INF/web.xml" file.

<Resource name="jdbc/storagerDB"

            auth="Container"

            type="javax.sql.DataSource"

            username="root"

            password="admin"

            driverClassName="com.mysql.jdbc.Driver"

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

            maxActive="15"

            maxIdle="3"/>

 <resource-ref>
<description>MySQL Datasource example</description>
<res-ref-name>jdbc/storagerDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>

public class DataAccessObject {

private CustomLogger logger = new CustomLogger(DataAccessObject.class);

private Statement stmt;
private Connection conn;
private DataSource ds;
private Context ctxt;

/**
 * Queries that creates the db.
 */
private static final String[] DB_CREATION_QUERIES = new String[] {

        "CREATE DATABASE IF NOT EXISTS storagerDB;", "USE storagerDB;",

        "CREATE TABLE IF NOT EXISTS user(id int NOT NULL PRIMARY KEY AUTO_INCREMENT,"
                + "username varchar(25) NOT NULL, password varchar(250) NOT NULL,"
                + "salt varchar(250) NOT NULL, email varchar(35) NOT NULL);",

        "CREATE TABLE IF NOT EXISTS header(id int NOT NULL PRIMARY KEY AUTO_INCREMENT,"
                + "user_id int, Foreign Key (user_id) REFERENCES user(id)," + "server varchar(50) NOT NULL,"
                + "content_type varchar(50) NOT NULL," + "status_code int NOT NULL," + "url varchar(250) NOT NULL);"

};

/**
 * Constructor JDBC driver could be initialized and loaded in Tomcat/lib
 * @throws NamingException 
 */
public DataAccessObject() {
    try {
        ctxt = new InitialContext();
        ds = (DataSource)ctxt.lookup("java:comp/env/jdbc/storagerDB");
        conn = ds.getConnection();

        stmt = conn.createStatement();

        for (String query : DB_CREATION_QUERIES) {
            stmt.execute(query);
        }

    }   catch (SQLException | NamingException e) {
        logger.logExceptionMessage("DAO constructor SQL", e.getStackTrace());
    }

}

It fails on "ds = (DataSource)ctxt.lookup("java:comp/env/jdbc/storagerDB")".

Exception in thread "main" javax.naming.NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file:  java.naming.factory.initial
at javax.naming.spi.NamingManager.getInitialContext(Unknown Source)
at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source)
at javax.naming.InitialContext.getURLOrDefaultInitCtx(Unknown Source)
at javax.naming.InitialContext.lookup(Unknown Source)
at com.wordpress.belichev.model.DataAccessObject.<init>(DataAccessObject.java:58)
at com.wordpress.belichev.model.DataAccessObject.main(DataAccessObject.java:279)

Who executed your code? Did it run / was called by Tomcat or did you try to start it as Java Standalone Application? (last option won't work this way!) — Niklas P
Well, actually I run it as a Standalone just to debug it easier, in Tomcat it fails with NullPointer exception on the next line - conn = ds.getConnection(); — Recoba20
Maybe a bug within the configuration. Please try to use two slashes in front of localhost: jdbc:mysql://localhost:3306/storagerDB (but within tomcat!) — Niklas P
Tx for the response, I tried it, but its still throwing the null pointer. — Recoba20
Regarding the NPE you get afterwards, my best guess is that you missed this part "As a Resource in your application's Context element" (I suspect this because you have not copied that file here). I am talking about the 2nd point of this tutorial: mkyong.com/tomcat/how-to-configure-mysql-datasource-in-tomcat-6 . — Gergely Bacso

Gergely Bacso Gergely Bacso · Accepted Answer · 2015-08-19T13:32:06

The reason you are getting this NoInitialContextException is that you are running this application as a standalone app. This will simply ignore your web.xml file, with all your resource-definition with it. You need to run it as a webapp to even give it a chance to work.

Getting an NPE in the next line (as you mentioned in your comment) is due to your app not having found a datasource after the context lookup. That is a different a problem, likely also related to your web.xml usage. I suggest taking a look at this post to see the main points.

Tomcat MySQL Connection DataSource not working

1 Answers