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

4

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.
1)  Class.forName("drivername")  // Manage Drive
   
    Class.formName("com.mysql.jdbc.Driver");  // MYSQL 
    Class.forName ("oracle.jdbc.driver.OracleDriver"); //Oracle
2)  Manage Connection String
    It establish connection from application server to database server, Java provide DriverManage class and getConnection that will return Connection object.
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename","username","password");
3)  Manage Statement to perform database operation.
JDBC provide createStatement(), preparedStatement() and callableStatement() to perform database operation(Insert, Update, Delete, Select)
   Statement st = conn.createStatement();       // query as a statement
   PrepareStatement ref = conn.preparedStatement()  // for parametrised query
   CallableStatement ct = conn.callableStatement();  //for stored procedure
4)  Use predfine method executeQuery() for data selection and executeUpdate() for data insertion,updation and deletion
executeQuery() return ResultSet type Object.
ResultSet res =  st.executeQuery();
while(res.next())
{
      Object o = res.next();
      }
executeUpdate() return number of affected rows after insertion,updation and deletion.
int x = st.executeUpdate()
5) close connection to dispose of connection.
Before database connectivity
1)  Queries for Create Table?
2)  What is Primary Key & Foreign Key?
3)  Queries for Insert, Update, Delete?
4) Queries for Data Selection?
5)  IN, NOT In, Between , Not Between, Like Not Like
6)  Join (Inner Join, Outer Join)
7)  Subquries
8)  Group by, Order by
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>


Post a Comment

4Comments

POST Answer of Questions and ASK to Doubt

  1. 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. 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. 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. 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