Mysql connection failure in JAVA

Question

I am using eclipse and tomcat7. I did import the mysql jar file and it is inside the tomcat folder. The error I get when I am trying to register on my page:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/mydb
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at connectionDb.openConnection(connectionDb.java:27)
    at connectionDb.getConnection(connectionDb.java:37)
    at DbManager.<clinit>(DbManager.java:7)
    at registrationServlet.doPost(registrationServlet.java:64)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Connection Opened
java.lang.NullPointerException

my code:

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

public class connectionDb {

    private static connectionDb instance = null;

    private final String USERNAME = "root";
    private final String PASSWORD = "mypass";
    private final String CONN_STRING = "jdbc:mysql://localhost:3306/mydb";

    private Connection conn = null;

    private connectionDb() {
    }

    public static connectionDb getInstance() {
        if (instance == null) {
            instance = new connectionDb();
        }
        return instance;
    }

    private boolean openConnection() {
        try {
            conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }

    public Connection getConnection() {
        if (conn == null) {
            if (openConnection()) {
                System.out.println("Connection Opened");
                return conn;
            } else {
                return null;
            }
        }
        return conn;
    }
    public void close(){
        System.out.println("Close connection");
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        conn=null;
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DbManager {

    private static Connection conn = connectionDb.getInstance().getConnection();

    public static void Insert(getset set) throws  ClassNotFoundException , SQLException {
        String driver = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driver).newInstance();

            // insert username and password
            String sql = "INSERT INTO lonininfo(username, password) VALUES (?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, set.getUserName());
            pstmt.setString(2, set.getPassword());
            pstmt.executeUpdate();
            // insert user info
            String sql1 = "INSERT INTO userinfo(fullName, email, dateOfBirth, phoneNumber, companyName, companyEmail, paymentMethod) VALUES (?,?,?,?,?,?,?)";
            PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1, set.getFullName());
            pstmt1.setString(2, set.getEmail());
            pstmt1.setString(3, set.getDateOfBirth());
            pstmt1.setString(4, set.getPhoneNumber());
            pstmt1.setString(5, set.getCompanyName());
            pstmt1.setString(6, set.getCompanyEmail());
            pstmt1.setString(7, set.getPaymentMethod());
            pstmt1.executeUpdate();
            connectionDb.getInstance().close();
        } catch (Exception e) {  
            System.out.println(e);  
        } finally {  
            if (conn != null) {  
                try {  
                    conn.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }
}

where did you placed the mysql driver jar ?

Josef Prochazka
– Josef Prochazka

2014-11-21 10:46:50 +00:00
Commented Nov 21, 2014 at 10:46 — Josef Prochazka
– Josef Prochazka, Commented Nov 21, 2014 at 10:46

BarrySW19 · Accepted Answer · 2014-11-21 10:46:27Z

3

Probably the openConnection() is being called before Class.forName(...) is ever executed. I'd try moving this call to the openConnection() method instead of the Insert() method.

answered Nov 21, 2014 at 10:46

BarrySW19

3,81715 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sridhar · Accepted Answer · 2014-11-21 10:54:13Z

2

The driver class must be loaded first, then make the JDBC connection. Move Class.forName inside openConnection method like below, it should work. Make sure mysql driver jar is in the classpath.

private boolean openConnection() {
        try {
             String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }

answered Nov 21, 2014 at 10:54

Sridhar

1,9923 gold badges24 silver badges46 bronze badges

Comments

Meena Chaudhary · Accepted Answer · 2014-11-21 11:13:14Z

Try this

  public class DbManager {

private static Connection conn;

public static void Insert(getset set) throws  ClassNotFoundException , SQLException {
    String driver = "com.mysql.jdbc.Driver";
    try {
        Class.forName(driver).newInstance();
        conn = connectionDb.getInstance().getConnection();
        // insert username and password
        String sql = "INSERT INTO lonininfo(username, password) VALUES (?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, set.getUserName());
        pstmt.setString(2, set.getPassword());
        pstmt.executeUpdate();
        // insert user info
        String sql1 = "INSERT INTO userinfo(fullName, email, dateOfBirth, phoneNumber, companyName, companyEmail, paymentMethod) VALUES (?,?,?,?,?,?,?)";
        PreparedStatement pstmt1 = conn.prepareStatement(sql1);
        pstmt1.setString(1, set.getFullName());
        pstmt1.setString(2, set.getEmail());
        pstmt1.setString(3, set.getDateOfBirth());
        pstmt1.setString(4, set.getPhoneNumber());
        pstmt1.setString(5, set.getCompanyName());
        pstmt1.setString(6, set.getCompanyEmail());
        pstmt1.setString(7, set.getPaymentMethod());
        pstmt1.executeUpdate();
        connectionDb.getInstance().close();
    } catch (Exception e) {  
        System.out.println(e);  
    } finally {  
        if (conn != null) {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
}

}

mevada.yogesh · Accepted Answer · 2014-11-21 10:58:27Z

1

From error stack trace it seems like you are calling openConnection method first. And your Class.forName(driver) call is in Insert method. So before loading class you are trying to open connection. So please move Class.forName(driver) and put it outside where you can call it before openConnection. Also you need not require to call Class.forName(driver) every time before Insertion or any DB manipulation call.

answered Nov 21, 2014 at 10:58

mevada.yogesh

1,1383 gold badges12 silver badges37 bronze badges

Collectives™ on Stack Overflow

Mysql connection failure in JAVA

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related