JDBC
Write a servlet program to enter the record through JDBC.
Data base : mysql
Table : studentdb
index.html
AddStudent.java
Web.xml
Step 1: Load the JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
Step 2: Establish a Connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mysql", "root"," " );
Step 3: Create a Statement
Statement statement = con.createStatement();
Step 4: Execute a Query
String query = "INSERT INTO students (id, name) VALUES (101,
'xyz')";// (?,?)
int rowsAffected = statement.executeUpdate(query);
System.out.println("Rows affected: " + rowsAffected);
Step 5: Close the Connection
statement.close();
connection.close();
index.html
<form action="AddStudent" method="post">
ID : <input type="text" name="id" ><br>
NAME : <input type="text" name="name"><br>
MARKS : <input type="text" name="marks"> <br>
<input type="submit" value="Submit">
</form>
Addstudent.java
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.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class AddStudent extends HttpServlet {
protected void processRequest(HttpServletRequest
request, HttpServletResponse response)
throws ServletException, IOException {
}
@Override
protected void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
int id =
Integer.parseInt(request.getParameter("id"));
String name = request.getParameter("name");
int marks =
Integer.parseInt(request.getParameter("marks"));
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/mys
ql","root","")) {
PreparedStatement ps =
con.prepareStatement("INSERT INTO studentdb VALUES(?,?,?)");
ps.setInt(1,id);
ps.setString(2,name);
ps.setInt(3,marks);
int i = ps.executeUpdate();
if(i>0){
out.println("<h3> Student Added <h3>");
}else{
out.println("<h3> Error Alert !! <h3>");
}
}
}catch(ClassNotFoundException | SQLException e)
{
out.println("Error"+e.getMessage());
}
}
}
CRUD Operation with emp table in mysql DB
Employee No:
Name :
Designation :
City :
Salary :
Department :
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class crud extends HttpServlet {
Connection con;
PreparedStatement ps;
private Object action;
@Override
public void init(){
try{
Class.forName("com.mysql.cj.jdbc.Driver");
con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/mys
ql");
}catch(Exception e){
System.out.println("Connection Error" +e);
}
}
protected void processRequest(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException,
SQLException {
res.setContentType("text/html;charset=UTF-8");
PrintWriter out = res.getWriter();
String empno =req.getParameter("empno");
String empnm =req.getParameter("empnm");
String designation =
req.getParameter("designation");
String city =req.getParameter("city");
String salary=req.getParameter("salary");
String department =req.getParameter("department");
try{
if(action.equals("Add")){
ps = con.prepareStatement("INSERT INTO emp
VALUES (?,?,?,?,?,?)");
ps.setInt(1,Integer.parseInt(empno));
ps.setString(2,empnm);
ps.setString(3,designation);
ps.setString(4,city);
ps.setString(5,salary);
ps.setString(6,department);
int i = ps.executeUpdate();
out.println("</h3> Record Added" +i+ "</h3>");
}
else if(action.equals("Update")){
ps = con.prepareStatement("UPDATE emp SET
empnm=?, designation=?, city=?, salary=?, department=? WHERE
empno=?");
ps.setString(2,empnm);
ps.setString(3,designation);
ps.setString(4,city);
ps.setString(5,salary);
ps.setString(6,department);
ps.setInt(1,Integer.parseInt(empno));
int i = ps.executeUpdate();
out.println("</h3> Record Updated" +i+ "</h3>");
}else if(action.equals("Delete")){
ps = con.prepareStatement("DELETE FROM emp
WHERE empno=?");
ps.setInt(1,Integer.parseInt(empno));
int i = ps.executeUpdate();
out.println("<h3> Record DELETED" +i+
"</h3>");
}else if(action.equals("search")){
ps = con.prepareStatement("SELECT *FROM emp
WHERE EMPNO=?");
ResultSet rs = ps.executeQuery();
if(rs.next()){
out.println("<h3> Employee Found
</h3>");
out.println("Emp No " +rs.getInt(1)+
"<br>");
out.println("Name" +rs.getString(2)+
"<br>");
out.println("Designation"
+rs.getString(3)+ "<br>");
out.println("City" +rs.getString(4)+
"<br>");
out.println("Salary" +rs.getInt(5)+
"<br>");
out.println("Department"
+rs.getString(6)+ "<br>");
}else{
out.println("</h3> NO RECORD FOUND </h3>");
}
}
}catch(Exception e){
out.println("<h3> error " +e+ "</h3>" );
}
}
}
PRACTICE PROGRAM :
1.Write a program to insert a record of an employee into emp
table.
“INSERT INTO emp (fields name ) VALUES (?,?,?,?,?,?);”
2.Write a program to display all the records of employees.
SELECT *FROM emp;
3.Write a program to display employees whose salary is greater
than 80000.
"SELECT * FROM employees WHERE salary > 80000"
4.Write a JDBC program to display employees who are from the
city ‘Rajkot’.
"SELECT * FROM employees WHERE city = 'Rajkot'"
5.Write a program to display employees whose name starts with
‘A’.
"SELECT * FROM employees WHERE name LIKE 'A%'";
6.Write a program to display employees whose designation is
manager.
"SELECT * FROM employees WHERE designation = 'Manager'";
7.Write a program to count the number of employees in the
table.
“SELECT COUNT(*) AS total FROM employees"
8.Write a program to display employees who have the highest
salary.
"SELECT * FROM employees WHERE salary = (SELECT MAX(salary)
FROM employees)";
9.Write a program to display employees in ascending order.
"SELECT * FROM employees ORDER BY name ASC"
10. Write a program which accepts empno from the user and
displays the corresponding employee name and designation.
"SELECT name, designation FROM employees WHERE empno = ?";
