Mysql driver problems

Question

This is my DAO:

package be.pxl.ticket.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import be.pxl.ticket.bean.TicketBean;

public class TicketDAO {
    private String url;
    private String user;
    private String password;

public TicketDAO(String url, String user, String password) {
    this.url = url;
    this.user = user;
    this.password = password;
}

public void setDriver(String driver)
         throws ClassNotFoundException {
      // Laad de driver
       Class.forName(driver);
   }

public TicketBean getTicketById(int id) {
    if (id > 0) {
        try (Connection con = getConnection();
                PreparedStatement stmt = con.prepareStatement(
                        "SELECT id, state, location, description, title, timeCreated, submitBy, assignedTo from tickettable WHERE id=?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    TicketBean ticket = new TicketBean();
                    ticket.setId(id);
                    ticket.setState(rs.getString("state"));
                    ticket.setLocation(rs.getString("location"));
                    ticket.setDescription(rs.getString("description"));
                    ticket.setTitle(rs.getString("title"));
                    ticket.setTimeCreated(rs.getDate("timeCreated"));
                    ticket.setSubmitBy(rs.getString("submitBy"));
                    ticket.setAssignedTo(rs.getString("assignedTo"));
                    return ticket;
                } else {
                    return null;// als ticket niet wordt gevonden
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("Failed to get Ticket");
                return null;
            }
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
            System.out.println("Failed to get Ticket 2");
            return null;
        }
    } else {
        System.out.println("Id must be greater than 0");
        return null;
    }
}

private Connection getConnection() throws SQLException {
       return DriverManager.getConnection(url, user, password);
}

public void setUrl(String url) {
    this.url = url;
}

public void setUser(String user) {
    this.user = user;
}

public void setPassword(String password) {
    this.password = password;
}

}

This is my servlet:

package be.pxl.ticket.servlet;

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;

import be.pxl.ticket.bean.TicketBean;
import be.pxl.ticket.dao.TicketDAO;
import be.pxl.ticket.service.ITicketService;
import be.pxl.ticket.service.TicketService;

@WebServlet("/Ticket")
public class TicketServlet extends HttpServlet {
    private ITicketService service;

    public void init() throws ServletException{
        super.init();
        service = new TicketService();
        service.setTicketDAO(new TicketDAO("mysql.jdbc://localhost/ticketdb1","root",""));
    }

    public void doGet(HttpServletRequest req, HttpServletResponse res) {
        int id = Integer.parseInt(req.getParameter("id"));
        TicketBean ticket = service.getTicketById(id);
        System.out.println(id);
        req.setAttribute("tb", ticket);
        try {
            req.getRequestDispatcher("TicketView.jsp").forward(req, res);//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
        } catch (ServletException e) {
            System.out.println("servlet-exception :0");
            e.printStackTrace();
        } catch (IOException e) {
            System.out.println("something wrong with IO");
            e.printStackTrace();
        }

        System.out.println("triggered doGet");
    }

    public void doPost(HttpServletRequest req, HttpServletResponse res) {
        System.out.println("triggered doPost");
    }

}

and this is my Service:

package be.pxl.ticket.service;

import java.sql.SQLException;

import be.pxl.ticket.bean.TicketBean;
import be.pxl.ticket.dao.TicketDAO;

public class TicketService implements ITicketService {
    private TicketDAO ticketDAO;

    @Override
    public TicketBean getTicketById(int id){
        // TODO Auto-generated method stub
            return ticketDAO.getTicketById(id);

    }

    public TicketDAO getTicketDAO() {
        return ticketDAO;
    }

    public void setTicketDAO(TicketDAO ticketDAO) {
        this.ticketDAO = ticketDAO;
    }

}

The error message is:

java.sql.SQLException: No suitable driver found for mysql.jdbc://localhost/ticketdb1
Failed to get Ticket 2
1
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at be.pxl.ticket.dao.TicketDAO.getConnection(TicketDAO.java:68)
    at be.pxl.ticket.dao.TicketDAO.getTicketById(TicketDAO.java:31)
    at be.pxl.ticket.service.TicketService.getTicketById(TicketService.java:14)
    at be.pxl.ticket.servlet.TicketServlet.doGet(TicketServlet.java:28)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)
triggered doGet

I have no idea what's wrong here. I have the mysql.jar in my library and the jstl.jar

It says something with the driver but its set so

Answer 1

check the line while connecting to server

service.setTicketDAO(new TicketDAO("mysql.jdbc://localhost/ticketdb1","root",""));

It should be

service.setTicketDAO(new TicketDAO("jdbc:mysql://localhost:3306/ticketdb1","root",""));

Check the database url, I changed it.

here 3306 is default mysql port number change if different in your case.

Also you have to load the drivers for mysql connector using

Class.forName("com.mysql.jdbc.Driver");

and need to include mysql-connector.jar file.

Hope it'll help.

Answer 2

Here is your problem with your URL

service.setTicketDAO(new TicketDAO("mysql.jdbc://localhost/ticketdb1","root",""))

try this jdbc:mysql://localhost/dbname instead of jdbc.mysql://localhost/dbname

Edit :1

Try putting the driver jar in the server lib folder. ($CATALINA_HOME/lib)

I believe that the connection pool needs to be set up even before the application is instantiated.

Answer 3

The JDBC JAR belongs in the Tomcat /lib folder. From these docs:

It means that only libraries visible to the listener such as the ones in $CATALINA_BASE/lib will be scanned for database drivers.

Your database URL is wrong, too. Look at the docs again. Your URL is:

mysql.jdbc://localhost/ticketdb1

It should be:

jdbc:mysql://localhost/ticketdb1

You aren't using root access for your application, are you? That's lazy and dangerous. Create a new user for your app and GRANT appropriate permissions.

This class connects to MySQL running on my machine. It gives some interesting messages that you'll find helpful. Take note of the JAR version and the driver class name.

package database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * Database utilities
 * Created by Michael
 * Creation date 5/3/2016.
 * @link https://stackoverflow.com/questions/36999860/mysql-driver-problems/37000276#comment61553720_37000276
 */
public class DatabaseUtils {

    public static final String DEFAULT_DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String DEFAULT_URL = "jdbc:mysql://localhost/world";
    public static final String DEFAULT_USERNAME = "root";
    public static final String DEFAULT_PASSWORD = "xxx";

    public static void main(String[] args) {
        Connection connection = null;
        try (connection = createConnection(DEFAULT_DRIVER, DEFAULT_URL, DEFAULT_USERNAME, DEFAULT_PASSWORD)) {
            DatabaseMetaData meta = connection.getMetaData();
            System.out.println(String.format("Connected to %s version %s", meta.getDatabaseProductName(), meta.getDatabaseProductVersion()));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection createConnection(String driverClass, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driverClass);
        return DriverManager.getConnection(url, username, password);
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

I use JDK 8 and this dependency in my pom.xml:

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.2</version>
    </dependency>

Here's the output I got:

Tue May 03 05:48:30 EDT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connected to MySQL version 5.7.11-log