Skip to main content

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


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

}

Labels:

Comments

  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

POST Answer of Questions and ASK to Doubt

Popular posts from this blog

DSA in C# | Data Structure and Algorithm using C#

  DSA in C# |  Data Structure and Algorithm using C#: Lecture 1: Introduction to Data Structures and Algorithms (1 Hour) 1.1 What are Data Structures? Data Structures are ways to store and organize data so it can be used efficiently. Think of data structures as containers that hold data in a specific format. Types of Data Structures: Primitive Data Structures : These are basic structures built into the language. Example: int , float , char , bool in C#. Example : csharp int age = 25;  // 'age' stores an integer value. bool isStudent = true;  // 'isStudent' stores a boolean value. Non-Primitive Data Structures : These are more complex and are built using primitive types. They are divided into: Linear : Arrays, Lists, Queues, Stacks (data is arranged in a sequence). Non-Linear : Trees, Graphs (data is connected in more complex ways). Example : // Array is a simple linear data structure int[] number...
Post a Comment
Read more

Conditional Statement in Python

It is used to solve condition-based problems using if and else block-level statement. it provides a separate block for  if statement, else statement, and elif statement . elif statement is similar to elseif statement of C, C++ and Java languages. Type of Conditional Statement:- 1) Simple if:- We can write a single if statement also in python, it will execute when the condition is true. for example, One real-world problem is here?? we want to display the salary of employees when the salary will be above 10000 otherwise not displayed. Syntax:- if(condition):    statements The solution to the above problem sal = int(input("Enter salary")) if sal>10000:     print("Salary is "+str(sal)) Q)  WAP to increase the salary of employees from 500 if entered salary will be less than 10000 otherwise the same salaries will be displayed. Solution:- x = int(input("enter salary")) if x<10000:     x=x+500 print(x)   Q) WAP to display th...
217 comments
Read more

JSP Page design using Internal CSS

  JSP is used to design the user interface of an application, CSS is used to provide set of properties. Jsp provide proper page template to create user interface of dynamic web application. We can write CSS using three different ways 1)  inline CSS:-   we will write CSS tag under HTML elements <div style="width:200px; height:100px; background-color:green;"></div> 2)  Internal CSS:-  we will write CSS under <style> block. <style type="text/css"> #abc { width:200px;  height:100px;  background-color:green; } </style> <div id="abc"></div> 3) External CSS:-  we will write CSS to create a separate file and link it into HTML Web pages. create a separate file and named it style.css #abc { width:200px;  height:100px;  background-color:green; } go into Jsp page and link style.css <link href="style.css"  type="text/css" rel="stylesheet"   /> <div id="abc"> </div> Exam...
Post a Comment
Read more