Note: Before using this code.
Please do following =>>
1. create a database, name of database should be 'company'
2. create a table inside this database, name of table should be employee
3. inside this table create 6 columns [id, name, desig, salary, tech]
note: id must be primary key
4. now insert few records inside the employee table
code of index.jsp ==>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
// load driver class
Class.forName("com.mysql.jdbc.Driver");
// get object of connection
Connection co = DriverManager.getConnection
("jdbc:mysql://localhost:3306/company","root","root");
// store the object of coonnection inside session.
// now this object of connection can be fetched by any jsp or servlet
// inside this web application.
// note: session stores client specific data (client means web browser)
session.setAttribute("conn",co);
%>
<a href="show-records.jsp">show all records</a>
</body>
</html>
code of show-records.jsp ==>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<table border="1">
<th>ID</th><th>NAME</th><th>DESIGNATION</th><th>SALARY</th><th>TECHNOLOGY</th>
<th>CHOICE-1</th><th>CHOICE-2</th>
<%
// fetch the object of connection from the session
Connection co = (Connection) session.getAttribute("conn");
// get the object of statement
Statement st = co.createStatement();
// create sql query
String sql = "select * from employee";
// execute sql query and get the result-set
ResultSet rs = st.executeQuery(sql);
while(rs.next()) // fetch a record from the result-set
{
// fetch data of all the columns of this record
int id = rs.getInt("id");
String name = rs.getString("name");
String desg = rs.getString("desig");
float salary = rs.getFloat("salary");
String tech = rs.getString("tech");
// show data of all the columns inside the table
out.println("<tr>");
out.println("<td>"); out.println(id); out.println("</td>");
out.println("<td>"); out.println(name); out.println("</td>");
out.println("<td>"); out.println(desg); out.println("</td>");
out.println("<td>"); out.println(salary); out.println("</td>");
out.println("<td>"); out.println(tech); out.println("</td>");
// look here ==========> we are using the URL-REWRITTING
out.println("<td>");
out.println("<a href=edit.jsp?id="+id+">edit</a>");
out.println("</td>");
out.println("<td>");
out.println("<a href=delete.jsp?id="+id+">delete</a>");
out.println("</td>");
out.println("</tr>");
}
%>
</table>
</center>
</body>
</html>
code of edit.jsp ==>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
// fetch the data of html form
// here we are fetching the data associated with url
String id = request.getParameter("id");
// fetch object of connection from session
Connection co = (Connection) session.getAttribute("conn");
// get object of statement
Statement st = co.createStatement();
// create sql query
String sql = "select * from employee where id='"+id+"'";
// execute sql query and get object of result-set
ResultSet rs = st.executeQuery(sql);
if(rs.next()) // fetch a record from the result-set
{
// fetch data of all the columns of this record
String name = rs.getString("name");
String desg = rs.getString("desig");
float salary = rs.getFloat("salary");
String tech = rs.getString("tech");
%>
<%--
# Here i am using EXPRESSION TAG to pass values inside html form
# This form will be submitted on update-record.jsp
--%>
<form action="update-record.jsp" method="post">
Id<br>
<input type="text" name="id" value="<%=id%>" readonly><br>
Name<br>
<input type="text" name="name" value="<%=name%>" required><br>
Desig<br>
<input type="text" name="desig" value="<%=desg%>" required><br>
Salary<br>
<input type="text" name="salary" value="<%=salary%>" required><br>
Tech<br>
<input type="text" name="tech" value="<%=tech%>" required><br>
<input type="submit" value="update this record">
</form>
<%
}
%>
</body>
</html>
code of delete.jsp ==>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
// fetch the data of html form
// here we are fetching the data associated with url
String id = request.getParameter("id");
// fetch object of connection from session
Connection co = (Connection) session.getAttribute("conn");
// get object of statement
Statement st = co.createStatement();
String sql = "delete from employee where id='"+id+"'";
// execute sql query and get no of records affected
int rec = st.executeUpdate(sql);
if(rec > 0) // if no of records affected are more than zero
// means records are deleted
{
// redirect the flow of control to the 'show-records.jsp'
response.sendRedirect("show-records.jsp");
}
%>
</body>
</html>
code of update-record.jsp ==>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<table border="1">
<th>ID</th><th>NAME</th><th>DESIGNATION</th><th>SALARY</th><th>TECHNOLOGY</th>
<th>CHOICE-1</th><th>CHOICE-2</th>
<%
// fetch html form data, this form is submitted from edit.jsp
String id = request.getParameter("id");
String name = request.getParameter("name");
String desig = request.getParameter("desig");
String salary = request.getParameter("salary");
String tech = request.getParameter("tech");
// fetch object of connection from the session
Connection co = (Connection) session.getAttribute("conn");
// fetch object of statement from the session
Statement st = co.createStatement();
// create sql query
String sql = "update employee set name='"+name+"',desig='"
+desig+"',salary='"+salary+"',tech='"+tech+"' where id='"+id+"'";
// execute sql query and get no of records affected inside the database
int rec = st.executeUpdate(sql);
// of no of records are more than zero
if(rec > 0)
{
// print message on client side
out.println("<p style='color : blue'><b><i>your data has been updated</i></b><br>");
out.println("<a href='index.jsp'>click here to goto home page</a>");
}
%>
</table>
</center>
</body>
</html>
Please do following =>>
1. create a database, name of database should be 'company'
2. create a table inside this database, name of table should be employee
3. inside this table create 6 columns [id, name, desig, salary, tech]
note: id must be primary key
4. now insert few records inside the employee table
code of index.jsp ==>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
// load driver class
Class.forName("com.mysql.jdbc.Driver");
// get object of connection
Connection co = DriverManager.getConnection
("jdbc:mysql://localhost:3306/company","root","root");
// store the object of coonnection inside session.
// now this object of connection can be fetched by any jsp or servlet
// inside this web application.
// note: session stores client specific data (client means web browser)
session.setAttribute("conn",co);
%>
<a href="show-records.jsp">show all records</a>
</body>
</html>
code of show-records.jsp ==>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<table border="1">
<th>ID</th><th>NAME</th><th>DESIGNATION</th><th>SALARY</th><th>TECHNOLOGY</th>
<th>CHOICE-1</th><th>CHOICE-2</th>
<%
// fetch the object of connection from the session
Connection co = (Connection) session.getAttribute("conn");
// get the object of statement
Statement st = co.createStatement();
// create sql query
String sql = "select * from employee";
// execute sql query and get the result-set
ResultSet rs = st.executeQuery(sql);
while(rs.next()) // fetch a record from the result-set
{
// fetch data of all the columns of this record
int id = rs.getInt("id");
String name = rs.getString("name");
String desg = rs.getString("desig");
float salary = rs.getFloat("salary");
String tech = rs.getString("tech");
// show data of all the columns inside the table
out.println("<tr>");
out.println("<td>"); out.println(id); out.println("</td>");
out.println("<td>"); out.println(name); out.println("</td>");
out.println("<td>"); out.println(desg); out.println("</td>");
out.println("<td>"); out.println(salary); out.println("</td>");
out.println("<td>"); out.println(tech); out.println("</td>");
// look here ==========> we are using the URL-REWRITTING
out.println("<td>");
out.println("<a href=edit.jsp?id="+id+">edit</a>");
out.println("</td>");
out.println("<td>");
out.println("<a href=delete.jsp?id="+id+">delete</a>");
out.println("</td>");
out.println("</tr>");
}
%>
</table>
</center>
</body>
</html>
code of edit.jsp ==>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
// fetch the data of html form
// here we are fetching the data associated with url
String id = request.getParameter("id");
// fetch object of connection from session
Connection co = (Connection) session.getAttribute("conn");
// get object of statement
Statement st = co.createStatement();
// create sql query
String sql = "select * from employee where id='"+id+"'";
// execute sql query and get object of result-set
ResultSet rs = st.executeQuery(sql);
if(rs.next()) // fetch a record from the result-set
{
// fetch data of all the columns of this record
String name = rs.getString("name");
String desg = rs.getString("desig");
float salary = rs.getFloat("salary");
String tech = rs.getString("tech");
%>
<%--
# Here i am using EXPRESSION TAG to pass values inside html form
# This form will be submitted on update-record.jsp
--%>
<form action="update-record.jsp" method="post">
Id<br>
<input type="text" name="id" value="<%=id%>" readonly><br>
Name<br>
<input type="text" name="name" value="<%=name%>" required><br>
Desig<br>
<input type="text" name="desig" value="<%=desg%>" required><br>
Salary<br>
<input type="text" name="salary" value="<%=salary%>" required><br>
Tech<br>
<input type="text" name="tech" value="<%=tech%>" required><br>
<input type="submit" value="update this record">
</form>
<%
}
%>
</body>
</html>
code of delete.jsp ==>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%><%@page import="java.sql.Connection"%>
<%@ 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>
<%
// fetch the data of html form
// here we are fetching the data associated with url
String id = request.getParameter("id");
// fetch object of connection from session
Connection co = (Connection) session.getAttribute("conn");
// get object of statement
Statement st = co.createStatement();
String sql = "delete from employee where id='"+id+"'";
// execute sql query and get no of records affected
int rec = st.executeUpdate(sql);
if(rec > 0) // if no of records affected are more than zero
// means records are deleted
{
// redirect the flow of control to the 'show-records.jsp'
response.sendRedirect("show-records.jsp");
}
%>
</body>
</html>
code of update-record.jsp ==>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<table border="1">
<th>ID</th><th>NAME</th><th>DESIGNATION</th><th>SALARY</th><th>TECHNOLOGY</th>
<th>CHOICE-1</th><th>CHOICE-2</th>
<%
// fetch html form data, this form is submitted from edit.jsp
String id = request.getParameter("id");
String name = request.getParameter("name");
String desig = request.getParameter("desig");
String salary = request.getParameter("salary");
String tech = request.getParameter("tech");
// fetch object of connection from the session
Connection co = (Connection) session.getAttribute("conn");
// fetch object of statement from the session
Statement st = co.createStatement();
// create sql query
String sql = "update employee set name='"+name+"',desig='"
+desig+"',salary='"+salary+"',tech='"+tech+"' where id='"+id+"'";
// execute sql query and get no of records affected inside the database
int rec = st.executeUpdate(sql);
// of no of records are more than zero
if(rec > 0)
{
// print message on client side
out.println("<p style='color : blue'><b><i>your data has been updated</i></b><br>");
out.println("<a href='index.jsp'>click here to goto home page</a>");
}
%>
</table>
</center>
</body>
</html>
useful information
ReplyDelete