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

Uncontrolled form input in React-JS

  Uncontrolled form input in React-JS? If we want to take input from users without any separate event handling then we can uncontrolled the data binding technique. The uncontrolled input is similar to the traditional HTML form inputs. The DOM itself handles the form data. Here, the HTML elements maintain their own state that will be updated when the input value changes. To write an uncontrolled component, you need to use a ref to get form values from the DOM. In other words, there is no need to write an event handler for every state update. You can use a ref to access the input field value of the form from the DOM. Example of Uncontrolled Form Input:- import React from "react" ; export class Info extends React . Component {     constructor ( props )     {         super ( props );         this . fun = this . fun . bind ( this ); //event method binding         this . input = React . createRef ();...
Post a Comment
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

JDBC using JSP and Servlet

JDBC means Java Database Connectivity ,It is intermediates from Application to database. JDBC has different type of divers and provides to communicate from database server. JDBC contain four different type of approach to communicate with Database Type 1:- JDBC-ODBC Driver Type2:- JDBC Vendor specific Type3 :- JDBC Network Specific Type4:- JDBC Client-Server based Driver  or JAVA thin driver:- Mostly we prefer Type 4 type of Driver to communicate with database server. Step for JDBC:- 1  Create Database using MYSQL ,ORACLE ,MS-SQL or any other database 2   Create Table using database server 3   Create Form according to database table 4  Submit Form and get form data into servlet 5  write JDBC Code:-     5.1)   import package    import java.sql.*     5.2)  Add JDBC Driver according to database ide tools     5.3)  call driver in program         ...
4 comments
Read more