CODEWITHSUNDEEP
javamysqleclipse
kaspertje100
kaspertje100

Reputation: 73

Mysql connection failure in JAVA

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();  
                }  
            }  
        }  
    }
}

Upvotes: 2

Views: 451

Answers (4)

Meena Chaudhary
Meena Chaudhary

Reputation: 10715

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();  
            }  
        }  
    }  
}

}

Upvotes: 2

mevada.yogesh
mevada.yogesh

Reputation: 1128

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.

Upvotes: 1

Sridhar
Sridhar

Reputation: 1962

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;
    }

Upvotes: 2

BarrySW19
BarrySW19

Reputation: 3819

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.

Upvotes: 3

Related Questions