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 web 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 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 server only driver 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 databasename
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 web 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 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 server only driver 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 databasename
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) {
}
}
}
Post a Comment
If you have any doubt in programming or join online classes then you can contact us by comment .