Sunday, April 14, 2013

Step by step JSP and MySQL connection



Step 1: Create a project as in the picture then create web.xml as follows:

<?xml version="1.0" encoding="UTF-8"?>
<web-app
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:web="http://java.sun.com/xml
/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com
/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
id="WebApp_ID" version="3.0">
 
  <display-name>WebApp</display-name>
 
  <welcome-file-list>
    <welcome-file>login.jsp</welcome-file>
  </welcome-file-list>

</web-app>

Step 2: Create login page under WebContent directory as follow:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>. : User Login Page : . </title>
</head>
<body>

<br /><br /><br /><br /><br /><br /><br /><br />
<center>
<form action="loginAction.jsp" name="loginForm" method="post">
      <table style="width:250px;" bgcolor="#FFFFFF" border="0" bordercolor="#FF3300">
            <tr>
                  <td colspan="3" bgcolor="#CCCCCC" align="center" style="height:30px;"> Login Form </td>
            </tr>
            <tr>
                  <td style="height:30px;"> User Name </td>
                  <td> : </td>
                  <td> <input type="text" name="userName" /> </td>
            </tr>
            <tr>
                  <td style="height:30px;"> Password </td>
                  <td> : </td>
                  <td> <input type="password" name="password" />
            </tr>
            <tr>
                  <td colspan="3" align="center" bgcolor="#993300" style="height:30px;"> <input type="submit" value="Submit" name="submit" /> </td>
            </tr>
      </table>
</form>

</center>
</body>
</html>
Step 3: Create DbConnection class under armukul.blogspot.com package as follows:
package armukul.blogspot.com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbConnection {
            private static Connection conn = null;
           
            public static Connection getConnection(){
                        if (conn != null)
                                    return conn;
                        else{
                                    try{
            // Create string of JDBC driver "com.mysql.jdbc.Driver".
                                                String dirver = "com.mysql.jdbc.Driver";
// Create string of connection url //
                                                String jdbcUrl = "jdbc:mysql://localhost:3306/";
                                                String dbName = "stock";
                                                String dbUser = "root";
                                                String dbPass = "";
                                                //load jdbc driver
                                                Class.forName(dirver);
/* Create a connection by using getConnection() method that takes parameters of
string type connection url, user name and password to connect to database. */
                                            conn = DriverManager.getConnection(jdbcUrl + dbName, dbUser, dbPass);
                                                System.out.println ("Connection established");
                                    }catch (ClassNotFoundException cnfe){
                                                cnfe.printStackTrace();
                                    } catch (SQLException sqle){
                                                sqle.printStackTrace();
                                    }
                                    return conn;
                        } //end of else
            }//end of connect method
           
            public static boolean colseConnection(Connection c){
                        try{
                                    c.close();
                                    return true;
                        } catch (SQLException sqle){
                                    sqle.printStackTrace();
                                    return false;
                        }
            }
           
} //end of DbConnection class
Step 4: Create LoginAction.jsp as follows:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="armukul.blogspot.com.*" %>
<%@ page import="java.sql.*" %>   
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title> Login Action </title>
</head>
<body>
<%

String user = request.getParameter("userName");
String pass = request.getParameter("password");

// call getConnection method
try{
      Connection con = DbConnection.getConnection();
      Statement st = con.createStatement();
      String sql = "select * from login where userName ='"+user+"' and password ='"+pass+"'";
      ResultSet rs = st.executeQuery(sql);
      int count = 0;
      while (rs.next()){
            out.println (user);
            count++;
      }
      if (count > 0){
            response.sendRedirect("index.jsp");
      } else {
            response.sendRedirect("login.jsp");
      }
      // colse db connection
      //DbConnection.colseConnection(con);
     
} catch (SQLException sqle){
      sqle.printStackTrace();
}


%>

</body>
</html>
Step 5: Create index.jsp as follows:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="armukul.blogspot.com.*" %>
<%@ page import="java.sql.*" %>
   
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Home Page</title>
</head>
<body>
<br /><br /><br /><br /><br /><br /><br /><br />
<center>

<%
String user = request.getParameter("userName");
%>

Registration is Successfull. Welcome to <%=user %>
     
</center>
</body>
</html>
Step 6: To run the project:
i)  Right click on your project then click on Run on Server from Run as option or
ii) Type http://localhost:8080/WebApp