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);
}
}
}
}
