Java JSP MySql CRUD Project

 I have shown how to create Create Read Update Delete (CRUD) in Java Server Pages(JSP).

1. Project Structure in Eclipse
2. DB_Connection.java

   package common;
   import java.sql.Connection;
   import java.sql.DriverManager;
   public class DB_Connection {
   public static void main(String[] args) {
   	DB_Connection obj_DB_Connection=new DB_Connection();
   	System.out.println(obj_DB_Connection.get_connection());
   }
   public Connection get_connection(){
   	Connection connection=null;
   	try {
   	Class.forName("com.mysql.jdbc.Driver");
   connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_crud","root", "root");
   	} catch (Exception e) {
   		System.out.println(e);
   	}
   		return connection;
   	}
   }
   

3. User_Bean.java

   package common;
   public class User_Bean {
   	private String sl_no;
   	private String user_name;
   	private String email;
   	private String mobile;
   	public String getSl_no() {
   		return sl_no;
   	}
   	public void setSl_no(String sl_no) {
   		this.sl_no = sl_no;
   	}
   	public String getUser_name() {
   		return user_name;
   	}
   	public void setUser_name(String user_name) {
   		this.user_name = user_name;
   	}
   	public String getEmail() {
   		return email;
   	}
   	public void setEmail(String email) {
   		this.email = email;
   	}
   	public String getMobile() {
   		return mobile;
   	}
   	public void setMobile(String mobile) {
   		this.mobile = mobile;
   	}
   }
   

4. Insert_Values.java

   package CRUD;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import common.DB_Connection;
   public class Insert_Values {
   public void insert_values(String sl_no,String user_name,String email,String mobile)
   {
   	 DB_Connection obj_DB_Connection=new DB_Connection();
   	 Connection connection=obj_DB_Connection.get_connection();
   	 PreparedStatement ps=null;
   	try {
   String query="insert into user(sl_no,user_name,email,mobile) values(?,?,?,?)";
   	 ps=connection.prepareStatement(query);
   	 ps.setString(1, sl_no);
   	 ps.setString(2, user_name);
   	 ps.setString(3, email);
   	 ps.setString(4, mobile);
   	 ps.executeUpdate();
   	} catch (Exception e) {
   	 System.err.println(e);
   	}
   }
   }
   

5. Read_Values.java

   package CRUD;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.util.ArrayList;
   import java.util.List;
   import common.DB_Connection;
   import common.User_Bean;
   public class Read_Values {
   public static void main(String[] args) {
   	Read_Values obj_Read_Values=new Read_Values();
   	obj_Read_Values.get_values();
   }
   public List get_values(){
   	DB_Connection obj_DB_Connection=new DB_Connection();
   	Connection connection=obj_DB_Connection.get_connection();
   	PreparedStatement ps=null;
   	ResultSet rs=null;
   	List list=new ArrayList();
   	try {
   	String querry="select * from user";
   	ps=connection.prepareStatement(querry);		
   	rs=ps.executeQuery();
   	while(rs.next()){
   	  User_Bean obj_User_Bean=new User_Bean();
   	  System.out.println(rs.getString("sl_no"));
   	  System.out.println(rs.getString("email"));
   	  System.out.println(rs.getString("user_name"));
   	  System.out.println(rs.getString("mobile"));
   	  obj_User_Bean.setSl_no(rs.getString("sl_no"));
             obj_User_Bean.setEmail(rs.getString("email"));
   	  obj_User_Bean.setUser_name(rs.getString("user_name"));
   	  obj_User_Bean.setMobile(rs.getString("mobile"));
   	list.add(obj_User_Bean);
   	}
   	} catch (Exception e) {
   	System.out.println(e);
   	}
   	return list;
   	}
   }
   

6. Edit_values.java

   package CRUD;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.util.ArrayList;
   import java.util.List;
   import common.DB_Connection;
   import common.User_Bean;
   public class Edit_values {
   public User_Bean get_value_of_user(String sl_no){
   	DB_Connection obj_DB_Connection=new DB_Connection();
   	Connection connection=obj_DB_Connection.get_connection();
   	PreparedStatement ps=null;
   	ResultSet rs=null;
   	User_Bean obj_User_Bean=new User_Bean();
   	try {
   	 String querry="select * from user where sl_no=?";
   	 ps=connection.prepareStatement(querry);		
   	 ps.setString(1, sl_no);;
   	 rs=ps.executeQuery();
   	 while(rs.next()){
   	  obj_User_Bean.setSl_no(rs.getString("sl_no"));
   	  obj_User_Bean.setEmail(rs.getString("email"));
   	  obj_User_Bean.setUser_name(rs.getString("user_name"));
   	  obj_User_Bean.setMobile(rs.getString("mobile"));
            }
   	} catch (Exception e) {
   	System.out.println(e);
   	}
   	return obj_User_Bean;
   }
   public void edit_user(User_Bean obj_User_Bean){
   	DB_Connection obj_DB_Connection=new DB_Connection();
   	Connection connection=obj_DB_Connection.get_connection();
   	PreparedStatement ps=null;
   	try {
   	String querry="update user set user_name=?,email=?,mobile=? where sl_no=?";
   	 ps=connection.prepareStatement(querry);		
   	 ps.setString(1, obj_User_Bean.getUser_name());;
   	 ps.setString(2, obj_User_Bean.getEmail());;
   	 ps.setString(3, obj_User_Bean.getMobile());;
   	 ps.setString(4, obj_User_Bean.getSl_no());;
   	 ps.executeUpdate();
   	} catch (Exception e) {
   	System.out.println(e);
   	}
   }
   }
   

