JDBC Introduction,CRUD in JSP,Servlet,Insert,Update,Delete,Select record using JDBC,JDBC CRUD

Author - Shiva Gautam
5/23/2023
3

It is intermediate technology of Java that is used to perform database operations using drivers and providers, JDBC contains a set of classes and methods to perform database operations for the different database servers.
JDBC means Java Database connectivity, It is used to create dynamic applications of Java because JDBC has database-related classes and methods to perform database operations.
It will work as a bridge between the java application and the database server.

It works using four different types:-
1)  type1:-     JDBC-ODBC Driver:-  It is used for desktop applications because ODBC provides local connection using DSN (Data source name)
2) type2:- JDBC-Network Specific Driver -  It is used to connect the database from a different network, It uses C and C++ Programming approach to connect the server machine.
3) type3:-   JDBC-Vendor Specific Driver, It uses a third-party library to communicate application data to the database server, It provides a different approach for the different database servers.
4) type4:-  Java thin driver or Client-Server Specific driver:-  it is a modern type of JDBC which is used to perform database operations using a client-server architecture.
It is common for all database servers only drivers will be different. it is a lightweight approach to communicate data from application to database server hence it is also called Java Thin Driver.
Step for JDBC using type 4:
1)  Create a Database and Table using any database, We are using MYSQL Database Server.
for MYSQL we will use PHPMYADMIN Software of XAMPP Server or MYSQL YOG or MYSQL Console
1.1 )   create database database name
1.2)  Create table student(rno int primary key,sname varchar(100),branch char(5),fees int)
2) Now Create Form using JSP which contain text-field according to table column
3)  Add JDBC Driver according to Database Server for MYSQL Add MySQL driver
4)  Add Servlet to write code for data insertion

Code of JSP:-
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <form action="StudentSer" method="post">
            <input type="text" name="txtrno" placeholder="Enter rno" />
            <br><br>
             <input type="text" name="txtname" placeholder="Enter name" /> <br><br>
              <input type="text" name="txtbranch" placeholder="Enter branch" /> <br><br>
               <input type="text" name="txtfees" placeholder="Enter fees" /> <br><br>
               <input type="submit" name="btnsubmit" value="Insert" />
        </form>
         <%
            if(request.getParameter("q")!=null)
            {
                out.print(request.getParameter("q"));
            }
         %>
    </body>
</html>

Code for Servlet
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class StudentSer extends HttpServlet {
   public void doPost(HttpServletRequest request,HttpServletResponse response) 
   {
       try {
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
           Statement st = con.createStatement();
           int x = st.executeUpdate("insert into student(rno,sname,branch,fees) values('"+request.getParameter("txtrno")+"','"+request.getParameter("txtname")+"','"+request.getParameter("txtbranch")+"','"+request.getParameter("txtfees")+"')");
           if(x!=0)
              response.sendRedirect("StudentForm.jsp?q=insert success");
           else
               response.sendRedirect("StudentForm.jsp?q=insert fail");
       } catch (Exception ex) {
           
       }
   }
}
Select Record From Database 
<%@page import="java.sql.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
       <%
          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
          Statement st = con.createStatement();
          ResultSet res = st.executeQuery("select * from student");
          while(res.next())
          {
              out.print(res.getString(1) +" "+res.getString(2) +" "+res.getString(3)+" "+res.getString(4)+"<br>");
          }          
        %>          
    </body>
</html>
Select Record From Database using Table Format:-
<%@page import="java.sql.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
    <table border="1">
            <tr>
                <th>RNO</th>
                <th>SNAME</th>
         
                <th>BRANCH</th>
                <th>FEES</th>
         
            </tr>    
       <%
          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
          Statement st = con.createStatement();
          ResultSet res = st.executeQuery("select * from student");
          while(res.next())
          {  %>
          <tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td></tr>
                  <% }
                 %>
           </table>
    </body>
