Connect to mysql database from servlet

Question

I am trying to connect to my database in order to check a username and password. I am following a tutorial but my connection is not working. My question are in these lines:

String url = "jdbc:mysql://localhost:3306/"; 
String dbName = "LoginExample";
String driver = "com.mysql.jdbc.Driver";  
String userName = "root";  
String password = "password"; 

What is the meaning of all those values? Are those standard? If not how can I find which ones correspond to my database. Also I wasn't asked for a username and password when I created my database.

public class Login extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Login() {
        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
    }

    public static boolean validate(String name, String pass) {
        boolean status = false;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/"; 
        String dbName = "LoginExample";
        String driver = "com.mysql.jdbc.Driver";  
        String userName = "root";  
        String password = ""; 

        try {  
            Class.forName(driver).newInstance();  
            conn = DriverManager  
                    .getConnection(url + dbName, userName, password);  

            pst = conn  
                    .prepareStatement("select * from Users where username=? and password=?");  
            pst.setString(1, name);  
            pst.setString(2, pass);  

            rs = pst.executeQuery();  
            status = rs.next();  

        } catch (Exception e) {  
            System.out.println(e);  
        } finally {  
            if (conn != null) {  
                try {  
                    conn.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
            if (pst != null) {  
                try {  
                    pst.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
            if (rs != null) {  
                try {  
                    rs.close();  
                } catch (SQLException e) {  
                    e.printStackTrace();  
                }  
            }  
        }
        return status;
    }

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

        String un = request.getParameter("username");
        String pw = request.getParameter("password");

        response.setContentType("text/html");    
        PrintWriter out = response.getWriter();  

        HttpSession session = request.getSession(false);
        if (session != null) {
            session.setAttribute("name", un);
        }

        if (validate(un,pw)) {
            RequestDispatcher rd=request.getRequestDispatcher("welcome.jsp");    
            rd.forward(request,response); 
        } else {
            out.print("<p style=\"color:red\">Sorry username or password error</p>"); 
        }
    }

}

Error log:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.ConnectException
MESSAGE: Connection refused: connect

STACKTRACE:

java.net.ConnectException: Connection refused: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at java.net.Socket.<init>(Unknown Source)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:271)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at servlets.Login.validate(Login.java:55)
    at servlets.Login.doPost(Login.java:112)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    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)


** END NESTED EXCEPTION **

Answer 1

Url tells the program where and how to find the database. In this case, you use JDBC to access a MySQL database running on your own computer (localhost).

dbname is the name of the database. The MySQL server may host multiple databases, so you need to specify which one you want.

Driver is the Java class that you will use to access the database.

Username and Password explain themselves.

Assuming the port in the url and the database name are correct, your problem is most likely the username/password combination. By default, the username is "root" and the password is empty.

Answer 2

Yes these all are standard you have to use it to connect to your database through java.

    //since you have to connect to database you have to connect through to database server with url(where localhost signifies your current system and 3306 is a port)
    String url = "jdbc:mysql://localhost:3306/"; 

    //In order to connect to specific database specify its name
    String dbName = "LoginExample";

    //Initialize a driver so you can open a communications channel with the database.
    String driver = "com.mysql.jdbc.Driver"; 

    //credentials required to connect 
    String userName = "root";  
    String password = "password"

Since you dint enter any username and password during installation then the default name and password were set.The default username is root and password is no password .You can also reset password.

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

First You do not need to instantiate the driver, just call it's static initialization (e.g., by calling Class.forName) so it gets registered in the DriverManager

So remove .newInstance() from Class.forName(driver).newInstance();

Also check if port 3306 is acquired by other process check this by

RUN resmon.exe and go to network tab to check if 3306 port is acquired by other process or not.

Answer 3

You should not manually try to establish database connection from your servlet. Instead you should define a DataSource and look it up from your servlet;

Step by step guide for tomcat:

1. Put your mysql driver in tomcat's /lib folder

2. Define a datasource in Tomcat's context.xml

   < Resource name="jdbc/DefaultDB"
         type="javax.sql.DataSource"  auth="Container"
         description="Default datasource"
         maxActive="100" maxIdle="30" maxWait="10000"
         username="" password="" 
         driverClassName="com.mysql.jdbc.Driver"
         url="com.mysql.jdbc.Driver"/>
   

3. Reference this data source in your web.xml

   < resource-ref>  
           < res-ref-name>jdbc/DefaultDB</res-ref-name>  
           < res-type>javax.sql.DataSource</res-type>  
           < res-auth>Container</res-auth>  
   < /resource-ref >
   

4. Look up the data source:

   InitialContext ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/DefaultDB");

   As an alternative you can inject the data source:

   @Resource(name="jdbc/DefaultDB") private javax.sql.DataSource ds;

5. Get connection:

Connection connection = ds.getConnection();