JDBC Database Connectivity using JSP and Servlet, Database connectivity on Java
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:-
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>
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
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
If you have any doubt in programming or join online classes then you can contact us by comment .