</html>
Update Record then we customize show record jsp page:-
<%@page import="java.sql.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
    <table border="1">
            <tr>
                <th>RNO</th>
                <th>SNAME</th>
           
                <th>BRANCH</th>
                <th>FEES</th>
                       </tr>      
       <%
          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
          Statement st = con.createStatement();
          ResultSet res = st.executeQuery("select * from student");
          while(res.next())
          {  %>
          <tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td><td><a href="EditRecord.jsp?q=<%= res.getString(1) %>">Edit</a></td><td>Delete</td></tr>
                    <% }
         
        %>
           </table>
    </body>
</html>
Show Update Servlet 

/*
 * 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.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
 *
 * @author Hp
 */
public class UpdateSer extends HttpServlet {

   public void doPost(HttpServletRequest request,HttpServletResponse response) 
   {
       try {
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
           Statement st = con.createStatement();
           int x = st.executeUpdate("update student  set sname='"+request.getParameter("txtsname")+"',branch='"+request.getParameter("txtbranch")+"',fees='"+request.getParameter("txtfees")+"' where rno='"+request.getParameter("txtrno")+"'");
           if(x!=0)
              response.sendRedirect("ViewStudent.jsp");
           else
               response.sendRedirect("ViewStudent.jsp");
       } catch (Exception ex) {
           
       }
   }

}

Tags
Shiva Gautam

Shiva Gautam

Hi, I'm Shiva Gautam. With over 15 years of diverse experience in various IT domains, I am now an entrepreneur focusing on both training and software development. My expertise spans across multiple technologies, and I've authored numerous articles on subjects including Java, Python, REACT, NODE, ANGULAR, Cloud, software testing, Django framework, C#, Flutter, Salesforce, Ruby on Rails, .NET, AWS, DEVOPS, AI/ML and more. For additional information, please visit shivaconceptsolution.com and kangaroosoftware.net.

You may like these posts

Show more

Post a Comment

3Comments

