How to Perform Database Connection and Database Operations in Servlets

In this tutorial how to perform database connection and database operations such as insert, update, delete and select operations in servlets is shown.

Code:

index.html

<html>
<head>
<title>DB Connection</title>
<meta charset=”UTF-8″>
<meta name=”viewport” content=”width=device-width, initial-scale=1.0″>
</head>
<body>
<form action=”Dbconn” method=”POST”>
ID: <input type=”text” name=”id”><br>
Name: <input type=”text” name=”nm”><br>
Department: <input type=”text” name=”dpt”><br>
<input type=”submit” value=”Insert” name=”s”>
<input type=”submit” value=”Update” name=”s”>
<input type=”submit” value=”Delete” name=”s”>
<input type=”submit” value=”Search” name=”s”>
<input type=”submit” value=”View” name=”s”>
</form>
</body>
</html>

Dbconn.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 Dbconn extends HttpServlet {
protected void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
res.setContentType(“text/html;charset=UTF-8”);
try (PrintWriter out = res.getWriter()) {
int id=Integer.parseInt(req.getParameter(“id”));
String nm=req.getParameter(“nm”);
String dpt=req.getParameter(“dpt”);
String s=req.getParameter(“s”);

try{
Class.forName(“net.ucanaccess.jdbc.UcanaccessDriver”);
String url=”jdbc:ucanaccess://D:/Employee.accdb”;

Connection con=DriverManager.getConnection(url);

if(con!=null)
{
out.println(“Database Connected Successfully”);
}
else
{
out.println(“Error in Connection”);
}
//For insert
if(“Insert”.equals(s))
{
//String qry=”insert into Employee(Name,Department) values ‘”+nm+”‘,'”+dpt+”‘;”;
String qry=”insert into Employee(Name,Department) values(?,?);”;
PreparedStatement ps=(PreparedStatement)con.prepareStatement(qry);
if(ps!=null)
{
out.println(“Statement Prepared Successfully”);
}
else
{
out.println(“Statement not Prepared”);
}

ps.setString(1,nm);
ps.setString(2,dpt);

int i=ps.executeUpdate();
if(i!=0)
{
out.println(“Record Inserted”);
}
else
{
out.println(“Record not Inserted”);
}
}
//For update
else if(“Update”.equals(s))
{
String qry=”update Employee SET Department=? where Name=?;”;
PreparedStatement ps=(PreparedStatement)con.prepareStatement(qry);
if(ps!=null)
{
out.println(“Statement Prepared Successfully”);
}
else
{
out.println(“Statement not Prepared”);
}

ps.setString(2,nm);
ps.setString(1,dpt);

int i=ps.executeUpdate();
if(i!=0)
{
out.println(“Record Updated”);
}
else
{
out.println(“Record does not exist”);
}
}
//For delete
else if(“Delete”.equals(s))
{
String qry=”delete from Employee where Name=?;”;
PreparedStatement ps=(PreparedStatement)con.prepareStatement(qry);
if(ps!=null)
{
out.println(“Statement Prepared Successfully”);
}
else
{
out.println(“Statement not Prepared”);
}

ps.setString(1,nm);

int i=ps.executeUpdate();
if(i!=0)
{
out.println(“Record Deleted”);
}
else
{
out.println(“Record does not exist”);
}
}
//For Search
else if(“Search”.equals(s))
{
Statement stmt=con.createStatement();
String qry=”select * from Employee where ID='”+id+”‘;”;
ResultSet rs=stmt.executeQuery(qry);

if(rs.next())
{
out.println(“<html><body><table border=’1′><tr><th>ID</th><th>Name</th><th>Department</th></tr>”);
String eid=rs.getString(“ID”);
String name=rs.getString(“Name”);
String dept=rs.getString(“Department”);

out.println(“<tr><td>”+eid+”</td><td>”+name+”</td><td>”+dept+”</td></tr></table></body></html>”);
}
else
{
out.println(“No records Found”);
}
rs.close();
}
//For View
else if(“View”.equals(s))
{
Statement stmt=con.createStatement();
String qry=”select * from Employee;”;
ResultSet rs=stmt.executeQuery(qry);

out.println(“<html><body><table border=’1’><tr><th>ID</th><th>Name</th><th>Department</th></tr>”);

while(rs.next())
{

String eid=rs.getString(“ID”);
String name=rs.getString(“Name”);
String dept=rs.getString(“Department”);

out.println(“<tr><td>”+eid+”</td><td>”+name+”</td><td>”+dept+”</td></tr>”);
}
out.println(“</table></body></html>”);
rs.close();
}
con.close();
}
catch(Exception e)
{
out.println(e);
}
}
}
}

Add a Comment

Your email address will not be published. Required fields are marked *