Java Servelt Examples for SQL operations | SQL connectivity
Servlet code
data.java
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
*
* @author nagendra
*/
public class data extends HttpServlet {
@Override
public void doGet(HttpServletRequest rq , HttpServletResponse rs){
try{
String a=rq.getParameter("t1");
String b=rq.getParameter("t2");
int bb=Integer.parseInt(b);
String c=rq.getParameter("t3");
int cc=Integer.parseInt(c);
String d=rq.getParameter("t4");
int dd =Integer.parseInt(d);
String e=rq.getParameter("t5");
rs.setContentType("text/html");
PrintWriter out=rs.getWriter();
out.print(a+bb+cc+dd+e+"Done!");
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/rana?autoReconnect=true&useSSL=false", "root" , "root") ;
PreparedStatement ps=con.prepareStatement("insert into edata values(?,?,?,?,?);");
ps.setString(1, a);
ps.setString(2, b);
ps.setString(3, c);
ps.setString(4, d);
ps.setString(5, e);
int i= ps.executeUpdate();
out.println(i+"Success");
RequestDispatcher rs1 = rq.getRequestDispatcher("employedata.html");
rs1.forward(rq, rs);
}
catch(Exception e)
{
try
{
PrintWriter out=rs.getWriter();
out.print(e.getMessage());
}
catch(Exception ee){
}
}
}
}view.java
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
public class view extends HttpServlet {
public void doGet(HttpServletRequest rq, HttpServletResponse rs)
{
try
{
//---------------------------------
rs.setContentType("text/html");
String empid =rq.getParameter("t6");
PrintWriter out=rs.getWriter();
//-----------------------------------
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/rana?autoReconnect=true&useSSL=fal se","root","root");
PreparedStatement ps=con.prepareStatement("select * from edata where employe_id=?");
ps.setString(1,empid);
ResultSet rs1=ps.executeQuery();
if (rs1.next()){
out.print(rs1.getString("employe_name")+"\t"+rs1.getString("employe_age")+"\t"+rs1.getString("emplo ye_salary") + "\t" + rs1.getString("employe_id")+"\t"+rs1.getString("employe_address")+"\n");
}
else
out.print("Record not exist");
con.close();
}
catch(Exception e)
{
try{
PrintWriter out=rs.getWriter();
out.print(e.getMessage());
}
catch(Exception ee)
{
}
}
}
}
HTML code
index.html
<!DOCTYPE html>
<!-
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
<head>
<link rel="stylesheet" href="style.css" type="text/css"/>
<style>
p {
text-align:center
}
.center {
text-align: center;
border: 3px solid green;
margin-left: 600px;
}
.button1{
text-align: center;
border: 5px greenyellow;
margin-left: 600px;
}
</style>
<title>TODO supply a title</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<form method="get" action="data">
<p>Employe name</p>
<input class="center" type="text" name="t1" value="" /><br>
<p>Employe age</p>
<input class="center" type="text" name="t2" value="" /><br>
<p>Employe salary</p>
<input class="center" type="text" name="t3" value="" /><br>
<p>Employe id</p>
<input class="center" type="text" name="t4" value="" /><br>
<p>Employe address</p>
<input class="center" type="text" name="t5" value="" /><br>
<br>
<input class="center" type="submit" value="Submit Details" /><br>
</form>
</body>
</html>
employedata.html
<!DOCTYPE html>
<!-
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
<head>
<title>TODO supply a title</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<style>
p {
text-align:center
}
.center {
text-align: center;
border: 3px solid green;
margin-left: 600px;
}
.button1{
text-align: center;
border: 5px greenyellow;
margin-left: 600px;
}
</style>
</head>
<body>
<div>Employe Data</div>
<h1>Enter Employe id to check the details of employ</h1><br><br>
<form method="get" action="view">
<br><br><input class="center" type="text" name="t6" value="Enter employe id" />
<br><br><input class="center" type="submit" value="Check Data" />
</form>
</body>
</html>SQL Commands
create table edata (employe_name char(10),employe_age int , employe_salary int ,employe_id int not null, employe_address char(20) ,PRIMARY KEY (employe_id));
Screenshots
Deleting Data from Database through servelt
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
public class delete extends HttpServlet {
public void doGet(HttpServletRequest rq, HttpServletResponse rs) {
try{
//---------------------------------
rs.setContentType("text/html");
String empid1=rq.getParameter("t7");
PrintWriter out=rs.getWriter();
//-----------------------------------
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/rana?autoReconnect=true&u seSSL=false","root","root"); PreparedStatement ps=con.prepareStatement("delete from edata where employe_id=?");
ps.setString(1,empid1);
int row=ps.executeUpdate();
if(row>0)
{
out.print("Record deleted successfully....");
}
}
catch(Exception e)
{
try{
PrintWriter out=rs.getWriter();
out.print(e.getMessage());
}
catch(Exception ee)
{
}
}
}
}
Updating data
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
public class update extends HttpServlet {
public void doGet(HttpServletRequest rq, HttpServletResponse rs) { try
{
//--------------------------------- rs.setContentType("text/html");
String name=rq.getParameter("a1");
String address=rq.getParameter("a2");
String id=rq.getParameter("t8");
PrintWriter out=rs.getWriter();
//----------------------------------- Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/rana?autoReconnect=true&u seSSL=false","root","root"); PreparedStatement ps=con.prepareStatement("update edata set employe_name=? ,employe_address=? where employe_id=? ");
ps.setString(1,name);
ps.setString(2,address);
ps.setString(3,id);
int row=ps.executeUpdate();
if(row>0)
{
out.print("Record updated successfully....");
}
}
catch(Exception e)
{
try{
PrintWriter out=rs.getWriter();
out.print(e.getMessage());
}
catch(Exception ee)
{
}
}
}
}