7. Delete_values.java

   package CRUD;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import common.DB_Connection;
   import common.User_Bean;
   public class Delete_values {
   public void delete_value(String sl_no){
   	DB_Connection obj_DB_Connection=new DB_Connection();
   	Connection connection=obj_DB_Connection.get_connection();
   	PreparedStatement ps=null;
   	try {
   	 String querry="delete from user where sl_no=?";
   	 ps=connection.prepareStatement(querry);		
   	 ps.setString(1, sl_no);;
   	 ps.executeUpdate();
   	} catch (Exception e) {
   	 System.out.println(e);
   	}
   	}
   }
   

8. insert_values.jsp

   <%@page import="java.util.Iterator"%>
   <%@page import="common.User_Bean"%>
   <%@page import="java.util.List"%>
   <%@page import="CRUD.Read_Values"%>
   <%@ 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>Insert title here</title>
   </head>
   <body>
   <center>
   <h1>Insert Values</h1>
   <form action="controller/insert_controller.jsp">
   Sl No :<input type="text" name="sl_no"><br>
   User Name :<input type="text" name="user_name"><br>
   Mobile:<input type="text" name="mobile"><br>
   Email:<input type="text" name="email"><br>
   <input type="submit" value="Insert">
   </form>
   <hr>
   <%
   Read_Values obj_Read_Values=new Read_Values();
   List<User_Bean> list=obj_Read_Values.get_values();
   Iterator<User_Bean> it_list=list.iterator();
   %>
   <table border="1">
   <%
   while(it_list.hasNext()){
   User_Bean obj_User_Bean=new User_Bean();
   obj_User_Bean=it_list.next();
   %>
   <tr>
   <td><%=obj_User_Bean.getSl_no() %></td>
   <td><%=obj_User_Bean.getUser_name() %></td>
   <td><%=obj_User_Bean.getEmail() %></td>
   <td><%=obj_User_Bean.getMobile() %></td>
   <td>
   <a href="edit.jsp?sl_no=<%=obj_User_Bean.getSl_no()%>">Edit</a>
   </td>
   <td>
   <a href="controller/delete_controller.jsp?sl_no=<%=obj_User_Bean.getSl_no()%>">Delete</a>
   </td>
   </tr>
   <%
   }
   %>
   </table>
   </center>
   </body>
   </html>
   

9. edit.jsp

   <%@page import="CRUD.Edit_values"%>
   <%@page import="common.User_Bean"%>
   <%@ 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>Insert title here</title>
   </head>
   <body>
   <%
   String sl_no=(String)request.getParameter("sl_no");
   Edit_values obj_Edit_values=new Edit_values();
   User_Bean obj_User_Bean=obj_Edit_values.get_value_of_user(sl_no);
   %>
   <center>
   <h1>Edit Values</h1>
   <form action="controller/edit_controller.jsp">
   Sl No :<input type="text" name="sl_no" value="<%=sl_no%>"><br>
   User Name :<input type="text" name="user_name" value="<%=obj_User_Bean.getUser_name()%>"><br>
   Mobile:<input type="text" name="mobile" value="<%=obj_User_Bean.getMobile()%>"><br>
   Email:<input type="text" name="email" value="<%=obj_User_Bean.getEmail()%>"><br>
   <input type="submit" value="Edit">
   </form>
   </center>
   </body>
   </html>
   

10. insert_controller.jsp

    <%@page import="CRUD.Insert_Values"%>
    <%@ 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>Insert title here</title>
    </head>
    <body>
    <%
    String sl_no=request.getParameter("sl_no");
    String email=request.getParameter("email");
    String mobile=request.getParameter("mobile");
    String user_name=request.getParameter("user_name");
    Insert_Values obj_Insert_Values=new Insert_Values();
    obj_Insert_Values.insert_values(sl_no, user_name, email, mobile);
    %>
    <script type="text/javascript">
    window.location.href="http://localhost:8080/JSP_MySQL_CRUD/insert_values.jsp"
    </script>
    </body>
    </html>
    

11. edit_controller.jsp

    <%@page import="CRUD.Edit_values"%>
    <%@page import="common.User_Bean"%>
    <%@page import="CRUD.Insert_Values"%>
    <%@ 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>Insert title here</title>
    </head>
    <body>
    <%
    String sl_no=request.getParameter("sl_no");
    String email=request.getParameter("email");
    String mobile=request.getParameter("mobile");
    String user_name=request.getParameter("user_name");
    User_Bean obj_User_Bean=new User_Bean();
    obj_User_Bean.setSl_no(sl_no);
    obj_User_Bean.setUser_name(user_name);
    obj_User_Bean.setMobile(mobile);
    obj_User_Bean.setEmail(email);
    Edit_values obj_Edit_values=new Edit_values();
    obj_Edit_values.edit_user(obj_User_Bean);
    %>
    <script type="text/javascript">
    window.location.href="http://localhost:8080/JSP_MySQL_CRUD/insert_values.jsp"
    </script>
    </body>
    </html>
    

12. delete_controller.jsp

    <%@page import="CRUD.Delete_values"%>
    <%@page import="CRUD.Edit_values"%>
    <%@page import="common.User_Bean"%>
    <%@page import="CRUD.Insert_Values"%>
    <%@ 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>Insert title here</title>
    </head>
    <body>
    <%
    String sl_no=request.getParameter("sl_no");
    Delete_values obj_Delete_values=new Delete_values();
    obj_Delete_values.delete_value(sl_no);
    %>
    <script type="text/javascript">
    window.location.href="http://localhost:8080/JSP_MySQL_CRUD/insert_values.jsp"
    </script>
    </body>
    </html>
    

13. 1. Insert Button
    2. Edit Button
    3. Delete Button
    http://localhost:8080/JSP_MySQL_CRUD/insert_values.jsp

14. Edit Page
    http://localhost:8080/JSP_MySQL_CRUD/insert_values.jsp