Skip to main content

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


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>


Labels:

Comments

  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

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

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

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