JDBC Introduction,CRUP in JSP,Servlet,Insert,Update,Delete,Select record using JDBC,JDBC CRUD
JDBC Introduction:-
It is intermediate technology of Java which is used to perform database operation using drivers and providers, JDBC contains a set of classes and methods to perform database operation for the different database servers.
JDBC means Java Database connectivity, It is used to create dynamic applications because JDBC has database-related classes and methods to perform database operation.
It works using four different types:-
1) type1:- JDBC-ODBC Driver:- It is used for desktop application because ODBC provides local connection using DSN (Data source name)
2) type2:- JDBC-Network Specific Driver - It is used to connect the database from a different network, It uses C and C++ Programming approach to connect the server machine.
3) type3:- JDBC-Vendor Specific Driver, It uses a third-party library to communicate application data to the database server, It provides a different approach for the different database server.
4) type4:- Java thin driver or Client-Server Specific driver:- it is a modern type of JDBC which is used to perform database operation using a client-server architecture.
It is common for all database servers only drivers will be different. it is a light-weight approach to communicate data from application to database server hence it is also called Java Thin Driver.
Step for JDBC using type 4:
1) Create a Database and Table using any database, We are using MYSQL Database Server.
for MYSQL we will use PHPMYADMIN Software of XAMPP Server or MYSQL YOG or MYSQL Console
1.1 ) create database database name
1.2) Create table student(rno int primary key,sname varchar(100),branch char(5),fees int)
2) Now Create Form using JSP which contain text-field according to table column
3) Add JDBC Driver according to Database Server for MYSQL Add MySQL driver
4) Add Servlet to write code for data insertion
Code of JSP:-
<%@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" />
</form>
<%
if(request.getParameter("q")!=null)
{
out.print(request.getParameter("q"));
}
%>
</body>
</html>
<%@page import="java.sql.*" %>
<%@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>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{
out.print(res.getString(1) +" "+res.getString(2) +" "+res.getString(3)+" "+res.getString(4)+"<br>");
}
%>
</body>
</html>
<%@page import="java.sql.*" %>
<%@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>
<table border="1">
<tr>
<th>RNO</th>
<th>SNAME</th>
<th>BRANCH</th>
<th>FEES</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{ %>
<tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td></tr>
<% }
%>
</table>
</body>
</html>
It is intermediate technology of Java which is used to perform database operation using drivers and providers, JDBC contains a set of classes and methods to perform database operation for the different database servers.
JDBC means Java Database connectivity, It is used to create dynamic applications because JDBC has database-related classes and methods to perform database operation.
It works using four different types:-
1) type1:- JDBC-ODBC Driver:- It is used for desktop application because ODBC provides local connection using DSN (Data source name)
2) type2:- JDBC-Network Specific Driver - It is used to connect the database from a different network, It uses C and C++ Programming approach to connect the server machine.
3) type3:- JDBC-Vendor Specific Driver, It uses a third-party library to communicate application data to the database server, It provides a different approach for the different database server.
4) type4:- Java thin driver or Client-Server Specific driver:- it is a modern type of JDBC which is used to perform database operation using a client-server architecture.
It is common for all database servers only drivers will be different. it is a light-weight approach to communicate data from application to database server hence it is also called Java Thin Driver.
Step for JDBC using type 4:
1) Create a Database and Table using any database, We are using MYSQL Database Server.
for MYSQL we will use PHPMYADMIN Software of XAMPP Server or MYSQL YOG or MYSQL Console
1.1 ) create database database name
1.2) Create table student(rno int primary key,sname varchar(100),branch char(5),fees int)
2) Now Create Form using JSP which contain text-field according to table column
3) Add JDBC Driver according to Database Server for MYSQL Add MySQL driver
4) Add Servlet to write code for data insertion
Code of JSP:-
<%@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" />
</form>
<%
if(request.getParameter("q")!=null)
{
out.print(request.getParameter("q"));
}
%>
</body>
</html>
Code for Servlet
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
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;
public class StudentSer extends HttpServlet {
public void doPost(HttpServletRequest request,HttpServletResponse response)
{
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
int x = st.executeUpdate("insert into student(rno,sname,branch,fees) values('"+request.getParameter("txtrno")+"','"+request.getParameter("txtname")+"','"+request.getParameter("txtbranch")+"','"+request.getParameter("txtfees")+"')");
if(x!=0)
response.sendRedirect("StudentForm.jsp?q=insert success");
else
response.sendRedirect("StudentForm.jsp?q=insert fail");
} catch (Exception ex) {
}
}
}
Select Record From Database
<%@page import="java.sql.*" %>
<%@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>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{
out.print(res.getString(1) +" "+res.getString(2) +" "+res.getString(3)+" "+res.getString(4)+"<br>");
}
%>
</body>
</html>
Select Record From Database using Table Format:-
<%@page import="java.sql.*" %>
<%@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>
<table border="1">
<tr>
<th>RNO</th>
<th>SNAME</th>
<th>BRANCH</th>
<th>FEES</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{ %>
<tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td></tr>
<% }
%>
</table>
</body>
</html>
Update Record then we customize show record jsp page:-
<%@page import="java.sql.*" %>
<%@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>
<table border="1">
<tr>
<th>RNO</th>
<th>SNAME</th>
<th>BRANCH</th>
<th>FEES</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{ %>
<tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td><td><a href="EditRecord.jsp?q=<%= res.getString(1) %>">Edit</a></td><td>Delete</td></tr>
<% }
%>
</table>
</body>
</html>
<%@page import="java.sql.*" %>
<%@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>
<table border="1">
<tr>
<th>RNO</th>
<th>SNAME</th>
<th>BRANCH</th>
<th>FEES</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
ResultSet res = st.executeQuery("select * from student");
while(res.next())
{ %>
<tr><td> <%= res.getString(1) %></td> <td> <%= res.getString(2) %></td><td> <%= res.getString(3) %></td><td> <%= res.getString(4) %></td><td><a href="EditRecord.jsp?q=<%= res.getString(1) %>">Edit</a></td><td>Delete</td></tr>
<% }
%>
</table>
</body>
</html>
Show Update Servlet
/*
* 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.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
*
* @author Hp
*/
public class UpdateSer extends HttpServlet {
public void doPost(HttpServletRequest request,HttpServletResponse response)
{
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java56","root","");
Statement st = con.createStatement();
int x = st.executeUpdate("update student set sname='"+request.getParameter("txtsname")+"',branch='"+request.getParameter("txtbranch")+"',fees='"+request.getParameter("txtfees")+"' where rno='"+request.getParameter("txtrno")+"'");
if(x!=0)
response.sendRedirect("ViewStudent.jsp");
else
response.sendRedirect("ViewStudent.jsp");
} catch (Exception ex) {
}
}
}
// create product table with pid,pname,brand,description using normal Statement
ReplyDeletepackage jdbc;
import java.sql.*;
import java.util.Scanner;
public class Product {
int pid;
String pname,brand,description;
Connection conn;
Statement st;
Scanner sc=new Scanner(System.in);
void accept(int pid,String pname,String brand,String description)
{
this.pid=pid;
this.pname=pname;
this.brand=brand;
this.description=description;
}
void connection() throws SQLException, ClassNotFoundException
{
Class.forName("com.mysql.cj.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcdemo","root","");
st=conn.createStatement();
}
void insert() throws SQLException
{
int x=st.executeUpdate("insert into product (pid,pname,brand,description) values ('"+pid+"','"+pname+"','"+brand+"','"+description+"')");
if(x!=0)
{
System.out.println("Data insert successfully");
}
else
System.out.println("Problem in inserting data");
}
void delete() throws SQLException
{
System.out.println("Enter product for delete");
pid=sc.nextInt();
int x=st.executeUpdate("delete from product where pid='"+pid+"'");
if(x!=0)
{
System.out.println("Data delete successfully");
}
else
System.out.println("Problem in deleting data");
}
void update() throws SQLException
{
System.out.println("Enter product for update");
pid=sc.nextInt();
int x=st.executeUpdate("update product set pname='"+pname+"',brand='"+brand+"',description='"+description+"'where pid='"+pid+"'");
if(x!=0)
{
System.out.println("Data update successfully");
}
else
System.out.println("Problem in updating data");
}
void view() throws SQLException
{
ResultSet res=st.executeQuery("select * from product");
while(res.next())
{
System.out.println(res.getInt(1)+"," + res.getString(2) + ","+ res.getString(3) + ","+ res.getString(4));
}
}
void closeConnection() throws SQLException
{
conn.close();
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Product obj=new Product();
obj.connection();
//obj.update();
//obj.accept(102,"full suit","arrowgun","fancy suit");
obj.insert();
//obj.delete();
obj.view();
obj.closeConnection();
}
}
Post a Comment
POST Answer of Questions and ASK to Doubt