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 Driver

   

    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

Class.forName ("oracle.jdbc.driver.OracleDriver");

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>