Java, .NET, PHP, PYTHON, ANGULAR, ML, Data Science, Testing, CI Tutorials in Easy Languages.

"Best Software Training, Internship, Project Development center of Indore India, Helpline 780506-3968"

JDBC Database Connectivity using JSP and Servlet, Database connectivity on Java

JDBC:-  JDBC means Java database connectivity, it is used to connect from the front-end(application server) to the back-end(database server) in the case of Java web application.

The database provides a set of tables to store records and JDBC will work similarly to the bridge between the database table and application form.


Step for JDBC operation:-

1)  First Install the Database server in your machine using MYSQL, ORACLE, SYBASE, etc.


2)  Create Database Under MYSQL Server using PHPMYADMIN OF XAMPP  or MYSQL YOG

     create database databasename;

    if you are working with XAMPP Server then open PHPMYADMIN IDE 
     http://localhost/phpmyadmin/

3)  Create Table using PHP MYADMIN or XAMPP

      create table tablename(columnname datatype,columnname datatype)


4)    Create a JSP page and design JSP Webform according to the table column


5)    Create a Servlet class and write data insertion code


6)    Add Jar file of MYSQL Driver using Netbeans




JDBC provides predefined interface, class, and methods to perform database operation:-

1)  Class.forName():-

   here Class is the predefine java class that is used to integrate drivers according to database software.

 forName() is used to integrate database server using JAR Extension.

Class.forName("com.mysql.jdbc.Driver");  // for mysql database software


It throws ClassNotFoundException

2)  Connection:-

  It is the predefined interface of Java that is used to manage connection String using DriverManager class and getConnection().


Connection conn = DriverManage.getConnection("connection string");


3)  Statement:-


It is the predefined Interface of Java that is used to write and manage SQL queries. Statement reference will be initialized by connection objects

Statement st= conn.createStatement().



4)  executeUpdate() and executeQuery():-


executeUpdate() is used to perform data insertion, updation and deletion.

executeQuery() is used to perform data selection.


Syntax:-


int var = st.executeUpdate("Query of Insertion | updation | deletion");

ResultSet  var = st.executeQuery("Query of Select Operation")


What is ResultSet:-

It is a predefined class that is used to display the record of the select statements and generate a recordset.

It uses next() to move the record from the first row to the next row.



while(res.next())
{
       out.print(res.getInt(1) + " "+res.getString(2) + " ");


}




getInt(), getString(), getFloat(), getDate()  is the predefine method to display integer, string, float and datetime column data of ResultSet class.



ResultSet uses the column index from 1.

We can also write a column name on the resultset method.

res.getString("sname")   here sname is the columnname



JSP Code:-

<%--
    Document   : StudentInfo
    Created on : Mar 6, 2020, 6:03:18 PM
    Author     : Hp
--%>

<%@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" /> <br><br>
         
         
        </form>
 <a href="viewstudent.jsp">VIEW STUDENT RECORD HERE</a>
    </body>
</html>



Servlet Code:-

/*
 * 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.DriverManager;
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;
import java.sql.*;
/**
 *
 * @author Hp
 */
public class StudentSer extends HttpServlet {

    
    
    
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            PrintWriter out = response.getWriter();
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stuinfo","root","");
            Statement st = con.createStatement();
            int x = st.executeUpdate("insert into student(rno,name,branch,fees) values('"+request.getParameter("txtrno")+"','"+request.getParameter("txtname")+"','"+request.getParameter("txtbranch")+"','"+request.getParameter("txtfees")+"')");
            if(x!=0)
            {
              out.print("Data Inserted Successfully");  
            }
            
        } catch (Exception ex) {
          
        }
    }

   
}

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

ViewStudent.jsp:-



<%--
    Document   : viewstudent
    Created on : Mar 7, 2020, 6:12:09 PM
    Author     : Hp
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
          <a href="StudentInfo.jsp">BACK</a>
          <br>
          <hr>
          <h6>VIEW STUDENT RECORD HERE</h6>
          <hr>
        <%
          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/stuinfo","root","");
          Statement st = con.createStatement(); 
          ResultSet res = st.executeQuery("select * from student");
          while(res.next())
          {
              out.println(res.getInt(1) +" "+res.getString(2) +" "+res.getString(3) +" "+res.getString(4)+"<br>");
          }
       
         
         
         %>
   
    </body>
