JSP + JDBC issue

Question

My project contains 4 classes. I don't have idea why it's not working.

ConnectionManager class:

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

public class ConnectionManager {

private static ConnectionManager instance = null;

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

private Connection conn = null;

private ConnectionManager(){

}

public static ConnectionManager getInstance(){
    if(instance==null){
        instance = new ConnectionManager();
    }
    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("Closing Connection");
    try {
        conn.close();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    conn=null;
}
}

DBManager class :

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

public class DbManager {

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

public static void Insert(GetsSets set) throws ClassNotFoundException, SQLException{
    Class.forName("com.mysql.jdbc.Driver");

    String sql = "INSERT INTO register (fname,lname,gender,state,email,password,age,address,skill) VALUES (?,?,?,?,?,?,?,?,?)";

    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, set.getFname());
    pstmt.setString(2, set.getLname());
    pstmt.setString(3, set.getGender());
    pstmt.setString(4, set.getState());
    pstmt.setString(5, set.getEmail());
    pstmt.setString(6, set.getPassword());
    pstmt.setString(7, set.getAge());
    pstmt.setString(8, set.getAddress());
    pstmt.setString(9, set.getSkill());
    pstmt.executeUpdate();

    ConnectionManager.getInstance().close();
}
}

GetSets class :

public class GetsSets {

private String fname;
private String lname;
private String gender;
private String email;
private String state;
private String password;
private String age;
private String address;
private String skill;
private int id;


public String getFname() {
    return fname;
}
public void setFname(String fname) {
    this.fname = fname;
}
public String getLname() {
    return lname;
}
public void setLname(String lname) {
    this.lname = lname;
}
public String getGender() {
    return gender;
}
public void setGender(String gender) {
    this.gender = gender;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}
public String getState() {
    return state;
}
public void setState(String state) {
    this.state = state;
}
public String getPassword() {
    return password;
}
public void setPassword(String password) {
    this.password = password;
}
public String getAge() {
    return age;
}
public void setAge(String age) {
    this.age = age;
}
public String getAddress() {
    return address;
}
public void setAddress(String address) {
    this.address = address;
}
public String getSkill() {
    return skill;
}
public void setSkill(String skill) {
    this.skill = skill;
}
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}}

and the servlet :

import java.io.IOException;
import java.sql.SQLException;

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

@WebServlet("/RegServlet")
public class RegServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

StringBuilder csvSkills = new StringBuilder();

public RegServlet() {
    super();
    // TODO Auto-generated constructor stub
}

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

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
 *      response)
 */
protected void doPost(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {

    String fname = request.getParameter("txtFName");
    String lname = request.getParameter("txtLName");
    String gender = request.getParameter("rdoGender");
    String email = request.getParameter("txtEmail");
    String password = request.getParameter("txtPassword");
    String state = request.getParameter("cboStates");
    String age = request.getParameter("cboAge");
    String address = request.getParameter("txtAddress");
    String skill[] = request.getParameterValues("chkSkills");

    for (String skills : skill) {
        if (csvSkills.length() > 0) {
            csvSkills.append(",");
            csvSkills.append(skills);
        }
        String csvSkill = csvSkills.toString();

        GetsSets sets = new GetsSets();

        sets.setFname(fname);
        sets.setLname(lname);
        sets.setGender(gender);
        sets.setEmail(email);
        sets.setPassword(password);
        sets.setState(state);
        sets.setAge(age);
        sets.setAddress(address);
        sets.setSkill(csvSkill);

        try {
            DbManager.Insert(sets);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("blad w RegServlet");
            e.printStackTrace();
        }
    }

}

}

I receive the following error :

INFO: Server startup in 1171 ms java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/jdbc
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)

(...)

at java.lang.Thread.run(Unknown Source)

Connection Opened

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:

at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1547)

at java.lang.Class.forName0(Native Method)

I also created the other class within the same project, simple jdbc + the same databse and it works perfectly (without jsp and servlet). - it proves that mysql connector is implemented in library correctly. am I right?. Also this class reads the table from localhost. I dont know what is wrong. I wanted to connect servlet with DB.

my project in eclipse: https://i.sstatic.net/uwS7i.jpg

Answer 1

You have to put your jar file in WEB_INF/lib/ folder (just copy paste jar into that folder) and then use Build-path. After that you can see your added jar into web app libraries under Libraries section which I can't see now in your screenshot:)

Answer 2

Add Class.forName in static block in ConnectionManager like this

static {
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace(); // remove this and add appropriate log
    }
}

And Remove the Class.forName from insert method ( you dont required) . You have to register the class before the DriverManager look for the connection ( openConnection())

Keeping the connection as static is not good practice

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

remove this and move to insert method as like this.

public static void Insert(GetsSets set) throws ClassNotFoundException, SQLException{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;

try {
      conn = ConnectionManager.getInstance().getConnection();
String sql = "INSERT INTO register (fname,lname,gender,state,email,password,age,address,skill) VALUES (?,?,?,?,?,?,?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, set.getFname());
pstmt.setString(2, set.getLname());
pstmt.setString(3, set.getGender());
pstmt.setString(4, set.getState());
pstmt.setString(5, set.getEmail());
pstmt.setString(6, set.getPassword());
pstmt.setString(7, set.getAge());
pstmt.setString(8, set.getAddress());
pstmt.setString(9, set.getSkill());
pstmt.executeUpdate();
} catch (Exceptione) {
e.printStackTrace();
}finally{
    ConnectionManager.getInstance().close(); // if possible pass the connection instance to close in DbManager
    }

}

Answer 3

According to his screenshot I guess he already add the jar.

Answer 4

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver clearly states that com.mysql.jdbc.Driver class cannot be found. This class resides in the driver jar provided my Mysql. Just download it from the internet(if you do not already have it) and add it to app server lib or web inf lib.

Answer 5

You have to add driver jar file in class path or you can directly paste file in WEB_INF/lib folder.