error " java.sql.SQLException: Cannot create JDBC driver of class 'com.mysql.jdbc.Dr

[Lisa Austin]

Hi everyone,

I'm currently taking a class to learn java but I'm at a point I don't know if I can move forward. I'm receiving an error "java.sql.SQLException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc://localhost:3306?autoReconnect=true' "

I tried Googling and found a few of the same questions at StackOverflow but the suggestions do not help me.

I'm using MySQL 5.7.13, Workbench is 6.3 and MySQL
mysql-connector-java-5.1.39-bin.jar is in both my WEB-INF folder and now ( as suggested on a StackOverflow ) in my Tomcat lib folder
Tomcat is under C:\tomcat-8.0
context.xml is under META-INF

My LocalMySQLCP class file has some unused variables on it currently. As part of our lesson we refactored a class file from trying to connect one way ( where the user name, password, database URL etc is on the class ) to trying to connect using information in the context.xml. My instructor said it looks like my servlet is trying to connect via. the old way and perhaps I may have left some code on my LocalMySQLCP class file but I can't see anything.

It does seem to connect as a browser comes up but then the error is shown on the console.

I don't see anything here that would use the old way of connecting. Any suggestions please?

My context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc" 
auth="Container"
type="javax.sql.DataSource" 
username="root" 
password="****"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc://localhost:3306?autoReconnect=true"
/>
</Context>

My Servlet

 package lab;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class ServletEEF
 */
@WebServlet("/ServletEEF")
public class ServletEEF extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ServletEEF() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		LocalMySQLCP connect = new LocalMySQLCP();
		connect.isAvailable();
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}

package lab;

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class LocalMySQLCP implements PreparedBaseJDBCPool{
	private Connection connection;
	private String dbURL= "jdbc:mysql://localhost:3306/country?autoReconnect=true&useSSL=false";
	private String username = "root";
	private String password = "Starwars99";
	
	



	public LocalMySQLCP() {
		this.connection = openDB();
	}



	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#isAvailable()
	 */
	@Override
	public boolean isAvailable() {
		return this.connection == null ? false : true;
	}
		
	
	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#executeQuery(java.lang.String, java.lang.String[])
	 */
	@Override
	public ResultSet executeQuery(String query, String[] parms) 
			throws SQLException {
		Statement statement = this.connection.createStatement();
		ResultSet users = statement.executeQuery(query);
		return users;
	}

	
	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#executeUpdate(java.lang.String, java.lang.String[])
	 */
	@Override
	public ResultSet executeUpdate(String query, String [] parms) 
			throws SQLException {
		PreparedStatement statement = this.connection.prepareStatement(query);
		if(parms != null){
			int i = 1;
			for (String parm : parms){
				statement.setString(i, parm);
			}
		}
		ResultSet users = statement.executeQuery();
		return users;
	}

	
    public Connection pooling(){
    	ConnectionPool pool = ConnectionPool.getInstance();
    	Connection connection = pool.getConnection();
    	return connection;
    }

	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#openDB()
	 */
	@Override
	public Connection openDB() {
			connection = pooling();
		return connection;
	}



	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#executeQuery(java.lang.String)
	 */
	
	@Override
	public ResultSet executeQuery(String query) throws SQLException {
		throw new SQLException ("Must supply sub parameter in SQL Query");
	}



	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#executeUpdate(java.lang.String)
	 */
	@Override
	public int executeUpdate(String query) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}



	

	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#printTrace(java.sql.SQLException)
	 */
	@Override
	public void printTrace(SQLException ex) {
		for (Throwable t : ex) {
			t.printStackTrace(System.out);  // stack trace to console
		}
	}

	
	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#setUsername(java.lang.String)
	 */
	@Override
	public void setUsername(String username) {
		this.username = username;
	}




	/* (non-Javadoc)
	 * @see lab.PreparedBaseJDBCPool#setPassword(java.lang.String)
	 */
	@Override
	public void setPassword(String password) {
		this.password = password;
	}
	
	
}

[Lisa Austin]

Also I forgot my ConnectionPool class

package lab;


import java.sql.SQLException;

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

import com.mysql.jdbc.Connection;

public class ConnectionPool {
	private static ConnectionPool pool = null;
	private static DataSource dataSource = null;
	
	
	private ConnectionPool(){
		try{
			InitialContext ic = new InitialContext();
			dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc");
		} catch (NamingException e) {
			System.out.println(e);
		}
	}

	public static synchronized ConnectionPool getInstance(){
		if (pool == null) {
			pool = new ConnectionPool();
		}
		return pool;
	}
	
	public Connection getConnection(){
		try {
			return (Connection) dataSource.getConnection();
		} catch (SQLException e){
			System.out.println(e);
			return null;
		}
	}
	
	public void freeConnection(Connection c) {
		try {
			c.close();
		} catch (SQLException e){
			System.out.println(e);
		}
	}
	
}

[Bear Bibeault]

Where did you put the jar file containing the driver class?

[Lisa Austin]

Bear Bibeault wrote:Where did you put the jar file containing the driver class?

Hey Bear,
It's under my WEB-INF lib folder. I made some changes and I think it seems to be working except for a warning about SSL connection but I think I can ignore a Warning ( right? )

Thank You so much for trying to help. I was getting pretty frustrated here.

[Bear Bibeault]

Glad it's working.

[Stefan Evans]

I'm pretty sure that for Tomcat connection pooling, the driver needs to be in the [TOMCAT]/lib directory (ref: https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html)

My other suggestion would be to clean up those unused variables and methods that you no longer need in the LocalMySQLCP class

[Tim Holloway]

Stefan Evans wrote:I'm pretty sure that for Tomcat connection pooling, the driver needs to be in the [TOMCAT]/lib directory (ref: https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html)

My other suggestion would be to clean up those unused variables and methods that you no longer need in the LocalMySQLCP class

Correct. Connection pools are created by Tomcat and owned by Tomcat. They are not part of the web application and in fact, one pool can be shared by multiple applications. So the driver used by the pool must be located in TOMCAT_HOME/lib.

You can take it as a general rule that any classes or jars referenced in the Tomcat server.xml or context.xml files must be located in TOMCAT_HOME/lib. That's because the server.xml and context xml files are digested by Tomcat itself and the core Tomcat classpath does not include any of the webapp classpaths. so their WEB-INF/classes and WEB-INF/lib directories would be inaccessible to the primary Tomcat processes.