This Jsp web development tutorial will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE . using Mysql Database.jsp crud tutorial will cover about the INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.
We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the records table in the database named schooll. schooll table consist of following columns name,course,fee.
Index.jsp
In this page system shall be able to record the student details then system shall be able to retrieve student details.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%
if(request.getParameter("submit")!=null)
{
String name = request.getParameter("sname");
String course = request.getParameter("course");
String fee = request.getParameter("fee");
Connection con;
PreparedStatement pst;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/schooll","root","");
pst = con.prepareStatement("insert into records(stname,course,fee)values(?,?,?)");
pst.setString(1, name);
pst.setString(2, course);
pst.setString(3, fee);
pst.executeUpdate();
%>
<script>
alert("Record Adddeddddd");
</script>
<%
}
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<link href="bootstrap/css/bootstrap.css" rel="stylesheet" type="text/css"/>
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<h1>Student Registation System Crud using-JSP</h1>
</br>
<div class="row">
<div class="col-sm-4">
<form method="POST" action="#" >
<div alight="left">
<label class="form-label">Student Name</label>
<input type="text" class="form-control" placeholder="Student Name" name="sname" id="sname" required >
</div>
<div alight="left">
<label class="form-label">Course</label>
<input type="text" class="form-control" placeholder="Course" name="course" id="course" required >
</div>
<div alight="left">
<label class="form-label">Fee</label>
<input type="text" class="form-control" placeholder="Fee" name="fee" id="fee" required >
</div>
</br>
<div alight="right">
<input type="submit" id="submit" value="submit" name="submit" class="btn btn-info">
<input type="reset" id="reset" value="reset" name="reset" class="btn btn-warning">
</div>
</form>
</div>
<div class="col-sm-8">
<div class="panel-body">
<table id="tbl-student" class="table table-responsive table-bordered" cellpadding ="0" width="100%">
<thead>
<tr>
<th>Student Name</th>
<th>Course</th>
<th>Fee</th>
<th>Edit</th>
<th>Delete</th>
</tr>
<%
Connection con;
PreparedStatement pst;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/schooll","root","");
String query = "select * from records";
Statement st = con.createStatement();
rs = st.executeQuery(query);
while(rs.next())
{
String id = rs.getString("id");
%>
<tr>
<td><%=rs.getString("stname") %></td>
<td><%=rs.getString("course") %></td>
<td><%=rs.getString("fee") %></td>
<td><a href="update.jsp?id=<%=id%>">Edit</a></td>
<td><a href="delete.jsp?id=<%=id%>">Delete</a></td>
</tr>
<%
}
%>
</table>
</div>
</div>
</div>
</body>
</html>
Select the row of the table which row need to be edit click the edit link of the row .
after clicked the edit link of the page redirect to update.jsp along with selected row data.all the data will be passing to the relavent textboxes.after made the changes system shall be able to update the records.
Update.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<%
if(request.getParameter("submit")!=null)
{
String id = request.getParameter("id");
String name = request.getParameter("sname");
String course = request.getParameter("course");
String fee = request.getParameter("fee");
Connection con;
PreparedStatement pst;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/schooll","root","");
pst = con.prepareStatement("update records set stname = ?,course =?,fee= ? where id = ?");
pst.setString(1, name);
pst.setString(2, course);
pst.setString(3, fee);
pst.setString(4, id);
pst.executeUpdate();
%>
<script>
alert("Record Updateddddd");
</script>
<%
}
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<link href="bootstrap/css/bootstrap.css" rel="stylesheet" type="text/css"/>
<link href="bootstrap/css/bootstrap.min.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<h1>Student Update</h1>
<div class="row">
<div class="col-sm-4">
<form method="POST" action="#" >
<%
Connection con;
PreparedStatement pst;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/schooll","root","");
String id = request.getParameter("id");
pst = con.prepareStatement("select * from records where id = ?");
pst.setString(1, id);
rs = pst.executeQuery();
while(rs.next())
{
%>
<div alight="left">
<label class="form-label">Student Name</label>
<input type="text" class="form-control" placeholder="Student Name" value="<%= rs.getString("stname")%>" name="sname" id="sname" required >
</div>
<div alight="left">
<label class="form-label">Course</label>
<input type="text" class="form-control" placeholder="Course" name="course" value="<%= rs.getString("course")%>" id="course" required >
</div>
<div alight="left">
<label class="form-label">Fee</label>
<input type="text" class="form-control" placeholder="Fee" name="fee" id="fee" value="<%= rs.getString("fee")%>" required >
</div>
<% } %>
</br>
<div alight="right">
<input type="submit" id="submit" value="submit" name="submit" class="btn btn-info">
<input type="reset" id="reset" value="reset" name="reset" class="btn btn-warning">
</div>
<div align="right">
<p><a href="index.jsp">Click Back</a></p>
</div>
</form>
</div>
</div>
</body>
</html>
select the row of the table which record need to delete click the delete link of the row.
delete.jsp
<%@page import="java.sql.*" %>
<%
String id = request.getParameter("id");
Connection con;
PreparedStatement pst;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/schooll","root","");
pst = con.prepareStatement("delete from records where id = ?");
pst.setString(1, id);
pst.executeUpdate();
%>
<script>
alert("Record Deletee");
</script>
i have attached the video link below. which will do this tutorials step by step.