</html>





Tags

SHIVA CONCEPT SOLUTION

4 Comments

POST Answer of Questions and ASK to Doubt

  1. SSTSeptember 14, 2021 at 2:42 PM

    Show Data From Database Using Java Class :

    package JDBCExam;

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


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

    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/herapheri","root","");
    Statement st = conn.createStatement();
    ResultSet res = st.executeQuery("select * from dept order by deptid");
    while(res.next())
    {
    System.out.println(res.getString(1)+" "+res.getString(2));
    }
    res.close();

    }
    }

    ReplyDelete
  2. SSTSeptember 14, 2021 at 2:45 PM

    Insert Data in Database and Show Final Data :

    package JDBCExam;

    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 insertdb {
    public static void main(String[] args) throws ClassNotFoundException, SQLException
    {
    int id;
    String dname;
    Scanner sc = new Scanner(System.in);
    System.out.println("Enter ID of Dept");
    id=sc.nextInt();
    System.out.println("Enter Name of Dept");
    dname = sc.next();
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/herapheri","root","");
    Statement st = conn.createStatement();
    int x = st.executeUpdate("insert into dept(deptid,deptname) values('"+id+"','"+dname+"')");
    if(x!=0)
    {
    System.out.println("Data Inserted Successfully");
    }
    else
    {
    System.out.println("Data not Inserted Successfully");
    }

    ResultSet res = st.executeQuery("select * from dept order by deptid");
    while(res.next())
    {
    System.out.println(res.getString(1)+" "+res.getString(2));
    }
    res.close();

    }
    }

    ReplyDelete
  3. SSTSeptember 14, 2021 at 2:48 PM

    Delete Data From DataBase by User, Show Data Before Delete And After Delete:


    package JDBCExam;

    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 DeleteData {

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

    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/herapheri","root","");
    Statement st = conn.createStatement();
    ResultSet res = st.executeQuery("select * from dept order by deptid");
    while(res.next())
    {
    System.out.println(res.getString(1)+" "+res.getString(2));
    }
    res.close();
    int id;
    Scanner sc=new Scanner(System.in);
    System.out.println("Delete Department By Id");
    id=sc.nextInt();
    int x = st.executeUpdate("delete from dept where deptid='"+id+"'");
    if(x!=0)
    {
    System.out.println("Delete Succesfully");

    }
    else
    {
    System.out.println("UnSuccessfull");
    }
    ResultSet res1 = st.executeQuery("select * from dept order by deptid");
    while(res1.next())
    {
    System.out.println(res1.getString(1)+" "+res1.getString(2));
    }
    res1.close();
    }
    }

    ReplyDelete
  4. SSTSeptember 14, 2021 at 2:49 PM

    Update database:

    package JDBCExam;

    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 UpdateData {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {


    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/herapheri","root","");
    Statement st = conn.createStatement();
    ResultSet res = st.executeQuery("select * from dept order by deptid");
    while(res.next())
    {
    System.out.println(res.getString(1)+" "+res.getString(2));
    }
    res.close();
    String dname;
    int id;
    Scanner sc =new Scanner(System.in);
    System.out.println("Update by id");
    id=sc.nextInt();
    System.out.println("Insert new Name For Update");
    dname=sc.next();
    int x = st.executeUpdate("update dept set deptname='"+dname+"' where deptid='"+id+"'");
    if(x!=0)
    {
    System.out.println("Update Succesfully");
    }
    else {
    System.out.println("UnSuccesfull");
    }
    ResultSet res1 = st.executeQuery("select * from dept order by deptid");
    while(res1.next())
    {
    System.out.println(res1.getString(1)+" "+res1.getString(2));
    }
    res1.close();
    }
    }

    ReplyDelete

Post a Comment

POST Answer of Questions and ASK to Doubt

Previous Post Next Post