POST Answer of Questions and ASK to Doubt

  1. Yash PanjreMarch 25, 2021 at 9:04 PM

    // create product table with pid,pname,brand,description using normal Statement


    package jdbc;
    import java.sql.*;
    import java.util.Scanner;
    public class Product {
    int pid;
    String pname,brand,description;
    Connection conn;
    Statement st;
    Scanner sc=new Scanner(System.in);
    void accept(int pid,String pname,String brand,String description)
    {
    this.pid=pid;
    this.pname=pname;
    this.brand=brand;
    this.description=description;
    }
    void connection() throws SQLException, ClassNotFoundException
    {
    Class.forName("com.mysql.cj.jdbc.Driver");
    conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","");
    st=conn.createStatement();
    }
    void insert() throws SQLException
    {
    int x=st.executeUpdate("insert into product (pid,pname,brand,description) values ('"+pid+"','"+pname+"','"+brand+"','"+description+"')");
    if(x!=0)
    {
    System.out.println("Data insert successfully");

    }
    else
    System.out.println("Problem in inserting data");
    }
    void delete() throws SQLException
    {
    System.out.println("Enter product for delete");
    pid=sc.nextInt();
    int x=st.executeUpdate("delete from product where pid='"+pid+"'");
    if(x!=0)
    {
    System.out.println("Data delete successfully");

    }
    else
    System.out.println("Problem in deleting data");

    }
    void update() throws SQLException
    {
    System.out.println("Enter product for update");
    pid=sc.nextInt();
    int x=st.executeUpdate("update product set pname='"+pname+"',brand='"+brand+"',description='"+description+"'where pid='"+pid+"'");
    if(x!=0)
    {
    System.out.println("Data update successfully");

    }
    else
    System.out.println("Problem in updating data");
    }
    void view() throws SQLException
    {
    ResultSet res=st.executeQuery("select * from product");
    while(res.next())
    {
    System.out.println(res.getInt(1)+"," + res.getString(2) + ","+ res.getString(3) + ","+ res.getString(4));
    }
    }
    void closeConnection() throws SQLException
    {
    conn.close();
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Product obj=new Product();
    obj.connection();
    //obj.update();
    //obj.accept(102,"full suit","arrowgun","fancy suit");
    obj.insert();

    //obj.delete();

    obj.view();
    obj.closeConnection();
    }

    }

    ReplyDelete
  2. AnonymousMay 15, 2022 at 7:15 PM

    employee record

    package SCS;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;

    public class InsertAllData {
    static Scanner sc = new Scanner(System.in);

    void insertEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("insert into emp(empid,empname,job,salary) values('"+empid+"','"+empname+"','"+job+"','"+salary+"')");

    if(x!=0)
    {
    System.out.println("Data Inserted Successfully");

    }
    else
    {
    System.out.println("Data Not Inserted Successfully");
    }
    }
    void updateEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("update emp set empname='"+empname+"' where empid='"+empid+"'");

    if(x!=0)
    {
    System.out.println("Data updated Successfully");

    }
    else
    {
    System.out.println("Data Not Updated Successfully");
    }
    }
    void showEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();
    ResultSet x = st.executeQuery("select * from emp");

    while(x.next())
    {
    System.out.println(x.getString(1) +" " + x.getString(2) +" " +x.getString(3) +" " +x.getString(4) );

    }


    }
    void deleteEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("delete from emp where empid='"+empid+"'");


    if(x!=0)
    {
    System.out.println("Data Deleted Successfully");

    }
    else
    {
    System.out.println("Data Not Deleted Successfully");
    }
    }

    public static void main(String args[]) throws ClassNotFoundException ,SQLException {


    InsertAllData obj = new InsertAllData();

    while(true)
    {
    System.out.println("Press 1 to Add Record \n Press 2 Update record \n Press 3 for Delete Record \n 4 for Display Record \n press other to exit ");

    char ch = sc.next().charAt(0);

    switch(ch)
    {
    case '1':
    obj.insertEmpData();
    break;

    case '2':
    obj.updateEmpData();
    break;

    case '3':
    obj.deleteEmpData();
    break;

    case '4':
    obj.showEmpData();
    break;

    default:
    System.exit(0);
    break;
    }
    }
    }
    }

    ReplyDelete
  3. AnonymousMay 15, 2022 at 7:15 PM

    employee record

    package SCS;

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;

    public class InsertAllData {
    static Scanner sc = new Scanner(System.in);

    void insertEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("insert into emp(empid,empname,job,salary) values('"+empid+"','"+empname+"','"+job+"','"+salary+"')");

    if(x!=0)
    {
    System.out.println("Data Inserted Successfully");

    }
    else
    {
    System.out.println("Data Not Inserted Successfully");
    }
    }
    void updateEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("update emp set empname='"+empname+"' where empid='"+empid+"'");

    if(x!=0)
    {
    System.out.println("Data updated Successfully");

    }
    else
    {
    System.out.println("Data Not Updated Successfully");
    }
    }
    void showEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();
    ResultSet x = st.executeQuery("select * from emp");

    while(x.next())
    {
    System.out.println(x.getString(1) +" " + x.getString(2) +" " +x.getString(3) +" " +x.getString(4) );

    }


    }
    void deleteEmpData() throws ClassNotFoundException, SQLException
    {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbms","root","");
    Statement st = conn.createStatement();

    int empid;
    String empname;
    String job;
    int salary;
    System.out.println("Enter empid");
    empid = sc.nextInt();
    System.out.println("Enter empname");
    empname = sc.next();
    System.out.println("Enter job");
    job = sc.next();
    System.out.println("Enter salary");
    salary = sc.nextInt();

    int x = st.executeUpdate("delete from emp where empid='"+empid+"'");


    if(x!=0)
    {
    System.out.println("Data Deleted Successfully");

    }
    else
    {
    System.out.println("Data Not Deleted Successfully");
    }
    }

    public static void main(String args[]) throws ClassNotFoundException ,SQLException {


    InsertAllData obj = new InsertAllData();

    while(true)
    {
    System.out.println("Press 1 to Add Record \n Press 2 Update record \n Press 3 for Delete Record \n 4 for Display Record \n press other to exit ");

    char ch = sc.next().charAt(0);

    switch(ch)
    {
    case '1':
    obj.insertEmpData();
    break;

    case '2':
    obj.updateEmpData();
    break;

    case '3':
    obj.deleteEmpData();
    break;

    case '4':
    obj.showEmpData();
    break;

    default:
    System.exit(0);
    break;
    }
    }
    }
    }

    ReplyDelete
Post a Comment
Share to other apps
Copy Post Link